在oracle数据库中，影响优化器生成执行计划的因素有哪些

9i前的RBO不熟，也就不敢妄言。

关于10g后的CBO，谈下我的理解。

首先，影响优化器执行计划最主要的因素是统计信息。优化器根据统计信息情况，单表上选择全表扫描还是索引。表联接方式上选择嵌套，哈希还是合并排序。不同的统计信息将会生成不同的执行计划。很多时候发现之前跑的好好的sql，突然变慢了，多数情况下重新收集下统计信息便解决了。统计信息这一块需要关注直方图这一块，很多生产环境都存在数据倾斜的情况，如若未准确收集直方图，那么生成的执行计划便有失偏颇。

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

第二点，sql语句的写法问题。比如字段上有索引，但谓词条件写成like '%xxx%'方式，将导致该字段上索引不可用。比如表连接方式用<>之类，将无法使用hash join。其实这些与其说是写法问题，倒不如说是oracle自身有一定的编码规则，符合该规则条件，方可用到index之类。

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

第三点，也是最无奈的一点，CBO的自身缺陷问题。很多时候，统计信息是最新的，也符合写法规范，但CBO就是不生成我们所期待的执行计划。这个时候，通常要改变sql语句的逻辑写法，比如标量子查询可否换成左连接，用with as替换一些子查询等，以期待oracle生成更高效率的执行计划。另外，就是使用hint来迫使CBO生成你所期待的执行计划，但CBO不一定就范。

谈及缺陷，也不算不上缺陷，尤其是表越多，将会出现更多排列的可能性，oracle不可能将所有执行计划都生成出来然后选择一个最优的，定是按照一定比例择取，但具体多少，我不详。也就是说，DBA完全可以凭借自身对你所管理的oracle了解程度，给sql语句指定一个最优的执行计划。

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

其他还有一些细节问题，可在日常工作中慢慢体会，cbo还是相当强大的。

SQL Server查询分析器里有一个叫做”显示实际执行计划”的选项(位于”查询”下拉菜单中)。如果打开了这个选项，那么无论何时在查询分析器中运行一个查询，都会得到一个显示在单独窗口的查询执行计划(以图形的格式)。这个执行计划就是描述了这个语句的执行过程

此外,在SQL Server 2005中，有3个命令可以用来查看关于SQL语句或批处理的查询执行计划的详细信息：SET SHOWPLAN_ALL、SHOWPLAN_TEXT和SET SHOWPLAN_XML。

简单的说就是 数据库都做了哪些 *** 作。如果完整地介绍执行计划，这个1靠经验，2需要扎实的数据库基础，下面做个简单对比来说明一下吧。

SQL> select from emp;

执行计划

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

Plan hash value: 3956160932

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 14 | 546 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 14 | 546 | 3 (0)| 00:00:01 |

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

Operation：TABLE ACCESS FULL全表扫描

Name ：EMP 全表扫描了emp表

Rows ：14 扫描了14行

Bytes ：546

Cost (%CPU)：占用CPU资源3%

所用时间：00:00:01

SQL> select from dept where deptno=10;

执行计划

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

Plan hash value: 2852011669

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |

| 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |

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

id：从2往1看，而前面带星号的为where子句中的数据库 *** 作过程。

SQL 语句 “where deptno=10 ” 使用到了DEPT表中的主键索引PK_DEPT，所以数据库在此步骤进行了一次INDEX UNIQUE SCAN *** 作，在扫描索引字段之后找出deptno为10所在位置，并在dept表中将所之相对应的数据，则数据库需要TABLE ACCESS BY INDEX ROWID。

Rows ：1 扫描了1行

Bytes ：20

Cost (%CPU)：占用CPU资源1%

所用时间：00:00:01

因为使用到了索引，所以Rows，Bytes，Cost 都要比全表扫描要好，这正是我们想要的~

而这就是此条语句在oracle数据库内部的一次执行计划，但这只是表面的直接的易懂的现实，在数据库内部还会有更复杂的其他运算。

ps：呼呼 绝对一个字一个字认真敲上的啊~~

在报表数据库的后台alert文件中发现了这个错误 简单记录一下问题的诊断和解决过程 数据库版本 for Solaris sparc

寻找产生问题的真正原因

在第一篇文章中 定位了问题并且找到了解决方法;在第二篇文章中 找到了导致源数据库和目标数据库执行计划不同的原因

