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

目录
相关文章
|
9天前
|
SQL 数据采集 Oracle
实时计算 Flink版产品使用问题之如何读取oracle中的blob类型的数据
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
10天前
|
Oracle 关系型数据库 数据库
实时计算 Flink版产品使用问题之连接到Oracle数据库但无法读取到数据,是什么导致的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2天前
|
SQL Oracle 关系型数据库
Oracle win 下expdp 导出数据出错的问题
Oracle win 下expdp 导出数据出错的问题
|
9天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用问题之在进行Oracle数据库的全量同步时,只同步了一条数据而源表实际上包含多条数据,是什么原因
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
10天前
|
资源调度 Oracle 关系型数据库
实时计算 Flink版产品使用问题之同步oracle表时,任务不报错,但是读不到数据,是什么导致的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
20天前
|
Oracle 关系型数据库 数据库
oracle误删除数据的恢复方法
oracle误删除数据的恢复方法
14 0
|
1月前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之是否支持多并行度采集Oracle全量数据
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
SQL Oracle 关系型数据库
oracle数据库带或不带数据导入导出操作大全
oracle数据库带或不带数据导入导出操作大全
|
Oracle 关系型数据库 数据库
|
Oracle 关系型数据库 数据库

推荐镜像

更多