Php文档 Php问答行业资讯 Php论坛 Php手册 Php博客

游戏榜单

软件榜单

关闭导航

热搜榜

热门下载

热门标签

关闭搜索

php爱好者> php文档>SQL 语句调优_减少或者避免笛卡尔乘积的发生

SQL 语句调优_减少或者避免笛卡尔乘积的发生

时间：2010-10-27 来源：djb1008

通过AWRPT的报告，发现了一个SQL 语句消耗CPU 资源比较厉害，而且这个SQL语句是一个核心的SQL 语句，每天被执行很多次。SQL 语句如下：

sql_id=9bukprkb82k31

SELECT * FROM PUB_MENU T WHERE ((T.MENU_STATE = 'Y' AND (T.MENU_ID IN (SELECT DISTINCT TC.MENU_ID FROM PUB_MENU TC START WITH TC.MENU_ID IN (SELECT DISTINCT TA.PARENT_MENU_ID FROM PUB_PAGE TB, PUB_MENU TA WHERE ( TB.NEED_ASSIGN = 0 OR EXISTS (SELECT 1 FROM PUB_ROLE_USER TB1, PUB_PAGE_OF_ROLE TB2 WHERE TB1.ROLE_ID = TB2.ROLE_ID AND TB2.PAGE_ID = TB.PAGE_ID AND TB1.CAN_BE_ASSIGN = 0 AND TB1.USER_ID = :1)) AND TA.PAGE_ID = TB.PAGE_ID OR ((TA.PAGE_ID = 'NO') AND TA.MENU_STATE = 'N')) CONNECT BY PRIOR TC.PARENT_MENU_ID = TC.MENU_ID)) ) OR (T.MENU_STATE = 'N' AND (T.PAGE_ID = ' ' OR T.PAGE_ID = 'NO' OR T.PAGE_ID IS NULL OR (T.PAGE_ID IN (SELECT P.PAGE_ID FROM PUB_PAGE P WHERE P.NEED_ASSIGN = 0 OR EXISTS (SELECT 1 FROM PUB_ROLE_USER T1, PUB_PAGE_OF_ROLE T2 WHERE T1.ROLE_ID = T2.ROLE_ID AND T2.PAGE_ID = P.PAGE_ID AND T1.CAN_BE_ASSIGN = 0 AND T1.USER_ID = :2)))))) AND T.PARENT_MENU_ID = :3 ORDER BY T.SORT_ID

优化步骤：

格式化SQL语句:

SELECT * FROM SCDC.PUB_MENU T WHERE

