|
In this Document
Applies to: Oracle Server - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Oracle Server - Personal Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Oracle Server - Standard Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.
Purpose
To provide one stop shop for automated statistics collection (operation) provided in 10g and 11g
Questions and Answers
What kind of statistics do the Automated tasks collect
The statistics jobs automatically gather Missing and Stale statistics for the Cost Based Optimizer (CBO).
How do I revert to a previous set of statistics?
From Oracle 10g, Oracle retains collected statistics for 31 days after they are changed.
Refer to:
Does the automatic statistic collection jobs populate CHAIN_CNT?
No. Chain count is not a statistic used as by the Optimizer and as such the job does not populate the chained rows information. Historically the legacy analyze command could be used to capture chain count but this functionality was not included in the DBMS_STATS package and is not collected by the automated jobs.
You can use the Automatic segment advisor for the purpose; it provides Chained row analysis??.
11g+ Automatic Maintenance Tasks
-
What are the default windows for the automated maintenance task to run?In 11g daily maintenance windows are provided. by default these are defined as :
- Weeknights: Starts at 10 p.m. and ends at 2 a.m.
- Weekends: Starts at 6 a.m. is 20 hours long.
See:
Oracle? Database Administrator's Guide 11g Release 2 (11.2) Part Number E25494-01 Automated Maintenance Tasks Reference Table 26-1 Predefined Maintenance Windows Note:743507.1 How to Benefit from Automatic Maintenance Tasks Following the Removal of the GATHER_STATS_JOB in 11g?
-
Whats new /changed in 11g with respect to automatic statistics collection?The GATHER_STATS_JOB does not exist in 11g (the name does not exist). Instead it has been included in Automatic Maintenance Tasks.
The following are the tasks that AutoTask automatically schedules in these maintenance windows:
select CLIENT_NAME from DBA_AUTOTASK_CLIENT
CLIENT_NAME
----------------------------------------------------------------
auto optimizer stats collection
auto space advisor
sql tuning advisor
-
How to enable auto stats collection?If for some reason automatic optimizer statistics collection is disabled, you can enable it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
exec DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
-
How to disable the auto stats collection?In situations when you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
exec DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
-
How can I check the status of the 'auto optimizer stats collection'?The status of the automatic statistics collection can be checked using:
select client_name, JOB_SCHEDULER_STATUS
from DBA_AUTOTASK_CLIENT_JOB
where client_name='auto optimizer stats collection';
The possible Job status:
- DISABLED
- RETRY SCHEDULED
- SCHEDULED
- RUNNING
- COMPLETED
- BROKEN
- FAILED
- REMOTE
- SUCCEEDED
- CHAIN_STALLED
-
How can I see the history of the automatic stats job for each day?
SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed
FROM dba_autotask_client_history
WHERE client_name like '%stats%';
CLIENT_NAME WINDOW_NAME JOBS JOBS JOBS
CREATED STARTED COMPLETED
------------------------------- ---------------- ------- -------- ----------
auto optimizer stats collection THURSDAY_WINDOW 1 1 1
auto optimizer stats collection SUNDAY_WINDOW 3 3 3
auto optimizer stats collection MONDAY_WINDOW 1 1 1
auto optimizer stats collection SATURDAY_WINDOW 2 2 2
-
How to manually execute the Optimizer Statistics Auto Task?
In 11g the Auto-Task infrastructure replaced the need for the gather_stats_job and you can execute the following command to accomplish manual statistics collection:
SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;
This will prompt the Automated Maintenance Tasks subsystem into starting a job that will gather optimizer statistics, unless such a job is already running (for example if a maintenance window is currently open). If an immediate job is created it will be named ORA$_AT_OS_MANUAL_nnnnnn (nnnnn is one or more decimal digits). Unlike regular Automated Maintenance jobs, the "MANUAL" job is not tied to a specific maintenance window.
-
How do to check values of parameter( estimate percent, type of histograms etc) used by the job?
DBMS_STATS.GET_PARAM (pname IN VARCHAR2) RETURN VARCHAR2;
If there were any non-default preferences set for the job:
DBMS_STATS.GET_PREFS (pname IN VARCHAR2,ownname IN VARCHAR2 DEFAULT NULL, tabname IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
Possible preferences: -
- AUTOSTATS_TARGET
- CASCADE
- DEGREE
- ESTIMATE_PERCENT
- METHOD_OPT
- NO_INVALIDATE
- GRANULARITY
- PUBLISH
- INCREMENTAL
- STALE_PERCENT
-
How to set preference for the next maintenance job run to pick?The automatic statistics-gathering job uses the default parameter values for the DBMS_STATS procedures. If you wish to change these default values you can use the DBMS_STATS.SET_GLOBAL_PREFS procedure. Remember these values will be used for all schemas including '??SYS'??.
SET_GLOBAL_PREFS- This procedure enables you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for any object in the database that does not have an existing table preference or for any new objects created after this. For Example:
To change the 'STALE_PERCENT' you can use :
exec DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','5');
SET_DATABASE_PREFS - This procedure enables you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for all user-defined schemas in the database. You can include system-owned schemas such as SYS and SYSTEM by setting the ADD_SYS parameter to TRUE. This will NOT affect any objects created after this.
New objects will use the GLOBAL_PREF values for all parameters.
-
What is the AUTOSTAT_TARGET of SET_GLOBAL_PREFS?This additional parameter controls which objects the automatic statistic gathering job (that runs in the nightly maintenance window) will monitor. The possible values for this parameter are:
- ALL
This setting means that the automatic statistics gathering job will gather statistics on all objects in the database APART FROM statistics on fixed objects. Since Fixed objects record current database activity, the representative workload you wan to capture may not be active at the time of automatic statistics collection. You should gather statistics when the database has representative activity. You can manually collect statistics on fixed objects, such as the dynamic performance tables, using GATHER_FIXED_OBJECTS_STATS procedure.
- ORACLE
ORACLE means that the automatic statistics gathering job will only gather statistics for Oracle owned schemas (sys, sytem, etc)
- AUTO (default)
means that Oracle will decide what objects to gather statistics on. Currently AUTO and ALL behave the same.
10g Collection: Via GATHER_STATS_JOB
-
What are the default windows for the GATHER_STATS_JOB ?In 10g, Two Scheduler windows are predefined upon installation of Oracle Database:
- WEEKNIGHT_WINDOW starts at 10 p.m. and ends at 6 a.m. every Monday through Friday.
- WEEKEND_WINDOW covers whole days Saturday and Sunday.
Together these windows constitute the MAINTENANCE_WINDOW_GROUP in which all system maintenance tasks are scheduled.
-
How do you disable the GATHER_STATS_JOB?The most direct approach is to disable the GATHER_STATS_JOB as follows:
SQL> exec sys.dbms_scheduler.disable ('GATHER_STATS_JOB');
-
How do you enablethe GATHER_STATS_JOB?This is enabled by default. If you have disabled it, then you can re-enable it as
SQL> exec sys.dbms_scheduler.enable (?"SYS"."GATHER_STATS_JOB");
-
How do you Determine That the GATHER_STATS_JOB Completed
SELECT job_name, state
FROM dba_scheduler_jobs
WHERE job_name='GATHER_STATS_JOB';
There are four types of jobs that are not running:
- FAILED
- BROKEN
- DISABLED
- COMPLETED
Note that if a job has recently completed successfully, but is scheduled to run again, the job state is set to 'SCHEDULED'. A job is marked as '??COMPLETED??' if 'end_date' or 'max_runs' (in dba_scheduler_jobs) is reached.
-
How to Change the NEXT_RUN_DATE on the GATHER_STATS_JOB ?You can adjust the predefined maintenance windows to a time suitable to your database environment using the DBMS_SCHEDULER.SET_ATTRIBUTE procedure
For Example:
begin
dbms_scheduler.disable('gather_stats_job');
dbms_scheduler.set_attribute_null('gather_stats_job','schedule_name');
dbms_scheduler.set_attribute(
'gather_stats_job','repeat_interval','freq=minutelty;byminute=1,11,21,31,41,51;byhour=0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,22,23;bysecond=0');
dbms_scheduler.enable('gather_stats_job');
end;
to run job on weekends only:
SQL> exec sys.dbms_scheduler.disable( '"SYS"."GATHER_STATS_JOB"' );
SQL> exec sys.dbms_scheduler.set_attribute( name => '"SYS"."GATHER_STATS_JOB"', attribute =>'schedule_name', value => 'SYS.WEEKEND_WINDOW');
SQL> exec sys.dbms_scheduler.enable( '"SYS"."GATHER_STATS_JOB"' );
|
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2012-6-29 18:08:40
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对
发表于 2012-6-29 21:56:17