一个connect by查询语句的优化

最新推荐文章于 2022-05-06 10:33:17 发布
最新推荐文章于 2022-05-06 10:33:17 发布
阅读量444 收藏 1
点赞数
文章标签： 数据库 大数据

问题描述:

数据库从10g升级到11g后发现以下查询语句的性能下降,其平均执行时间从0.04秒增长到了0.32秒

  1. with q as
  2.                   (Select trading, calculated_date,
  3.            rank() over (partition by calculated_date order by mkt_for_nda_id nulls last) rank1
  4.                    From Operating_Calendar
  5.                    Where trunc(CALCULATED_DATE) BETWEEN v3_stdt and v3_eddt
  6.                    AND (mkt_for_nda_id = v_mkt_nda_id or geo_for_Nda_id in
  7.                        (Select Nda_id From Geog
  8.                            Where Nda_id in
  9.                            (Select Geo_in_nda_id From Geog_Structure
  10.                             Start With Geo_with_nda_id = v_geog_nda_id
  11.                             Connect By Prior Geo_in_nda_id = Geo_with_nda_id)
  12.               UNION (SELECT NDA_ID FROM GEOG WHERE NDA_ID = v_geog_nda_id)
  13.                            )
  14.                 )
  15.                   )
  16.                select T_calcdt_reason_Type(calculated_date, NULL)
  17.                bulk collect into v_calcdt_reason -- bulk collect into a table object
  18.            from q
  19.            where rank1 = 1 and trading = \'H\';


分析:

该语句中包括一个connect by查询, 对于connect by查询来说, 数据库在解析阶段很难根据统计信息对其cardinality值作出准确估计,所以oracle在为这种查询选择执行计划时,由于该局限性,常常难以得到令人满意的结果,如下例:

  1. explain plan for SELECT GEO_IN_NDA_ID FROM GEOG_STRUCTURE START WITH GEO_WITH_NDA_ID = 858 CONNECT BY PRIOR GEO_IN_NDA_ID = GEO_WITH_NDA_ID;
  2. PLAN_TABLE_OUTPUT
  3. ----------------------------------------------------------------------------------------------------
  4. Plan hash value: 498378953
  5.  
  6. ---------------------------------------------------------------------------------------------------
  7. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
  8. ---------------------------------------------------------------------------------------------------
  9. | 0 | SELECT STATEMENT | | 2 | 52 | 3 (34)| 00:00:01
  10. |* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | |
  11. | 2 | INDEX FAST FULL SCAN | GST_UK1 | 2356 | 18848 | 2 (0)| 00:00:01
  12. -------------------------------------------------------------------------------------------------
  13. explain plan for SELECT GEO_IN_NDA_ID FROM GEOG_STRUCTURE START WITH GEO_WITH_NDA_ID = 100 CONNECT BY PRIOR GEO_IN_NDA_ID = GEO_WITH_NDA_ID;
  14. PLAN_TABLE_OUTPUT
  15. -------------------------------------------------------------------------------------------------
  16. Plan hash value: 498378953
  17. -------------------------------------------------------------------------------------------------
  18. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
  19. -------------------------------------------------------------------------------------------------
  20. | 0 | SELECT STATEMENT | | 2 | 52 | 3 (34)| 00:00:01 |* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | |
  21. | 2 | INDEX FAST FULL SCAN | GST_UK1 | 2356 | 18848 | 2 (0)| 00:00:01
  22. -------------------------------------------------------------------------------------------------
  23. SQL> select count(*) from GEOG_STRUCTURE
  24.         START WITH GEO_WITH_NDA_ID = 858
  25.         CONNECT BY PRIOR GEO_IN_NDA_ID = GEO_WITH_NDA_ID;
  26.   COUNT(*)
  27. ----------
  28.          7
  29. SQL> select count(*) from GEOG_STRUCTURE
  30.         START WITH GEO_WITH_NDA_ID = 100
  31.         CONNECT BY PRIOR GEO_IN_NDA_ID = GEO_WITH_NDA_ID;
  32.   COUNT(*)
  33. ----------
  34.          4