(

(T.MENU_STATE = 'Y' AND

(T.MENU_ID IN

(SELECT DISTINCT TC.MENU_ID FROM SCDC.PUB_MENU TC START WITH TC.MENU_ID IN

(SELECT DISTINCT TA.PARENT_MENU_ID FROM SCDC.PUB_PAGE TB, SCDC.PUB_MENU TA WHERE

( TB.NEED_ASSIGN = 0 OR EXISTS

(SELECT 1 FROM SCDC.PUB_ROLE_USER TB1, SCDC.PUB_PAGE_OF_ROLE TB2 WHERE TB1.ROLE_ID = TB2.ROLE_ID AND TB2.PAGE_ID = TB.PAGE_ID

AND TB1.CAN_BE_ASSIGN = 0 AND TB1.USER_ID = :1

)

) AND TA.PAGE_ID = TB.PAGE_ID OR ((TA.PAGE_ID = 'NO') AND TA.MENU_STATE = 'N') ####OR 条件里没有对TB表进行约束,造成TB 全表与OR 条件筛选后的TA记录进行了笛卡尔乘积

) CONNECT BY PRIOR TC.PARENT_MENU_ID = TC.MENU_ID

)

(T.MENU_STATE = 'N' AND

(T.PAGE_ID = ' ' OR T.PAGE_ID = 'NO' OR T.PAGE_ID IS NULL OR

(T.PAGE_ID IN

(SELECT P.PAGE_ID FROM SCDC.PUB_PAGE P WHERE P.NEED_ASSIGN = 0 OR EXISTS

(SELECT 1 FROM SCDC.PUB_ROLE_USER T1, SCDC.PUB_PAGE_OF_ROLE T2 WHERE T1.ROLE_ID = T2.ROLE_ID AND T2.PAGE_ID = P.PAGE_ID AND T1.CAN_BE_ASSIGN = 0 AND T1.USER_ID = :2

)

AND T.PARENT_MENU_ID = :3 ORDER BY T.SORT_ID

寻找问题的原因

使用PL/SQL 开发工具分析该语句的执行计划,发现存在笛卡尔乘积的现象,该SQL语句在执行是占用了大量的CPU 资源,buffer gets也很,仔细分析了该语句,发现了产生笛卡尔乘积的SQL 所在: AND TA.PAGE_ID = TB.PAGE_ID OR ((TA.PAGE_ID = 'NO') AND TA.MENU_STATE = 'N')。

原因是:OR 条件里没有对TB表进行约束,造成TB 全表与OR 条件筛选后的TA记录进行了笛卡尔乘积

进行SQL 语句优化，使用UNION ALL 代替OR.

SELECT * FROM SCDC.PUB_MENU T WHERE

(

(T.MENU_STATE = 'Y' AND

(T.MENU_ID IN

(SELECT DISTINCT TC.MENU_ID FROM SCDC.PUB_MENU TC START WITH TC.MENU_ID IN

(SELECT DISTINCT TA.PARENT_MENU_ID FROM SCDC.PUB_PAGE TB, SCDC.PUB_MENU TA WHERE

( TB.NEED_ASSIGN = 0 OR EXISTS

(SELECT 1 FROM SCDC.PUB_ROLE_USER TB1, SCDC.PUB_PAGE_OF_ROLE TB2 WHERE TB1.ROLE_ID = TB2.ROLE_ID AND TB2.PAGE_ID = TB.PAGE_ID

AND TB1.CAN_BE_ASSIGN = 0 AND TB1.USER_ID = :1

)

) AND TA.PAGE_ID = TB.PAGE_ID

UNION ALL

SELECT DISTINCT TA.PARENT_MENU_ID FROM SCDC.PUB_MENU TA WHERE

(TA.PAGE_ID = 'NO') AND TA.MENU_STATE = 'N'

) CONNECT BY PRIOR TC.PARENT_MENU_ID = TC.MENU_ID

)

(T.MENU_STATE = 'N' AND

(T.PAGE_ID = ' ' OR T.PAGE_ID = 'NO' OR T.PAGE_ID IS NULL OR

(T.PAGE_ID IN

(SELECT P.PAGE_ID FROM SCDC.PUB_PAGE P WHERE P.NEED_ASSIGN = 0 OR EXISTS

(SELECT 1 FROM SCDC.PUB_ROLE_USER T1, SCDC.PUB_PAGE_OF_ROLE T2 WHERE T1.ROLE_ID = T2.ROLE_ID AND T2.PAGE_ID = P.PAGE_ID AND T1.CAN_BE_ASSIGN = 0 AND T1.USER_ID = :2

)

AND T.PARENT_MENU_ID = :3 ORDER BY T.SORT_ID

对新的SQL语句进行执行计划分析,发现笛卡尔乘积被消除，系统开销明显减少

附录: 数据库的执行计划

1. 原语句:

SQL> SELECT * FROM SCDC.PUB_MENU T WHERE

2 (

3 (T.MENU_STATE = 'Y' AND

4 (T.MENU_ID IN

5 (SELECT DISTINCT TC.MENU_ID FROM SCDC.PUB_MENU TC START WITH TC.MENU_ID IN

6 ( SELECT DISTINCT TA.PARENT_MENU_ID FROM SCDC.PUB_PAGE TB, SCDC.PUB_MENU TA WHERE

7 ( TB.NEED_ASSIGN = 0 OR EXISTS

8 (SELECT 1 FROM SCDC.PUB_ROLE_USER TB1, SCDC.PUB_PAGE_OF_ROLE TB2 WHERE TB1.ROLE_ID = TB2.ROLE_ID AND TB2.PAGE_ID = TB.PAGE_ID AND TB1.CAN_BE_ASSIGN = 0 AND TB1.USER_ID = '00000002' )

9 ) AND TA.PAGE_ID = TB.PAGE_ID OR ((TA.PAGE_ID = 'NO') AND TA.MENU_STATE = 'N')

10 ) CONNECT BY PRIOR TC.PARENT_MENU_ID = TC.MENU_ID

11 )

12 )

13 )

14 ) AND T.PARENT_MENU_ID = '1000091' ORDER BY T.SORT_ID;

no rows selected

Execution Plan

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

Plan hash value: 3411878230

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

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

| 0 | SELECT STATEMENT | | 1 | 130 | 4 (25)| 00:00:01 |

| 1 | SORT ORDER BY | | 1 | 130 | 4 (25)| 00:00:01 |

| 2 | NESTED LOOPS | | 1 | 130 | 4 (25)| 00:00:01 |

| 3 | VIEW | VW_NSO_1 | 1 | 6 | 2 (0)| 00:00:01 |

| 4 | HASH UNIQUE | | 1 | 12 | | |

|* 5 | CONNECT BY WITH FILTERING | | | | | |

| 7 | CONCATENATION | | | | | |

| 8 | MERGE JOIN CARTESIAN | | 1379 | 73087 | 5 (0)| 00:00:01 |####执行计划里有笛卡尔乘积

| 9 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 |

|* 10 | INDEX RANGE SCAN | IX_PUB_MENU_PAGEID | 1 | 23 | 2 (0)| 00:00:01 |

|* 11 | INDEX UNIQUE SCAN | PK_PUB_MENU | 1 | 6 | 0 (0)| 00:00:01 |

| 12 | BUFFER SORT | | 1255 | 30120 | 5 (0)| 00:00:01 |

| 13 | INDEX FAST FULL SCAN | IX_PUB_PAGE | 1255 | 30120 | 3 (0)| 00:00:01 |

|* 14 | FILTER | | | | | |

| 15 | NESTED LOOPS | | 1471 | 77963 | 8 (13)| 00:00:01 |

|* 16 | HASH JOIN | | 1534 | 72098 | 8 (13)| 00:00:01 |

| 17 | INDEX FAST FULL SCAN | IX_PUB_PAGE | 1255 | 30120 | 3 (0)| 00:00:01 |

|* 18 | INDEX FAST FULL SCAN | IX_PUB_MENU_PAGEID | 1547 | 35581 | 4 (0)| 00:00:01 |

|* 19 | INDEX UNIQUE SCAN | PK_PUB_MENU | 1 | 6 | 0 (0)| 00:00:01 |

| 20 | NESTED LOOPS | | 1 | 39 | 3 (0)| 00:00:01 |

|* 21 | INDEX RANGE SCAN | IX_PUB_ROLE_USER | 73 | 1168 | 3 (0)| 00:00:01 |

|* 22 | INDEX UNIQUE SCAN | PK_PUB_PAGE_OF_ROLE | 1 | 23 | 0 (0)| 00:00:01 |

| 23 | NESTED LOOPS | | | | | |

| 24 | CONNECT BY PUMP | | | | | |

| 25 | TABLE ACCESS BY INDEX ROWID| PUB_MENU | 1 | 12 | 2 (0)| 00:00:01 |

|* 26 | INDEX UNIQUE SCAN | PK_PUB_MENU | 1 | | 1 (0)| 00:00:01 |

|* 27 | TABLE ACCESS BY INDEX ROWID | PUB_MENU | 1 | 124 | 1 (0)| 00:00:01 |

|* 28 | INDEX UNIQUE SCAN | PK_PUB_MENU | 1 | | 0 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

5 - access("TC"."MENU_ID"=PRIOR "TC"."PARENT_MENU_ID")

10 - access("TA"."PAGE_ID"='NO' AND "TA"."MENU_STATE"='N')

11 - access("TC"."MENU_ID"="TA"."PARENT_MENU_ID")

14 - filter("TB"."NEED_ASSIGN"=0 OR EXISTS (SELECT 0 FROM "SCDC"."PUB_PAGE_OF_ROLE"

"TB2","SCDC"."PUB_ROLE_USER" "TB1" WHERE "TB1"."CAN_BE_ASSIGN"=0 AND "TB1"."USER_ID"='00000002'

AND "TB2"."PAGE_ID"=:B1 AND "TB1"."ROLE_ID"="TB2"."ROLE_ID"))

16 - access("TA"."PAGE_ID"="TB"."PAGE_ID")

18 - filter("TA"."PAGE_ID" IS NOT NULL AND (LNNVL("TA"."MENU_STATE"='N') OR

LNNVL("TA"."PAGE_ID"='NO')))

19 - access("TC"."MENU_ID"="TA"."PARENT_MENU_ID")

21 - access("TB1"."USER_ID"='00000002' AND "TB1"."CAN_BE_ASSIGN"=0)

22 - access("TB1"."ROLE_ID"="TB2"."ROLE_ID" AND "TB2"."PAGE_ID"=:B1)

26 - access("TC"."MENU_ID"=PRIOR "TC"."PARENT_MENU_ID")

27 - filter("T"."PARENT_MENU_ID"=1000091 AND "T"."MENU_STATE"='Y')

28 - access("T"."MENU_ID"="$nso_col_1")

Statistics

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

1 recursive calls

0 db block gets

33320 consistent gets

0 physical reads

0 redo size

1063 bytes sent via SQL*Net to client

481 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

7 sorts (memory)

0 sorts (disk)

0 rows processed

2. 新语句:

SQL> set autotrace on;

SQL> set lines 200

SQL> SELECT * FROM SCDC.PUB_MENU T WHERE

2 (

3 (T.MENU_STATE = 'Y' AND

4 (T.MENU_ID IN

5 (SELECT DISTINCT TC.MENU_ID FROM SCDC.PUB_MENU TC START WITH TC.MENU_ID IN

6 ( SELECT DISTINCT TA.PARENT_MENU_ID FROM SCDC.PUB_PAGE TB, SCDC.PUB_MENU TA WHERE

7 ( TB.NEED_ASSIGN = 0 OR EXISTS

8 (SELECT 1 FROM SCDC.PUB_ROLE_USER TB1, SCDC.PUB_PAGE_OF_ROLE TB2 WHERE TB1.ROLE_ID = TB2.ROLE_ID AND TB2.PAGE_ID = TB.PAGE_ID AND TB1.CAN_BE_ASSIGN = 0 AND TB1.USER_ID = '00000002' )

9 ) AND TA.PAGE_ID = TB.PAGE_ID

10 UNION ALL

11 SELECT DISTINCT TA.PARENT_MENU_ID FROM SCDC.PUB_MENU TA WHERE

12 (TA.PAGE_ID = 'NO') AND TA.MENU_STATE = 'N'

13 ) CONNECT BY PRIOR TC.PARENT_MENU_ID = TC.MENU_ID

14 )

15 )

16 )

17 ) AND T.PARENT_MENU_ID = '1000091' ORDER BY T.SORT_ID;

no rows selected

Execution Plan

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

Plan hash value: 3770468488

####执行计划里没有笛卡尔乘积.COST 明显减少

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

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

| 0 | SELECT STATEMENT | | 1 | 130 | 4 (25)| 00:00:01 |

| 1 | SORT ORDER BY | | 1 | 130 | 4 (25)| 00:00:01 |

| 2 | NESTED LOOPS | | 1 | 130 | 4 (25)| 00:00:01 |

| 3 | VIEW | VW_NSO_2 | 1 | 6 | 2 (0)| 00:00:01 |

| 4 | HASH UNIQUE | | 1 | 12 | | |

|* 5 | CONNECT BY WITH FILTERING | | | | | |

| 7 | NESTED LOOPS | | 191 | 3629 | 17 (18)| 00:00:01 |

| 8 | VIEW | VW_NSO_1 | 191 | 2483 | 17 (18)| 00:00:01 |

| 9 | UNION-ALL | | | | | |

| 10 | SORT UNIQUE | | 190 | 8550 | 14 (15)| 00:00:01 |

|* 11 | FILTER | | | | | |

|* 12 | HASH JOIN | | 1245 | 56025 | 13 (8)| 00:00:01 |

| 13 | INDEX FAST FULL SCAN | IX_PUB_PAGE | 1255 | 30120 | 3 (0)| 00:00:01 |

|* 14 | TABLE ACCESS FULL | PUB_MENU | 1548 | 32508 | 9 (0)| 00:00:01 |

| 15 | NESTED LOOPS | | 1 | 39 | 3 (0)| 00:00:01 |

|* 16 | INDEX RANGE SCAN | IX_PUB_ROLE_USER | 73 | 1168 | 3 (0)| 00:00:01 |

|* 17 | INDEX UNIQUE SCAN | PK_PUB_PAGE_OF_ROLE | 1 | 23 | 0 (0)| 00:00:01 |

| 18 | SORT UNIQUE | | 1 | 23 | 3 (34)| 00:00:01 |

|* 19 | INDEX RANGE SCAN | IX_PUB_MENU_PAGEID | 1 | 23 | 2 (0)| 00:00:01 |

|* 20 | INDEX UNIQUE SCAN | PK_PUB_MENU | 1 | 6 | 0 (0)| 00:00:01 |

| 21 | NESTED LOOPS | | | | | |

| 22 | CONNECT BY PUMP | | | | | |

| 23 | TABLE ACCESS BY INDEX ROWID| PUB_MENU | 1 | 12 | 2 (0)| 00:00:01 |

|* 24 | INDEX UNIQUE SCAN | PK_PUB_MENU | 1 | | 1 (0)| 00:00:01 |

|* 25 | TABLE ACCESS BY INDEX ROWID | PUB_MENU | 1 | 124 | 1 (0)| 00:00:01 |

|* 26 | INDEX UNIQUE SCAN | PK_PUB_MENU | 1 | | 0 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

5 - access("TC"."MENU_ID"=PRIOR "TC"."PARENT_MENU_ID")

11 - filter("TB"."NEED_ASSIGN"=0 OR EXISTS (SELECT 0 FROM "SCDC"."PUB_PAGE_OF_ROLE"

"TB2","SCDC"."PUB_ROLE_USER" "TB1" WHERE "TB1"."CAN_BE_ASSIGN"=0 AND "TB1"."USER_ID"='00000002'

AND "TB2"."PAGE_ID"=:B1 AND "TB1"."ROLE_ID"="TB2"."ROLE_ID"))

12 - access("TA"."PAGE_ID"="TB"."PAGE_ID")

14 - filter("TA"."PAGE_ID" IS NOT NULL)

16 - access("TB1"."USER_ID"='00000002' AND "TB1"."CAN_BE_ASSIGN"=0)

17 - access("TB1"."ROLE_ID"="TB2"."ROLE_ID" AND "TB2"."PAGE_ID"=:B1)

19 - access("TA"."PAGE_ID"='NO' AND "TA"."MENU_STATE"='N')

20 - access("TC"."MENU_ID"="$nso_col_1")

24 - access("TC"."MENU_ID"=PRIOR "TC"."PARENT_MENU_ID")

25 - filter("T"."PARENT_MENU_ID"=1000091 AND "T"."MENU_STATE"='Y')

26 - access("T"."MENU_ID"="$nso_col_1")

Statistics

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

0 recursive calls

0 db block gets

34339 consistent gets

0 physical reads

0 redo size

1063 bytes sent via SQL*Net to client

481 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

9 sorts (memory)

0 sorts (disk)

0 rows processed