|
Category :
Database
Resources -> Database Administration
DB Version
:
Oracle 9i
OS Details :
Sun Solaris9
System
statistics allow the optimizer to consider a system’s I/O and CPU
performance and utilization. For each candidate plan, the CBO
optimizer computes estimates for I/O and CPU costs. The decision by
oracle to include CPU costs with its computation of execution costs is
controlled internally by the hidden parameter _optimizer_cost_model.
This hidden parameter can have a value of IO or CPU or CHOOSE. The
default value for this parameter is CHOOSE which means that oracle
will include system statistics if they have been collected within the
database, as part of its cost estimation process for query plans. If
system statistics are not available within the database then oracle
will not include CPU costs. System statistics required for CPU costing
can be gathered using the dbms_stats.gather_system_stats procedure.
The following information is gathered by oracle as part of system
statistics:
- single block readtime in ms
- multiblock readtime in ms
- cpu speed in mhz
- average multiblock_read_count in number of blocks
Listed below are some of the methods that can be used to collect and
import system statistics.
(1) Pre-requisites:
(i) Create a table to hold the
system statistics:
SQL> exec
dbms_stats.create_stat_table(ownname=>'<table owner>', stattab=>
'<Name of the table>', tbsspace=> '<Tablespace Name>')
(ii) Make sure the job queue
processes are running. If not, activate the job queue processes:
SQL> ALTER SYSTEM SET
JOB_QUEUE_PROCESSES = 1;
(2) Start the statistics
gathering process:
Use
dbms_stats.gather_system_stats procedure to start gathering system
statistics:
SQL> exec
dbms_stats.gather_system_stats(gathering_code=>'INTERVAL',
interval=>600, stattab =>'sys_stats_tab', statid=> 'prod_day')
Gathering mode => Could be either
of INTERVAL, START or STOP. Interval allows us to specify the amount
of elapsed time in minutes after which the statistics table will be
updated with system stats. START will initiate the statistics
gathering process immediately and STOP will update the statistics
table with system stats gather since the last START.
INTERVAL => Specifies the amount
of time in minutes for gathering system statistics in INTERVAL mode.
STATTAB => Identifies the user
created statistics table that will be updated with the current system
statistics.
STATID => Identifier user to
associate a particular system stats with the table specified through
stattab.
(3) Check and verify if the
collected system stats are valid:
SQL> column statid format a7
SQL> column c1 format a13
SQL> column c2 format a16
SQL> column c3 format a16
SQL> select STATID, C1, C2, C3 from sys_stats_tab;
The value of C1 for the above
query can either be AUTOGATHERING or COMPLETED or BADSTATS.
An alternative way of verifying
the collected system stats would be to use this method:
SET SERVEROUTPUT ON
DECLARE
STATUS VARCHAR2(20);
DSTART DATE;
DSTOP DATE;
PVALUE NUMBER;
PNAME VARCHAR2(30);
BEGIN
PNAME := 'cpuspeed';
DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue,
stattab => '
sys_stats_tab', statid => 'prod_day', statown => 'SYSTEM');
DBMS_OUTPUT.PUT_LINE('status : '||status);
DBMS_OUTPUT.PUT_LINE('cpu in mhz : '||pvalue);
PNAME := 'sreadtim';
DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue,
stattab => '
sys_stats_tab', statid => 'prod_day', statown => 'SYSTEM');
DBMS_OUTPUT.PUT_LINE('single block readtime in ms : '||pvalue);
PNAME := 'mreadtim';
DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue,
stattab => '
sys_stats_tab', statid => 'prod_day', statown => 'SYSTEM');
DBMS_OUTPUT.PUT_LINE('multiblock readtime in ms : '||pvalue);
PNAME := 'mbrc';
DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue,
stattab => '
sys_stats_tab', statid => 'prod_day', statown => 'SYSTEM');
DBMS_OUTPUT.PUT_LINE('average multiblock readcount: '||pvalue);
END;
/
|
