Oracle数据库的数据统计(Analyze).doc

Oracle数据库的数据统计(Analyze) Oracle数据的优化器有两种优化方法：基于代价/成本的优化器（CBO）和基于规则的优化器（RBO），系统在对SQL进行优化的时候，使用哪种优化决定于初始化参数OPTIMIZER_MODE，该参数值可以设置为：CHOOSE，RULE，ALL_ROWS，FIRST_ROWS。在Oracle9i以后的版本中还引入了新的优化参数命令：FIRST_ROWS_1，FIRST_ROWS_10， FIRST_ROWS_100，FIRST_ROWS_1000。（具体的说明将在以后的BLOG文章中介绍）Oracle推荐使用CBO优化方式，当系统使用CBO方式优化SQL的时候，要使其执行计划达到最优化，需要定期执行数据统计，必须保证统计数据的及时性，否则可能得不到预计的优化效果，或与预计的优化效果相差悬殊。 要对数据库对象生成统计信息，可以有以下方法： 完全统计法：analyze table table_name compute statistics; 抽样估计法：analyze table table_name estimate statistics sample 30 percent; 对表使用抽样估计法要比完全统计法的生成统计速度要快，但是统计数据可能不够精确。在开发过程中，我们可能要涉及很多的表的查询，而我们在使用CBO的时候就需要经常对这些表执行分析统计，得到CBO所需要的统计数据。通常有以下几种方法来收集统计信息： 1导出所有需要分析的表的语句脚本，然后执行该脚本。 SQL SPOOL OFF; SQL SPOOL C:ANALYZE_TAB.SQL SQL SELECT ANALYZE TABLE |OWNER|.|TABLE_NAME| COMPUTE STATISTICS; FROM ALL_TABLES WHERE OWNER NOT IN (SYS,SYSTEM); 然后调整一下脚本，并执行： SQLC:ANALYZE_TAB.SQL 可以将该脚本放到服务器端并设置自动执行。 2使用Oracle提供的过程：DBMS_DDL.ANALYZE_OBJECT，该过程可以对某个特定用户的特定表执行统计。例如： 完全统计： SQLexecute dbms_ddl.analyze_object(TABLE,DINYAR,DINYA_TEST01,COMPUTE); PL/SQL procedure successfully completed SQL 50%抽样统计 SQLexecute dbms_ddl.analyze_object(TABLE,DINYAR,DINYA_TEST01,ESTIMATE,NULL,50); PL/SQL procedure successfully completed SQL 可以使用该过程，生成分析统计数据库对象的脚本，并定时执行该脚本。 3使用Oracle提供的过程DBMS_UTILITY.ANALYZE_SCHEMA该过程执行对某个特定用户下的TABLE，INDEX和CLUSTER的分析统计。如： 完全统计SCHEMA下的所有对象： SQL execute dbms_utility.analyze_schema(DINYAR,COMPUTE); PL/SQL procedure successfully completed Executed in 6.9 seconds SQL 抽样50%统计SCHEMA下的所有对象： SQL execute dbms_utility.analyze_schema(DINYAR,ESTIMATE,NULL,50); PL/SQL procedure successfully completed Executed in 1.933 seconds SQL 从执行的时间上看，抽样统计的时间要比完全统计所花费的时间要短，执行的更快。 4使用Oracle提供的过程DBMS_UTILITY.ANALYZE_DATABASE，该过程可以对整个数据库中的对象进行分析统计。但需要当前登陆用户具备足够的权限，否则系统将提示出错。如： SQL execute dbms_utility.analyze_database(COMPUTE); begin dbms_utility.analyze_database(COMPUTE); end; ORA-20000: You have insufficient privileges for an object in this database. ORA-06512: at SYS.DBMS_UTILITY, line 501 ORA-06512: at line 1 SQL 改换有DBA权限的用户登陆： SQL execute dbms_utility.analyze_database(COMPUTE); begin dbms_utility.analyze_database(COMPUTE); end; ORA-30657: operation not supported on external organized table ORA-06512: at SYS.DBMS_DDL, line 179 ORA-06512: at SYS.DBMS_UTILITY, line 497 ORA-06512: at line 1 SQL 从上面的错误信息可以看出，不支持对外部表的分析统计，查看Oracle的解决办法，Oracle称不要试图做这项操作。 DBMS_UTILITY.ANALYZE_DATABASE的抽样分析统计和上例中类似。 5使用Oracle提供的过程：DBMS_STATS，该包中的过程dbms_stats.gather_index_stats， DBMS_STATS.gather_table_stats，DBMS_STATS.gather_schema_stats，DBMS_STATS.gather_database_stats，DBMS_STATS.gather_system_stats分别执行对索引、表、某个schema、数据库、系统的统计信息。例如： SQL execute dbms_stats.gather_table_stats(DINYAR,DINYA_TEST01); PL/SQL procedure successfully completed Executed in 0.29 seconds SQL execute dbms_stats.gather_schema_stats(DINYAR); PL/SQL procedure successfully completed Executed in 7.07 seconds SQL （该包中还有其他的一些过程，可以对数据库的对象进行操作，不在这里讨论。） 6定时执行分析统计，使用DBMS_JOB包，创建一个JOB，定时执行过程，对数据库对象进行分析统计： PL/SQL procedure successfully completed Executed in 0.581 seconds job_num - 41 SQL 这样就可以在数据库中定时执行数据库对象统计信息的收集，保证了使用CBO优化器优化时优化路径的准确性。SQL analyze table employee compute statistics; 表已分析。 SQL set autotrace on SQL select count(*) from employee ; COUNT(*) - 299999 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1) 1 0 SORT (AGGREGATE) 2 1 BITMAP CONVERSION (COUNT) 3 2 BITMAP INDEX (FAST FULL SCAN) OF IDX_BMP_EMP_SEX Statistics - 153 recursive calls 0 db block gets 96 consistent gets 11 physical reads 0 redo size 370 bytes sent via SQL*Net to client 425 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL analyze table employee delete statistics; 表已分析。 SQL select count(*) from employee; COUNT(*) - 299999 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF EMPLOYEE Statistics - 0 recursive calls 0 db block gets 5418 consistent gets 3144 physical reads 0 redo size 370 bytes sent via SQL*Net to client 425 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL analyze table empployee ESTIMATE statistics; analyze table empployee ESTIMATE statistics * ERROR 位于第 1 行: ORA-00942: 表或视图不存在 SQL analyze table employee ESTIMATE statistics; 表已分析。 SQL select count(*) from employee; COUNT(*) - 299999 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1) 1 0 SORT (AGGREGATE) 2 1 BITMAP CONVERSION (COUNT) 3 2 BITMAP INDEX (FAST FULL SCAN) OF IDX_BMP_EMP_SEX Statistics - 0 recursive calls 0 db block gets 12 consistent gets 8 physical reads 0 redo size 370 bytes sent via SQL*Net to client 425 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL analyze table employee delete statistics; 表已分析。 SQL exec DBMS_STATS.GATHER_table_STATS(ownname = user,tabname = employee, method_opt = for all indexed columns size 1); PL/SQL 过程已成功完成。 SQL select count(*) from employee; COUNT(*) - 299999 Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FAST FULL SCAN) OF EMPLOYEE_PK (UNIQUE) (Cost=4 Card=299999) Statistics - 14 recursive calls 0 db block gets 680 consistent gets 670 physical reads 0 redo size 370 bytes sent via SQL*Net to client 425 bytes received