今天qq上一好友發過來一個sql讓我幫他看看，說這個sql加上一個條件查詢時間在3~4秒左右，而不加上這個條件則非常快，正好這段時間也在學習最佳化，就看看問題所在。

sql語句大致如下：

SQL> SELECT a.mc_id AS company_id,

a.mc_name AS company_name,

b.area_name AS district_name,

c.code_name AS operating_quality,

a.agent_tel AS mobile_phone,

a.od_lng,

a.od_lat

FROM tp_company a

LEFT JOIN ads_area b

ON a.district = b.area_id

LEFT JOIN (SELECT b.code, b.code_name

FROM md_cat a, md_dict b

WHERE a.cat_id = b.cat_id

AND a.cat_code = 'BUSINESS_GRADE') c

ON a.operating_quality = c.code

WHERE EXISTS (SELECT 1

FROM tp_company_role m

WHERE a.mc_id = m.mc_id

AND m.com_role_type = 4)

AND a.district IN (330903, 330900, 330921, 330902, 330922)

ORDER BY a.mc_id DESC;

其中m.com_role_type=4根據好友說加上這個語句就變得慢了，由於他的環境上沒有sqlplus，就直接使用下面方法檢視執行計劃(為了保密，SQL已處理過)

SQL> explain plan for

SELECT a.mc_id AS company_id,

a.mc_name AS company_name,

b.area_name AS district_name,

c.code_name AS operating_quality,

a.agent_tel AS mobile_phone,

a.od_lng,

a.od_lat

FROM tp_company a

LEFT JOIN ads_area b

ON a.district = b.area_id

LEFT JOIN (SELECT b.code, b.code_name

FROM md_cat a, md_dict b

WHERE a.cat_id = b.cat_id

AND a.cat_code = 'BUSINESS_GRADE') c

ON a.operating_quality = c.code

WHERE EXISTS (SELECT 1

FROM tp_company_role m

WHERE a.mc_id = m.mc_id

AND m.com_role_type = 4)

AND a.district IN (330903, 330900, 330921, 330902, 330922)

ORDER BY a.mc_id DESC;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2353373994

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

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

| 0 | SELECT STATEMENT | | 40749 | 5690K|

| 1 | SORT ORDER BY | | 40749 | 5690K| 616

|* 2 | HASH JOIN RIGHT OUTER | | 40749 | 5690K|

| 3 | VIEW | | 42 | 2352 |

| 4 | NESTED LOOPS | | 42 | 1890 |

| 5 | TABLE ACCESS BY INDEX ROWID| MD_CAT | 1 | 21 |

|* 6 | INDEX UNIQUE SCAN | IDX_MD_CAT | 1 | |

|* 7 | TABLE ACCESS FULL | MD_DICT | 42 | 1008 |

|* 8 | HASH JOIN RIGHT OUTER | | 40749 | 3462K|

| 9 | TABLE ACCESS FULL | ADS_AREA | 8 | 120 |

| 10 | NESTED LOOPS | | 40749 | 2865K|

| 11 | NESTED LOOPS | | 40749 | 2865K|

| 12 | SORT UNIQUE | | 1995 | 13965 |

|* 13 | TABLE ACCESS FULL | TP_COMPANY_ROLE | 1995 | 13965 |

|* 14 | INDEX UNIQUE SCAN | PK_TP_COMPANY | 1 | |

|* 15 | TABLE ACCESS BY INDEX ROWID| TP_COMPANY | 20 | 1300 |

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

Predicate Information (identified by operation id):

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

2 - access("A"."OPERATING_QUALITY"="C"."CODE"(+))

6 - access("A"."CAT_CODE"='BUSINESS_GRADE')

7 - filter("A"."CAT_ID"="B"."CAT_ID")

8 - access("B"."AREA_ID"(+)=TO_NUMBER("A"."DISTRICT"))

13 - filter(TO_NUMBER("M"."COM_ROLE_TYPE")=4)

14 - access("A"."MC_ID"="M"."MC_ID")

15 - filter(TO_NUMBER("A"."DISTRICT")=330900 OR TO_NUMBER("A"."DISTRICT")=3309

TO_NUMBER("A"."DISTRICT")=330903 OR TO_NUMBER("A"."DISTRICT")=3309

TO_NUMBER("A"."DISTRICT")=330922)

