SQL 語句調優_減少或者避免笛卡爾乘積的發生

djb1008發表於2010-10-27
SQL

透過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

[@more@]

最佳化步驟：

  1. 格式化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

)

)

)

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 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

  1. 尋找問題的原因

使用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記錄進行了笛卡爾乘積

  1. 進行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

)

)

)

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 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

  1. 對新的SQL語句進行執行計劃分析,發現笛卡爾乘積被消除，系統開銷明顯減少

  1. 附錄: 資料庫的執行計劃

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

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 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 | | | | | |

| 6 | TABLE ACCESS BY INDEX ROWID | PUB_MENU | | | | |

| 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 明顯減少

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 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 | | | | | |

| 6 | TABLE ACCESS BY INDEX ROWID | PUB_MENU | | | | |

| 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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/32980/viewspace-1040473/，如需轉載，請註明出處，否則將追究法律責任。

相關文章