问答文章1 问答文章501 问答文章1001 问答文章1501 问答文章2001 问答文章2501 问答文章3001 问答文章3501 问答文章4001 问答文章4501 问答文章5001 问答文章5501 问答文章6001 问答文章6501 问答文章7001 问答文章7501 问答文章8001 问答文章8501 问答文章9001 问答文章9501

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

发布网友 发布时间：2022-04-08 08:13

我来回答

共2个回答

懂视网 时间：2022-04-08 12:34 

SQL> conn scott/scott
Connected.
SQL> create table a (id int,name varchar2(10));
Table created.
SQL> create table b (id int,name varchar2(10));
Table created.
SQL> insert into a values(1,‘a1‘);
1 row created.
SQL> insert into a values(2,‘a2‘);
1 row created.
SQL> insert into b values(1,‘b1‘);
1 row created.
SQL> insert into b values(2,‘b2‘);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from table(dbms_xplan.display_cursor(null,null,‘ALLSTATS LAST‘));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8rv825dykpx1m, child number 1
-------------------------------------
select a.*,(select name from b where b.id=a.id) from a
Plan hash value: 2657529235
------------------------------------------------------------------------------------
| Id  | Operation   | Name | Starts | E-Rows | A-Rows | A-Time  | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  | 1 |    |   2 |00:00:00.01 |  8 |
|*  1 |  TABLE ACCESS FULL| B  | 2 |  1 |   2 |00:00:00.01 | 14 |
|   2 |  TABLE ACCESS FULL| A  | 1 |  2 |   2 |00:00:00.01 |  8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("B"."ID"=:B1)
Note
-----
   - dynamic sampling used for this statement (level=2)

23 rows selected.

B表被执行2次，返回2条数据。

SQL> insert into a values(3,‘a3‘);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from table(dbms_xplan.display_cursor(null,null,‘ALLSTATS LAST‘));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9rufvg18a2vfq, child number 0
-------------------------------------
select a.*,(select name from b where b.id=a.id) from a
Plan hash value: 2657529235
------------------------------------------------------------------------------------
| Id  | Operation   | Name | Starts | E-Rows | A-Rows | A-Time  | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  | 1 |    |   3 |00:00:00.01 |  8 |
|*  1 |  TABLE ACCESS FULL| B  | 3 |  1 |   2 |00:00:00.01 | 21 |
|   2 |  TABLE ACCESS FULL| A  | 1 |  3 |   3 |00:00:00.01 |  8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("B"."ID"=:B1)
Note
-----
   - dynamic sampling used for this statement (level=2)

23 rows selected.

B表被执行3次，返回2条数据。

SQL> insert into a values(4,‘a4‘);
1 row created.
SQL> insert into a values(5,‘a5‘);
1 row created.
SQL> insert into a values(6,‘a6‘);
1 row created.
SQL> insert into a values(7,‘a7‘);
1 row created.
SQL> insert into a values(8,‘a8‘);
1 row created.
SQL> insert into a values(9,‘a9‘);
1 row created.
SQL> commit;
Commit complete.
SQL> select a.*,(select name from b where b.id=a.id) from a;
 ID NAME       (SELECTNAM
---------- ---------- ----------
  1 a1       b1
  2 a2       b2
  3 a3
  4 a4
  5 a5
  6 a6
  7 a7
  8 a8
  9 a9
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,‘ALLSTATS LAST‘));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8rv825dykpx1m, child number 1
-------------------------------------
select a.*,(select name from b where b.id=a.id) from a
Plan hash value: 2657529235
------------------------------------------------------------------------------------
| Id  | Operation   | Name | Starts | E-Rows | A-Rows | A-Time  | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  | 1 |    |   9 |00:00:00.01 |  8 |
|*  1 |  TABLE ACCESS FULL| B  | 9 |  1 |   2 |00:00:00.01 | 63 |
|   2 |  TABLE ACCESS FULL| A  | 1 |  2 |   9 |00:00:00.01 |  8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("B"."ID"=:B1)
Note
-----
   - dynamic sampling used for this statement (level=2)

23 rows selected.

B表被执行9次，返回2行数据，说明a表向b传值，能匹配上就返回，匹配不上就返回null

