ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【优化】Oracle 执行计划

【优化】Oracle 执行计划

原创 Linux操作系统 作者:散叶涔 时间:2012-04-18 11:01:49 0 删除 编辑

1,什么是执行计划

所谓执行计划,顾名思义,就是对一个查询任务,做出一份怎样去完成任务的详细方案。举个生活中的例子,我从珠海要去英国,我可以

选择先去香港然后转机,也可以先去北京转机,或者去广州也可以。但是到底怎样去英国划算,也就是我的费用最少,这是一件值得考究

的事情。同样对于查询而言,我们提交的SQL仅仅是描述出了我们的目的地是英国,但至于怎么去,通常我们的SQL中是没有给出提示信息

的,是由数据库来决定的。

  我们先简单的看一个执行计划的对比:

  SQL> set autotrace traceonly

  执行计划一:

  SQL> select count(*) from t;
  COUNT(*)
  ----------
  24815
  Execution Plan
  0   SELECT STATEMENT ptimizer=CHOOSE
  1  0  SORT (AGGREGATE)
  2  1   TABLE Access (FULL) OF 'T'

  执行计划二:

  SQL> select count(*) from t;
  COUNT(*)
  24815
  Execution Plan
  0   SELECT STATEMENT ptimizer=CHOOSE (Cost=26 Card=1)
  1  0  SORT (AGGREGATE)
  2  1   INDEX (FULL SCAN) OF 'T_INDEX' (NON-UNIQUE) (Cost=26 Card=28180)

  这两个执行计划中,第一个表示求和是通过进行全表扫描来做的,把整个表中数据读入内存来逐条累加;第二个表示根据表中索引,把

整个索引读进内存来逐条累加,而不用去读表中的数据。但是这两种方式到底哪种快呢?通常来说可能二比一快,但也不是绝对的。这是一

个很简单的例子演示执行计划的差异。对于复杂的SQL(表连接、嵌套子查询等),执行计划可能几十种甚至上百种,但是到底那种最好呢?

我们事前并不知道,数据库本身也不知道,但是数据库会根据一定的规则或者统计信息(statistics)去选择一个执行计划,通常来说选择的是

比较优的,但也有选择失误的时候,这就是这次讨论的价值所在。

Oracle优化器模式

  Oracle优化器有两大类,基于规则的和基于代价的,在SQLPLUS中我们可以查看init文件中定义的缺省的优化器模式。

  SQL> show parameters optimizer_mode
  NAME                 TYPE  VALUE
  optimizer_mode           string  CHOOSE
  SQL>

  这是Oracle8.1.7 企业版,我们可以看出,默认安装后数据库优化器模式为CHOOSE,我们还可以设置为 RULE、

FIRST_ROWS,ALL_ROWS。可以在init文件中对整个instance的所有会话设置,也可以单独对某个会话设置:

  SQL> ALTER SESSION SET optimizer_mode = RULE;
  会话已更改。
  SQL> ALTER SESSION SET optimizer_mode = FIRST_ROWS;
  会话已更改。
  SQL> ALTER SESSION SET optimizer_mode = ALL_ROWS;
  会话已更改。

  基于规则的查询,数据库根据表和索引等定义信息,按照一定的规则来产生执行计划;基于代价的查询,数据库根据搜集的表和索引的

数据的统计信息(通过analyze 命令或者使用dbms_stats包来搜集)综合来决定选取一个数据库认为最优的执行计划(实际上不一定最优)。

RULE是基于规则的,CHOOSE表示如果查询的表存在搜集的统计信息则基于代价来执行(在CHOOSE模式下Oracle采用的是 FIRST_ROWS)

,否则基于规则来执行。在基于代价的两种方式中,FIRST_ROWS指执行计划采用最少资源尽快的返回部分结果给客户端,对于排序分页

页显示这种查询尤其适用,ALL_ROWS指以总体消耗资源最少的方式返回结果给客户端。

  基于规则的模式下,数据库的执行计划通常比较稳定。但在基于代价的模式下,我们才有更大的机会选择最优的执行计划。也由于

Oracle的很多查询方面的特性必须在基于代价的模式下才能体现出来,所以我们通常不选择RULE(并且Oracle宣称从 Oracle 10i版本数据库

开始将不再支持 RULE)。既然是基于代价的模式,也就是说执行计划的选择是根据表、索引等定义和数据的统计信息来决定的,这个统计

信息是根据 analyze 命令或者dbms_stats包来定期搜集的。首先存在着一种可能,就是由于搜集信息是一个很消耗资源和时间的动作,尤

其当表数据量很大的时候,因为搜集信息是对整个表数据进行重新的完全统计,所以这是我们必须慎重考虑的问题。我们只能在服务器空