但是到目前为止 还没有找到这个问题产生的真正原因

首先理一下思路 根据第一篇文章的描述 产生ORA 问题的原因是由于一个大数据量的插入语句选择了一个十分糟糕的执行计划 而导致Oracle选择了这个执行计划的直接原因是由于列的统计信息出现了错误 而在第二篇文章中 可以确认由于源数据库的版本为 没有使用列统计信息中的DENSITY列 所以没有引发这个问题 而在目标数据库版本为 Oracle使用了统计信息列DENSITY的值 所以Oracle认为访问ORD_HIT_M表且通过ENABLE_FLAG列进行限制 只会返回 条记录 这就导致了Oracle产生了一个错误的离谱的执行计划

现在的问题是什么导致了源数据库错误统计信息的产生

这就需要检查源数据库数据和统计的来源 因为在源数据库 上直接收集统计信息 是不会得到这种DENSITY的

经过检查发现这个 的源数据库仍然不是数据的真正源头 而真正的来源数据库版本是

发现了这个信息 那么问题的产生就不奇怪了

看一下 上这张表的统计信息

      SQL> SELECT COLUMN_NAME  NUM_DISTINCT  NUM_NULLS  DENSITY  NUM_BUCKETS  HISTOGRAM  FROM USER_TAB_COLUMNS  WHERE TABLE_NAME =  ORD_HIT_M  AND COLUMN_NAME =  ENABLE_FLAG ; COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS HISTOGRAM           ENABLE_FLAG      E    FREQUENCY

可以看到 在Oracle g使用了BUCKETS的设置 而且USER_TAB_COLUMNS添加了一个字段HISTOGRAM用来表示列的统计信息的类型

FREQUENCY类型和以往的HEIGHT BALANCED类似的列统计不同 使用FREQUENCY类型 Oracle会选择与NUM_DISTINCT相同数量的NUM_BUCKETS来进行直方图统计 而直方图统计信息方式和基于高度的统计信息是不同的 最关键的是 这种统计方式的DENSITY的结果和HEIGHT BALANCED的计算方式大不相同

因此在 g中 由于Oracle了解当前列的统计信息方式为FREQUENCY类型 因此可以根据直方图的信息得到正确的执行计划和返回记录数

      SQL> SELECT COLUMN_NAME  NUM_DISTINCT  NUM_NULLS  DENSITY  NUM_BUCKETS  HISTOGRAM  FROM USER_TAB_COLUMNS  WHERE TABLE_NAME =  ORD_HIT_M  AND COLUMN_NAME =  ENABLE_FLAG ; COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS HISTOGRAM           ENABLE_FLAG      E    FREQUENCY  row selected SQL> EXPLAIN PLAN FOR  SELECT  FROM ORD_HIT_M WHERE ENABLE_FLAG =  ;

Explained SQL> SELECT  FROM TABLE(DBMS_XPLAN DISPLAY); PLAN_TABLE_OUTPUT | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| |   | SELECT STATEMENT | |  K|  M|   ( )| |   | TABLE ACCESS FULL| ORD_HIT_M |  K|  M|   ( )|  rows selected

但是如果将统计信息导入到 数据库中 就会存在严重的问题 由于 i的数据库中没有表示统计信息类型的HISTOGRAM列 因此即使是基于FREQUENCY类型的统计信息 也会被当作基于HEIGHT BALANCED类型的统计信息

而且从 导入的统计信息可以看到 虽然直方图的统计信息被导入 但是USER_TAB_COLUMNS中的NUM_BUCKETS列的值为 也就是说 中优化器根本不会去考虑直方图信息 而是直接通过NUM_DISTINCT和DENSITY的值来确定执行计划和返回记录数

对于 版本 Oracle都使用NUM_DISTINCT的值 也就避免了问题的产生 而在 中 Oracle使用了DENSITY的值 而这个值并不是 版本的DBMS_STATS包生成的统计信息 而是从 g环境中导入的 且这个值在 g的FREQUENCY类型的统计信息中已经改变了计算方法 使得计算结果比 环境中要小得多 从而导致了 上错误执行计划的产生

显然 整个问题完全是由于版本差异造成的 这个问题说明在将 g的表导入到 环境中 最好不要导入统计信息

在导出阶段或在导入阶段设置STATISTICS = NONE 避免 g的统计信息导入到 环境中 在导入过程结束后 手工在 环境上重新收集统计信息

