一条connect by的BUG导致性能降低的案例-dingjun123-ChinaUnix博客

博客访问： 1393385
博文数量： 173
博客积分： 0
博客等级：民兵
技术积分： 3841
用户组：普通用户
注册时间： 2011-03-30 13:00

个人简介

About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc

文章分类

全部博文（173）

CBO（55）
Tuning&Performan（34）
Concepts&Archite（4）
TroubleShooting（5）
New Feature（7）
SQL（52）
PL/SQL（8）
Miscellaneous（3）
MySQL（5）
未分配的博文（0）

文章存档

2025年（1）

2024年（27）

2023年（28）

2022年（43）

2020年（62）

2014年（3）

2013年（9）

我的朋友

相关博文

一条connect by的BUG导致性能降低的案例

分类： Oracle

2020-07-02 12:55:53

昨晚的数据库升级很成功，大清早就去现场加入到保障行列中，突然，CPU使用率急速上升，赶紧查查啥原因，通过检查TOP SQL，得知某条SQL有很多session都在执行，查看效率，发现很久都执行不出来。SQL信息如下：

VAR v1 VARCHAR2(100);
VAR v2 VARCHAR2(100);
VAR v3 VARCHAR2(100);
EXEC :v1 := '0';
EXEC :v2 := '0';
EXEC :v3 := '15';

SELECT *
FROM   (SELECT t_1.*, rownum AS row_num
        FROM   (SELECT *
                FROM   TAB_USER a, tab_employee b
                WHERE a.employee_id = b.employee_id
                       AND (a.USER_CODE LIKE '%1000MM%' OR
                       a.user_name LIKE '%1000MM%' OR
                       a.user_id LIKE '%1000MM%')
                       AND a.dept_id IN
                       (SELECT dept_id
                            FROM   (SELECT dept_id, dept_pid
                                    FROM   tab_dept
                                    UNION
                                    SELECT dept_id, dept_pid
                                    FROM   tab_dept_his)
                            WHERE 1 = 1
                                   AND 1 = 1
                            START WITH dept_id = :v1
                            CONNECT BY PRIOR dept_id = dept_pid)
                       AND a.dept_id IS NOT NULL) t_1) t_2
WHERE t_2.row_num > :v2
       AND t_2.row_num <= :v3;

通过SQL考验看出，这是个普通的关联查询，TAB_USER和TAB_EMPLOYEE关联然后通过子查询获取结果，关键在于这个子查询，是个UNION并且是CONNECT BY，而且特别的是子查询的表tab_dept_his是个remote表（这里使用了同义词，所以看不到DBLINK），我们知道，一般语句包含remote表的时候容易引发性能问题，特别这个TAB_DEPT_HIS显然是个历史表，数据量巨大，如果不是一次性从远端拉到本地执行，或者将本地的小表送到远端执行，那么执行效率将是非常低的，走了NESTED LOOPS，可能几个小时都执行不完。

关于ORACLE里包含DBLINK的SQL TUNING，请参考之前写的一篇文章：
这条语句的执行计划如下所示：

