有哪些功能是DBMS_STATS无法实现而Analyze命令可以做到的?

今天有同行问我DBMS_STATS与Analyze的区别, 我简单地介绍了一下DBMS_STATS提供了更丰富的功能,从8i开始推荐使用DBMS_STATS来完全替代Analayz Table/Index/Cluster 命令收集数据库中对象的统计信息。

 

之后他又问起我有那些功能是Analyze 命令可以做到,反而DBMS_STATS做不到的?

 

这个问题我倒是没有很系统地去考虑过, 闪过脑子的2个念头就是Analyze Table/Index validate structure 和  Analyze Table list chained rows into chained_rows 这2个命令。

 

Google了一下相关的信息,发现Asktom对这个已经讨论过了,从8i以后analyze被提名为具备”validate”验证功能的命令,主要负责验证表和索引的结构以及链式行(chained and migrated rows)信息; 而DBMS_STATS包负责统计信息的管理。

 

analyze is slated to do this and this alone at some time in the future.  analyze is destined to be
the “validate” thing — analyze validate structure, analyze list chained rows, etc.

dbms_stats will stick with stats and stats only.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:735625536552

 

因为Oracle Development 开发部门已经给予 Analyze 命令较为精确地功能定位了,  所以其最主要的2个功能validate structure和list chained rows目前和将来都不会被植入到DBMS_STATS包当中。

 

Analyze validate structure 的主要语法包括:

 

@?/rdbms/admin/utlvalid.sql                  /* 创建存放验证信息的表 */
desc invalid_rows

ANALYZE INDEX <index_name>
[PARTITION <partition_name>]
[SUBPARTITION <subpartition_name>]
VALIDATE STRUCTURE CASCADE
INTO <table_name> <OFFLINE | ONLINE>

select * from index_stats;

ANALYZE TABLE <table_name>
[PARTITION <partition_name>]
[SUBPARTITION <subpartition_name>]
VALIDATE STRUCTURE CASCADE
[INTO <table_name>] <OFFLINE | ONLINE>

ANALYZE CLUSTER <cluster_name> VALIDATE STRUCTURE CASCADE
INTO <table_name> <OFFLINE | ONLINE>

 

 

补充:analyze index validate structure会填充index_stats视图,该视图包含了丰富的索引空间信息:

 

SQL> desc index_stats;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 HEIGHT                                             NUMBER
 BLOCKS                                             NUMBER
 NAME                                               VARCHAR2(30)
 PARTITION_NAME                                     VARCHAR2(30)
 LF_ROWS                                            NUMBER
 LF_BLKS                                            NUMBER
 LF_ROWS_LEN                                        NUMBER
 LF_BLK_LEN                                         NUMBER
 BR_ROWS                                            NUMBER
 BR_BLKS                                            NUMBER
 BR_ROWS_LEN                                        NUMBER
 BR_BLK_LEN                                         NUMBER
 DEL_LF_ROWS                                        NUMBER
 DEL_LF_ROWS_LEN                                    NUMBER
 DISTINCT_KEYS                                      NUMBER
 MOST_REPEATED_KEY                                  NUMBER
 BTREE_SPACE                                        NUMBER
 USED_SPACE                                         NUMBER
 PCT_USED                                           NUMBER
 ROWS_PER_KEY                                       NUMBER
 BLKS_GETS_PER_ACCESS                               NUMBER
 PRE_ROWS                                           NUMBER
 PRE_ROWS_LEN                                       NUMBER
 OPT_CMPR_COUNT                                     NUMBER
 OPT_CMPR_PCTSAVE                                   NUMBER

 

但是请注意validate structure会锁住table/index/cluster等对象,对于在线业务可以考虑使用validate structure online在线验证方法,但是validate strucutre online也有它的缺点,那就是在线模式下结构验证命令将不填充索引的状态信息到index_stats视图。

 

Analyze list chained rows的主要语法包括:

 

@?/rdbms/admin/utlchain.sql

desc chained_rows

ANALYZE TABLE <table_name> LIST CHAINED ROWS
INTO <table_name>;

ANALYZE CLUSTER <cluster_name> LIST CHAINED ROWS INTO <table_name>;

 

