|
Oracle10g Cost Base Optimizer -Monitoring Attribute-
|
Last week we mentioned that statistics_level=typical has become the default setting since Oracle 10g and is automatically set as MONITORING attribute. Oracle Scheduler picks up the statistics once a day, the monitoring information disappears, and the statistics will be created.
However, how about the tables created on the current day or when the statistics are not created yet? This is the point we want to check today.
[Testing Environment]
Redhat Linux Advanced Server 2.1
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
|
We will create a table, input data and then obtain the trace of gselecth statement.
Experiment
SQL> create table emp_test as select * from emp;
The table is created.
SQL> alter session set sql_trace = true;
The session is altered.
SQL> select * from emp_test;
|
The statistics do not exist, so there should be a gselecth statement executed. Letfs take a look at the trace file.
SELECT /* OPT_DYN_SAMP */
/*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) */
NVL(SUM(C1),0),
NVL(SUM(C2),0)
FROM (SELECT /*+ NO_PARALLEL("EMP_TEST") FULL("EMP_TEST") NO_PARALLEL_INDEX("EMP_TEST") */
1 AS C1,
1 AS C2
FROM "EMP_TEST" "EMP_TEST") SAMPLESUB
|
The dynamic sampling is executed as recursive SQL. Dynamic sampling has been a feature since Release 2 of Oracle9i to obtain the statistics automatically when the statistics do not exist yet.
SQL> show parameter optimizer_dynamic_sampling
NAME TYPE VALUE
------------------------------------ ----------- ---------------------
optimizer_dynamic_sampling integer 2
|
In Release 2 of Oracle 9i, the default value of optimizer_dynamic_sampling is 1. How will the situation be different? According to the book gOracle Database Performance Tuning Guide,h when:
# optimizer_dynamic_sampling = 1:
Under the following conditions, all the tables not analyzed yet will be the target of sampling.
(1) At least one table which is not analyzed yet exists in the query.
(2) The table which is not yet analyzed is merged with other tables, or there exists subquery that cannot be merged.
(3) There is no index in the not-analyzed table.
(4) There are more blocks in the not-analyzed table than the blocks used in dynamic sampling of this table. The number of block being sampled is the default value of the dynamic sampling block (32).
# optimizer_dynamic_sampling = 2 :
The dynamic sampling takes all tables not analyzed yet as the targets. The number of sampled blocks is twice as many as the default number of dynamic sampling blocks.
In other words, since Oracle 10g, as long as the table is being used by default, the statistics will be created automatically even if the table does not have statistics originally. Moreover, to execute dynamic sampling explicitly, dynamic_sampling hint will be used.
select /*+ dynamic_sampling(emp_test,2) */ * from emp_test;
|
However, dynamic sampling is not stored in the dictionary but in the shared pool, and that statistics will be used. Therefore, whenever the statistics are Aged Out, the dynamic sampling will be executed, and the load of Parsing will become higher.
Letfs check if the process is true: execute the dynamic sampling hint statement-> execute dynamic sampling-> Parsing occurs -> no re-use.
select /*+ dynamic_sampling(emp_test,2) */ * from emp_test;
|
Even though we check the trace file, no recursive SQL occurred. When there is any re-usable SQL, Parsing will not occur. However, if the level of dynamic sampling is change, Parsing will occur.
Conclusion
Even if we do not intend to obtain the statistics, the dyamic sampling will be executed automatically. That means CBO is used. As one day passes by, the statistics will be obtained. That means we shall be able to use the statistics all the time. That is all for today.
Masaru Hayashi
|
|