闲的时候定期的进行信息搜集。这说明我们在一段时期内,统计信息可能和数据库本身的数据并不吻合;另外就是Oracle的统计数据本身也

存在着不精确部分(详细参考Oracle DOCUMENT),更重要的一个问题就是及时统计数据相对已经比较准确,但是Oracle的优化器的选择也

并不是始终是最优的方案。这也倚赖于Oracle对不同执行计划的代价的计算规则(我们通常是无法知道具体的计算规则的)。这好比我们决定

从香港还是从北京去英国,车票、机票等实际价格到底是怎么核算出来的我们并不知道,或者说我们现在了解的价格信息,在我们乘车前

往的时候,真实价格跟我们的预算已经发生了变化。所有的因素,都将影响我们的整个开销。

  执行计划稳定性能带给我们什么

  Oracle存在着执行计划选择失误的可能。这也是我们经常遇见的一些现象,比如总有人说我的程序在测试数据库中跑的很好,但在产

品数据库上就是跑的很差,甚至后者硬件条件比前者还好,这到底是为什么?硬件资源、统计信息、参数设置都可能对执行计划产生影响。

由于因素太多,我们总是对未来怀着一种莫名的恐惧,我的产品数据库上线后到底跑的好不好?于是Oracle提供了一种稳定执行计划的能力

,也就是把在测试环境中的运行良好的执行计划所产生的OUTLINES移植到产品数据库,使得执行计划不会随着其他因素的变化而变化。

  那么OUTLINES是什么呢?先要介绍一个内容,Oracle提供了在SQL中使用HINTS来引导优化器产生我们想要的执行计划的能力。这在

多表连接、复杂查询中特别有效。HINTS的类型很多,可以设置优化器目标(RULE、CHOOSE、FIRST_ROWS、ALL_ROWS),可以指定表

连接的顺序,可以指定使用哪个表的哪个索引等等,可以对SQL进行很多精细的控制。通过这种方式产生我们想要的执行计划的这些

HINTS,Oracle可以存储这些HINTS,我们称之为OUTLINES。通过STORE OUTLINES可以使得我们拥有以后产生相同执行计划的能力,也

就是使我们拥有了稳定执行计划的能力。

  这里想给出一个附加的说明就是,实际上,我们通过工具改写SQL,比如使用SQL EXPERT改写后的SQL,这些不仅仅是加了HINTS

而且文本都已经发生了变化的SQL,也可以存储OUTLINES,并可被应用到应用中。但这不是一定生效,我们必须测试检查是否生效。但由

于就算给了错误的OUTLINES,数据库在执行的时候,也只是忽略过去重新生成执行计划而不会返回错误,所以我们才敢放心的这么使用。

当然在Oracle文档中并没有指明可以这样做,文档中只是说明,如果存在OUTLINES的同时又在SQL中加了HINTS,则会使用OUTLINES而

忽略HINTS。这个功能在LECCO将发布的产品中会使用这一功能,这样可以将SQL EXPERT的改写SQL的能力和稳定执行计划的能力结合起

来,那么我们就对不能更改源代码的应用具有了相当强大的SQL优化能力。

  也许我们会有疑问,假如稳定了执行计划,那还搜集统计信息干吗?这是因为几个原因造成的,首先,现在的执行计划对于未来发生了

变化的数据未必就是合适的,存在着当前的执行计划不满足未来数据的变化后的效率,而新的统计信息的情况下所产生的执行计划也并不

是全部都合理的。那这个时候,我们可以采用新搜集的统计信息,但是却对新统计信息下不良的执行计划采用Oracle提供的执行计划稳定

性这个能力固定执行计划,这样结合起来我们可以建立满意的高效的数据库运行环境。

  我们还需要关注的一个东西,Oracle提供的dbms_stats包除了具有搜集统计信息的能力,还具有把数据库中统计信息(statistics)

export/import的能力,还具有只搜集统计信息而使得统计信息不应用于数据库的能力(把统计信息搜集到一个特定的表中而不是立即生效)

,在这个基础上我们就可以把统计信息export出来再import到一个测试环境中,再运行我们的应用,在测试环境中我们观察最新的统计信

息会导致哪些执行计划发生变化(DB EXPERT的Plan Version Tracer是模拟不同环境并自动检查不同环境中执行计划变化的工具),是变好了

还是变差了。我们可以把变差的这一部分在测试环境中使用hints或者利用工具(SQL EXPERT是在重写SQL这一领域目前最强有力的工具)产

生良好的执行计划的SQL,利用这些SQL可以产生OUTLINES,然后在产品数据库应用最新的统计信息的同时移植进这些OUTLINES。

  最后说一下我们不得不使用执行计划稳定性能力的场合。我们假定Oracle的优化器的选择都是准确的,但是优化器选择的基础就是我

