最近有个客户发现oracle数据库中有些分区表并未收集统计信息，需要确认是统计信息作业是否启用？ 最近作业是否成功？如何列出需要收集统计数据的对象的问题？他的系统在周末收集统计数据，但想要检查本周是否有任何对象的统计数据已过时。如何加快统计信息收集？ 实际上，执行此检查非常容易， 使用dbms_stats包可以提供需要收集的对象列表。
如何查看统计信息运行情况？
如果统计信息收集在大多数晚上都完成，并且在特定时间之前不需要新的统计数据，那么就没有必要做更多的事情。
select client_name, job_status, job_start_time, job_duration,job_info from dba_autotask_job_history where and client_name like 'auto optimizer stats%' and JOB_START_TIME>sysdate-3 order by 3,1;
自动统计信息作业有时无法完成（状态为“STOPPED”）。JOB_INFO列揭示了原因：自动统计信息收集有时花费的时间超时，并在批处理窗口关闭时终止。我们该如何解决这个问题？我们可以 （a） 延长批处理窗口和/或 （b） 加快统计信息收集速度。如何加快统计数据收集速度？如果数据库服务器上有资源，则可以将更多资源用于收集统计信息。
| Indicator | View Containing Information |
What this might indicate | Potential Solution |
|---|---|---|---|
| Is ‘auto optimizer stats collection’ enabled ? | DBA_AUTOTASK_CLIENT DBA_AUTOTASK_OPERATION DBA_AUTOTASK_TASK |
The Job needs to be enabled to run | Enable ‘auto optimizer stats collection’ |
| Is ORA$AT_WGRP_OS window group is enabled ? | DBA_AUTOTASK_CLIENT | The Job needs to be enabled to run | Enable ORA$AT_WGRP_OS window group |
| When is the ORA$AT_WGRP_OS window group scheduled to start next? | DBA_AUTOTASK_CLIENT | If the window group is not being scheduled to run then this may be why statistics are not being collected | Check why the ORA$AT_WGRP_OS window group is not scheduled to be run. |
| Has the ‘auto optimizer stats collection’ run in the last 7/30 days and how did it perform? | DBA_AUTOTASK_CLIENT | If the job has not run/completed recently then this may be why statistics are not being collected | Check why job has not run/completed |
| Has ‘auto optimizer stats collection’ ever successfully completed? | DBA_AUTOTASK_CLIENT_HISTORY DBA_SCHEDULER_JOB_RUN_DETAILS |
If the job completed at some time in the past then something has changed such that it no longer completes. | Check why job has not run/completed |
| When did ‘auto optimizer stats collection’ last successfully complete? When did it start to fail? | DBA_AUTOTASK_CLIENT_HISTORY DBA_AUTOTASK_JOB_HISTORY DBA_SCHEDULER_JOB_RUN_DETAILS |
If a change has occurred then it was after the last successful completion | Check why job has not run/completed |
| Is ‘auto optimizer stats collection’ job running currently? Is it supposed to be running? |
DBA_AUTOTASK_CLIENT_JOB DBA_AUTOTASK_TASK |
If the job is still running it may be that since it never completes the statistics are not being collected | Check why job has not completed yet |
| If the job is still running, is there a history of this occurring? When did it start? |
DBA_SCHEDULER_JOB_LOG | Identifying when the behavior changed provides a target for investigating what might have changed | Check why job did not completed in past runs |
| Did the Job stop with an error? | DBA_AUTOTASK_JOB_HISTORY DBA_SCHEDULER_JOB_RUN_DETAILS DBA_SCHEDULER_JOB_LOG |
An error may or may not be an indicator of a problem | Check what the error is and how to resolve it. |
| When did the Job last run successfully? | DBA_AUTOTASK_TASK DBA_SCHEDULER_JOB_RUN_DETAILS DBA_SCHEDULER_JOB_LOG |
Something changed after this time such that it was unsuccessful | Look for changes after the job last ran successfully and revert to the previous state. |
| What was the duration of the last successful job? What is the duration of the unsuccessful jobs? |
DBA_AUTOTASK_TASK DBA_SCHEDULER_JOB_RUN_DETAILS |
If unsuccessful jobs have an excessive duration then this may be preventing completion | Determine what part of the unsuccessful job is taking all the time. Look for issues and resolutions |
| Are the maintenance window jobs that run the ‘auto optimizer stats collection’ enabled ? | DBA_AUTOTASK_WINDOW_CLIENTS DBA_SCHEDULER_WINDOWS |
These need to be enabled for the ‘auto optimizer stats collection’ to run | Enable ‘auto optimizer stats collection’ |
| Is the optimizer task enabled? | DBA_AUTOTASK_WINDOW_CLIENTS | The optimizer task needs to be enabled for the ‘auto optimizer stats collection’ to run | Enable the optimizer task using DBMS_AUTO_TASK_ADMIN |
| Are the WEEKNIGHT_WINDOW and WEEKEND_WINDOW disabled? | DBA_SCHEDULER_WINDOWS | These are the old 10g windows and may interfere with collection if enabled | Disable the WEEKNIGHT_WINDOW and WEEKEND_WINDOW using DBMS_SCHEDULER.DISABLE |
| Is there a group for ‘auto optimizer stats collection’? Is it enabled? Is the next start date ok? |
DBA_SCHEDULER_WINDOW_GROUPS | The group needs to be enabled and the next start date should be scheduled in the future | Enable the group for ‘auto optimizer stats collec |
DBA_OPTSTAT_OPERATIONS DBA_OPTSTAT_OPERATIONS contains a history of statistics operations performed at the schema and database level using the DBMS_STATS package.
更多视图可以参考 Automatic Statistics Gathering does not Complete – Diagnostics Interpretation Guidelines (Doc ID 1902112.1)
如何加快统计信息收集？
1， 并行收集统计信息 – AUTO_DEGREE
您可以识别大型表并定义特定的并行度 （DOP）：
exec dbms_stats.set_table_prefs(user, 'BIG_TABLE', 'DEGREE', 16)
可以让 Oracle 为您决定 DOP
exec dbms_stats.set_table_prefs(user, 'BIG_TABLE', 'DEGREE', DBMS_STATS.AUTO_DEGREE)
全局级别设置属性
exec dbms_stats.set_global_prefs('DEGREE', DBMS_STATS.AUTO_DEGREE)
在并行执行的情况下，统计信息收集可能会消耗大量系统资源，因此您需要考虑如何控制这一点，资源管理计划DEFAULT_MAINTENANCE_PLAN和名为 ORA$AUTOTASK 的使用者组，使用dbms_resource_manager.update_plan_directive可以控制cpu使用的范围如10-60%.
禁用并行
exec dbms_stats.set_global_prefs('DEGREE', 1)
2， 并发收集统计信息 – 并发
并行统计信息收集使我们能够显著提高 CPU 利用率，但是如果我们有多余的机器资源并希望运行得更快，该怎么办？CONCURRENT 首选项允许DBMS_SCHEDULER一次启动多个统计信息收集作业，以便数据库将同时收集多个表和分区的统计信息。我们可以选择仅为自动统计信息收集启用此行为：
exec dbms_stats.set_global_prefs('CONCURRENT','AUTOMATIC')
如果表的大小相似，则并发统计信息收集效果很好，但如果不并行执行，则在非常大的表上运行的串行作业可能需要很长时间，并且可能无法在批处理窗口关闭之前完成。如果这对您来说是一个问题，您可以结合使用并发处理和并行执行。
3，并发和并行收集统计信息 – 并发和AUTO_DEGREE
在实现并发和并行执行时需要小心，因为存在执行大量并发并行执行服务器并生成非常高系统负载的范围。将启动多个作业，每个作业都有可能启动多个并行查询服务器。
exec dbms_stats.set_global_prefs('CONCURRENT','AUTOMATIC')
exec dbms_stats.set_global_prefs('DEGREE', DBMS_STATS.AUTO_DEGREE)
4, 手动收集 使用并发和并行
如果要手动启动统计信息收集，并且仍充分利用并行和并发设置，则可以使用以下方法：
exec dbms_stats.set_global_prefs('CONCURRENT','MANUAL')
exec dbms_stats.set_global_prefs('DEGREE', DBMS_STATS.AUTO_DEGREE)
exec dbms_stats.gather_database_stats();
性能与时间对比，可以查看How to Gather Optimizer Statistics Fast!
查看当前的系统级默认配置:
SET ECHO OFF
SET TERMOUT ON
SET SERVEROUTPUT ON
SET TIMING OFF
DECLARE
v1 varchar2(100);
v2 varchar2(100);
v3 varchar2(100);
v4 varchar2(100);
v5 varchar2(100);
v6 varchar2(100);
v7 varchar2(100);
v8 varchar2(100);
v9 varchar2(100);
v10 varchar2(100);
v11 varchar2(100);
BEGIN
dbms_output.put_line('Automatic Stats Gathering Job - Parameters');
dbms_output.put_line('==========================================');
v1 := dbms_stats.get_prefs('AUTOSTATS_TARGET');
dbms_output.put_line(' AUTOSTATS_TARGET: ' || v1);
v2 := dbms_stats.get_prefs('CASCADE');
dbms_output.put_line(' CASCADE: ' || v2);
v3 := dbms_stats.get_prefs('DEGREE');
dbms_output.put_line(' DEGREE: ' || v3);
v4 := dbms_stats.get_prefs('ESTIMATE_PERCENT');
dbms_output.put_line(' ESTIMATE_PERCENT: ' || v4);
v5 := dbms_stats.get_prefs('METHOD_OPT');
dbms_output.put_line(' METHOD_OPT: ' || v5);
v6 := dbms_stats.get_prefs('NO_INVALIDATE');
dbms_output.put_line(' NO_INVALIDATE: ' || v6);
v7 := dbms_stats.get_prefs('GRANULARITY');
dbms_output.put_line(' GRANULARITY: ' || v7);
v8 := dbms_stats.get_prefs('PUBLISH');
dbms_output.put_line(' PUBLISH: ' || v8);
v9 := dbms_stats.get_prefs('INCREMENTAL');
dbms_output.put_line(' INCREMENTAL: ' || v9);
v10:= dbms_stats.get_prefs('STALE_PERCENT');
dbms_output.put_line(' STALE_PERCENT: ' || v10);
v11:= dbms_stats.get_prefs('CONCURRENT');
dbms_output.put_line(' CONCURRENT: ' || v11);
END;
/
如何知道下次统计信息收集会收哪些表？
了解Oracle数据库统计信息收集下次将收集哪些对象？如统计信息为空或过期的对象。 其实比较容易检查是否下次收集的对象过多， 使用dbms_stats.gather_database_stats和DBMS_STATS.REPORT_GATHER_DATABASE_STATS可以输出。
dbms_stats.gather_database_stats列出
declare
m_objects dbms_stats.ObjectTab;
begin
-- dbms_stats.gather_schema_stats(
-- ownname => user,
dbms_stats.gather_database_stats(
options => 'LIST AUTO', ----- 可以修改这里
objlist => m_objects,
GATHER_SYS=>false
);
dbms_output.new_line;
dbms_output.put_line('Auto: ' || m_objects.count);
dbms_output.put_line('--------------------');
for i in 1..m_objects.count loop
dbms_output.put(rpad(m_objects(i).ownname,10));
dbms_output.put(rpad(m_objects(i).objname,32));
dbms_output.put(rpad(nvl(m_objects(i).partname,' '),32));
dbms_output.put(rpad(nvl(m_objects(i).subpartname,' '),32));
dbms_output.put(rpad(m_objects(i).objtype,6));
dbms_output.new_line;
end loop;
end;
/
— 创建测试表
CREATE TABLE anbob.sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
);
— 使用上面的LIST AUTO方法:
Auto: 7 -------------------- ANBOB SALES TABLE ANBOB SALES SALES_Q4_2006 TABLE ANBOB SALES SALES_Q3_2006 TABLE ANBOB SALES SALES_Q2_2006 TABLE ANBOB SALES SALES_Q1_2006 TABLE MDSYS SDO_TXN_JOURNAL_REG P1 TABLE CTXSYS DR$DBO TABLE PL/SQL procedure successfully completed.
options选项，auto似乎是empty+stale
LIST AUTO: Returns a list of objects to be processed with GATHER AUTO
LIST STALE: Returns a list of stale objects as determined by looking at the *_tab_modifications views
LIST EMPTY: Returns a list of objects which currently have no statistics
DBMS_STATS.REPORT_GATHER_*_STATS列出(12c+)
dbms_stats包提供了一些report方法,可以仅以报告模式不实际执行的方法，以xml, html,text不同的格式输出报告.注意这里的option 不可以是list *，而是gather*， 如options=>’GATHER EMPTY’,
VARIABLE my_report CLOB;
BEGIN
:my_report :=DBMS_STATS.REPORT_GATHER_DATABASE_STATS(
detail_level => 'TYPICAL' ,
options=>'GATHER EMPTY',
GATHER_SYS=>false,
format => 'text' );
END;
8 /
PL/SQL procedure successfully completed.
SQL> print my_report;
MY_REPORT
---------------------------------------------------------------------------------------------------------------------------------------
| Operation Id | Operation | Target | Start Time | End Time | Status | Total Tasks | Successful Tasks | Failed Tasks | Active Tasks |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 4681 | gather_schema_stats (reporting mode) | ANBOB | | | | 24 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------
| |
| --------------------------------------------------------------------------------------------------------------------------------- |
| T A S K S |
| --------------------------------------------------------------------------------------------------------------------------------- |
| --------------------------------------------------------------------------------------------------------------------------- |
| | Target | Type | Start Time | End Time | Status | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."DATA_ARCHIVE_DETAIL" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."DATA_ARCHIVE_DETAIL_HISTORY" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."DATA_ARCHIVE_RULE" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."GIMG_202204" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."MF20200301ABC_XXX1XXX1XXX1XX" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."MF20200301_XX" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."MF20200302ABC_XXX1XXX1XXX1XX" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."MF20200302_XX" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."MF20200303ABC_XXX1XXX1XXX1XX" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."MR20200302_XX" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."RAOCHENG_RMS_199901" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."REPEAT_SFR_20220101" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."REPEAT_SFR_20220102" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."REPEAT_SFR_20220131" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."SALES" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."SALES"."SALES_Q1_2006" | TABLE PARTITION | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."SALES"."SALES_Q2_2006" | TABLE PARTITION | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."SALES"."SALES_Q3_2006" | TABLE PARTITION | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."SALES"."SALES_Q4_2006" | TABLE PARTITION | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."SFR20200302" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."TEST1" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."IDX_TEST1_ID" | INDEX | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."TEST100" | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| | "ANBOB"."IDX_TEST100_ID" | INDEX | | | | |
| --------------------------------------------------------------------------------------------------------------------------- |
| |
| |
-------------------------------------------------------------------------------------------------------------------------------------
Reference：
https://blogs.oracle.com/optimizer/post/how-to-gather-optimizer-statistics-fast
Automatic Statistics Gathering does not Complete – Diagnostics Interpretation Guidelines (Doc ID 1902112.1)
— over —