|
Oracle10g Cost Base Optimizer -DBMS_STATS-
|
Hi, everyone. We would like to continue the varification of system statistics of Oracle 9i and Oracle 10g. It is recommended that you read this issue while referring to the previous issue of OraOraOracle.
Experiment
Let's obtain the system statistics from Oracle 9i.
SQL> EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode => 'INTERVAL',
interval => 120, statown => 'SYSTEM');
PL/SQL procedure successfully completed.
|
Let's confirm whether the value is in AUX_STATS$ view.
SQL> select * from aux_stats$;
SNAME PNAME PVAL1 PVAL2
--------------- ---------- ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 12-02-2004 18:37
SYSSTATS_INFO DSTOP 12-02-2004 20:37
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN SREADTIM 9.744
SYSSTATS_MAIN MREADTIM -1
SYSSTATS_MAIN CPUSPEED 572
SYSSTATS_MAIN MBRC -1
SYSSTATS_MAIN MAXTHR 730112
SYSSTATS_MAIN SLAVETHR -1
|
There are 10 records in this view. Let's execute the same SQL statement to confirm the cost.
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=49 Bytes=1274)
1 0 TABLE ACCESS (FULL) OF 'STATS_TEST' (Cost=8 Card=49 Bytes=1274)
|
Cost has been changed from 7 to 8. Let's compare the trace file before and after the execution of DBMS_STATS.
IO-RSC :7?E
IO-RSP :7?E
CPU-RSC :0?E163416
CPU-RSP :0?E408466
|
The values have been changed. The CPU load affects the calculation of cost. The result is more correct although higher value is not preferred. Moreover, letfs discuss about AUX_STATS$. In SYSSTATS_INFO, the result after executing DBMS_STATS will be displayed; in SYSSTATS_MAIN, each measurement is included.
For example, CPUSPEED is 572, and this is the speed measured by Oracle with appropriate actions. In other words, when other applications occupy much space of CPU, the value should decrease. We will check this later. SREADTIM is the average of Block Read Time, and MREADTIM is the average of Multi Block Read Time. The unit of both of them is milli-second. How about the -1? Does it mean that value cannot be measured? We would like to check this, too.
Next week we will start to check the parts changed since Oracle 10g. By installing Oracle9iR2 and Oracle10g in the same machine, the value should not be so different. That is all for today.
Masaru Hayashi
|
|
