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是无效的

发表评论