全表分析—统计信息&自动任务(库存统计与分析表)

lewis 4年前 (2021-02-27) 阅读数 8 #技术

问题描述：前段时间，九龙坡人民医院反馈测试库缓慢，要求对全库做全表分析，因为表太多，后来降低到只对重要用户zlhis的表做分析.当时并不懂什么是全表分析，现在对全表分析这个知识点做一个归纳，有点像对表收集统计信息，那么得到的执行计划就是最新的.

在Oracle数据库中，我们需要去手工分折表和索引，或者某个模式下所有的表和索引等.

oracle提供了两种分析方法，分别是 dbms_stats包和analyze命令.

在10g中分析某一个用户下所有的对象，如scott用户:

sys.dbms_stats.gather_schema_stats(ownname => ‘scott’,estimate_percent => 30,method_opt => ‘FOR ALL INDEXED COLUMNS SIZE AUTO’,cascade => true,options => ‘GATHER’);

说明：此为分折一个用户所有对象，取样比例为30％，分折所有索引字段，生成柱形图，同时也分折索引.

在10g中分析某个用户下某个表及索引，如scott用户下info表和它的索引

execute dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'info',estimate_percent => 30,method_opt => ‘for   indexed columns size auto’);

说明：此为单独分析一个用户中一张表的分析方法，取样比例为30％，分析所有索引字段，生成柱形图，并且也分折索引.

使用analzye命令分折表的方法：

analyze table table_name compute statistics;

说明：此代码会将索引和表一起分折;

analyze table table_name compute statistics for table;

说明：仅分折表

analyze table table_name compute statistics for all columns;

说明：仅分折表，和上面所有的字段，生成柱形图

analyze table table_name compute statistics for all indexed columns;

说明：仅分折表，和上面所有建立索引的字段，生成柱形图

备注：在9i中，我们常常使用analyze方法，在10g以后，就改为dbms_stats.gather_schema_stats类似的包来分折数据库对象. 10g的分折函数包比9i的analyze方法更灵活一些.在10g中，会有个系统的计划任务，每天10点和周末两天都自动分析新的数据库对象和它认为统计信息已经过期的数据库对象.这个分析结果有时候不靠谱.因此，通常采用10g中提供的dbms_stats.gather_schema_stats方法作为补充，设置一个计划任务定期去分折业务用户下所有数据库对象.

variable job number;
begin 
sys.dbms_job.submit(job => :job, 
what => ‘sys.dbms_stats.gather_schema_stats(ownname
=> "USR MIKI",estimate_percent => 30,method_opt => “FOR ALL
INDEXED COLUMNS SIZE AUTO”,cascade => true,options => “GATHER”);’, 
next_date => to_date(‘16-12-2012 00:56:24’,’dd-mm-yyyy
hh24:mi:ss’), 
interval => ‘sysdate+7’);
commit;
end;
/

说明：以上语句未做测试.

oracle 11g中，默认有3个自动任务，分别是：自动统计信息收集、SQL调优顾问、段空间调整顾问，查看方法如下：

SQL> select client_name,task_name,operation_name,status from dba_autotask_task

CLIENT_NAME                         TASK_NAME                      OPERATION_NAME                 STATUS
----------------------------------- ------------------------------ ------------------------------ ----------------
auto optimizer stats collection     gather_stats_prog              auto optimizer stats job       ENABLED
sql tuning advisor                  AUTO_SQL_TUNING_PROG           automatic sql tuning task      ENABLED
auto space advisor                  auto_space_advisor_prog        auto space advisor job         ENABLED

任务gather_stats_prog调用dbms_stats.gather_database_stats_job_proc存储过程.

SQL> select program_name,program_type,program_action from dba_scheduler_programs where program_name='GATHER_STATS_PROG'

PROGRAM_NAME                        PROGRAM_TYPE                     PROGRAM_ACTION
----------------------------------- -------------------------------- ---------------------------------------------
GATHER_STATS_PROG                   STORED_PROCEDURE                 dbms_stats.gather_database_stats_job_proc

在oracle 11g中，一共配置了7个自动维护窗口，每天一个窗口

SQL> select * from dba_autotask_window_clients;