Plan hash value: 2942662850
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1660 (100)| | | |
|* 1 | FILTER | | | | | | | |
|* 2 | VIEW | | 1806 | 1500K| 1660 (2)| 00:00:11 | | |
| 3 | COUNT | | | | | | | |
|* 4 | HASH JOIN | | 1806 | 264K| 1660 (2)| 00:00:11 | | |
|* 5 | HASH JOIN | | 1806 | 202K| 1546 (2)| 00:00:10 | | |
| 6 | VIEW | VW_NSO_1 | 1356 | 16272 | 1412 (1)| 00:00:09 | | |
| 7 | HASH UNIQUE | | 1356 | 32544 | 1412 (1)| 00:00:09 | | |
|* 8 | FILTER | | | | | | | |
|* 9 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | | | |
| 10 | VIEW | | 2 | 48 | 404 (1)| 00:00:03 | | |
| 11 | SORT UNIQUE | | 2 | 78 | 404 (1)| 00:00:03 | | |
| 12 | UNION-ALL | | | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID | TAB_DEPT | 1 | 15 | 2 (0)| 00:00:01 | | |
|* 14 | INDEX UNIQUE SCAN | PK_TAB_DEPT_SYNC | 1 | | 1 (0)| 00:00:01 | | |
| 15 | REMOTE | TAB_DEPT_HIS | 1 | 24 | 400 (0)| 00:00:03 | TTT1 | R->S |
| 16 | VIEW | | 1354 | 32496 | 1007 (1)| 00:00:07 | | |
| 17 | SORT UNIQUE | | 1354 | 92754 | 1007 (1)| 00:00:07 | | |
| 18 | UNION-ALL | | | | | | | |
| 19 | NESTED LOOPS | | 263 | 7101 | 568 (1)| 00:00:04 | | |
| 20 | NESTED LOOPS | | 263 | 7101 | 568 (1)| 00:00:04 | | |
| 21 | CONNECT BY PUMP | | | | | | | |
|* 22 | INDEX RANGE SCAN | IDX_DEPT_SYNC_DEPT_PID | 131 | | 1 (0)| 00:00:01 | | |
| 23 | TABLE ACCESS BY INDEX ROWID | TAB_DEPT | 131 | 1965 | 82 (0)| 00:00:01 | | |
| 24 | NESTED LOOPS | | 1091 | 39276 | 438 (1)| 00:00:03 | | |
| 25 | CONNECT BY PUMP | | | | | | | |
|* 26 | FILTER | | 545 | 13080 | 16 (0)| 00:00:01 | | |
| 27 | REMOTE | TAB_DEPT_HIS | | | | | TTT1 | R->S |
|* 28 | TABLE ACCESS FULL | TAB_USER | 9450 | 950K| 133 (4)| 00:00:01 | | |
| 29 | TABLE ACCESS FULL | TAB_EMPLOYEE | 57815 | 1976K| 114 (3)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:3)>TO_NUMBER(:2))
2 - filter(("T_2"."ROW_NUM"<=TO_NUMBER(:3) AND "T_2"."ROW_NUM">TO_NUMBER(:2)))
4 - access("A"."EMPLOYEE_ID"="B"."EMPLOYEE_ID")
5 - access("A"."DEPT_ID"="DEPT_ID")
8 - filter((1=1 AND 1=1))
9 - access("DEPT_PID"=PRIOR NULL)
14 - access("DEPT_ID"=:1)
22 - access("connect$_by$_pump$_008"."prior dept_id "="DEPT_PID")
26 - filter("connect$_by$_pump$_008"."prior dept_id "="DEPT_PID")
28 - filter((("A"."USER_CODE" LIKE '%1000MM%' OR "A"."USER_NAME" LIKE '%1000MM%' OR TO_CHAR("A"."USER_ID") LIKE
'%1000MM%') AND "A"."DEPT_ID" IS NOT NULL))

Remote SQL Information (identified by operation id):
----------------------------------------------------
15 - SELECT "DEPT_ID","DEPT_PID" FROM "TAB_DEPT_HIS" "TAB_DEPT_HIS" WHERE "DEPT_ID"=:1 (accessing 'TTT1' )

27 - SELECT "DEPT_ID","DEPT_PID" FROM "TAB_DEPT_HIS" "TAB_DEPT_HIS" (accessing 'TTT1' )

从执行计划看出，远程表TAB_DEPT_HIS以NESTED LOOPS方式做CONNECT BY，那么效率显然是非常慢的。
而在升级前这条SQL的执行计划是走HASH JOIN的，可以通过/*+optimizer_features_enable('10.2.0.4')*/测试是否在新库里执行计划正确，如下所示：

Plan hash value: 787558584

