Bug 7716219 HASH GROUP BY can use excessive TEMP space-优快云博客

本文解析了Oracle 11g Enterprise Edition在使用HASH GROUP BY时遇到的一个BUG，该BUG导致创建表操作消耗大量临时空间且执行时间过长。通过调整参数禁用HASH GROUP BY后，创建表的操作效率显著提升。

数据库版本

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for HPUX: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

有如下SQL

create table hier1 as SELECT ROOT_ID,
CTLG_ID,
ACTVY_CTLG_IND,
MAX (PATH) AS PATH
FROM ( SELECT CONNECT_BY_ROOT (PROD_ID) AS ROOT_ID,
PROD_ID,
CTLG_ID,
ACTVY_CTLG_IND,
LEVEL AS LVL,
SYS_CONNECT_BY_PATH (
DECODE (HDN_IND, 'Y', 'Y:', NULL) || PROD_ID,
',')
AS PATH
FROM OPT_PROD_ASSOC_ESI
-- WHERE ACTVY_CTLG_IND = 'Y' --Add this filter to delete those unused catalog, 2010/04/12
CONNECT BY PRIOR PARNT_PROD_ID = PROD_ID
AND PRIOR CTLG_ID = CTLG_ID)
GROUP BY ROOT_ID, CTLG_ID, ACTVY_CTLG_IND;

执行计划如下

SQL> explain plan for create table hier1 as SELECT ROOT_ID,

2 CTLG_ID,

3 ACTVY_CTLG_IND,

4 MAX (PATH) AS PATH

5 FROM ( SELECT CONNECT_BY_ROOT (PROD_ID) AS ROOT_ID,

6 PROD_ID,

7 CTLG_ID,

8 ACTVY_CTLG_IND,

9 LEVEL AS LVL,

10 SYS_CONNECT_BY_PATH (

11 DECODE (HDN_IND, 'Y', 'Y:', NULL) || PROD_ID,

12 ',')

13 AS PATH

14 FROM OPT_PROD_ASSOC_ESI

15 -- WHERE ACTVY_CTLG_IND = 'Y' --Add this filter to delete those unused catalog, 2010/04/12

16 CONNECT BY PRIOR PARNT_PROD_ID = PROD_ID

17 AND PRIOR CTLG_ID = CTLG_ID)

18 GROUP BY ROOT_ID, CTLG_ID, ACTVY_CTLG_IND;

Explained.

Elapsed: 00:00:01.42

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 597393069

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

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

| 0 | CREATE TABLE STATEMENT | | 46 | 93012 | 14433 (2)| 00:00:46 |

| 2 | HASH GROUP BY | | 46 | 93012 | 631 (22)| 00:00:02 |

| 3 | VIEW | | 663K| 1278M| 536 (8)| 00:00:02 |

|* 4 | CONNECT BY WITHOUT FILTERING| | | | | |

| 5 | TABLE ACCESS FULL | OPT_PROD_ASSOC_ESI | 663K| 23M| 271 (14)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("PROD_ID"=PRIOR "PARNT_PROD_ID" AND "CTLG_ID"=PRIOR "CTLG_ID")

17 rows selected.

Elapsed: 00:00:02.89

SQL> alter session set "_gby_hash_aggregation_enabled" =false;

Session altered.

Elapsed: 00:00:01.25

SQL> explain plan for create table hier1 as SELECT ROOT_ID,

2 CTLG_ID,

3 ACTVY_CTLG_IND,

4 MAX (PATH) AS PATH

5 FROM ( SELECT CONNECT_BY_ROOT (PROD_ID) AS ROOT_ID,

6 PROD_ID,

7 CTLG_ID,

8 ACTVY_CTLG_IND,

9 LEVEL AS LVL,

10 SYS_CONNECT_BY_PATH (

11 DECODE (HDN_IND, 'Y', 'Y:', NULL) || PROD_ID,

12 ',')

13 AS PATH

14 FROM OPT_PROD_ASSOC_ESI

15 -- WHERE ACTVY_CTLG_IND = 'Y' --Add this filter to delete those unused catalog, 2010/04/12

16 CONNECT BY PRIOR PARNT_PROD_ID = PROD_ID

17 AND PRIOR CTLG_ID = CTLG_ID)

18 GROUP BY ROOT_ID, CTLG_ID, ACTVY_CTLG_IND;

Explained.

Elapsed: 00:00:01.32

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 764406477

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

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

| 0 | CREATE TABLE STATEMENT | | 46 | 93012 | 14433 (2)| 00:00:46 |

| 2 | SORT GROUP BY | | 46 | 93012 | 631 (22)| 00:00:02 |

| 3 | VIEW | | 663K| 1278M| 536 (8)| 00:00:02 |

|* 4 | CONNECT BY WITHOUT FILTERING| | | | | |

| 5 | TABLE ACCESS FULL | OPT_PROD_ASSOC_ESI | 663K| 23M| 271 (14)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("PROD_ID"=PRIOR "PARNT_PROD_ID" AND "CTLG_ID"=PRIOR "CTLG_ID")

17 rows selected.

Elapsed: 00:00:02.62

SQL> alter session set "_gby_hash_aggregation_enabled" =false;

Session altered.

Elapsed: 00:00:00.76

SQL> create table hier as SELECT ROOT_ID,

2 CTLG_ID,

3 ACTVY_CTLG_IND,

4 MAX (PATH) AS PATH

5 FROM ( SELECT CONNECT_BY_ROOT (PROD_ID) AS ROOT_ID,

6 PROD_ID,

7 CTLG_ID,

8 ACTVY_CTLG_IND,

9 LEVEL AS LVL,

10 SYS_CONNECT_BY_PATH (

11 DECODE (HDN_IND, 'Y', 'Y:', NULL) || PROD_ID,

12 ',')

13 AS PATH

14 FROM OPT_PROD_ASSOC_ESI

15 -- WHERE ACTVY_CTLG_IND = 'Y' --Add this filter to delete those unused catalog, 2010/04/12

16 CONNECT BY PRIOR PARNT_PROD_ID = PROD_ID

17 AND PRIOR CTLG_ID = CTLG_ID)

18 GROUP BY ROOT_ID, CTLG_ID, ACTVY_CTLG_IND;

Table created.

Elapsed: 00:02:20.01

禁止 HASH GROUP BY之后， temp 占用了2057Mb,创建表大概2分钟完成如果不禁止 HASH GROUP BY，占用temp 18732Mb,SQL跑了10分钟还未完成。

Metalink上面说这个bug已经在11.1.0.7.1中修复，

SYSTEM DBA 给我回复

Note that the bug states as been resolved in 11.1.0.7.1 (Patch Set Update), we do have 11.1.0.7.4 installed already (that includes all 11.1.0.7.1 fixes).

我们已经升到11.1.0.7.4了，不过还是遇到了这个BUG，只能提交SR再问问了。