|
Oracle10g Cost Base Optimizer -DBMS_STATS-
|
Question from the reader about the previous Ora Ora Oracle article
Total CPU sort cost: 10331355 when executing "Sort Merge Join."
Total CPU sort cost: 3980211 when executing "Order by."
How is the "Total CPU sort cost" calculated in each case?
Actually, the values were not computed but were just the numbers displayed in the trace file. However, this question reminds me that we have not verified the calculation of cost. Letfs take a look.
Environment
Redhat Linux Advanced Server 2.1
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
|
Experiment
Since Oracle9i the cost has been calculated base on Time. Such calculation shows that not the estimation of I/O but the time spent on I/O is considered to be the cost. Since Oracle10g, the statistics of the system can be obtained by default to increase the accuracy. When you do not want to obtain the statistics, please set STATISTICS_LEVEL=BASIC. However, how are the statistics obtained by DBMS_STATS.GATHER_SYSTEM_STATS used? Let's just check AUX_STATS$ for now.
SQL> select * from aux_stats$;
SNAME PNAME PVAL1 PVAL2
--------------- ---------- ---------- ----------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 12-27-2004 14:38
SYSSTATS_INFO DSTOP 12-27-2004 14:38
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 566.04
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 14.603
SYSSTATS_MAIN MREADTIM 92.778
SYSSTATS_MAIN CPUSPEED 230
SYSSTATS_MAIN MBRC 6
SYSSTATS_MAIN MAXTHR 131072
SYSSTATS_MAIN SLAVETHR
|
In order to take a look how Oracle calculates the cost, we will simply execute FullScan to the table. First we want to execute Full Scan to the table STATS_TEST (10000 records), which we have used in the previous experiments many times.
When we check High Water Mark of the table STATS_TEST by dbms_space.unused_space, it seems to obtain 1M byte of Uniform Size, using 64 blocks. We then apply the average number of actually obtained blocks at Multi Block Read (MBRC), which is the average number of blocks read in one multi block read operation. According to AUX_STATS$, it is 6.
Therefore, we come up with the equation: # of block to read / MBRC = required I/O of Multi Block Read (estimation). This means that 64 Block/ 6 MBRC = 10 I/O of Multi Block Read (estimation). The details should be 4 Single Block Reads and 10 Multi Block Reads. By considering the remaining 4 blocks as Single Block Reads, the cost seems to be calculated as follows:
Single Block Read x SREADTIM=58.412 ms
Multi Block Read x MREADTIM=927.78 ms
986.192 ms / SREADTIM = 67.53
We will continue talking about further details of cost calculation.
Masaru Hayashi
|
|
