Gather system (CPU) stats using DBMS_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.

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