|
Oracle10g Cost Base Optimizer -DBMS_STATS-
|
We would like to continue the experiment of CBO. We have checked Monitoring attribute, dynamic sampling and Oracle Scheduler, and we have confirmed that using these features will cause CBO to be used. This time we would like to examine the statistics which affect the execution plan.
Regarding the statistics, we have obtained them by Analyze command or DBMS_STATS package. However, in the book entitled gPerformance Tuning Guide,h it is mentioned that:
Please do not use Analyze, Compute or Estimate statement to obtain optimizer statistics. Due to their lower compatibility, they might be abolished in the future releases.
In other words, Analyze command will not be available in the future. DBMS_STATS can also obtain system statistics. By obtaining CPU or I/O statistics, the cost including CPU can be calculated. In addition, we would also like to check its difference from Oracle 9i.
[Testing Environment]
Redhat Linux Advanced Server 2.1
Oracle Database 9i Enterprise Edition Release 9.2.0.5.0
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
|
First of all, letfs confirm the view in Oracle 9i Release 2 which shows that system statistics are obtained.
Experiment
SQL> select * from aux_stats$;
No record is selected.
|
By default nothing exists. In order to set the condition similar to Oracle 10g, we set optimizer_goal as ALL_ROWS. Moreover, since we want to know the details of execution plan, we will execute SQL upon trace mechanism,
SQL> alter session set optimizer_goal=ALL_ROWS;
Session altered.
SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.
SQL> select * from stats_test where id = '25';
This is the execution plan of the SQL statement.
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=49 Bytes=1274)
1 0 TABLE ACCESS (FULL) OF 'STATS_TEST' (Cost=7 Card=49 Bytes=1274)
|
Before using DBMS_STATS, the cost is 7. In order to know the breakdown of this cost (7), we obtain the following result by confirming the trace.
IO-RSC :7
IO-RSP :7
CPU-RSC :0
CPU-RSP :0
Cost of plan :7
|
I/O cost (7) is calculated as the total cost. CPU-RSC and CPU-RSP are both 0. In other words, CPU cost is not calculated, i.e. only I/O cost is assessed. Even though Oracle can include CPU cost to obtain more correct cost, the feature is actually not used. That is all for today.
Masaru Hayashi
|
|
