一个connect by查询语句的优化

最新推荐文章于 2023-07-05 10:55:38 发布
最新推荐文章于 2023-07-05 10:55:38 发布
阅读量486 收藏 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
关注
Oracle 树查询 性能优化纪实（start with, connect by）
iteye_21324的博客
12-08 565
在项目中做权限控制时，需要用组织阶层来控制能够访问的数据，比如A组织的人可以看到其下属组织的人员数据，或者只有A组织是B组织上级的时候才有看B组织人员数据的权利。根据需求需要构筑DB的表结构，如下（ORG_RANK） 组织ID(PK) 上位组织ID ORG_ID HIGH_ORG_ID     根据上面的结构，使用Oracle的树查询语句(start with和con...
ORACLE进阶（十）start with connect by 实现递归查询_oracle start with connect by优化
最新发布
04-21 1081
在工作中，查看到类似于如下的SQL语句：orglev,orgcde,我们经常会将一个比较复杂的目录树存储到一个表中。或者将一些部门存储到一个表中，而这些部门互相有隶属关系。这个时候你就会用到。oracle 提供了语法结构可以实现递归查询。connect by是结构化查询中用到的，其基本语法是：select … from tablename start with 条件1 connect by prior 条件2 where 条件3;
oracle connect by 表,Oracle 有表连接的connect by 的优化
weixin_39611047的博客
04-09 346
代入变量值，加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 ...
About - CONNECT_BY_FILTERING
weixin_30776863的博客
11-19 310
再次碰到一个诡异的事情，同样的SQL在两个类似数据库中执行时间相差非常之多。直观反应就是执行计划不一样，果不其然。 在跑得很快的环境中的执行计划, 但是在另外一个环境中的执行计划却是， 从执行计划中可以看到低效的执行计划的consistent gets相当之高，差别就在于connect by走了 “CONNECT BY NO FILTERING WITH SW(UNIQUE)"...
Operation CONNECT BY WITH FILTERING
jackred的专栏
07-02 774
It happened to me several times to being asked about the mysterious full table scan in CONNECT BY operations. In this post I would like to share with you some of the information I wrote about it in
深入理解connect by level<xxx
热门推荐
haiross的专栏
12-26 3万+
对于connect by，现在大多数人已经很熟悉了 connect by中的条件就表示了父子之间的连接关系 比如 connect by id=prior  pid 但如果connect by中的条件没有表示记录之间的父子关系（如 connect by里没有 id=prior  pid） 那会出现什么情况？ 常见的，connect by会在构造序列的时候使用 用select ro
从一个MySQL的例子来学习查询语句
12-15
本文将通过一个简单的MySQL查询语句示例来讲解如何使用SQL进行数据的查询、插入和更新操作，以此来加深对MySQL的理解。 首先，例子中的PHP脚本用于记录网站访问者的IP地址和访问次数。它首先获取用户的IP地址，然后...
mysql树状查询效率_Oracle 树查询 性能优化纪实（start with, connect by）
weixin_31864641的博客
02-08 584
在项目中做权限控制时，需要用组织阶层来控制能够访问的数据，比如A组织的人可以看到其下属组织的人员数据，或者只有A组织是B组织在项目中做权限控制时，需要用组织阶层来控制能够访问的数据，比如A组织的人可以看到其下属组织的人员数据，或者只有A组织是B组织上级的时候才有看B组织人员数据的权利。根据需求需要构筑DB的表结构，如下(ORG_RANK)组织ID(PK) 上位组织IDORG_ID HIGH_ORG...
MySQL SQL语句优化的10条建议
09-10
7. **避免ORDER BY RAND()**：这种操作可能导致全表扫描，对于大数据量，应尽量避免，可以考虑先随机生成一个ID，然后根据ID进行查询。 8. **拆分大DELETE或INSERT语句**：大语句可能导致长时间的表锁定，影响其他...
oracle查询语句大全(oracle 基本命令大全一)
12-15
` 这个查询返回`mview_log`表的所有列名和表名。 执行SQL脚本文件可以使用`@`符号，如`SQL>@PATH/filename.sql;`。在C#中，如果你需要通过OLEDB提供者执行多条SQL语句，需要遵守Oracle的规范，使用`BEGIN`和`END;`...
oracle connect by很强，但是要慎用，不然有你哭的时候
qq_29061315的博客
07-05 5601
6000字说透 connect by， connect by看这一篇就够了~
1次connect by的优化
cr13324309138的博客
08-06 489
昨天一条sql造成了大量的CBC latch等待，CPU超过了90%，sql每次执行消耗了6441854逻辑读，耗时130s，只返回3行，基本全部消耗在CPU上，sql文本以及执行计划如下： sql文本 ...
connect by递归查询
cr13324309138的博客
08-05 368
对于递归查询，在11g之前只能用start with ... connnect by prior来实现，11g之后可用with as来实现，这里的讨论只基于connect by的形式。 在10.2.0...
[Oracle SQL]最基本的connect by的用法
weixin_33697898的博客
06-18 853
最基本的connect by的用法: 需求1: 我需要下钻所有的树得到level和parent_name create table test_lvl1 (id number, parent_id number, name varchar2(10)); insert into test_lvl1 values (1,null,'SLI1'); insert into...
oracle connect by用法篇
一个懒鬼的博客
05-06 8824
1、基本语法 select * from table [start with condition1] connect by [prior] id=parentid 一般用来查找存在父子关系的数据，也就是树形结构的数据；其返还的数据也能够明确的区分出每一层的数据。 start with condition1 是用来限制第一层的数据，或者叫根节点数据；以这部分数据为基础来查找第二层数据，然后以第二层数据查找第三层数据以此类推。 connect by [prior]id=paren.
Hierarchical query-层次查询之START WITH CONNECT BY用法
还不算晕的专栏
11-01 2477
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
Start With... ... Connect By Prior语句
寒潭清
11-16 966
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 213
SQL> select employee_id ,manager_id ,first_name ,last_name 2 from emp 3 start with employee_id =1--- 定义层次化查...
VastbaseG100中递归查询：connect by的深入解析与使用
在VastbaseG100等数据库系统中，`connect by` 是一种递归查询的关键语句，主要用于在数据表中实现层级关系的数据挖掘和遍历。它在处理组织结构、父子关系或者层级关系的数据时表现出强大的能力。`connect by` 的核心...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值