Oracle/Tuning 一支跑不出來的 view
User 回報查詢一個 view 的內容跑不出來, 原始建 view 的 SQL statement 如下:
CREATE VIEW PS_M_COFE31RH (ACAD_ORG, SUBJECT, STRM, ACAD_CAREER,
CRSE_ID, CLASS_NBR, CRSE_OFFER_NBR, SESSION_CODE, CLASS_SECTION,
DESCR_LABEL, DESCR, DESCR15, CATALOG_NBR, RQRMNT_GROUP, DESCR254A,
DESCR10, UNITS_MINIMUM, XLATSHORTNAME, WEEK_WORKLOAD_HRS,
CONTACT_HOURS, DESCRSHORT, ENRL_CAP, ENRL_TOT, WAIT_CAP,
SCTN_COMBINED_ID, CLASS_STAT, ATTRVALUE, RQRMNT_GROUP_LAST) AS SELECT
A.ACAD_ORG , A.SUBJECT , A.STRM , A.ACAD_CAREER , A.CRSE_ID ,
A.CLASS_NBR , A.CRSE_OFFER_NBR , A.SESSION_CODE , A.CLASS_SECTION ,
DECODE(SUBSTR(A.CATALOG_NBR , 5 , 5) , 'R' , '必修' , 'E' , '選修') ,
A.DESCR , A_T.DESCRSHORT , A.CATALOG_NBR ,
DECODE(SUBSTR(B.RQRMNT_GROUP , 1 , 2) , '31' , B.RQRMNT_GROUP , ' ')
, DECODE(SUBSTR(B.RQRMNT_GROUP , 1 , 2) , '31' , B_E.DESCR254A , ' ')
, DECODE(B.SSR_CRSE_TYPOFF_CD , 'SH' , '半年' , '全年') , C.UNITS_MINIMUM (下略)
查看了執行計劃
Step # Step Name
79 SELECT STATEMENT
8 HASH JOIN
4 NESTED LOOPS
1 SYSADM.PS_CLASS_ATTRIBUTE INDEX [RANGE SCAN]
3 SYSADM.PS_CRSE_ATTR_VALUE TABLE ACCESS [BY INDEX ROWID]
2 SYSADM.PS_CRSE_ATTR_VALUE INDEX [RANGE SCAN]
7 SYS.VW_SQ_1 VIEW
6 SORT [GROUP BY]
5 SYSADM.PS_CRSE_ATTR_VALUE INDEX [RANGE SCAN]
16 HASH JOIN
12 NESTED LOOPS
9 SYSADM.PS_CLASS_ATTRIBUTE INDEX [RANGE SCAN]
11 SYSADM.PS_CRSE_ATTR_VALUE TABLE ACCESS [BY INDEX ROWID]
10 SYSADM.PS_CRSE_ATTR_VALUE INDEX [RANGE SCAN]
15 SYS.VW_SQ_2 VIEW
14 SORT [GROUP BY]
13 SYSADM.PS_CRSE_ATTR_VALUE INDEX [RANGE SCAN]
78 FILTER
75 NESTED LOOPS
72 NESTED LOOPS
69 NESTED LOOPS [OUTER]
67 NESTED LOOPS
64 NESTED LOOPS [OUTER]
61 NESTED LOOPS
58 HASH JOIN
50 NESTED LOOPS
47 HASH JOIN
39 NESTED LOOPS
36 HASH JOIN
28 NESTED LOOPS
26 HASH JOIN (下略)
沒有統計資料還用 Cost Base, 真是的,
算了, 收集統計資料先...
********* 過了很久 **********
再測試一次, 情況還是沒有改善, 改用 HINT 測看看
CREATE VIEW PS_M_COFE31RH (ACAD_ORG, SUBJECT, STRM, ACAD_CAREER,
CRSE_ID, CLASS_NBR, CRSE_OFFER_NBR, SESSION_CODE, CLASS_SECTION,
DESCR_LABEL, DESCR, DESCR15, CATALOG_NBR, RQRMNT_GROUP, DESCR254A,
DESCR10, UNITS_MINIMUM, XLATSHORTNAME, WEEK_WORKLOAD_HRS,
CONTACT_HOURS, DESCRSHORT, ENRL_CAP, ENRL_TOT, WAIT_CAP,
SCTN_COMBINED_ID, CLASS_STAT, ATTRVALUE, RQRMNT_GROUP_LAST) AS SELECT
/*+ USE_HASH (A A_D B A_T B_E C D D_E H I) */ A.ACAD_ORG , A.SUBJECT , A.STRM , A.ACAD_CAREER , A.CRSE_ID ,
A.CLASS_NBR , A.CRSE_OFFER_NBR , A.SESSION_CODE , A.CLASS_SECTION ,
DECODE(SUBSTR(A.CATALOG_NBR , 5 , 5) , 'R' , '必修' , 'E' , '選修') ,
A.DESCR , A_T.DESCRSHORT , A.CATALOG_NBR ,
DECODE(SUBSTR(B.RQRMNT_GROUP , 1 , 2) , '31' , B.RQRMNT_GROUP , ' ')
, DECODE(SUBSTR(B.RQRMNT_GROUP , 1 , 2) , '31' , B_E.DESCR254A , ' ')
, DECODE(B.SSR_CRSE_TYPOFF_CD , 'SH' , '半年' , '全年') , C.UNITS_MINIMUM (下略)
總算有改善了
原因:根據以往的經驗，當表格資料中筆數超過一定的比率或範圍時，CBO往往比較偏好選擇Hash Join，也就是代表Hash Join所花費的成本會比Nested Loop來得低，當聯集的表格為一大一小時，CBO反而會比較偏好使用Nested Loop，但是當兩表格筆數相當或相對趨向於大時，CBO則又會比較偏好選擇Hash Join，當然大家也不要忘記PGA的大小也是CBO決定聯集方式的重要關鍵因素。
CREATE VIEW PS_M_COFE31RH (ACAD_ORG, SUBJECT, STRM, ACAD_CAREER,
CRSE_ID, CLASS_NBR, CRSE_OFFER_NBR, SESSION_CODE, CLASS_SECTION,
DESCR_LABEL, DESCR, DESCR15, CATALOG_NBR, RQRMNT_GROUP, DESCR254A,
DESCR10, UNITS_MINIMUM, XLATSHORTNAME, WEEK_WORKLOAD_HRS,
CONTACT_HOURS, DESCRSHORT, ENRL_CAP, ENRL_TOT, WAIT_CAP,
SCTN_COMBINED_ID, CLASS_STAT, ATTRVALUE, RQRMNT_GROUP_LAST) AS SELECT
A.ACAD_ORG , A.SUBJECT , A.STRM , A.ACAD_CAREER , A.CRSE_ID ,
A.CLASS_NBR , A.CRSE_OFFER_NBR , A.SESSION_CODE , A.CLASS_SECTION ,
DECODE(SUBSTR(A.CATALOG_NBR , 5 , 5) , 'R' , '必修' , 'E' , '選修') ,
A.DESCR , A_T.DESCRSHORT , A.CATALOG_NBR ,
DECODE(SUBSTR(B.RQRMNT_GROUP , 1 , 2) , '31' , B.RQRMNT_GROUP , ' ')
, DECODE(SUBSTR(B.RQRMNT_GROUP , 1 , 2) , '31' , B_E.DESCR254A , ' ')
, DECODE(B.SSR_CRSE_TYPOFF_CD , 'SH' , '半年' , '全年') , C.UNITS_MINIMUM (下略)
查看了執行計劃
Step # Step Name
79 SELECT STATEMENT
8 HASH JOIN
4 NESTED LOOPS
1 SYSADM.PS_CLASS_ATTRIBUTE INDEX [RANGE SCAN]
3 SYSADM.PS_CRSE_ATTR_VALUE TABLE ACCESS [BY INDEX ROWID]
2 SYSADM.PS_CRSE_ATTR_VALUE INDEX [RANGE SCAN]
7 SYS.VW_SQ_1 VIEW
6 SORT [GROUP BY]
5 SYSADM.PS_CRSE_ATTR_VALUE INDEX [RANGE SCAN]
16 HASH JOIN
12 NESTED LOOPS
9 SYSADM.PS_CLASS_ATTRIBUTE INDEX [RANGE SCAN]
11 SYSADM.PS_CRSE_ATTR_VALUE TABLE ACCESS [BY INDEX ROWID]
10 SYSADM.PS_CRSE_ATTR_VALUE INDEX [RANGE SCAN]
15 SYS.VW_SQ_2 VIEW
14 SORT [GROUP BY]
13 SYSADM.PS_CRSE_ATTR_VALUE INDEX [RANGE SCAN]
78 FILTER
75 NESTED LOOPS
72 NESTED LOOPS
69 NESTED LOOPS [OUTER]
67 NESTED LOOPS
64 NESTED LOOPS [OUTER]
61 NESTED LOOPS
58 HASH JOIN
50 NESTED LOOPS
47 HASH JOIN
39 NESTED LOOPS
36 HASH JOIN
28 NESTED LOOPS
26 HASH JOIN (下略)
沒有統計資料還用 Cost Base, 真是的,
算了, 收集統計資料先...
********* 過了很久 **********
再測試一次, 情況還是沒有改善, 改用 HINT 測看看
CREATE VIEW PS_M_COFE31RH (ACAD_ORG, SUBJECT, STRM, ACAD_CAREER,
CRSE_ID, CLASS_NBR, CRSE_OFFER_NBR, SESSION_CODE, CLASS_SECTION,
DESCR_LABEL, DESCR, DESCR15, CATALOG_NBR, RQRMNT_GROUP, DESCR254A,
DESCR10, UNITS_MINIMUM, XLATSHORTNAME, WEEK_WORKLOAD_HRS,
CONTACT_HOURS, DESCRSHORT, ENRL_CAP, ENRL_TOT, WAIT_CAP,
SCTN_COMBINED_ID, CLASS_STAT, ATTRVALUE, RQRMNT_GROUP_LAST) AS SELECT
/*+ USE_HASH (A A_D B A_T B_E C D D_E H I) */ A.ACAD_ORG , A.SUBJECT , A.STRM , A.ACAD_CAREER , A.CRSE_ID ,
A.CLASS_NBR , A.CRSE_OFFER_NBR , A.SESSION_CODE , A.CLASS_SECTION ,
DECODE(SUBSTR(A.CATALOG_NBR , 5 , 5) , 'R' , '必修' , 'E' , '選修') ,
A.DESCR , A_T.DESCRSHORT , A.CATALOG_NBR ,
DECODE(SUBSTR(B.RQRMNT_GROUP , 1 , 2) , '31' , B.RQRMNT_GROUP , ' ')
, DECODE(SUBSTR(B.RQRMNT_GROUP , 1 , 2) , '31' , B_E.DESCR254A , ' ')
, DECODE(B.SSR_CRSE_TYPOFF_CD , 'SH' , '半年' , '全年') , C.UNITS_MINIMUM (下略)
總算有改善了
原因:根據以往的經驗，當表格資料中筆數超過一定的比率或範圍時，CBO往往比較偏好選擇Hash Join，也就是代表Hash Join所花費的成本會比Nested Loop來得低，當聯集的表格為一大一小時，CBO反而會比較偏好使用Nested Loop，但是當兩表格筆數相當或相對趨向於大時，CBO則又會比較偏好選擇Hash Join，當然大家也不要忘記PGA的大小也是CBO決定聯集方式的重要關鍵因素。
留言
張貼留言