WINDOW_NAME          WINDOW_NEXT_TIME                    WINDOW_ACTIVE   AUTOTASK_STATUS OPTIMIZER_STATS SEGMENT_ADVISOR SQL_TUNE_ADVISOR         HEALTH_MONITOR
-------------------- ----------------------------------- --------------- --------------- --------------- --------------- ------------------------ ------------------------
MONDAY_WINDOW        08-AUG-22 10.00.00.000000 PM PRC    FALSE           ENABLED         ENABLED         ENABLED         ENABLED                  DISABLED
TUESDAY_WINDOW       09-AUG-22 10.00.00.000000 PM PRC    FALSE           ENABLED         ENABLED         ENABLED         ENABLED                  DISABLED
WEDNESDAY_WINDOW     10-AUG-22 10.00.00.000000 PM PRC    FALSE           ENABLED         ENABLED         ENABLED         ENABLED                  DISABLED
THURSDAY_WINDOW      11-AUG-22 10.00.00.000000 PM PRC    FALSE           ENABLED         ENABLED         ENABLED         ENABLED                  DISABLED
FRIDAY_WINDOW        12-AUG-22 10.00.00.000000 PM PRC    FALSE           ENABLED         ENABLED         ENABLED         ENABLED                  DISABLED
SATURDAY_WINDOW      06-AUG-22 06.00.00.000000 AM PRC    TRUE            ENABLED         ENABLED         ENABLED         ENABLED                  DISABLED
SUNDAY_WINDOW        07-AUG-22 06.00.00.000000 AM PRC    FALSE           ENABLED         ENABLED         ENABLED         ENABLED                  DISABLED

7 rows selected.

每个窗口的运行时间如下：

SQL> select a.window_name,a.repeat_interval,a.duration from dba_scheduler_windows a where enabled='TRUE'

WINDOW_NAME          REPEAT_INTERVAL                                              DURATION
-------------------- ------------------------------------------------------------ --------------------
MONDAY_WINDOW        freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00
TUESDAY_WINDOW       freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00
WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00
THURSDAY_WINDOW      freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00
FRIDAY_WINDOW        freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00
SATURDAY_WINDOW      freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00
SUNDAY_WINDOW        freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00

7 rows selected.

可以看到，周一到周五，窗口运行时间为晚上22点开始，最多运行4个小时，周六周日从早上6点开始，最多运行20个小时.

在窗口任务启动时，自动任务GATHER_STATS_PROG每次运行时会先生成ORA$AT_OS_OPT_xxx的作业，然后再执行这个作业.

SQL> r
  1  SELECT a.JOB_NAME, a.ACTUAL_START_DATE, a.RUN_DURATION, a.STATUS
  2    FROM dba_scheduler_job_run_details a
  3*  WHERE a.JOB_NAME LIKE 'ORA$AT_OS_OPT%'

JOB_NAME                       ACTUAL_START_DATE                   RUN_DURATION                   STATUS
------------------------------ ----------------------------------- ------------------------------ --------------------
ORA$AT_OS_OPT_SY_64            31-JUL-22 03.47.48.842890 PM PRC    +000 00:00:36                  SUCCEEDED
ORA$AT_OS_OPT_SY_1             17-JUL-22 12.54.53.367293 AM PRC    +000 00:01:07                  SUCCEEDED
ORA$AT_OS_OPT_SY_61            31-JUL-22 11.47.16.394399 AM PRC    +000 00:00:28                  SUCCEEDED
ORA$AT_OS_OPT_SY_41            30-JUL-22 10.49.12.161899 PM PRC    +000 00:00:38                  SUCCEEDED
ORA$AT_OS_OPT_SY_21            26-JUL-22 10.00.17.319647 PM PRC    +000 00:01:06                  SUCCEEDED
ORA$AT_OS_OPT_SY_81            06-AUG-22 08.01.11.593113 PM PRC    +000 00:00:30                  SUCCEEDED

6 rows selected.

总结：Oracle 11g自动统计信息收集是通过每天执行自动任务gather_stats_prog来实现的，每天会自动生成ORA$AT_OS_OPT_xxx的Job，然后执行Job来收集统计信息，其本质也是执行DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存储过程.

统计信息收集策略：每次自动收集统计信息，并不是对所有表都进行收集，Oracle只对那些统计信息已经失效的对象进行收集.

Oracle收集失效的统计信息的策略：自上次自动统计信息收集作业完成之后，如果DBA_TAB_MODIFICATIONS中记录的INSERT+UPDATE+DELETE所影响的行记录之和超过了DBA_TABLES中目标表记录数的10%，或者是自上次统计信息收集完成之后目标表执行过truncate操作，那么Oracle会认为目标表的统计信息已经失效，自动统计信息收集作业就会对目标表重新收集统计信息.

参考网址：

/news/upload/ueditor/image/202208/qlf2kcgzmst

/news/upload/ueditor/image/202208/ankoq0m5ibo.html

https://55.wendadaohang.com/zd/IILGGFec4LI8FIQFRIG.html