Google+ Followers

Pages

Thursday, July 14, 2011

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

No comments:

Post a Comment