注意因为DBMS_STATS仅仅收集对CBO有用的统计信息,所以一些空间使用情况信息和使用FreeList管理的信息都不会被收集,这些信息包括:

 

If statistics unrelated to the cost based optimizer are required, then these
must still be collected using the the ANALYZE command. These statistics include:

Space Usage information :

EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT

Information on freelist blocks

AVG_SPACE_FREELIST_BLOCKS,
NUM_FREELIST_BLOCKS

 

因为以上信息对于CBO计算成本并没有帮助,所以DBMS_STATS也就无意也无法收集它们,但是Analyze命令还是可以做到收集以上这些信息。

 

此外因为CBO其实并不会参考Cluster类型对象的统计信息来计算Cost成本,而是使用cluster中各个表的统计信息(DBMS_STATS does not gather cluster statistics, but you can use DBMS_STATS to gather statistics on the individual tables instead of the whole cluster. )
所以DBMS_STATS也不支持收集Cluster的统计信息。

 

这么总结一下,发现Analyze命令的独有功能还真不少,因为Oracle公司已经明确了Analyze作为”validate”验证命令的功能定位,且很多内部的工具和脚本仍在使用Analyze Table/Cluster/Index的特有功能,所以可以预期Analyze命令在未来的一段时间内也不会被废弃。

 

总结以上DBMS_STATS无法实现,而传统的Analyze命令可以做到的功能:

  1. Analyze validate structure 验证表、簇、索引的结构的完整性,使用cascade选项可以交叉验证表和索引的数据完整,online选项可以做到在线验证
  2. Analyze list chained rows 收集表、簇上的Migrated and Chained Rows链式或迁移行信息
  3. Analyze table compute statistics 收集表上的 empty_blocks、avg_space 空间使用信息
  4. Analyze Cluster 收集簇的信息,其实cluster上唯一可统计的信息是DBA_CLUSTERS.AVG_BLOCKS_PER_KEY(Number of blocks in the table divided by number of cluster keys) , 所以收集cluster的statistics意义不大

 

附Analyze命令的详细语法如下:

 

Syntax:

   ANALYZE TABLE tablename COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options
   ANALYZE INDEX indexname COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options
   ANALYZE CLUSTER clustername COMPUTE|ESTIMATE|DELETE STATISTICS options

ptnOption
   PARTITION (partion)
   SUBPARTITION (subpartition)

options
   VALIDATE STRUCTURE [CASCADE] [INTO tablename]
   LIST CHAINED ROWS [INTO tablename]
   COMPUTE|ESTIMATE STATISTICS FOR TABLE
   COMPUTE|ESTIMATE STATISTICS FOR ALL COLUMNS
   COMPUTE|ESTIMATE STATISTICS FOR ALL INDEXED COLUMNS
   COMPUTE|ESTIMATE STATISTICS FOR COLUMNS [SIZE int] column [SIZE int]

   When Estimating statistics you can optionally
    specify
    ... ESTIMATE STATISTICS SAMPLE n ROWS
    ... ESTIMATE STATISTICS SAMPLE n PERCENT

Does GATHER_STATS_JOB gather all objects’ stats every time?

周五在一家客户的调优会议中讨论了一个由于统计信息陈旧导致SQL执行计划偏差的问题,这是一个10g的库并且禁用了自动收集统计信息的定时作业GATHER_STATS_JOB;当问及应用程序开发商为何要禁用自动统计信息收集时,开发商的一位工程师说因为该库的数据量较大,考虑到该JOB每天都会将所有大表统计一遍可能要花费大量时间所以予以停用。

这里就存在一个问题,GATHER_STATS_JOB自动统计作业是每次打开都会将数据库中所有的对象的统计信息都收集一遍吗?细心的朋友一定会发觉实际上该JOB的运行时间是时长时短的,同时绝对不是如这位开发工程师所说的会每天都重复统计所有表。

10g的官方文档中对该GATHER_STATS_JOB描述为”The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.The stop_on_window_close attribute controls whether the GATHER_STATS_JOB continues when the maintenance window closes. The default setting for the stop_on_window_close attribute is TRUE, causing Scheduler to terminate GATHER_STATS_JOB when the maintenance window closes. The remaining objects are then processed in the next maintenance window.The GATHER_DATABASE_STATS_JOB_PROC procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).”

