Oracle Analyze-Aaron的搬砖生涯

继吉

一、概述
　　1、要使CBO发挥最大的作用，就必须对对象进行分析。Oracle根据分析的结果取cost最小的方案执行SQL。
　　2、数据库的分析可以通过analyze对表执行，也可以通过DBMS_DDL、DBMS_UTILITY、DBMS_STATS等包批量分析某个用户或者整个数据库。
　　二、完全分析与抽样分析
　　完全计算法： analyze table table_name compute statistics;
　　抽样估算法(抽样20%)： analyze table table_name estimate statistics sample 20 percent;
　　如果无法做完全计算，建议对表分析采用抽样估算，对索引分析采用完全计算。
　　三、通过analyze分析
　　analyze table 可以指定分析： 表、所有字段、所有索引字段、所有索引。 若不指定则全部都分析。
　　1、全分析
　　说明：全分析，包括表、字段、索引。统计信息产生在user_tables、user_tab_columns、user_indexes中。
　　analyze table my_table compute statistics for table for all indexes for all columns;
　　2、指定表分析
　　说明：只分析表。统计信息只产生在user_tables中。
　　analyze table my_table compute statistics for table;
　　查看表的统计信息：select table_name,num_rows,blocks,empty_blocks from user_table;
　　3、指定所有字段分析
　　说明：只分析字段。统计信息只产生在user_tab_columns中，且全字段有。
　　analyze table my_table compute statistics for all columns;
　　查看字段的统计信息：select table_name,column_name,num_distinct,low_value,high_value,density from user_tab_columns;
　　4、指定有索引的字段分析
　　说明：只分析有索引的字段。统计信息只产生在user_tab_columns中，且只有含索引的字段有。
　　analyze table my_table compute statistics for all indexed columns;
　　5、指定索引分析
　　说明：只分析索引。统计信息只产生在user_indexes中。
　　analyze table my_table compute statistics for all indexes;
　　查看索引的统计信息
　　select table_name,index_name,blevel,leaf_blocks,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,num_rows from user_indexes;
　　另外，可以删除分析数据：
　　SQL> analyze table my_table delete statistics;
　　SQL> analyze table my_table delete statistics for table for all indexes for all indexed columns;
　　特别需要注意的：
　　truncate命令不会修改数据的统计信息,也就是如果我们想让CBO利用合理利用数据的统计信息的时候,需要我们及时的使用analyze命令或者dbms_stats重新统计数据的统计信息。
　　四、通过ORACLE提供的程序包（PACKAGE）对相关的数据库对象进行分析
　　1、包过程DBMS_DDL.ANALYZE_OBJECT
　　程序包可以对表，索引，簇表进行分析
　　1.1、格式及参数说明
　　DBMS_DDL.ANALYZE_OBJECT(
　　TYPE VARCHAR2,
　　SCHEMA VARCHAR2,
　　NAME VARCHAR2,
　　METHOD VARCHAR2,
　　ESTIMATE_ROWS NUMBER DEFAULT NULL,
　　ESTIMATE_PERCENT NUMBER DEFAULT NULL,
　　METHOD_OPT VARCHAR2 DEFAULT NULL,
　　PARTNAME VARCHAR2 DEFAULT NULL
　　);
　　TYPE可以是：TABLE，INDEX，CLUSTER中其一。
　　SCHEMA为：TABLE，INDEX，CLUSTER的所有者，NULL为当前用户。
　　NAME为：相关对象的名称。
　　METHOD是：ESTIMATE，COMPUTE，DELETE中其一，当选用ESTIMATE，
　　下面两项，ESTIMATE_ROWS和ESTIMATE_PERCENT不能同时为空值。
　　ESTIMATE_ROWS是：估算的抽样行数。
　　ESTIMATE_PERCENT是：估算的抽样百分比。
　　METHOD_OPT是：有以下选项，
　　FOR TABLE
　　[FOR ALL [INDEXED] COLUMNS] [SIZE N]
　　FOR ALL INDEXES
　　PARTNAME是：指定要分析的分区名称。
　　1.2、例子
　　对MID_SC用户的SC_MID_SUBSTATION表，进行90%的抽样分析
　　begin
　　DBMS_DDL.ANALYZE_OBJECT('TABLE','MID_SC','SC_MID_SUBSTATION','ESTIMATE',NULL,90);
　　end;
　　2、包过程DBMS_UTILITY.ANALYZE_SCHEMA
　　该包对某个用户拥有的所有TABLE，INDEX和CLUSTER的分析统计
　　2.1、格式及参数说明
　　DBMS_UTILITY.ANALYZE_SCHEMA (
　　SCHEMA VARCHAR2,
　　METHOD VARCHAR2,
　　ESTIMATE_ROWS NUMBER DEFAULT NULL,
　　ESTIMATE_PERCENT NUMBER DEFAULT NULL,
　　METHOD_OPT VARCHAR2 DEFAULT NULL ) ;
　　3、包过程DBMS_UTILITY.ANALYZE_DATABASE
　　该包用于对整个数据库进行分析统计。
　　3.1、格式及参数说明
　　DBMS_UTILITY.ANALYZE_DATABASE (
　　METHOD VARCHAR2,
　　ESTIMATE_ROWS NUMBER DEFAULT NULL,
　　ESTIMATE_PERCENT NUMBER DEFAULT NULL,
　　METHOD_OPT VARCHAR2 DEFAULT NULL );
　　4、包DBMS_STATS中的过程GATHER_TABLE_STATS、GATHER_INDEX_STATS、GATHER_SCHEMA_STATS、GATHER_DATABASE_STATS
　　DBMS_STATS是在ORACLE8I中新增的程序包，它使统计数据的生成和处理更加灵活方便，并且可以并行方式生成统计数据。
　　begin

　　dbms_stats.gather_table_stats('mid_gis','temp_t',cascade=>true,method_opt=>'for all columns>　　end;
　　五、一些应用
　　在系统设置定时任务，执行分析脚本。
　　在数据库服务器端，我们以UNIX用户oracle，运行脚本analyze，在analyze中，我们生成待执行sql脚本，并运行。（假设我们要分析scott用户下的所有表和索引）
　　Analyze脚本内容如下：
　　sqlplus scott/tiger 100,cascade=> TRUE);
　　dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
　　这是对命令与工具包的一些总结
　　1、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。
　　a) 可以并行进行，对多个用户，多个Table
　　b) 可以得到整个分区表的数据和单个分区的数据。
　　c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区
　　d) 可以倒出统计信息
　　e) 可以用户自动收集统计信息
　　2、DBMS_STATS的缺点
　　a) 不能Validate Structure
　　b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。
　　c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True
　　3、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。
　　据我所知 11g里STATISTICS是自动进行的, 用‘select * from dba_autotask_client’ 你可以看到‘auto optimizer stats collection’，它自动调用DBMS_STATS包每天执行一次，所以无需手工ANALYZE
　　11g前手工执行dbms_stats和ANALYSE命令，无论11g 还是9i因为执行的结果是静态的，如果你的DB有大量的DML操作，必须经常运行，否则统计数据很快就会过期。不准确的数据会影响执行计划的判定
　　dbms_stats.gather_table_stats和analyse的确是对表和索引作统计，比如表中共几行，NULL值有多少，平均长度，并将统计结果放入SYSTEM TABLESPACE.
　　这些统计主要目的是在生成执行计划时利用统计结果判断最优方案，你看到的方案可能没变，但在后台具体执行时准确的统计数据会加速SQL执行。统计数据对SQL有加速作用但对PL/SQL是无效的