------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:08.65 | 12251 | 294 | 294 | | | | |
|* 1 | FILTER | | 1 | | 1 |00:00:08.65 | 12251 | 294 | 294 | | | | |
|* 2 | VIEW | | 1 | 1 | 1 |00:00:08.65 | 12251 | 294 | 294 | | | | |
| 3 | COUNT | | 1 | | 1 |00:00:08.65 | 12251 | 294 | 294 | | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 1 |00:00:08.65 | 12251 | 294 | 294 | | | | |
|* 5 | HASH JOIN SEMI | | 1 | 1 | 1 |00:00:08.65 | 12248 | 294 | 294 | 816K| 816K| 409K (0)| |
|* 6 | TABLE ACCESS FULL | TAB_USER | 1 | 30 | 1 |00:00:00.34 | 2637 | 0 | 0 | | | | |
| 7 | VIEW | VW_NSO_1 | 1 | 220K| 1 |00:00:08.31 | 9611 | 294 | 294 | | | | |
|* 8 | FILTER | | 1 | | 1 |00:00:08.31 | 9611 | 294 | 294 | | | | |
|* 9 | CONNECT BY WITH FILTERING| | 1 | | 1 |00:00:08.31 | 9611 | 294 | 294 | 19M| 1618K| 16M (0)| |
|* 10 | FILTER | | 1 | | 1 |00:00:01.01 | 1373 | 0 | 0 | | | | |
| 11 | COUNT | | 1 | | 211K|00:00:00.98 | 1373 | 0 | 0 | | | | |
| 12 | VIEW | | 1 | 220K| 211K|00:00:00.95 | 1373 | 0 | 0 | | | | |
| 13 | SORT UNIQUE | | 1 | 220K| 211K|00:00:00.91 | 1373 | 0 | 0 | 13M| 1479K| 11M (0)| |
| 14 | UNION-ALL | | 1 | | 248K|00:00:00.54 | 1373 | 0 | 0 | | | | |
| 15 | TABLE ACCESS FULL | TAB_DEPT | 1 | 62511 | 65799 |00:00:00.02 | 1373 | 0 | 0 | | | | |
| 16 | REMOTE | TAB_DEPT_HIS | 1 | 157K| 182K|00:00:00.42 | 0 | 0 | 0 | | | | |
|* 17 | HASH JOIN | | 6 | | 211K|00:00:06.91 | 8238 | 294 | 294 | 7152K| 2337K| 7009K (0)| 3072 |
| 18 | CONNECT BY PUMP | | 6 | | 210K|00:00:00.04 | 0 | 0 | 0 | | | | |
| 19 | COUNT | | 6 | | 1266K|00:00:06.23 | 8238 | 0 | 0 | | | | |
| 20 | VIEW | | 6 | 220K| 1266K|00:00:05.99 | 8238 | 0 | 0 | | | | |
| 21 | SORT UNIQUE | | 6 | 220K| 1266K|00:00:05.75 | 8238 | 0 | 0 | 13M| 1479K| 11M (0)| |
| 22 | UNION-ALL | | 6 | | 1491K|00:00:03.71 | 8238 | 0 | 0 | | | | |
| 23 | TABLE ACCESS FULL | TAB_DEPT | 6 | 62511 | 394K|00:00:00.13 | 8238 | 0 | 0 | | | | |
| 24 | REMOTE | TAB_DEPT_HIS | 6 | 157K| 1097K|00:00:03.03 | 0 | 0 | 0 | | | | |
| 25 | TABLE ACCESS BY INDEX ROWID | TAB_EMPLOYEE | 1 | 1 | 1 |00:00:00.01 | 3 | 0 | 0 | | | | |
|* 26 | INDEX UNIQUE SCAN | PK_EMPLOYEE | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | 0 | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_NUMBER(:V3)>TO_NUMBER(:V2))
2 - filter(("T_2"."ROW_NUM"<=TO_NUMBER(:V3) AND "T_2"."ROW_NUM">TO_NUMBER(:V2)))
5 - access("A"."DEPT_ID"="DEPT_ID")
6 - filter(("A"."DEPT_ID" IS NOT NULL AND ("A"."USER_CODE" LIKE '%1000MM%' OR "A"."USER_NAME" LIKE '%1000MM%' OR TO_CHAR("A"."USER_ID") LIKE
'%1000MM%')))
8 - filter((1=1 AND 1=1))
9 - access("DEPT_PID"=PRIOR NULL)
10 - filter("DEPT_ID"=:V1)
17 - access("DEPT_PID"=PRIOR NULL)
26 - access("A"."EMPLOYEE_ID"="B"."EMPLOYEE_ID")

Note
-----
- dynamic sampling used for this statement (level=2)

很显然，是新版本带来的问题，去MOS上查找，找到“Bug 13263174 - Suboptimal plan for some CONNECT BY queries (Doc ID 13263174.8)”比较符合，如下所示：

Symptoms:	Related To:
Optimizer Bad Cardinality Performance Of Query/ies Affected	Connect By / Hierarchical Queries _connect_by_use_union_all CONNECT BY

Description

The estimated cardinality for a CONNECT BY query block may vary depending
on the CONNECT BY method chosen. This can cause some CONNECT BY queries
to show poor performance due to an inappropriate plan choice.

Rediscovery Notes If you have a query using CONNECT BY, and the expected cardinality of the
 CONNECT BY query block changes depending on the method used, you may be
 hitting this bug.

Workaround Set "_connect_by_use_union_all" = "old_plan_mode"

按照MOS workaroud,设置"_connect_by_use_union_all" = "old_plan_mode"后执行计划正常，如下所示：

