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

梓沐發表於2016-04-26

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

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

| Id  | Operation                       | Name            | Rows  | Bytes |TempS

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

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

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

| Id  | Operation                      | Name                     | Rows  | Byte

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

|   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了。由於手頭上還有其他事情,時間已經達到最佳化目的,就沒有再去檢查其他可以最佳化的地方了


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

相關文章