从以上测试中可看到,在给查询谓词中GEO_WITH_NDA_ID列分别指定100或858时,执行计划中cardinality的估计值均是2,但实际上他们分别为7和4. 说到这里仍然没有解释为什么前面的查询语句在数据库升级后性能发生了大幅下降, 但实际上正是这种对connect by查询cardinality估计的缺陷导致了这类查询执行计划的不稳定性.
我们来看看上述语句在10g和11g中的执行计划有什么不同:

10g: 

------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |       |       |    32 (100)|          |
|*  1 |  VIEW                                 |                    |     5 |   125 |    32  (13)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK             |                    |     5 |    80 |    32  (13)| 00:00:01 |
|*  3 |    FILTER                             |                    |       |       |            |          |
|*  4 |     TABLE ACCESS FULL                 | OPERATING_CALENDAR |    99 |  1584 |    31  (10)| 00:00:01 |
|   5 |     SORT UNIQUE                       |                    |     2 |    21 |     7  (58)| 00:00:01 |
|   6 |      UNION-ALL                        |                    |       |       |            |          |
|   7 |       NESTED LOOPS                    |                    |     1 |    17 |     4  (25)| 00:00:01 |
|*  8 |        VIEW                           | VW_NSO_1           |     1 |    13 |     3  (34)| 00:00:01 |
|   9 |         SORT UNIQUE                   |                    |     1 |     8 |     3  (34)| 00:00:01 |
|* 10 |          CONNECT BY WITH FILTERING    |                    |       |       |            |          |
|  11 |           TABLE ACCESS BY INDEX ROWID | GEOG_STRUCTURE     |     1 |     8 |     2   (0)| 00:00:01 |
|* 12 |            INDEX RANGE SCAN           | GST_IND03          |     1 |       |     1   (0)| 00:00:01 |
|  13 |           NESTED LOOPS                |                    |       |       |            |          |
|  14 |            CONNECT BY PUMP            |                    |       |       |            |          |
|  15 |            TABLE ACCESS BY INDEX ROWID| GEOG_STRUCTURE     |     1 |     8 |     2   (0)| 00:00:01 |
|* 16 |             INDEX RANGE SCAN          | GST_IND03          |     1 |       |     1   (0)| 00:00:01 |
|* 17 |        INDEX RANGE SCAN               | GEO_PK             |     1 |     4 |     1   (0)| 00:00:01 |
|* 18 |       FILTER                          |                    |       |       |            |          |
|* 19 |        INDEX RANGE SCAN               | GEO_PK             |     1 |     4 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

 

11g: 

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |                    |       |       |    32 (100)|          |
|*  1 |  VIEW                                            |                    |     5 |   125 |    32  (13)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK                        |                    |     5 |    80 |    32  (13)| 00:00:01 |
|*  3 |    FILTER                                        |                    |       |       |            |          |
|*  4 |     TABLE ACCESS FULL                            | OPERATING_CALENDAR |    99 |  1584 |    31  (10)| 00:00:01 |
|   5 |     SORT UNIQUE                                  |                    |     2 |    21 |     8  (63)| 00:00:01 |
|   6 |      UNION-ALL                                   |                    |       |       |            |          |
|*  7 |       HASH JOIN                                  |                    |     1 |    17 |     5  (40)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN                          | GEO_PK             |     1 |     4 |     1   (0)| 00:00:01 |
|*  9 |        VIEW                                      | VW_NSO_1           |     2 |    26 |     3  (34)| 00:00:01 |
|  10 |         SORT UNIQUE                              |                    |     2 |    34 |     3  (34)| 00:00:01 |
|* 11 |          CONNECT BY NO FILTERING WITH SW (UNIQUE)|                    |       |       |            |          |
|  12 |           INDEX FAST FULL SCAN                   | GST_UK1            |  2356 | 18848 |     2   (0)| 00:00:01 |
|* 13 |       FILTER                                     |                    |       |       |            |          |
|* 14 |        INDEX RANGE SCAN                          | GEO_PK             |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

比较执行计划可以看到, connect by查询的执行计划从10g的CONNECT BY WITH FILTERING变为了11g中的CONNECT BY NO FILTERING WITH SW (UNIQUE).

