oracle優化一例之sql優化

skuary發表於2012-01-10
Oracle優化SQL

前2天在我的blog中寫到，oracle優化方法之一----使用hints對sql進行優化，剛好昨天開發的同事寫了一個報表的sql語句，邏輯也不算複雜，但是執行時間卻很久，足足花了差不多2分鐘，由於需要緊急釋出到正式環境，發給我，讓我這裡進行優化一下，其中用到了我上一篇文章中提到的hints，以及一些其他的小技巧，也算經驗吧，詳細記錄如下：

優化之前的sql：

SELECT Y.*,
       X.order_count torder_count,
       X.order_amount tbox_count,
       trunc(Y.order_count / X.order_count, 4) bili
  FROM (SELECT Y.express_name,
               Y.express_id,
               sum(Y.box_count) box_count,
               sum(Y.order_count) order_count,
               sum(Y.order_amount) order_amount,
               sum(Y.send_order_count) send_order_count,
               sum(Y.wait_order_count) wait_order_count,
               sum(Y.again_order_count) again_order_count,
               sum(Y.return_order_count) return_order_count,
               sum(Y.received_order_count) received_order_count,
               sum(Y.getself_order_count) getself_order_count,
               sum(Y.contact_order_count) contact_order_count
          FROM (select ei.express_name,
                       ei.express_id,
                       sum(pi.box_count) box_count,
                       count(oi.order_id) order_count,
                       CASE oi.ORDER_STATUS
                         when 'ORDER_STATUS_SEND' then
                          count(oi.order_id)
                         else
                          0
                       end send_order_count,
                       CASE op.process_status
                         when 'ORDER_PROBLEM_STATUS_WAIT' then
                          count(oi.order_id)
                         else
                          0
                       end wait_order_count,
                       CASE op.process_status
                         when 'ORDER_PROBLEM_STATUS_AGAIN' then
                          count(oi.order_id)
                         else
                          0
                       end again_order_count,
                       CASE op.process_status
                         when 'ORDER_PROBLEM_STATUS_RETURN' then
                          count(oi.order_id)
                         else
                          0
                       end return_order_count,
                       CASE op.process_status
                         when 'ORDER_PROBLEM_STATUS_RECEIVED_MK' then
                          count(oi.order_id)
                         else
                          0
                       end received_order_count,
                       CASE op.process_status
                         when 'ORDER_PROBLEM_STATUS_GETSELF' then
                          count(oi.order_id)
                         else
                          0
                       end getself_order_count,
                       CASE op.process_status
                         when 'ORDER_PROBLEM_STATUS_CONTACT' then
                          count(oi.order_id)
                         else
                          0
                       end contact_order_count,
                       sum(oi.amount) order_amount
                  from m_order_info      oi,
                       m_order_problem   op,
                       m_send_page_info  pi,
                       m_express_info    ei,
                       m_order_send_page sp
                 where oi.order_id = sp.order_id
                   AND sp.send_page_id = pi.send_page_id(+)
                   AND oi.order_id = op.order_id(+)
                   and pi.express_id = ei.express_id
                   and oi.send_date + ei.feedback_day >=
                       to_date('2012-01-06', 'yyyy-mm-dd')
                   and oi.send_date + ei.feedback_day <=
                       to_date('2012-01-06 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                   and pi.cancel_flag = 0
                   and oi.order_status not in
                       ('ORDER_STATUS_SIGNED', 'ORDER_STATUS_RETURN_GOODS')
                   and oi.send_date 
                 group by ei.express_id,
                          ei.express_name,
                          oi.ORDER_STATUS,
                          op.process_status
                 order by ei.express_id) Y
         group by Y.express_id, Y.express_name
         order by Y.express_id) Y
  LEFT JOIN (select ei.express_name,
                    ei.express_id,
                    sum(pi.box_count) box_count,
                    count(oi.order_id) order_count,
                    sum(oi.amount) order_amount
               from m_order_info      oi,
                    m_send_page_info  pi,
                    m_express_info    ei,
                    m_order_send_page sp
              where oi.order_id = sp.order_id
                AND sp.send_page_id = pi.send_page_id
                and pi.express_id = ei.express_id
                and pi.cancel_flag = 0
                and oi.send_date >= to_date('2012-01-06', 'yyyy-mm-dd')
                and oi.send_date <=
                    to_date('2012-01-06 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
              group by ei.express_id, ei.express_name
              order by ei.express_id) X ON X.express_id = Y.express_id;
其具體執行計劃及相關統計資訊如下：
23 rows selected.
Elapsed: 00:01:51.91
Execution Plan
----------------------------------------------------------
Plan hash value: 3884932126
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |    67 | 14003 | 21970   (2)| 00:04:24 |
|*  1 |  HASH JOIN RIGHT OUTER              |                     |    67 | 14003 | 21970   (2)| 00:04:24 |
|   2 |   VIEW                              |                     |     4 |   156 |   881   (4)| 00:00:11 |
|   3 |    SORT GROUP BY                    |                     |     4 |   252 |   881   (4)| 00:00:11 |
|   4 |     NESTED LOOPS                    |                     |     4 |   252 |   880   (4)| 00:00:11 |
|   5 |      NESTED LOOPS                   |                     |     4 |   192 |   876   (4)| 00:00:11 |
|*  6 |       HASH JOIN                     |                     |     4 |   124 |   868   (4)| 00:00:11 |
|   7 |        TABLE ACCESS BY INDEX ROWID  | M_ORDER_INFO        |     4 |    72 |     7   (0)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN            | M_ORDER_INFO_IDX5   |     4 |       |     3   (0)| 00:00:01 |
|   9 |        TABLE ACCESS FULL            | M_ORDER_SEND_PAGE   |   746K|  9481K|   852   (3)| 00:00:11 |
|* 10 |       TABLE ACCESS BY INDEX ROWID   | M_SEND_PAGE_INFO    |     1 |    17 |     2   (0)| 00:00:01 |
|* 11 |        INDEX UNIQUE SCAN            | PK_M_SEND_PAGE_INFO |     1 |       |     1   (0)| 00:00:01 |
|  12 |      TABLE ACCESS BY INDEX ROWID    | M_EXPRESS_INFO      |     1 |    15 |     1   (0)| 00:00:01 |
|* 13 |       INDEX UNIQUE SCAN             | PK_M_EXPRESS_INFO   |     1 |       |     0   (0)| 00:00:01 |
|  14 |   VIEW                              |                     |    67 | 11390 | 21088   (2)| 00:04:14 |
|  15 |    SORT GROUP BY                    |                     |    67 |  9715 | 21088   (2)| 00:04:14 |
|  16 |     VIEW                            |                     |    67 |  9715 | 21088   (2)| 00:04:14 |
|  17 |      HASH GROUP BY                  |                     |    67 |  8241 | 21088   (2)| 00:04:14 |
|  18 |       NESTED LOOPS OUTER            |                     |    67 |  8241 | 21087   (2)| 00:04:14 |
|* 19 |        HASH JOIN                    |                     |    67 |  5829 | 21078   (2)| 00:04:13 |
|* 20 |         HASH JOIN                   |                     |  9690 |   662K|  9554   (1)| 00:01:55 |
|* 21 |          TABLE ACCESS BY INDEX ROWID| M_ORDER_INFO        |    67 |  2613 |    60   (0)| 00:00:01 |
|  22 |           NESTED LOOPS              |                     |  9690 |   539K|  8692   (1)| 00:01:45 |
|  23 |            TABLE ACCESS FULL        | M_EXPRESS_INFO      |   144 |  2592 |     6   (0)| 00:00:01 |
|* 24 |            INDEX RANGE SCAN         | M_ORDER_INFO_IDX5   |    29 |       |    32   (0)| 00:00:01 |
|  25 |          TABLE ACCESS FULL          | M_ORDER_SEND_PAGE   |   746K|  9481K|   852   (3)| 00:00:11 |
|* 26 |         TABLE ACCESS FULL           | M_SEND_PAGE_INFO    |  1267K|    20M| 11509   (2)| 00:02:19 |
|  27 |        TABLE ACCESS BY INDEX ROWID  | M_ORDER_PROBLEM     |     1 |    36 |     1   (0)| 00:00:01 |
|* 28 |         INDEX UNIQUE SCAN           | PK_M_ORDER_PROBLEM  |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("X"."EXPRESS_ID"(+)="Y"."EXPRESS_ID")
   6 - access("OI"."ORDER_ID"="SP"."ORDER_ID")
   8 - access("OI"."SEND_DATE">=TO_DATE(' 2012-01-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "OI"."SEND_DATE"<=TO_DATE(' 2012-01-06 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
  10 - filter("PI"."CANCEL_FLAG"=0)
  11 - access("SP"."SEND_PAGE_ID"="PI"."SEND_PAGE_ID")
  13 - access("PI"."EXPRESS_ID"="EI"."EXPRESS_ID")
  19 - access("SP"."SEND_PAGE_ID"="PI"."SEND_PAGE_ID" AND "PI"."EXPRESS_ID"="EI"."EXPRESS_ID")
  20 - access("OI"."ORDER_ID"="SP"."ORDER_ID")
  21 - filter("OI"."ORDER_STATUS"<>'ORDER_STATUS_SIGNED' AND
              "OI"."ORDER_STATUS"<>'ORDER_STATUS_RETURN_GOODS')
  24 - access("OI"."SEND_DATE"<SYSDATE@!-INTERNAL_FUNCTION("EI"."FEEDBACK_DAY"))
       filter(INTERNAL_FUNCTION("OI"."SEND_DATE")+INTERNAL_FUNCTION("EI"."FEEDBACK_DAY")>=TO_DATE('
               2012-01-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              INTERNAL_FUNCTION("OI"."SEND_DATE")+INTERNAL_FUNCTION("EI"."FEEDBACK_DAY")<=TO_DATE(' 2012-01-06
              23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
  26 - filter("PI"."CANCEL_FLAG"=0)
  28 - access("OI"."ORDER_ID"="OP"."ORDER_ID"(+))

Statistics
----------------------------------------------------------
         29  recursive calls
          0  db block gets
    1058742  consistent gets
          0  physical reads
          0  redo size
       2960  bytes sent via SQL*Net to client
       3742  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         23  rows processed
這條sql執行總花費時長111秒左右，其中一致讀很高，達到了1058742，從執行計劃看，執行成本和io並不高，幾張大表都走索引了，這也應該是oracle所期望的，較好的平衡了cpu使用和io負載，但是速度卻不理想，尤其是在一個實時性要求很高的系統中，最快的速度響應需求才是第一位的；
優化之後的sql：
SELECT Y.*,
       X.order_count torder_count,
       X.order_amount tbox_count,
       trunc(Y.order_count / X.order_count, 4) bili
  FROM (SELECT Y.express_name,
               Y.express_id,
               sum(Y.box_count) box_count,
               sum(Y.order_count) order_count,
               sum(Y.order_amount) order_amount,
               sum(Y.send_order_count) send_order_count,
               sum(Y.wait_order_count) wait_order_count,
               sum(Y.again_order_count) again_order_count,
               sum(Y.return_order_count) return_order_count,
               sum(Y.received_order_count) received_order_count,
               sum(Y.getself_order_count) getself_order_count,
               sum(Y.contact_order_count) contact_order_count
          FROM (select /*+use_hash(oi,op,pi,ei,sp) first_rows*/
                 ei.express_name,
                 ei.express_id,
                 sum(pi.box_count) box_count,
                 count(oi.order_id) order_count,
                 CASE oi.ORDER_STATUS
                   when 'ORDER_STATUS_SEND' then
                    count(oi.order_id)
                   else
                    0
                 end send_order_count,
                 CASE op.process_status
                   when 'ORDER_PROBLEM_STATUS_WAIT' then
                    count(oi.order_id)
                   else
                    0
                 end wait_order_count,
                 CASE op.process_status
                   when 'ORDER_PROBLEM_STATUS_AGAIN' then
                    count(oi.order_id)
                   else
                    0
                 end again_order_count,
                 CASE op.process_status
                   when 'ORDER_PROBLEM_STATUS_RETURN' then
                    count(oi.order_id)
                   else
                    0
                 end return_order_count,
                 CASE op.process_status
                   when 'ORDER_PROBLEM_STATUS_RECEIVED_MK' then
                    count(oi.order_id)
                   else
                    0
                 end received_order_count,
                 CASE op.process_status
                   when 'ORDER_PROBLEM_STATUS_GETSELF' then
                    count(oi.order_id)
                   else
                    0
                 end getself_order_count,
                 CASE op.process_status
                   when 'ORDER_PROBLEM_STATUS_CONTACT' then
                    count(oi.order_id)
                   else
                    0
                 end contact_order_count,
                 sum(oi.amount) order_amount
                  from m_order_info      oi,
                       m_order_problem   op,
                       m_send_page_info  pi,
                       m_express_info    ei,
                       m_order_send_page sp
                 where oi.order_id = sp.order_id
                   AND sp.send_page_id = pi.send_page_id(+)
                   AND oi.order_id = op.order_id(+)
                   and pi.express_id = ei.express_id
                   and pi.cancel_flag = 0
                   and oi.order_status not in
                       ('ORDER_STATUS_SIGNED', 'ORDER_STATUS_RETURN_GOODS')
                   and oi.send_date 
                   and oi.send_date >=
                       to_date('2012-01-06', 'yyyy-mm-dd') - ei.feedback_day
                   and oi.send_date <=
                       to_date('2012-01-06 23:59:59', 'yyyy-mm-dd hh24:mi:ss') -
                       ei.feedback_day
                 group by ei.express_id,
                          ei.express_name,
                          oi.ORDER_STATUS,
                          op.process_status
                 order by ei.express_id) Y
         group by Y.express_id, Y.express_name
         order by Y.express_id) Y
  LEFT JOIN (select /*+use_hash(oi,pi,ei,sp) first_rows*/
              ei.express_name,
              ei.express_id,
              sum(pi.box_count) box_count,
              count(oi.order_id) order_count,
              sum(oi.amount) order_amount
               from m_order_info      oi,
                    m_send_page_info  pi,
                    m_express_info    ei,
                    m_order_send_page sp
              where oi.order_id = sp.order_id
                AND sp.send_page_id = pi.send_page_id
                and pi.express_id = ei.express_id
                and pi.cancel_flag = 0
                and oi.send_date >= to_date('2012-01-06', 'yyyy-mm-dd')
                and oi.send_date <=
                    to_date('2012-01-06 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
              group by ei.express_id, ei.express_name
              order by ei.express_id) X ON X.express_id = Y.express_id;
其具體執行計劃及相關統計資訊如下：
23 rows selected.
Elapsed: 00:00:03.44
Execution Plan
----------------------------------------------------------
Plan hash value: 1236569700
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                   |    67 | 14003 |       | 60235   (2)| 00:12:03 |
|*  1 |  HASH JOIN RIGHT OUTER            |                   |    67 | 14003 |       | 60235   (2)| 00:12:03 |
|   2 |   VIEW                            |                   |     4 |   156 |       | 12400   (2)| 00:02:29 |
|   3 |    SORT GROUP BY                  |                   |     4 |   252 |       | 12400   (2)| 00:02:29 |
|*  4 |     HASH JOIN                     |                   |     4 |   252 |       | 12399   (2)| 00:02:29 |
|*  5 |      HASH JOIN                    |                   |     4 |   192 |       | 12393   (2)| 00:02:29 |
|*  6 |       HASH JOIN                   |                   |     4 |   124 |       |   868   (4)| 00:00:11 |
|   7 |        TABLE ACCESS BY INDEX ROWID| M_ORDER_INFO      |     4 |    72 |       |     7   (0)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN          | M_ORDER_INFO_IDX5 |     4 |       |       |     3   (0)| 00:00:01 |
|   9 |        TABLE ACCESS FULL          | M_ORDER_SEND_PAGE |   746K|  9481K|       |   852   (3)| 00:00:11 |
|* 10 |       TABLE ACCESS FULL           | M_SEND_PAGE_INFO  |  1267K|    20M|       | 11509   (2)| 00:02:19 |
|  11 |      TABLE ACCESS FULL            | M_EXPRESS_INFO    |   144 |  2160 |       |     6   (0)| 00:00:01 |
|  12 |   VIEW                            |                   |    67 | 11390 |       | 47834   (2)| 00:09:35 |
|  13 |    SORT GROUP BY                  |                   |    67 |  9715 |       | 47834   (2)| 00:09:35 |
|  14 |     VIEW                          |                   |    67 |  9715 |       | 47834   (2)| 00:09:35 |
|  15 |      HASH GROUP BY                |                   |    67 |  8241 |       | 47834   (2)| 00:09:35 |
|* 16 |       HASH JOIN OUTER             |                   |    67 |  8241 |       | 47833   (2)| 00:09:34 |
|* 17 |        HASH JOIN                  |                   |    67 |  5829 |       | 46652   (2)| 00:09:20 |
|  18 |         TABLE ACCESS FULL         | M_EXPRESS_INFO    |   144 |  2592 |       |     6   (0)| 00:00:01 |
|* 19 |         HASH JOIN                 |                   |   538K|    35M|    26M| 46639   (2)| 00:09:20 |
|* 20 |          TABLE ACCESS FULL        | M_ORDER_INFO      |   538K|    20M|       | 28799   (2)| 00:05:46 |
|* 21 |          HASH JOIN                |                   |   746K|    21M|    17M| 15029   (2)| 00:03:01 |
|  22 |           TABLE ACCESS FULL       | M_ORDER_SEND_PAGE |   746K|  9481K|       |   852   (3)| 00:00:11 |
|* 23 |           TABLE ACCESS FULL       | M_SEND_PAGE_INFO  |  1267K|    20M|       | 11509   (2)| 00:02:19 |
|  24 |        TABLE ACCESS FULL          | M_ORDER_PROBLEM   |   193K|  6812K|       |  1178   (1)| 00:00:15 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("X"."EXPRESS_ID"(+)="Y"."EXPRESS_ID")
   4 - access("PI"."EXPRESS_ID"="EI"."EXPRESS_ID")
   5 - access("SP"."SEND_PAGE_ID"="PI"."SEND_PAGE_ID")
   6 - access("OI"."ORDER_ID"="SP"."ORDER_ID")
   8 - access("OI"."SEND_DATE">=TO_DATE(' 2012-01-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "OI"."SEND_DATE"<=TO_DATE(' 2012-01-06 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
  10 - filter("PI"."CANCEL_FLAG"=0)
  16 - access("OI"."ORDER_ID"="OP"."ORDER_ID"(+))
  17 - access("PI"."EXPRESS_ID"="EI"."EXPRESS_ID")
       filter("OI"."SEND_DATE"<SYSDATE@!-INTERNAL_FUNCTION("EI"."FEEDBACK_DAY") AND
              "OI"."SEND_DATE">=TO_DATE(' 2012-01-06 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')-INTERNAL_FUNCTION("EI"."FEEDBACK_DAY") AND "OI"."SEND_DATE"<=TO_DATE(' 2012-01-06
              23:59:59', 'syyyy-mm-dd hh24:mi:ss')-INTERNAL_FUNCTION("EI"."FEEDBACK_DAY"))
  19 - access("OI"."ORDER_ID"="SP"."ORDER_ID")
  20 - filter("OI"."ORDER_STATUS"<>'ORDER_STATUS_SIGNED' AND
              "OI"."ORDER_STATUS"<>'ORDER_STATUS_RETURN_GOODS')
  21 - access("SP"."SEND_PAGE_ID"="PI"."SEND_PAGE_ID")
  23 - filter("PI"."CANCEL_FLAG"=0)

Statistics
----------------------------------------------------------
         29  recursive calls
          0  db block gets
     202832  consistent gets
          0  physical reads
          0  redo size
       2963  bytes sent via SQL*Net to client
       3573  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         23  rows processed
這條sql語句，通過使用hints和改變sql語句中的一些條件的順序（多表之間強制使用hash連線，多了很多全表掃描，而原來的sql語句，使用巢狀迴圈，較好的平衡了io負載和cpu使用成本，同時使用了first_rows提示以提高響應速度，而where條件中的一些語句作了調整之後，過濾掉了大部分無關的資料），執行只花費了3秒多，一致讀降低了差不多5倍，其響應速度得到了明顯的提升，但是顯著的問題也出來了，cpu使用成本漲了2倍，io負載也上去了，所以，具體的優化措施，得根據相應的業務來權衡，同時還要結合具體的伺服器資源等綜合考慮。
 
以上都是我的一些心得體會，有不對的地方，望各位博友能批評指正，以互相學習。

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

相關文章