SQL> update b set name=‘b1‘;
2 rows updated.
SQL> commit;
Commit complete.
SQL> select a.*,(select name from b where b.id=a.id) from a;
 ID NAME       (SELECTNAM
---------- ---------- ----------
  1 a1       b1
  2 a2       b1
  3 a3
  4 a4
  5 a5
  6 a6
  7 a7
  8 a8
  9 a9
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,‘ALLSTATS LAST‘));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8rv825dykpx1m, child number 1
-------------------------------------
select a.*,(select name from b where b.id=a.id) from a
Plan hash value: 2657529235
------------------------------------------------------------------------------------
| Id  | Operation   | Name | Starts | E-Rows | A-Rows | A-Time  | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  | 1 |    |   9 |00:00:00.01 |  8 |
|*  1 |  TABLE ACCESS FULL| B  | 9 |  1 |   2 |00:00:00.01 | 63 |
|   2 |  TABLE ACCESS FULL| A  | 1 |  2 |   9 |00:00:00.01 |  8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("B"."ID"=:B1)
Note
-----
   - dynamic sampling used for this statement (level=2)

23 rows selected.

理想状态下，a.id为主键，没有重复值，那么a表返回多少行，b表就要被执行多少次。

标量子查询改写:

1
SQL> select * from a;
 ID NAME
---------- ----------
  1 a1
  2 a2
SQL> select * from b;
 ID NAME
---------- ----------
  1 b1
  2 b2
SQL> select name,(select name from b where b.id=a.id) from a;
NAME    (SELECTNAM
---------- ----------
a1    b1
a2    b2

改写：

SQL> select a.name,b.name from a,b where a.id=b.id(+);
NAME    NAME
---------- ----------
a1    b1
a2    b2

oracle标量子查询

标签：oracle

热心网友 时间：2022-04-08 09:42

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还是相当强大的。
声明声明：本网页内容为用户发布，旨在传播知识，不代表本网认同其观点，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。E-MAIL:11247931@qq.com
洛阳火车站到洛阳关林的钢厂怎么坐公车去?钢厂目前是否分为三个小... 李永昌的《桃花运》 歌词 失眠特效药有哪些 失眠有什么快速特效药 长期失眠用什么药最好?失眠治疗特效药有哪些 失眠怎么办办,有没有什么特效药 本人严重失眠,有特效药吗? 长期焦虑失眠怎么办?有没有好的特效药? 离婚了,小孩抚养费对方拖着不给,玩失踪,怎么办? 小孩抚养费前夫每月都拖着不给怎么解决 d5渲染器界面显示不全 d5渲染器达不到官方的速度? 影视后期工作所需的电脑配置？不要求最好的，但是起码渲染高清毫无压力。 推荐3DMAX PS 能跑起来的,渲染出色的电脑配置 d5渲染器打不开是显卡问题吗? 3dmax 作图渲染和CAD制图 电脑配置要求 matebookxpro MX150显卡 会影响D5渲染器的运行吗? 笔记本Nvidia1660ti可以用D5渲染器吗? 华米手表遥控三星手机拍照怎么连接 华米手表怎么插卡 Amazfit华米手表的电量如何查看 2米等于多少分米等于多少厘米30平方分米等于多少平方米3.5米等于多少米多少分？ 0·52m等于多少dm? 5.2m=()dm oracle标量子查询 与外连接 哪个好 山东省临沂市平邑县农村信用合作社个人贷款利率 山东省公积金贷款利率是多少 想知道山东农村信用社今年的贷款利息是多少?一至三年? 山东省庆云县得贷款利率是多少 浅绿色呢料衣服染色后怎么办 美的ECO节能模式的介绍 苹果6plus照片为什么左右翻转 苹果6plus照片为什么左右翻转? 新中国的伟大成就 新中国的伟大成就有哪些? 沃柑泡药有了回应,到底是怎样回应的? 新中国建立以来的伟大成就有哪些? 谁有棒棒堂男孩的详细资料? 模范棒棒堂第一届所有底迪的本名 棒棒堂成员介绍 棒棒堂第二代成员简介是什么 模范棒棒堂第一代到第七代成员名单(要真实姓名) 这段自我简介用日语应该怎么读 棒棒堂王子的发型要怎么弄啊? 棒棒堂男生都叫什么名字 模范棒棒堂里terry,毛弟,阿本的资料! 棒棒堂资料 如何用SQL语句取出数据库中的特定一条数据? iphone6为什么突然不能删除程序 苹果iPhone 6不能删除软件,怎么解决?
懂视IT 51dongshi.com 版权所有
Copyright © 2019-2024