從執行計劃中的id=13可以看出，TP_COMPANY_ROLE根據COM_ROLE_TYPE=4這個條件返回1995行，然後與TP_COMPANY返回的20行資料進行NESTED LOOPS。凡是這種慢的SQL遇上NESTED LOOPS就要非常小心了，這個時候我讓好友去查了下這個TP_COMPANY_ROLE根據COM_ROLE_TYPE=4這個條件實際返回了多少行，得到的結果是返回大概49萬行，到此為止可以斷定是由於統計資訊過舊導致CBO計算返回的行數少從而本該進行HASH JOIN的卻進行了大量的NESTED LOOPS。

重新收集統計資訊

BEGIN

dbms_stats.gather_table_stats(ownname => 'TPSM',

tabname => 'TP_COMPANY_ROLE',

estimate_percent => 100,

method_opt => 'for all columns size auto',

no_invalidate => FALSE,

degree => 1,

cascade => TRUE);

END;

/

並根據SQL建立了，MC_ID和COM_ROLE_TYPE的組合索引，再次執行語句的時候，語句從原來的3~4秒已經提升到了0.3秒左右，檢視修改過後的執行計劃

SQL> explain plan for

SELECT a.mc_id AS company_id,

a.mc_name AS company_name,

b.area_name AS district_name,

c.code_name AS operating_quality,

a.agent_tel AS mobile_phone,

a.od_lng,

a.od_lat

FROM tp_company a

LEFT JOIN ads_area b

ON a.district = b.area_id

LEFT JOIN (SELECT b.code, b.code_name

FROM md_cat a, md_dict b

WHERE a.cat_id = b.cat_id

AND a.cat_code = 'BUSINESS_GRADE') c

ON a.operating_quality = c.code

WHERE EXISTS (SELECT 1

FROM tp_company_role m

WHERE a.mc_id = m.mc_id

AND m.com_role_type = 4)

AND a.district IN (330903, 330900, 330921, 330902, 330922)

ORDER BY a.mc_id DESC;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 37531519

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

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

| 0 | SELECT STATEMENT | | 290K| 4

|* 1 | HASH JOIN RIGHT OUTER | | 290K| 4

| 2 | VIEW | | 42 | 289

| 3 | NESTED LOOPS | | 42 | 189

| 4 | TABLE ACCESS BY INDEX ROWID| MD_CAT | 1 | 2

|* 5 | INDEX UNIQUE SCAN | IDX_MD_CAT | 1 |

|* 6 | TABLE ACCESS FULL | MD_DICT | 42 | 100

|* 7 | HASH JOIN RIGHT OUTER | | 290K| 2

| 8 | TABLE ACCESS FULL | ADS_AREA | 8 | 12

|* 9 | HASH JOIN RIGHT SEMI | | 290K| 2

|* 10 | INDEX FAST FULL SCAN | IDX_MC_ID_COM_ROLE_TYPE | 490K| 622

|* 11 | TABLE ACCESS FULL | TP_COMPANY | 290K| 1

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

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

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

1 - access("A"."OPERATING_QUALITY"="C"."CODE"(+))

5 - access("A"."CAT_CODE"='BUSINESS_GRADE')

6 - filter("A"."CAT_ID"="B"."CAT_ID")

7 - access("B"."AREA_ID"(+)=TO_NUMBER("A"."DISTRICT"))

9 - access("A"."MC_ID"="M"."MC_ID")

10 - filter(TO_NUMBER("M"."COM_ROLE_TYPE")=4)

11 - filter(TO_NUMBER("A"."DISTRICT")=330900 OR TO_NUMBER("A"."DISTRICT")=3309

TO_NUMBER("A"."DISTRICT")=330903 OR TO_NUMBER("A"."DISTRICT")=3309

30 rows selected

Id=9這裡已經可以看出執行計劃已經在走HASH JOIN而不是原來錯誤的NESTED LOOPS了。由於手頭上還有其他事情，時間已經達到最佳化目的，就沒有再去檢查其他可以最佳化的地方了

統計資訊過期導致SQL進行NESTED LOOPS查詢緩慢

相關文章