以上这段描述还是比较清晰的,MAINTENANCE_WINDOW_GROUP维护窗口组中的工作日窗口(WEEKNIGHT_WINDOW,周一到周五)会在每个工作日的22:00启动并于第二天的6:00结束,在周末该维护窗口组中的周末窗口(WEEKEND_WINDOW)从周六Sat的0点开始并持续48小时(你不难发现这2个窗口在周六0点到6点之间存在overlay,实际的情况是WEEKEND_WINDOW窗口是从周六的0点整到周一的0点,具体可以观察dba_scheduler_windows视图的NEXT_START_DATE列,这里不再赘述)。在数据库一直打开的情况下,GATHER_STATS_JOB会伴随维护窗口一起被启动,默认情况下如果到维护窗口关闭该JOB仍未结束则将被终止(这取决于该JOB的属性stop_on_window_close),剩下的有待收集信息的对象将在下一个维护窗口中得到处理;如果数据库一直处于关闭的状态,并在某维护窗口的时间范围内该DB被打开,那么相应的维护窗口会被立即激活(ACTIVE),同时GATHER_STATS_JOB自动作业也会被启动,但该自动作业仅会在一个窗口中自动运行一次(因REASON="ORA-01014: ORACLE shutdown in progress"等原因失败的不算做一次)。

以上介绍了GATHER_STATS_JOB的运行周期,和我们要介绍的问题没有直接的联系。我们这里要谈的是,GATHER_STATS_JOB自动统计信息收集作业每次启动时是由针对性地收集统计信息的而非对数据库中所有schema下的对象都分析一遍;以上引用的文字中介绍了该JOB挑选分析对象的条件,即:

  1. 对象之前从未收集过统计信息,或由于某些原因没有统计信息
  2. 对象的统计信息相对陈旧(stale),是否陈旧的评判标准是由上次收集信息到此次收集期间被修改过的行数超过10%

条件1显得理所当然,剔除一些复杂的情况,一个对象没有统计信息的原因往往是这个对象刚刚被创建或者加载到数据库中,并且用户没有手动地去收集过统计信息,那么Oracle有充分的理由去分析这些对象。而后者则体现了查询优化器对统计信息陈旧度的容忍在超过10%的情况下导致执行计划偏差的可能性将大幅上升,为了遏制这种势头有必要再次统计这些对象。

让我们来看看GATHER_STATS_JOB针对”陈旧”(stale)统计信息的实际表现:

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------
www.askmaclean.com

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> conn maclean/maclean
Connected.

SQL> create table need_analyze tablespace users as select rownum t1 from dba_objects where rownum<10001;
Table created. 

SQL> select count(*)  from need_analyze;

  COUNT(*)
----------
     10000

SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';

  NUM_ROWS     BLOCKS
---------- ----------

/* 以上创建了一张具有10000行记录的测试用表,因为是新建的所以没有num_rows和blocks等等统计信息 */

/* 手动调用GATHER_STATS_JOB自动作业 */

SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/

SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';

  NUM_ROWS     BLOCKS
---------- ----------
     10000	   20

/* 删除999条记录,即不到10%的数据 */

SQL> delete need_analyze where rownum<1000;
999 rows deleted. 

SQL> commit;
Commit complete.

/* 再次调用GATHER_STATS_JOB */

begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/

/* 可以看到统计信息并未被更新 */

SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';

  NUM_ROWS     BLOCKS
---------- ----------
     10000	   20

SQL> delete need_analyze where rownum<2;
1 row deleted. 

SQL> commit;
Commit complete.

SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/  2    3    4  

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';

  NUM_ROWS     BLOCKS
---------- ----------
     10000	   20

SQL>  delete need_analyze where rownum<2;
1 row deleted. 

SQL> commit;
Commit complete.

SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/  2    3    4  

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';

  NUM_ROWS     BLOCKS
---------- ----------
      8999	   20

/* 可以看到修改的行数必须超过10%后才会被收集 */

有的朋友肯定要问Oracle是怎么知道某张表是否有过DML操作,而DML操作又涉及到了多少行数据呢?这都是通过表监控特性(a table monitoring facility)来实现的,当初始化参数STATISTICS_LEVEL设置为TYPICAL或ALL时默认启用这种特性。Oracle会默认监控表上的INSERT,UPDATE,DELETE以及表是否被TRUNCATE截断,并记录这些操作数量的近似值到数据字典。我们可以通过访问user_tab_modifications视图来了解这些信息:

