Oracle数据的优化器有两种优化方法:-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

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

简介: Oracle数据的优化器有两种优化方法:基于代价/成本的优化器(CBO)和基于规则的优化器(RBO),系统在对SQL进行优化的时候,使用哪种优化决定于初始化参数OPTIMIZER_MODE,该参数值可以设置为:CHOOSE,RULE,ALL_ROWS,FIRST_ROWS。

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

  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的方式。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: