|
Oracle10g Cost Base Optimizer -Monitoring Attribute-
|
From this week, we would like to start discussing about Cost Base Optimizer. SQL analysis by Rule Base is not included in Oracle Support since Oracle 10g. In other words, Cost Base Optimizer is so well developed that users can make use of it by themselves.
How well is Cost Base Optimizer developed, then? This is the most important point. You can check about the comparison of performance on the Oracle website. We would like to test it in our unique way as usual. Oracle 10g adopts some features to strengthen Cost Base Optimizer.
Please try the following SQL statement in your Oracle 10g environment. What happens in MONITORING column? I guess results are almost "YES."
[Testing Environment]
Miracle Linux Standard Edition V2.1
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
|
SQL> select TABLE_NAME,MONITORING from tabs;
TABLE_NAME MON
------------------------------ ---
SALGRADE YES
BONUS YES
EMP YES
DEPT YES
|
"YES" in MONITORING column means these tables have Monitoring attribute. Monitoring attribute is a feature equipped since Oracle 8i. When upgrading of table (insert, update, delete, direct load) occurs to a table or table partition, the feature records how many rows are updated.
Wait...but there is something wrong. We have not specified Monitoring attribute when creating the table. Let's create a table and take a look.
SQL> create table test (col1 number);
Table created.
SQL> select TABLE_NAME,MONITORING from tabs where table_name='TEST';
TABLE_NAME MON
------------------------------ ---
TEST YES
|
MONITORING column still shows "YES." Let's change the attribute by alter command.
SQL> alter table test nomonitoring;
Table altered.
SQL> select table_name,MONITORING from tabs where table_name='TEST';
TABLE_NAME MON
------------------------------ ---
TEST YES
|
Nothing changes. The manual is your best friend when you have any problem with the database. The following is SQL reference 16-55 in the manual.
MONITORING / NOMONITORING
In the earlier versions, collecting statistics of table changes could be launched or stopped
by these sentences. However, these sentences are not used currently.
- Statistics of table changes used to be collected by using DBMS_STATS package in
GATHER AUTO or GATHER STALE mode, but users do not have to do such a task anymore.
The statistics will be collected automatically. Errors do not occur even if there are keywords
such as MONITORING and NOMONITORING in the existing code.
- When the statistics are not collected due to the performance issue, it is necessary to set
the initial parameter STATISTICS_LEVEL as BASIC. However, please pay attention
that this will cause many management features to be prohibited.
|
It seems that by setting statistics_level=basic the Monitoring attribute can be changed.
SQL> alter system set statistics_level=basic;
System altered.
SQL> select table_name,monitoring from tabs;
TABLE_NAME MON
------------------------------ ---
TEST NO
SALGRADE NO
BONUS NO
EMP NO
DEPT NO
|
It has changed! In Oracle 10g, if statistics_level=typical(default) or all, Monitoring attribute will be set automatically. In other words, by the default operation, all tables are set as "Monitoring." Why does the system execute such a kind of setting that increases system load?
Why does not Oracle suggest users to set up statistics_level=basic? We will reveal the reason in the coming weeks.
Takuya Kishimoto
|
|