们的SQL,这些SQL才从根本上决定了运行效率,这是更重要的一个优化的环节。SQL是基础(当然数据库的设计是基础的基础),一个SQL写

的好不好,就相当于我们同样是要想去英国,但是我的起点在珠海,你的起点却在西藏的最边缘偏僻的一个地方,那不管你做怎样的最优

路线选择,你都不如我在珠海去英国所花费的代价小。

2,怎么生成的

1.Explain plan
explain plan for
select * from aa;
查看结果:
select * from table(dbms_xplan.display());
2.Autotrace Set timing on --记录所用时间
Set autot trace --自动记录执行计划
3.SQL_TRACE
ORACLE SQL_TRACE

“SQL TRACE”是Oracle提供的用于进行SQL跟踪的手段,是强有力的辅助诊断工具。在日常的数据库问题诊断和解决中,“SQL TRACE”是

非常常用的方法。

一般,一次跟踪可以分为以下几步:

1、界定需要跟踪的目标范围,并使用适当的命令启用所需跟踪。


2、经过一段时间后,停止跟踪。此时应该产生了一个跟踪结果文件。


3、找到跟踪文件,并对其进行格式化,然后阅读或分析。


本文就“SQL TRACE”的这些使用作简单探讨,并通过具体案例对SQL_TRACE的使用进行说明。

3,怎么查看执行计划

从Oracle10g开始,可以通过EXPLAIN PLAN FOR查看DDL语句的执行计划了。

在9i及以前版本,Oracle只能看到DML的执行计划,不过从10g开始,通过EXPLAIN PLAN FOR的方式,已经可以看到DDL语句的执行计划

了。

这对于研究CREATE TABLE AS SELECT、CREATE MATERIALIZED VIEW AS SELECT以及CREATE INDEX,ALTER INDEX REBUILD等语

句有很大的帮助。

举个简单的例子,Oracle的文档上对于索引的建立有如下描述:

The optimizer can use an existing index to build another index. This results in a much faster index build.

如果看不到DDL的执行计划,只能根据执行时间的长短去猜测Oracle的具体执行计划,但是这种方法没有足够的说服力。但是通过DDL的执

行计划,就使得结果一目了然了。

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

表已创建。

SQL> EXPLAIN PLAN FOR
2 CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);

已解释。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 3035241083

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 57915 | 3732K| 75 (2)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IND_T_NAME | | | | |
| 2 | SORT CREATE INDEX | | 57915 | 3732K| | |
| 3 | TABLE ACCESS FULL | T | 57915 | 3732K| 41 (3)| 00:00:01 |
-------------------------------------------------------------------------------------

Note
-----
- estimated index size: 5242K bytes

已选择14行。

SQL> CREATE INDEX IND_T_OWNER_NAME ON T(OWNER, OBJECT_NAME);

索引已创建。

SQL> EXPLAIN PLAN FOR
2 CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);

已解释。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 517242163

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 57915 | 3732K| 75 (2)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IND_T_NAME | | | | |
| 2 | SORT CREATE INDEX | | 57915 | 3732K| | |
| 3 | INDEX FAST FULL SCAN| IND_T_OWNER_NAME | | | | |
-------------------------------------------------------------------------------------------

Note
-----
- estimated index size: 5242K bytes

已选择14行。

SQL> SET AUTOT ON
SQL> CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);

索引已创建。

注意,查看DDL的执行计划需要使用EXPLAIN PLAN FOR,AUTOTRACE对于DDL是无效的。


4,如何读懂执行计划:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'USER_INFO'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'USER_NUM_TABLE'
5 3 INDEX (RANGE SCAN) OF 'PK_USER_INFO' (UNIQUE)
请问以上执行计划语句是如何看的?语句的执行顺序是什么?
让我们来解释一下怎么看吧,左边的两列数字,第一列表示这条计划的编号,第二列是这条计划的父计划的编号;如果一条计划有子计划,

那么先要执行其子计划;在这个例子中:从第一条编号为0的(SELECT STATEMENT ptimizer=CHOOSE)开始,他有个子计划1(SORT

(AGGREGATE)),然后1有个子计划2,2有子计划3, 3 有子计划4和5,4是3的第一个子计划,所以先执行4(TABLE ACCESS (FULL)

OF 'USER_NUM_TABLE'),再执行5(INDEX (RANGE SCAN) OF 'PK_USER_INFO' (UNIQUE)),4和5执行完返回到其父计划3(NESTED

LOOPS),3把4和5取到的rows进行nested loops,结果再返回到2,再到1排序,再到0select.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26892340/viewspace-721500/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-04-17

  • 博文量
    44
  • 访问量
    118670