SQL> delete need_analyze;
8999 rows deleted.

SQL> commit;
Commit complete.

SQL> select * from user_tab_modifications where table_name='NEED_ANALYZE';
no rows selected

/* 从实际的DML操作完成到*_tab_modifications视图到更新可能存在几分钟的延迟 */

/* 通过dbms_stats包中的FLUSH_DATABASE_MONITORING_INFO存储过程可以
   将这些监控数据刷新到字典中  */

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed

SQL> col table_name for a20

SQL> select table_name,inserts,updates,deletes,timestamp from user_tab_modifications where table_name='NEED_ANALYZE';

TABLE_NAME		INSERTS    UPDATES    DELETES TIMESTAMP
-------------------- ---------- ---------- ---------- ---------
NEED_ANALYZE		      0 	 0	 8999 26-MAR-11

/* 可以看到*_tab_modifications视图中记录了上次收集统计信息以来
   NEED_ANALYZE表上删除过8999条记录,因为测试时仅用少量的串行DML,所以这里十分精确 */

SQL> set autotrace on;

/* 通过以上执行计划可以猜测,monitoring监控数据来源于MON_MODS_ALL$基表上 */

SQL> desc sys.MON_MODS_ALL$;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#						    NUMBER
 INSERTS					    NUMBER
 UPDATES					    NUMBER
 DELETES					    NUMBER
 TIMESTAMP					    DATE
 FLAGS						    NUMBER
 DROP_SEGMENTS					    NUMBER

SQL> select * from mon_mods_all$ where obj#=(select object_id from dba_objects where object_name='NEED_ANALYZE');

      OBJ#    INSERTS	 UPDATES    DELETES TIMESTAMP	   FLAGS DROP_SEGMENTS
---------- ---------- ---------- ---------- --------- ---------- -------------
     52565	    0	       0       8999 26-MAR-11	       0	     0

/* 需要注意的该mon_mods_all$修改监控基表仅记录上次该对象统计信息以来的修改(modify)情况,
   并不能做为某张表的实际修改历史记录来利用 */

虽然我们现在对GATHER_STATS_JOB在如何选择分析对象的条件上更清晰了,但是不少朋友可能还是会疑惑难道Oracle不对那些长久以来没有显著修改的表一直不予以收集信息吗?这似乎有悖于我们的常识,试看下例子:

/ * NEED_ANALYZE现在扮演一张静态表,它上次被分析是在2011年3月26日 */

SQL> select last_analyzed from dba_tables where table_name='NEED_ANALYZE';

LAST_ANAL
---------
26-MAR-11

SQL> select sysdate from dual;
SYSDATE
---------
26-MAR-11

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host
[maclean@rh8 ~]$ su - root
Password: 

/* 我们把时钟调快到2012年的12月30日,希望我们能安全度过2012! */

[root@rh8 ~]# date -s "2012-12-30 00:00:00"
Sun Dec 30 00:00:00 CST 2012
[root@rh8 ~]# date
Sun Dec 30 00:00:01 CST 2012

[maclean@rh8 ~]$ exit
exit

SQL> startup;
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size		    1218292 bytes
Variable Size		   75499788 bytes
Database Buffers	   83886080 bytes
Redo Buffers		    7168000 bytes
Database mounted.
Database opened.

SQL> select sysdate from dual;

SYSDATE
---------
30-DEC-12

/* 再次手动调用GATHER_STATS_JOB自动作业 */

SQL> set timing on;
SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/
  2    3    4
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.33

/* :-),运行结束不到1s */

SQL>  select last_analyzed from dba_tables where table_name='NEED_ANALYZE';

LAST_ANAL
---------
26-MAR-11

是的,默认情况下GATHER_STATS_JOB不会反复去分析那些静态表,无论过去”多久”。

