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');
然后调整一下脚本,并执行:
SQL>@C:\ANALYZE_TAB.SQL
可以将该脚本放到服务器端并设置自动执行。
2.使用Oracle提供的过程:DBMS_DDL.ANALYZE_OBJECT,该过程可以对某个特定用户的特定表执行统计。例如:
完全统计:
SQL>execute dbms_ddl.analyze_object('TABLE','DINYAR','DINYA_TEST01','COMPUTE');
PL/SQL procedure successfully completed
SQL>
50%抽样统计
SQL>execute 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优化器优化时优化路径的准确性。
Oracle提供的包中的其他功能请参考文档:<>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11893231/viewspace-683241/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11893231/viewspace-683241/