一旦 g的统计信息被导入到 环境中 就必须重新收集统计信息

      SQL> SELECT  FROM V$VERSION; BANNER Oracle i Enterprise Edition Release      bit Production PL/SQL Release     Production CORE   Production TNS for Linux: Version     Production NLSRTL Version     Production SQL> SELECT COLUMN_NAME  NUM_DISTINCT  NUM_NULLS  DENSITY  NUM_BUCKETS  FROM USER_TAB_COLUMNS  WHERE TABLE_NAME =  ORD_HIT_M  AND COLUMN_NAME =  ENABLE_FLAG ; COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS         ENABLE_FLAG      E   SQL> EXPLAIN PLAN FOR  SELECT  FROM ORD_HIT_M WHERE ENABLE_FLAG =  ; 已解释 SQL> SELECT  FROM TABLE(DBMS_XPLAN DISPLAY); PLAN_TABLE_OUTPUT | Id | Operation | Name | Rows | Bytes | Cost | |   | SELECT STATEMENT | |   |   |   | |   | TABLE ACCESS FULL | ORD_HIT_M |   |   |   | Predicate Information (identified by operation id):    filter( ORD_HIT_M ENABLE_FLAG = ) Note: cpu costing is off

已选择 行

这时 g的统计信息已经导入到 环境中 如果忘记重新收集统计信息就会导致这个错误的产生

      SQL> EXEC DBMS_STATS GATHER_TABLE_STATS(USER   ORD_HIT_M )

PL/SQL 过程已成功完成

      SQL> SELECT COLUMN_NAME  NUM_DISTINCT  NUM_NULLS  DENSITY  NUM_BUCKETS  FROM USER_TAB_COLUMNS  WHERE TABLE_NAME =  ORD_HIT_M  AND COLUMN_NAME =  ENABLE_FLAG ; COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS         ENABLE_FLAG        SQL> EXPLAIN PLAN FOR  SELECT  FROM ORD_HIT_M WHERE ENABLE_FLAG =  ; 已解释 SQL> SELECT  FROM TABLE(DBMS_XPLAN DISPLAY); PLAN_TABLE_OUTPUT | Id | Operation | Name | Rows | Bytes | Cost | |   | SELECT STATEMENT | |  K|  M|   | |   | TABLE ACCESS FULL | ORD_HIT_M |  K|  M|   | Predicate Information (identified by operation id):    filter( ORD_HIT_M ENABLE_FLAG = ) Note: cpu costing is off

已选择 行

lishixinzhi/Article/program/Oracle/201311/18563

你是说过去对这张表所做的查询的执行计划，这个查不了。如果这个查询很慢（或者特别耗费时间，或者特别耗费cpu资源）那么可能能通过awr报告查到语句（一般时间为1月内或者一周内），如果仅仅想查执行计划，那是不可能的。

1SQL查询语句的重写，对于一个查询可以用多种查询语句实现，但不同查询语句的数据库执行计划是不同的，一旦不能够使用索引或造成较大的内存占用会导致性能下降，因此需要对查询语句进行重写优化，最典型的例子就是notin语句使用外连接方式实现来进行优化

2创建合理的索引结构，根据查询语句的中查询条件，在关系表上建立相应的索引，如B树索引和hash索引

3修改程序业务逻辑，有些功能如果使用SQL语句实现，不但SQL语句复杂，还将导致数据库的负担增加，因此可以将有些数据 *** 作的业务逻辑放到应用层进行实现，就是通过java编程实现

4修改数据库服务器相关参数，优化服务器性能

第一步：应用程序把查询SQL语句发给服务器端执行

我们在数据层执行SQL语句时，应用程序会连接到相应的数据库服务器，把SQL语句发送给服务器处理。

第二步：服务器解析请求的SQL语句

SQL计划缓存，经常用查询分析器的朋友大概都知道这样一个事实，往往一个查询语句在第一次运行的时候需要执行特别长的时间，但是如果你马上或者在一定时间内运行同样的语句，会在很短的时间内返回查询结果。原因是：

服务器在接收到查询请求后，并不会马上去数据库查询，而是在数据库中的计划缓存中找是否有相对应的执行计划。如果存在，就直接调用已经编译好的执行计划，节省了执行计划的编译时间。