Plan hash value: 1856881156

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:10.41 | 12251 | 294 | 294 | | | | |
|* 1 | FILTER | | 1 | | 1 |00:00:10.41 | 12251 | 294 | 294 | | | | |
|* 2 | VIEW | | 1 | 2168 | 1 |00:00:10.41 | 12251 | 294 | 294 | | | | |
| 3 | COUNT | | 1 | | 1 |00:00:10.41 | 12251 | 294 | 294 | | | | |
|* 4 | HASH JOIN | | 1 | 2168 | 1 |00:00:10.41 | 12251 | 294 | 294 | 792K| 792K| 408K (0)| |
| 5 | NESTED LOOPS | | 1 | 30 | 1 |00:00:00.33 | 2640 | 0 | 0 | | | | |
| 6 | NESTED LOOPS | | 1 | 30 | 1 |00:00:00.33 | 2639 | 0 | 0 | | | | |
|* 7 | TABLE ACCESS FULL | TAB_USER | 1 | 30 | 1 |00:00:00.33 | 2637 | 0 | 0 | | | | |
|* 8 | INDEX UNIQUE SCAN | PK_EMPLOYEE | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | 0 | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | TAB_EMPLOYEE | 1 | 1 | 1 |00:00:00.01 | 1 | 0 | 0 | | | | |
| 10 | VIEW | VW_NSO_1 | 1 | 220K| 195K|00:00:10.03 | 9611 | 294 | 294 | | | | |
| 11 | HASH UNIQUE | | 1 | 220K| 195K|00:00:09.99 | 9611 | 294 | 294 | 12M| 3956K| 8728K (0)| |
|* 12 | CONNECT BY WITH FILTERING (UNIQUE)| | 1 | | 211K|00:00:09.86 | 9611 | 294 | 294 | 17M| 1554K| 15M (0)| |
|* 13 | FILTER | | 1 | | 1 |00:00:00.98 | 1373 | 0 | 0 | | | | |
| 14 | VIEW | | 1 | 220K| 211K|00:00:00.96 | 1373 | 0 | 0 | | | | |
| 15 | SORT UNIQUE | | 1 | 220K| 211K|00:00:00.92 | 1373 | 0 | 0 | 13M| 1479K| 11M (0)| |
| 16 | UNION-ALL | | 1 | | 248K|00:00:00.53 | 1373 | 0 | 0 | | | | |
| 17 | TABLE ACCESS FULL | TAB_DEPT | 1 | 62511 | 65799 |00:00:00.02 | 1373 | 0 | 0 | | | | |
| 18 | REMOTE | TAB_DEPT_HIS | 1 | 157K| 182K|00:00:00.41 | 0 | 0 | 0 | | | | |
|* 19 | HASH JOIN | | 6 | | 211K|00:00:08.18 | 8238 | 294 | 294 | 7152K| 2337K| 6969K (0)| 3072 |
| 20 | CONNECT BY PUMP | | 6 | | 210K|00:00:00.04 | 0 | 0 | 0 | | | | |
| 21 | VIEW | | 6 | 220K| 1266K|00:00:07.54 | 8238 | 0 | 0 | | | | |
| 22 | SORT UNIQUE | | 6 | 220K| 1266K|00:00:07.30 | 8238 | 0 | 0 | 13M| 1479K| 11M (0)| |
| 23 | UNION-ALL | | 6 | | 1491K|00:00:05.29 | 8238 | 0 | 0 | | | | |
| 24 | TABLE ACCESS FULL | TAB_DEPT | 6 | 62511 | 394K|00:00:00.13 | 8238 | 0 | 0 | | | | |
| 25 | REMOTE | TAB_DEPT_HIS | 6 | 157K| 1097K|00:00:04.60 | 0 | 0 | 0 | | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_NUMBER(:V3)>TO_NUMBER(:V2))
2 - filter(("T_2"."ROW_NUM"<=TO_NUMBER(:V3) AND "T_2"."ROW_NUM">TO_NUMBER(:V2)))
4 - access("A"."DEPT_ID"="DEPT_ID")
7 - filter((("A"."USER_CODE" LIKE '%1000MM%' OR "A"."USER_NAME" LIKE '%1000MM%' OR TO_CHAR("A"."USER_ID") LIKE '%1000MM%') AND "A"."DEPT_ID" IS NOT NULL))
8 - access("A"."EMPLOYEE_ID"="B"."EMPLOYEE_ID")
12 - access("DEPT_PID"=PRIOR NULL)
13 - filter("DEPT_ID"=:V1)
19 - access("DEPT_PID"=PRIOR NULL)

至此，问题解决，这个问题主要是由于union all里的语句做connect by导致错误的cardinality估算，从而走了NESTED LOOPS，新版本的特性改变导致问题。遇到BUG，要么通过改写语句避免，要么看是否可以通过设置参数改变。
做完这条语句的优化，基本没有碰到啥问题，升级完美成功。

阅读(2365) | 评论(0) | 转发(0) |

上一篇：PARTITION RANGE SUBQUERY 导致SQL执行效率降低的处理案例

下一篇：有关DBLINK的分布式SQL执行机制及优化方法

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6

Symptoms:

Related To:

Description