|
Oracle10g Cost Base Optimizer -DBMS_STATS-
|
DBMS_STATS.GATHER_SYSTEM_STATS usually takes the data to obtain the statistics. However, almost all systems' loads change with time. The system may operate smoothly during the daytime but may turn terribly slow at night. What can we do about this problem?
This time we would like to take the operation during online time and patch processing time into consideration when testing GATHER_SYSTEM_STATS.
Environment
Redhat Linux Advanced Server 2.1
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
|
Experiment
Let's confirm what will happen when CPU load is high. So far we have used the simple SELECT statement; this time we would like to use Subquery to confirm how execution plan will change. Letfs insert a table with 1,000,000 entries of data to execute SubQuery.
SQL> create table dodekai_table ( id number, value number, data varchar2(200));
Table created.
SQL> begin
2 for cnt in 1..1000000 loop
3 insert into dodekai_table (id, value, data)
4 values ( cnt, cnt,
5 'Data Data Data Data Data Data Data Data Data Data Data Data Data
Data Data Data Data Data Data Data Data Data Data Data Data Data
6 end loop;
7 commit;
8 end;
9 /
|
Let's keep the statistics we got last time for the case of gno-load to the system?Eand use SubQuery to execute sorting process as a CPU-occupying action.
SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.
SQL> select out.id, out.value from
( select avg(VALUE) avg_value from dodekai_table ) sbq,
2 stats_test out where out.value <= sbq.avg_value order by id;
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4981 Card=500 Bytes=10000)
1 0 SORT (ORDER BY) (Cost=4981 Card=500 Bytes=10000)
2 1 NESTED LOOPS (Cost=4980 Card=500 Bytes=10000)
3 2 VIEW (Cost=4962 Card=1 Bytes=13)
4 3 SORT (AGGREGATE)
5 4 TABLE ACCESS (FULL) OF 'DODEKAI_TABLE' (TABLE)
(Cost=4962 Card=974847 Bytes=12673011)
6 2 TABLE ACCESS (FULL) OF 'STATS_TEST' (TABLE)
(Cost=18 Card=500 Bytes=3500)
|
The table DODEKAI_TABLE with 1,000,000 entries of data has been full-scanned and the the Sort View is created. It seems that Nested Loop joins the created Views and STATS_TEST table.
It seems that the cost of joining condition is obtained, and we would like to check the CPU cost of sorting process. In the case of Sort Merge Join, total CPU sort cost is 10331355; in the case of Order by, total CPU sort cost is 3980211.
The result explains that Sort Merge Join is not used but Order By is used.
Let's raise CPU load and execute DBMS_STATS.GATHER_SYSTEM_STATS. Letfs use CPU heavily for one hour and then obtain the statistics to compare with the situation before increasing the load.
SYSSTATS_MAIN CPUSPEEDNW 566.641?E66.641
SYSSTATS_MAIN IOSEEKTIM 10?E0
SYSSTATS_MAIN IOTFRSPEED 4096?E096
SYSSTATS_MAIN SREADTIM 6.699?E4.603
SYSSTATS_MAIN MREADTIM 0.385?E2.778
SYSSTATS_MAIN CPUSPEED 564?E30
SYSSTATS_MAIN MBRC 9?E
SYSSTATS_MAIN MAXTHR 27648?E31072
|
CPUSPEEDNW, IOSEEKTIM and IOTFRSPEED remain the same but others changed. IO and CPU status are deteriorated. This is the value under the condition of high-load. Letfs execute the same SQL statement under the same condition.
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=20127 Card=500 Bytes=10000)
1 0 SORT (ORDER BY) (Cost=20127 Card=500 Bytes=10000)
2 1 NESTED LOOPS (Cost=20126 Card=500 Bytes=10000)
3 2 VIEW (Cost=20060 Card=1 Bytes=13)
4 3 SORT (AGGREGATE)
5 4 TABLE ACCESS (FULL) OF 'DODEKAI_TABLE' (TABLE)
(Cost=20060 Card=974847 Bytes=12673011)
6 2 TABLE ACCESS (FULL) OF 'STATS_TEST' (TABLE)
(Cost=67 Card=500 Bytes=3500)
|
It was expected that the execution plan might change but it did not. However, the cost changed after increasing the load. If CBO selects the execution plan with lower cost, the execution plan might change depending on the load.
Conclusion
# DBMS_STATS.GATHER_SYSTEM_STATS changes depending on the load at the time of obtaining statistics.
# The cost at the time when the execution plan is created will also be changed. It means the execution plan may change.
Next time we will test the application of the statistics.
Masaru Hayashi
|
|
