Oracle数据的优化器有两种优化方法

 

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;

　　我们可以采用以下两种方法，对数据库的表和索引及簇表定期分析生成统计信息，保证应用的正常性能。

　　1. 在系统设置定时任务，执行分析脚本。

　　在数据库服务器端，我们以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

　　2. 利用ORACLE提供的程序包（PACKAGE）对相关的数据库对象进行分析。

　　有以下的程序包可以对表，索引，簇表进行分析。

　　包中的存储过程的相关参数解释如下：

　　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)

　　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 ) ;

　　该存储过程可对特定的表，索引和簇表进行分析。例如，对SCOTT用户的EMP表，进行50%的抽样分析，参数如下：

　　DBMS_DDL.ANALYZE_OBJECT("TABLE", "SCOTT", "EMP", "ESTIMATE", NULL,50);
2)

　　DBMS_UTILITY.ANALYZE_SCHEMA (

　　SCHEMA VARCHAR2,

　　METHOD VARCHAR2,

　　ESTIMATE_ROWS NUMBER DEFAULT NULL,

　　ESTIMATE_PERCENT NUMBER DEFAULT NULL,

　　METHOD_OPT VARCHAR2 DEFAULT NULL ) ;

　　DBMS_UTILITY.ANALYZE_DATABASE (

　　METHOD VARCHAR2,

　　ESTIMATE_ROWS NUMBER DEFAULT NULL,

　　ESTIMATE_PERCENT NUMBER DEFAULT NULL,

　　METHOD_OPT VARCHAR2 DEFAULT NULL );

　　其中，ANALYZE_SCHEMA用于对某个用户拥有的所有TABLE，INDEX和CLUSTER的分析统计。ANALYZE_DATABASE用于对整个数据库进行分析统计。

　　3) DBMS_STATS是在ORACLE8I中新增的程序包，它使统计数据的生成和处理更加灵活方便，并且可以并行方式生成统计数据。在程序包中的以下过程分别分析统计TABLE，INDEX，SCHEMA，DATABASE级别的信息。

　　DBMS_STATS.GATHER_TABLE_STATS

　　DBMS_STATS.GATHER_INDEX_STATS

　　DBMS_STATS.GATHER_SCHEMA_STATS

　　DBMS_STATS.GATHER_DATABASE_STATS

　　在这里，我们以数据库JOB的方式，定时对数据库中SCOTT模式下所有的表和索引进行分析：

　　在SQL*PLUS下运行：

　　VARIABLE jobno number;

　　BEGIN

　　DBMS_JOBS.SUBMIT ( :jobno ,

　　" dbms_utility.analyze_schema ( "scott", "estimate", NULL, 20) ; ",

　　sysdate, "sysdate+30");

　　commit;

　　end;

　　/

　　Statement processed.

　　Print jobno

　　JOBNO

　　-------------

　　16

　　以上作业，每隔一个月用DBMS_UTILITY.ANALYZE_SCHEMA对用户SCOTT的所有表，簇表和索引作统计分析

Oracle中的CBO方式
　　Oracle的优化器有两种优化方式,即基于规则的优化方式(Rule-Based Optimization，简称为RBO)和基于代价的优化方式(Cost-Based Optimization，简称为CBO)，在Oracle8及以后的版本,Oracle强列推荐用CBO的方式
　　RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走索引。
　　CBO方式：它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时候过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。
　　Examda提示：主索引不一定就是优的，比如一个表只有两行数据，一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时全表扫描(full table scan)是最好
　　优化模式包括Rule、Choose、First rows、All rows四种方式：
　　Rule：基于规则的方式。
　　Choose：默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息，则走CBO的方式，如果表或索引没统计信息，表又不是特别的小，而且相应的列有索引时，那么就走索引，走RBO的方式。
　　First Rows：它与Choose方式是类似的，所不同的是当一个表有统计信息时，它将是以最快的方式返回查询的最先的几行，从总体上减少了响应时间。
　　All Rows:也就是我们所说的Cost的方式，当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐量。没有统计信息则走RBO的方式。