Generate Workload CPU statistics using dbms_stats.gather_system_stats

In Oracle 9i and up, you can generate CPU cost information during a workload with a procedure of the dbms_stats package.



The dbms_stats.gather_system_stats packaged procedure can be used during a certain interval to measure the actual I/O and CPU usage during a workload, typically a days work.

SQL> exec dbms_stats.gather_system_stats('START')

PL/SQL procedure successfully completed.

SQL> -- days processing
SQL> exec dbms_stats.gather_system_stats('STOP')

PL/SQL procedure successfully completed.

Now, when you gathered workload CPU statistics, one can query the sys.aux_stats$ data dictionary table to see the actual values that will be used when generating your Sql plan:

select sname, pname, pval1
from sys.aux_stats$;

SNAME       	PNAME      PVAL1
-------------	---------  -------
SYSSTATS_INFO   STATUS
SYSSTATS_INFO   DSTART
SYSSTATS_INFO   DSTOP
SYSSTATS_INFO   FLAGS	    1
SYSSTATS_MAIN   CPUSPEEDNW  502.005
SYSSTATS_MAIN   IOSEEKTIM   10
SYSSTATS_MAIN   IOTFRSPEED  4096
SYSSTATS_MAIN   SREADTIM    7.618
SYSSTATS_MAIN   MREADTIM    14.348
SYSSTATS_MAIN   CPUSPEED    507
SYSSTATS_MAIN   MBRC 	    6
SYSSTATS_MAIN 	MAXTHR      32768
SYSSTATS_MAIN 	SLAVETHR      

13 rows selected.

CPUSPEEDNW, IOSEEKTIM, and IOTFRSPEED are noworkload statistics;

SREADTIM, MREADTIM, CPUSPEED, MBRC, MAXTHR, and SLAVETHR represent workload statistics.



When you have both workload and noworkload statistics, the optimizer will use workload statistics.

• SREADTIM - single block read time (msec): the average time Oracle takes to read a single block


• MREADTIM - multiblock read time (msec): the average time taken to read sequentially


• MBRC - multiblock read count: the average amount of blocks read during multiblock sequential reads. This value is used instead of the db_ multiblock_read_count parameter during query optimization to compute costs for table and fast full index scans


• MAXTHR - maximum I/O system throughput: is captured only if the database runs parallel queries


• SLAVETHR - maximum slave I/O throughput: is captured only if the database runs parallel queries