如果所查询的行已经存在于数据缓冲存储区中，就不用查询物理文件了，而是从缓存中取数据，这样从内存中取数据就会比从硬盘上读取数据快很多，提高了查询效率。数据缓冲存储区会在后面提到。

如果在SQL计划缓存中没有对应的执行计划，服务器首先会对用户请求的SQL语句进行语法效验，如果有语法错误，服务器会结束查询 *** 作，并用返回相应的错误信息给调用它的应用程序。

注意：此时返回的错误信息中，只会包含基本的语法错误信息，例如select写成selec等，错误信息中如果包含一列表中本没有的列，此时服务器是不会检查出来的，因为只是语法验证，语义是否正确放在下一步进行。

语法符合后，就开始验证它的语义是否正确。例如，表名、列名、存储过程等等数据库对象是否真正存在，如果发现有不存在的，就会报错给应用程序，同时结束查询。

接下来就是获得对象的解析锁，我们在查询一个表时，首先服务器会对这个对象加锁，这是为了保证数据的统一性，如果不加锁，此时有数据插入，但因为没有加锁的原因，查询已经将这条记录读入，而有的插入会因为事务的失败会回滚，就会形成脏读的现象。

接下来就是对数据库用户权限的验证。SQL语句语法，语义都正确，此时并不一定能够得到查询结果，如果数据库用户没有相应的访问权限，服务器会报出权限不足的错误给应用程序，在稍大的项目中，往往一个项目里面会包含好几个数据库连接串，这些数据库用户具有不同的权限，有的是只读权限，有的是只写权限，有的是可读可写，根据不同的 *** 作选取不同的用户来执行。稍微不注意，无论你的SQL语句写的多么完善，完美无缺都没用。

解析的最后一步，就是确定最终的执行计划。当语法、语义、权限都验证后，服务器并不会马上给你返回结果，而是会针对你的SQL进行优化，选择不同的查询算法以最高效的形式返回给应用程序。例如在做表联合查询时，服务器会根据开销成本来最终决定采用hashjoin,mergejoin，还是loopjoin，采用哪一个索引会更高效等等。不过它的自动化优化是有限的，要想写出高效的查询SQL还是要优化自己的SQL查询语句。

当确定好执行计划后，就会把这个执行计划保存到SQL计划缓存中，下次在有相同的执行请求时，就直接从计划缓存中取，避免重新编译执行计划。

第三步：语句执行

服务器对SQL语句解析完成后，服务器才会知道这条语句到底表态了什么意思，接下来才会真正的执行SQL语句。

此时分两种情况：

如果查询语句所包含的数据行已经读取到数据缓冲存储区的话，服务器会直接从数据缓冲存储区中读取数据返回给应用程序，避免了从物理文件中读取，提高查询速度。

如果数据行没有在数据缓冲存储区中，则会从物理文件中读取记录返回给应用程序，同时把数据行写入数据缓冲存储区中，供下次使用。

说明：SQL缓存分好几种，这里有兴趣的朋友可以去搜索一下。有时因为缓存的存在，使得我们很难马上看出优化的结果，因为第二次执行因为有缓存的存在，会特别快速，所以一般都是先消除缓存，然后比较优化前后的性能表现，这里有几个常用的方法：

1DBCC

2从缓冲池中删除所有清除缓冲区。

3DBCC

4从过程缓存中删除所有元素。

5DBCC

6从所有缓存中释放所有未使用的缓存条目。

SQLServer2005数据库引擎会事先在后台清理未使用的缓存条目，以使内存可用于当前条目。但是，可以使用此命令从所有缓存中手动删除未使用的条目。

这只能基本消除SQL缓存的影响，目前好像没有完全消除缓存的方案，如果大家有，请指教。

执行顺序：

FROM子句返回初始结果集。

WHERE子句排除不满足搜索条件的行。

GROUPBY子句将选定的行收集到GROUPBY子句中各个唯一值的组中。

选择列表中指定的聚合函数可以计算各组的汇总值。

此外，HAVING子句排除不满足搜索条件的行。

计算所有的表达式；

使用orderby对结果集进行排序。

查找你要搜索的字段。

以上就是关于在oracle数据库中，影响优化器生成执行计划的因素有哪些全部的内容，包括:在oracle数据库中，影响优化器生成执行计划的因素有哪些、SQL2005中怎么查看数据库的执行过程，比如说那个表或者存储过程、oracle 执行计划，有人能跟我解释一下 执行计划里面的 operation字段 的含义吗等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！