Oracle Analyze

一、概述
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 size auto');
end;


五、一些应用
在系统设置定时任务，执行分析脚本。
在数据库服务器端，我们以UNIX用户oracle，运行脚本analyze，在analyze中，我们生成待执行sql脚本，并运行。（假设我们要分析scott用户下的所有表和索引）
Analyze脚本内容如下：
sqlplus scott/tiger << EOF
set pagesize 5000
set heading off
SPOOL ANALYTAB.SQL
SELECT "ANALYZE TABLE SCOTT."||TABLE_NAME||" ESTIMATE STATISTICS SAMPLE 20 PERCENT ;" FROM USER_TABLES；
SPOOL OFF
SPOOL ANALYIND.SQL
SELECT "ANALYZE TABLE SCOTT."||TABLE_NAME||" ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALL INDEXES;" FROM USER_TABLES；
SPOOL OFF
SPOOL ANALYZE.LOG
@ANALYTAB.SQL
@ANALYIND.SQL
SPOOL OFF
EXIT
在UNIX平台上crontab加入，以上文件，设置为每个月或合适的时间段运行。
或者将如下脚本保存成analyze.sql,然后在sqlplus里面执行：
set pagesize 5000
set linesize 300
set trims on
set heading off
set feedback off
SPOOL analyTab.sql
SELECT 'ANALYZE TABLE ZFMI.'||TABLE_NAME||' COMPUTE STATISTICS ;'
FROM USER_TABLES;
SPOOL OFF
SPOOL analyIdx.sql
SELECT 'ANALYZE TABLE ZFMI.'||TABLE_NAME||' COMPUTE STATISTICS
FOR ALL INDEXES;' FROM USER_TABLES;
SPOOL OFF
SPOOL analyLog.log
@@analyTab.sql
@@analyIdx.sql
SPOOL OFF


参考文献：
http://www.cnblogs.com/einyboy/archive/2012/08/09/2630321.html
http://blog.sina.com.cn/s/blog_4c705ccb0100cf4n.html




自从Oracle8.1.5引入dbms_stats包，Experts们便推荐使用dbms_stats取代analyze。 理由如下

dbms_stats可以并行分析
dbms_stats有自动分析的功能(alter table monitor )
analyze 分析统计信息的不准确some times


1,2好理解，且第2点实际上在VLDB中是最吸引人的；3以前比较模糊，看了metalink236935.1 解释，analyze在分析Partition表的时候，有时候会计算出不准确的Global statistics .

原因是，dbms_stats会实在的去分析表全局统计信息（当指定参数）；而analyze是将表分区（局部）的statistics 汇总计算成表全局statistics ,可能导致误差。

如果想分析整个用户或数据库，还可以采用工具包，可以并行分析
Dbms_utility(8i以前的工具包)
Dbms_stats(8i以后提供的工具包)
如
dbms_stats.gather_schema_stats(User,estimate_percent=>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是无效的