| Oracle10g Cost Base Optimizer
-DBMS_STATS- |
Review
Last week we calculated the
cost but focused merely on the I/O. In other words, we only
calculated the cost of I/O. Even if we obtained
GATHER_SYSTEM_STATS, but it is bizarre that CPU is not taken
into account. How is CPUSPEED obtained by
GATHER_SYSTEM_STATS used?
Environment
Redhat Linux Advanced Server 2.1
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
|
Experiment
The equation we mentioned
last week is as follows: ((Single Block Read x SREADTIM) + (Multi
Block Read x MREADTIM))/SREADTIM.
Let's include the estimation
of CPU: ((Single Block Read x SREADTIM) + (Multi Block Read x
MREADTIM) + (CPU Cycles / CPUSPEED))/SREADTIM.
CPUSPEED is
the average number of CPU cycles per second, in Mhz; CPU Cycle is
the number of CPU cycles actually used. Therefore, the CPU time can
be calculated by CPU Cycle/ CPUSPEED.
- Single Block Read
time
- Multi Block Read time
- CPU time
This time we
want to verify without high-load by executing GATHER_SYSTEM_STATS
during off-peak. AUX_STATS$ changes as follows.
SQL> select * from aux_stats$ where sname = 'SYSSTATS_MAIN';
SNAME PNAME PVAL1 PVAL2
--------------- ---------- ---------- -----------------
SYSSTATS_MAIN CPUSPEED 569
SYSSTATS_MAIN CPUSPEEDNW 566.04
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN MAXTHR 131072
SYSSTATS_MAIN MBRC 6
SYSSTATS_MAIN MREADTIM 92.778
SYSSTATS_MAIN SLAVETHR
SYSSTATS_MAIN SREADTIM 7.404
|
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=129 Card=10000 Bytes=70000)
1 0 TABLE ACCESS (FULL) OF 'STATS_TEST' (TABLE)
(Cost=129 Card=10000 Bytes=70000)
|
The cost
is reported as 129. In this case, there should be table scan cost.
Replacing the value in the equation leads to the following result.
(1)Single Block Read
(HWM-(INT(HWM/MBRC)*MBRC))
--> 64-(INT(64/6)*6)=4
(2)Multi Block Read
INT(HWM/MBRC)
--> INT(64/6)=10
(3)CPU Cycle
CPU Cycle is reported in the trace file as CPU-RSC.
1727286/1000=1727.286 ms
(((1) * 7.404) + ((2) * 92.778) + ((3) / 569)) / 7.404 = 129.72
The result is correct.
|
How
about in the case of IndexScan? Even in the case when we scan
one record from the table with 10000 records, the equation is
supposed to be applicable. Let's create PK in the table STATS_TEST
and execute scan.
SQL> alter table stats_test add constraint
PK_STATS_TEST primary key (id ) using index;
Table altered.
SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.
SQL> select * from stats_test where id = '438';
ID VALUE
---------- ----------
438 438
|
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=7)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'STATS_TEST' (TABLE)
(Cost=2 Card=1 Bytes=7)
2 1 INDEX (UNIQUE SCAN) OF 'PK_STATS_TEST'
(INDEX (UNIQUE))(Cost=1 Card=1)
|
This
becomes INDEX SCAN. According to the trace file, Block Access is 2
blocks, and MBRC is 6, so we estimate by Single Block Read.
Therefore, ((2 * 7.404) + (0 * 92.778) + (21734 * 569)) = 2.
It
seems that the cost of reading data from the disk is calculated in
the same way.
Summary
The cost is calculated by the
time consumed, using the number of blocks that are read and the
system statistics of AUX_STATS. Based on the value, Optimizer
will decide the execution plan.
Next time we will discuss about
the application of system statistics.
Masaru
Hayashi
|
|