查询相关资料可发现:这是oracle对connect by查询所引入的一种新的算法,在原来的算法中(CONNECT BY WITH FILTERING),会在每次循环中作行过滤操作,然后只读取需要的行.而在新的算法中(CONNECT BY NO FILTERING WITH SW),则会把所有需要的数据读入内存,然后统一作内存排序来过滤掉不需要的列.

个人认为前一种算法适用于从数据集中取很小一部分数据的场景,而后一种算法则适用于从大数据量中取大量数据的操作.而我们的场景正是前一种:全表有2000多行,而每次只是取其中的几行,但由于oracle对connect by语句cardinality估算的不准确性,导致oracle并不能准确地在两种算法间作出合理的抉择.

备注:这里所说的”新的算法”实际上是在10.2.0.2版本就引入了,但以前的版本中似乎并没有将其作为一种优先选择.

好在oracle提供了hint /*+ connect_by_filtering */来帮助我们干预执行计划的选择.我们给原语句加上hint:

  1. WITH Q AS
  2.   (SELECT TRADING,
  3.     CALCULATED_DATE,
  4.     RANK() OVER (PARTITION BY CALCULATED_DATE ORDER BY MKT_FOR_NDA_ID NULLS LAST) RANK1
  5.   FROM OPERATING_CALENDAR
  6.   WHERE TRUNC(CALCULATED_DATE) BETWEEN to_date(\'20130704\',\'yyyymmdd\') AND to_date(\'40000101\',\'yyyymmdd\')
  7.   AND (MKT_FOR_NDA_ID = 455
  8.   OR GEO_FOR_NDA_ID IN
  9.     (SELECT NDA_ID
  10.     FROM GEOG
  11.     WHERE NDA_ID IN
  12.       (SELECT /*+ connect_by_filtering */GEO_IN_NDA_ID
  13.       FROM GEOG_STRUCTURE
  14.         START WITH GEO_WITH_NDA_ID = 858
  15.         CONNECT BY PRIOR GEO_IN_NDA_ID = GEO_WITH_NDA_ID
  16.       )
  17.     UNION
  18.       (SELECT NDA_ID FROM GEOG WHERE NDA_ID = 858
  19.       )
  20.     ) )
  21.   )
  22. SELECT T_CALCDT_REASON_TYPE(CALCULATED_DATE, NULL)
  23. FROM Q
  24. WHERE RANK1 = 1
  25. AND TRADING = \'H\';


新的执行计划如下： 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                    |       |       |    32 (100)|          |
|*  1 |  VIEW                                      |                    |    44 |  1100 |    32  (13)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK                  |                    |    44 |   704 |    32  (13)| 00:00:01 |
|*  3 |    FILTER                                  |                    |       |       |            |          |
|*  4 |     TABLE ACCESS FULL                      | OPERATING_CALENDAR |   872 | 13952 |    31  (10)| 00:00:01 |
|   5 |     SORT UNIQUE                            |                    |     2 |    21 |    12  (42)| 00:00:01 |
|   6 |      UNION-ALL                             |                    |       |       |            |          |
|*  7 |       HASH JOIN                            |                    |     1 |    17 |     9  (23)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN                    | GEO_PK             |     1 |     4 |     1   (0)| 00:00:01 |
|*  9 |        VIEW                                | VW_NSO_1           |     2 |    26 |     7  (15)| 00:00:01 |
|  10 |         SORT UNIQUE                        |                    |     2 |    34 |     7  (15)| 00:00:01 |
|* 11 |          CONNECT BY WITH FILTERING (UNIQUE)|                    |       |       |            |          |
|  12 |           TABLE ACCESS BY INDEX ROWID      | GEOG_STRUCTURE     |     1 |     8 |     2   (0)| 00:00:01 |
|* 13 |            INDEX RANGE SCAN                | GST_IND03          |     1 |       |     1   (0)| 00:00:01 |
|  14 |           NESTED LOOPS                     |                    |     1 |    21 |     4   (0)| 00:00:01 |
|  15 |            CONNECT BY PUMP                 |                    |       |       |            |          |
|  16 |            TABLE ACCESS BY INDEX ROWID     | GEOG_STRUCTURE     |     1 |     8 |     2   (0)| 00:00:01 |
|* 17 |             INDEX RANGE SCAN               | GST_IND03          |     1 |       |     1   (0)| 00:00:01 |
|* 18 |       FILTER                               |                    |       |       |            |          |
|* 19 |        INDEX RANGE SCAN                    | GEO_PK             |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

从新的执行计划我们可以看到,oracle已经选择了CONNECT BY WITH FILTERING执行计划.

到这里,与10g中的执行计划比较会发现,新的执行计划还是与原有的计划有所不同,主要体现在第7步,原来采用的是nested loop连接,而这里使用的是hash join. view VW_NSO_1的真实结果集实际上只有几行, GEO_PK索引所在的GEOG表大约2000行,这种情况理论上nested loop会比较合适,实际测试也发现的确是采用nested loop的执行计划更高效,那为什么这里会选择hash join呢?

通过10053事件跟踪解析过程,发现11g中新增的隐含参数'_optimizer_connect_by_elim_dups'和'_connect_by_use_union_all'较为可疑,查询metalink和google也发现11g中这两个参数的引入伴随着许多的bug,所以有些专家建议在新的系统中disable掉这两个参数.我对这两个参数尚没有更为深入的理解,但可以肯定的是这里他们对connect by查询的cost结果是有影响的,进而会干扰后面的连接方式的选择.

所以这里我尝试着disable了他们: 

alter system set "_optimizer_connect_by_elim_dups" = false;
alter system set "_connect_by_use_union_all" = "old_plan_mode";

然后,可以看到执行计划回归了与10g中所采用的执行计划.
注： 如果不想在整个数据库更改以上隐含参数，可以使用opt_param hint在语句级修改。

总结：
前面的分析过程已经包括解决方案，就是使用connect_by_filtering以及修改隐含参数来影响语句执行计划的选择。由于该案例中我们知道什么样的执行计划是适用的，所以也可以选择hint, sql profile等常规手段去控制。该文的重点不是采用什么方式去解决该问题，而是了解connect by查询本身所存在的问题，以及connect by查询获取数据的可能方式，从而在遇到这类查询时心中有数。

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

转载于:http://blog.itpub.net/27243841/viewspace-1147046/

congge8840
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle查询语句大全(oracle 基本命令大全一)
12-15
1.create user username identified by password;//建用户名和密码oracle ,oracle 2.grant connect,resource,dba to username;//授权 grant connect,resource,dba,sysdba to username; 3.connect username/password/...
oracle connect by 表,Oracle 有表连接的connect by 的优化
weixin_39611047的博客
04-09 275
代入变量值，加nl的hint,走NESTED LOOPSSELECT /*+ use_nl(r,o) */r.OUT_VER_BEGIN_ID dataIDFROM DMS_DATA_RELA r, DMS_OBJ oWHERE r.DELETE_FLAG = '0'AND r.RELA_TYPE_CODE = 'parent'AND r.OUT_OBJ_CODE = o.OBJ_CODEAND ...
oracle connect by优化小探
u011165335的博客
09-23 4254
前一篇介绍了connect by的用法 https://blog.csdn.net/u011165335/article/details/82822224 这里在看看如何优化: 环境跟上一篇的一样:11g -- 表结构 drop table menu; create table menu( mid varchar2(64) not null, parent_id varchar2(64) ...
oracle connect by的cardinality预估基数问题
u011165335的博客
09-23 382
转载自老熊的三分地:http://www.laoxiong.net/category/oracle/oraperf 本文不讲述connect by 如何使用，只是提出在使用树形查询时需要考虑的一个有关于性能方面的问题。 这里提到的问题，主要是Oracle优化器在评估connect by 语句的cardinality时，存在的缺陷，下面将举例说明。 在这个例子中所使用的表，是一个真实的生产系统中的表...
Oracle 树查询 性能优化纪实（start with, connect by）
iteye_21324的博客
12-08 478
在项目中做权限控制时，需要用组织阶层来控制能够访问的数据，比如A组织的人可以看到其下属组织的人员数据，或者只有A组织是B组织上级的时候才有看B组织人员数据的权利。根据需求需要构筑DB的表结构，如下（ORG_RANK） 组织ID(PK) 上位组织ID ORG_ID HIGH_ORG_ID     根据上面的结构，使用Oracle的树查询语句(start with和con...
oracle connect by用法篇
一个懒鬼的博客
05-06 8082
1、基本语法 select * from table [start with condition1] connect by [prior] id=parentid 一般用来查找存在父子关系的数据，也就是树形结构的数据；其返还的数据也能够明确的区分出每一层的数据。 start with condition1 是用来限制第一层的数据，或者叫根节点数据；以这部分数据为基础来查找第二层数据，然后以第二层数据查找第三层数据以此类推。 connect by [prior]id=paren.
Oracle常用查询操作大全
07-01
Oracle常用的sql语句文件以及说明（doc、txt）文件 例如：analyze分析表、oracle 常用表和视图、Select Start with ... Connect by 分层次输出、SQL语句优化、函数的使用和说明。
SQL性能优化
12-20
 SQL在运行时先取出数个查询的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。  实际大部分应用中是不会产生重复的记录，推荐采用UNION ALL操作符...
SQL培训第一期
03-14
当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值；如果表之间有匹配行，则整个结果集行包含基表的数据值。 1.6.4.2 语法 select A.*, B.* from student_A A full outer join student_B B on A.Uuid...
Hierarchical query-层次查询之START WITH CONNECT BY用法
还不算晕的专栏
11-01 2436
Hierarchical query-层次查询中start with...connect by prior子句用法； connect by 是结构化查询中用到的，其基本语法是：  select ... from tablename start with 条件1  connect by 条件2  where 条件3;  例：  select * from table  start wi
mysql更改执行计划_11g中 connect by 语句执行计划改变
weixin_28702613的博客
01-28 274
从10.2.0.3升级到11.2.0.4的朋友，如果细心会发现，以下sql在11.2.0.4中执行效率变低(该sql主要是获取连接用户获取权限信息)select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and priv...
oracle update exsits,Oracle 11g 递归+ exists执行计划的改变
weixin_34280781的博客
04-04 284
有一个递归查询在Oracle 10g上运行很快，但在11g上运行不出来。SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release...
子查询中使用了递归CONNECT BY巨慢的解决方案
zwxiaole的专栏
12-09 2130
有一段sql执行速度很慢 要50s 经查询是由于在sql的子查询中使用了递归查询导致 sql如下 select (SELECT to_char(temp.rwwcsx, 'yyyy-mm-dd') from (SELECT * FROM fxyd_rwdy_mx mx order by mx.rwdy_sj desc...
CONNECT BY PRIOR语句优化案例
congan6145125的博客
12-10 859
原语句文本 SELECT * FROM (SELECT DISTINCT A.AUTHID,A.AUTHCODE,A.SUPERCODE,A.AUTHNAME,A.MENUFLAG,A.DESCRI...
Start With... ... Connect By Prior语句
寒潭清
11-16 840
Start With… … Connect By Prior语句 oracle的start with connect by prior是根据条件递归查询"树" 作用：主要是用于B树结构类型的数据递归查询，给出B树结构类型中的任意一个结点，遍历其最终父结点或者子结点 start with 子句：遍历起始条件，如果要查父结点，这里可以用子结点的列，反之亦然。 connect by 子句：连接条件。关键词prior，prior跟父节点列parentid放在一起，就是往父结点方向遍历；prior跟子结点列subi
【SQL 学习】层次化查询之CONNECT BY 和 START WITH
cojm55771的博客
09-12 141
SQL> select employee_id ,manager_id ,first_name ,last_name 2 from emp 3 start with employee_id =1--- 定义层次化查...
oracle 主表与connect by join后效能降低
最新发布
06-08
通常情况下，使用 Oracle 的 CONNECT BY 语句来查询层级数据是非常有效的。但是，如果在连接大量数据时使用 CONNECT BY，就会导致性能下降。这是因为 CONNECT BY 在执行过程中会递归遍历整个表，这会导致查询耗费...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值