| Oracle10g Cost Base Optimizer
-Monitoring Attribute- |
Last time we confirmed
that monitoring information of tables with Monitoring attribute can
be checked by dba_tab_modifications view. Moreover, we also
confirmed that once the statistics are obtained, the monitoring
information will disappear. Then, what is the relation between the
monitoring information that can be checked by
dba_tab_modifications view and Cost Base Optimizer? The
monitoring information seems to remain no longer than one day.
Environment
Miracle
Linux Standard Edition V2.1
Oracle Database 10g Enterprise
Edition Release 10.1.0.2.0
Obtaining
the monitoring information of EMP2
SQL> select sysdate from dual;
SYSDATE
--------
04-11-03
SQL> select TABLE_NAME,INSERTS from user_tab_modifications;
TABLE INSERTS
----- ----------
EMP2 42
|
Obtaining the monitoring information of EMP2 (Next
day)
SQL> select sysdate from dual;
SYSDATE
--------
04-11-04
SQL> select TABLE_NAME,INSERTS from user_tab_modifications;
no rows selected
|
For
monitoring information to disappear, someone must have obtained the
statistics. However, I was the only one using this system. How could
it happen? Does it mean that Oracle obtain the statistics by itself?
Yes, the statistics will be obtained automatically by default since
Oracle 10g. However, if Oracle tries to obtain all the statistics of
all tables, it will result in heavy load. Therefore, it uses the
monitoring information to judge if over 10% of the rows in the table
have been changed. Oracle obtains the statistics only in this case.
This job is scheduled by the new feature of Oracle 10g, Oracle
Scheduler.
Details of the job that
executes the statistics automatically
SQL> select owner,
job_name,
schedule_name,
program_name,
last_start_date,
last_run_duration,
comments
from dba_scheduler_jobs;
OWNER JOB_NAME
------------------------------ ------------------------------
SYS GATHER_STATS_JOB
SCHEDULE_NAME PROGRAM_NAME
------------------------------ ------------------------------
MAINTENANCE_WINDOW_GROUP GATHER_STATS_PROG
LAST_START_DATE LAST_RUN_DURATION
---------------------------------- --------------------------
04-11-04 15:00:02.784079 +09:00 +000000000 00:00:37.191593
COMMENTS
------------------------------------------------------------
system default gather statistics maintenance job
SQL> select owner,program_name,program_action,comments
from dba_scheduler_programs;
OWNER PROGRAM_NAME
------------------------------ ------------------------------
SYS GATHER_STATS_PROG
PROGRAM_ACTION
-------------------------------------------------------------
dbms_stats.gather_database_stats_job_proc
COMMENTS
-------------------------------------------------------------
gather statistics maintenance program
SQL> select * from dba_scheduler_wingroup_members;
WINDOW_GROUP_NAME WINDOW_NAME
------------------------------ ------------------------------
MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW
SQL> select window_name,repeat_interval,duration,comments
from dba_scheduler_windows;
WINDOW_NAME
------------------------------
WEEKNIGHT_WINDOW
WEEKEND_WINDOW
REPEAT_INTERVAL
---------------------------------------------------------------------
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0
DURATION COMMENTS
------------------------------ --------------------------------------
+000 08:00:00 Weeknight window for maintenance task
+002 00:00:00 Weekend window for maintenance task
|
The job
GATHER_STATS_JOB calls the procedure
DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC. This procedure is
an internal procedure, and it is similar to the procedure
DBMS_STATS.GATHER_DATABASE_STATS which uses GATHER
AUTO option. The main difference is that
DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC prioritizes the
object that requires statistics, and it first deals with the object
that requires the updated statistics most. When the statistics have
not been obtained by this job before, or when over 10% of the rows
of the table are changed by checking dba_tab_modifications
view, Oracle will obtain the statistics. This job is scheduled to be
executed from 22 o'clock to 6 o'clock of the next day from Monday to
Friday, as well as all day on Saturdays and Sundays.
Conclusion
1. The statistics are
obtained automatically at certain time everyday since Oracle
10g.
2. The object is determined by checking
dba_tab_modifications. The criteria is over 10% of the rows
have been changed.
3. The monitoring information that is
recorded in dba_tab_modifications view is about the
monitoring attribute table.
4. Since Oracle 10g, if the initial
parameter statistics_level=typical(default) or all, the table
attribute will be set automatically as monitoring attribute.
Takuya Kishimoto
|
|