好了,我们需要对GATHER_STATS_JOB和DBMS_STATS包下属的统计信息收集存储过程(gather_*_stats)有一个饱满的认识,他们远没有我们想象的那么2,实际上这个GATHER_STATS_JOB调用的PROGRAM存储过程是DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC,虽然这是一个内部存储过程(interal procedure);但实际上它和我们经常手动调用的DBMS_STATS.GATHER_DATABASE_STATS收集整库统计信息的存储过程在以GATHER AUTO选项运行时的行为方式上几乎一致,主要的区别是GATHER_DATABASE_STATS_JOB_PROC总是优先收集那些急需收集统计信息的对象,这保证了在维护窗口关闭之前那些最需要收集的统计信息总是能得到满足。而在手动调用GATHER_DATABASE_STATS等存储过程时因为不需要考虑窗口时间线的限制,而不论优先级。

to be continued ………….

滚动游标失效(Rolling Cursor Invalidations)

在Oracle 10g中DBMS_STATS包针对GATHER_TABLE/INDEX_STATS和DELETE_TABLE/INDEX_STATS等收集统计信息的存储过程提供了AUTO_INVALIDATE选项;
该参数允许用户指定是否让那些对统计信息有依存关系的游标失效,举例来说如果SQL游标涉及到的表,索引,列或固有对象的统计信息收到以上存储过程修改时,使用NO_INVALIDATE选项可以指定是否让这些受到影响的游标失效,何时失效。
NO_INVALIDATE选项可以有以下三种值:

  • TRUE : 不让相关游标失效
  • FALSE: 立即让相关游标失效
  • AUTO_INVALIDATE(default):让Oracle自己决定何时让游标失效。
--   no_invalidate - Do not invalide the dependent cursors if set to TRUE.
--      The procedure invalidates the dependent cursors immediately
--      if set to FALSE.
--      Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
--      invalidate dependend cursors. This is the default. The default
--      can be changed using set_param procedure.

当统计信息为DBMS_STATS包所修改,新的尚未在共享池中缓存的游标将直接使用这些统计信息; 对于已经存在的共享池中游标缓存,我们无法在原始子游标的基础上更新它们的执行计划;这些旧的子游标将被新的参考最新统计信息的子游标替代,这个过程包含一次硬解析以便获得新的优化树和执行计划;换而言之传统的立即游标失效(Immediate Cursor Invalidation)就是在统计信息更新后立即导致原始子游标的失效,而我们所说的滚动游标失效(Rolling Cursor Invalidations)是在统计信息成功更新的前提下保证原始子游标不立即失效;设想如果系统中有一张业务相关表,一旦我们更新了该表的统计信息可能导致大量共享失效,短期内硬解析将十分频繁并占用大量cpu,而且很多时候我们并不期望执行计划有显著变化;为了防止dbms_stats包统计信息时不要越帮越忙,就可以考虑到使用NO_INVALIDATE选项。

我们来看看RCI的具体表现:
[Read more…]

11g Multi-Column Correlation Stats and Dynamic Sampling

Oracle CBO优化模式中列的统计信息是一个十分重要的概念,但在11g之前我们所讨论的都是基于单列的统计信息或直方图,也就是说基于成本的优化器总是假设where子句后的谓词中列与列之间不存在联系。但是有的查询包含一个表的多个列,而每个列又都与不同的选择度。这些列中有的是相关的,但优化器并不知道这些关系。在这种情况下,优化器如果要估计出真实的基数(card),必须要了解增加另一列到某个给定列是否会引起结果集的减少。多列上的相关统计数据能提供比单列统计数据或直方图更好的基数估计。当2个列紧密相关时,增加额外的谓词可以减少结果集。Oracle database 11g中引入了扩展统计(也叫多列统计,multicolumn statistics),可以收集一组列上的统计数据,从而让优化器能准确地计算多个单列谓词的选择性。因为把紧密相关的列作为一个组才能正确地放映其组合选择性,所以把相关列作为一组,在其上(列祖)收集统计数据,这些信息足以让优化器能准确地进行选择性估计,在包含使用相关列的谓词查询中,这是我们实际关心的问题。多列统计的引入意味着,在11g中cbo优化器可以对具有多列复杂谓词判断的SQL语句做出更准确的成本估算,许多原本”误用”全表扫描的查询现在可以使用索引扫描的执行计划,语句将运行地更快速。

我们试看下例:
[Read more…]

沪ICP备14014813号

沪公网安备 31010802001379号