|
Oracle10g Cost Base Optimizer -DBMS_STATS-
|
We would like to continue the experiment to see how DBMS_STATS affects CBO.
Environment
Redhat Linux Advanced Server 2.1
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
|
Experiment
Last week we checked the situation in Oracle9i, and this time we would like to see what will happen in Oracle 10g. In Oracle 10g, what will change due to DBMS_STATS.GATHER_SYSTEM_STATS? Let's use the same method as we used to check in Oracle 9i. There are three phases:
1. The AUX_STATS$ view when DBMS_STATS is not executed
2. The AUX_STATS$ view when DBMS_STATS is executed under low system load
3. The AUX_STATS$ view when DBMS_STATS is executed under high system load
.
SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.
|
Execute SELECT statement and confirm the created trace file.
IO-RSC : 15
IO-RSP : 15
CPU-RSC : 2427306
CPU-RSP : 2427306
|
Although we have not executed DBMS_STATS, the values already exist. How come that happens? Let's confirm AUX_STATS$ view.
SQL> select * from aux_stats$;
SNAME PNAME PVAL1 PVAL2
-------------------- ---------- ---------- ---------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 02-05-2004 13:38
SYSSTATS_INFO DSTOP 02-05-2004 13:38
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 400.897016
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
|
By default, there are 13 records. It seems that, since Oracle 10g, the data obtained by GATHER_SYSTEM_STATS by default will appear. The purpose of these values of SYSTEM_STATS is to help CBO calculate the details of cost. Then, what will change after executing DBMS_STATS.GATHER_SYSTEM_STATS?
SQL> EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode =>
'INTERVAL',interval => 120, statown => 'SYSTEM');
PL/SQL procedure successfully completed.
|
By the way, if DBMS_STATS is not completed yet, the result will be as follows.
SQL> select * from aux_stats$;
SNAME PNAME PVAL1 PVAL2
--------------- ---------- ---------- --------------------
SYSSTATS_INFO STATUS AUTOGATHERING
SYSSTATS_INFO DSTART 12-14-2004 19:10
SYSSTATS_INFO DSTOP 12-14-2004 21:10
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 569.718
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
SYSSTATS_TEMP SBLKRDS 4374
SYSSTATS_TEMP SBLKRDTIM 45970
SYSSTATS_TEMP MBLKRDS 449
SYSSTATS_TEMP MBLKRDTIM 7600
SYSSTATS_TEMP CPUCYCLES 146412
SYSSTATS_TEMP CPUTIM 257011
SYSSTATS_TEMP JOB 41
SYSSTATS_TEMP CACHE_JOB 42
SYSSTATS_TEMP MBRTOTAL 7694
22 rows selected.
|
In Oracle 9i, the statistics are managed as SYSSTATS_TEMP until the time defined by INTERVAL comes. It prevents the sudden change of SYSSTATS_MAIN which causes the failure of obtaining statistics. By executing SELECT after the time displayed in DSTOP (21:10 in this case), the result will be as follows.
SNAME PNAME PVAL1 PVAL2
--------------- -------------------- ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 12-14-2004 19:10
SYSSTATS_INFO DSTOP 12-14-2004 21:10
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 569.718
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 6.699
SYSSTATS_MAIN MREADTIM .385
SYSSTATS_MAIN CPUSPEED 564
SYSSTATS_MAIN MBRC 9
SYSSTATS_MAIN MAXTHR 27648
SYSSTATS_MAIN SLAVETHR
|
STATUS is also changed from AUTOGATHERING to COMPLETED. Letfs confirm the cost of the same SQL.
IO-RSC: 17
IO-RSP: 17
CPU-RSC: 2427306
CPU-RSP: 2427306
|
I/O cost has changed, but CPU cost has not. In other words, the value by default does not vary from the obtained value so much. Regarding the I/O cost, by using DBMS_STATS.GATHER_SYSTEM_STATS, I/O cost such as SREADTIM and MREADTIM is taken into consideration. The value changed from 15 to 17.
In Oracle 10g, CBO can function more correctly with DBMS_STATS.GATHER_SYSTEM_STATS.
Conclusion
# In Oracle 10g, unlike in Oracle9i, the default value is used, so CPU cost will never be 0. In other words, there will always be something for calculating the cost. -> CBO coalition
# The statistics will be collected by SYSSTATS_TEMP until the time defined by INTERVAL is done.
# In Oracle 10g, the precision of cost calculation can still be improved by DBMS_STATS.GATHER_SYSTEM_STATS.
Next time, we will talk about the application of the statistics obtained by DBMS_STATS and the new feature of DBMS_STATS of Oracle 10g.
Masaru Hayashi
|
|
