For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
How can we determine correct settings DBIO_EXPECTED?
measure the average rad time of a single database block read for your hardware.
set the DBIO_EXPECTED value.
eg. if the measured value is 8000 microseconds you should execute
SQL> conn as sys
SQL> exec DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM','DBIO_EXPECTED',8000);
SQL>select parameter_value, is_default from dba_advisor_def_parameters where advisor_name='ADDM' and parameter_name='DBIO_EXPECTED';
can tell u the current value of the parameter.
As you set, the value you will get 8000 microseconds.
SQL> SELECT * FROM DBA_ADVISOR_DEF_PARAMETERS WHERE PARAMETER_NAME='DBIO_EXPECTED';
vansi wrote: How can we determine correct settings DBIO_EXPECTED?
vansi wrote:
1. It was collected/measure based on dbms_stats.gather_system_stats
2. Now you have workload and no workload stats (default method is gathering_mode VARCHAR2 DEFAULT 'NOWORKLOAD',)
Remember
NOWORKLOAD: Will capture characteristics of the I/O system. Gathering may take a few minutes and depends on the size of the database. During this period Oracle will estimate the average read seek time and transfer speed for the I/O system. This mode is suitable for the all workloads. Oracle recommends to run GATHER_SYSTEM_STATS ('noworkload') after creation of the database and tablespaces.
GATHER_SYSTEM_STATS
interval
Time, in minutes, to gather statistics. This parameter applies only when gathering_mode='INTERVAL'
gathering_mode='INTERVAL'
3. Based on above you able to judge the DBIO_EXPECTED -- that is sreadtiem in microseconds
Update :
Workload Stats vs NoWorkload stats ... good info further
- Pavan Kumar N