利用複合索引解決效能問題一例

利用複合索引解決效能問題一例

轉帖自： http://www.laoxiong.net/composite_index_overcome_performance_problem.html

 

老熊的部落格(http://www.laoxiong.net)

 

Oracle效能最佳化 12月 8th, 2008

故障發生時間：12月6日早上
系統內容：HP Superdome系列，128G記憶體，64CPU,Oracle 9.2.0.8
故障現象：CPU佔用將近100%，運行隊列達到60-80，應用反應速度很慢。

這是一個省級電信的核心系統。

在使用者反映速度很慢後，在主機上檢查發現CPU很高，將近100%，而運行隊列達到了60-80。檢查Oracle，發現很多的會話在等待latch free，latch#為98

SQL> select * from v$latchname where latch#=98;

    LATCH# NAME
---------- ----------------------------------------------------------------
        98 cache buffers chains

檢查正在等待latch free的會話正在執行的SQL，大部分都在執行類似於下面的SQL：

SELECT SUM(cnt),
       to_char(nvl(SUM(nvl(amount, 0)) / 100, 0), ’FM9999999999990.90′) amount
  FROM (select count(payment_id) cnt, SUM(amount) amount
          from payment
         where staff_id = 592965
           and CREATED_DATE >= trunc(sysdate)
           and state = ’C0C’
           and operation_type in (’5KA’, ’5KB’, ’5KC’, ’5KP’))

看起來這個SQL並不複雜，查看其執行計畫：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             |  Name                      | Rows  | Bytes | Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                            |     1 |    26 |   125K|       |       |
|   1 |  SORT AGGREGATE                       |                            |     1 |    26 |       |       |       |
|   2 |   VIEW                                |                            |     1 |    26 |   125K|       |       |
|   3 |    SORT AGGREGATE                     |                            |     1 |    30 |       |       |       |
|*  4 |     TABLE ACCESS BY GLOBAL INDEX ROWID| PAYMENT                    | 19675 |   576K|   125K| ROWID | ROW L |
|*  5 |      INDEX RANGE SCAN                 | IDX_PAYMENT_CREATED_DATE8  |  1062K|       |  3919 |       |       |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(”PAYMENT”.”STAFF_ID”=521840 AND ”PAYMENT”.”STATE”=’C0C’ AND (”PAYMENT”.”OPERATION_TYPE”=’5KA’ OR
              ”PAYMENT”.”OPERATION_TYPE”=’5KB’ OR ”PAYMENT”.”OPERATION_TYPE”=’5KC’ OR ”PAYMENT”.”OPERATION_TYPE”=’5KP’))
   5 - access(”PAYMENT”.”CREATED_DATE”>=TRUNC(SYSDATE@!))

Note: cpu costing is off

從執行計畫裡面可以看到，Oracle評估出，利用索引掃描返回的行數高達100萬行，可想而知，這就是產生眾多latch buffers chains latch爭用的原因。
檢查PAYMENT表的索引：

SQL> select index_name,index_type from dba_indexes where table_name=’PAYMENT’ and table_owner=’ACCT’;

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
IDX_OPERATED_PAYMENT_SERIAL8   NORMAL
IDX_PAYMENT_ACCT_ID8           NORMAL
IDX_PAYMENT_CREATED_DATE8      NORMAL
IDX_PAYMENT_PAYED_METHOD8      NORMAL
IDX_PAYMENT_PAYMENT_METHOD8    NORMAL
IDX_PAYMENT_SERV_ID8           NORMAL
IDX_PAYMENT_STAFF_DATE8        NORMAL
IDX_PAYMENT_STATE_DATE8        NORMAL
PK_PAYMENT13                   NORMAL

SQL> select index_name,column_name,column_position from
dba_ind_columns where table_owner=’ACCT’ and table_name=’PAYMENT’ order
by 1,3;

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
IDX_OPERATED_PAYMENT_SERIAL8   OPERATED_PAYMENT_SERIAL_NBR                  1
IDX_PAYMENT_ACCT_ID8           ACCT_ID                                      1
IDX_PAYMENT_CREATED_DATE8      CREATED_DATE                                 1
IDX_PAYMENT_PAYED_METHOD8      PAYED_METHOD                                 1
IDX_PAYMENT_PAYMENT_METHOD8    PAYMENT_METHOD                               1
IDX_PAYMENT_SERV_ID8           SERV_ID                                      1
IDX_PAYMENT_STAFF_DATE8        STAFF_ID                                     1
IDX_PAYMENT_STAFF_DATE8        STATE_DATE                                   2
PK_PAYMENT13                   PAYMENT_ID                                   1

可以看到執行計畫中的使用的索引IDX_PAYMENT_CREATED_DATE8是在CREATED_DATE列上建立的單列索引。

這個SQL在之前卻沒有出現這個問題，那問題在哪裡？
如果瞭解電信系統的人，會知道在出帳後會有一個批量銷帳的動作，這導致在這個特殊的時間裡，用
CREATED_DATE>=TRUNCATE(SYSDATE)這個條件會從索引掃描中返回大量的行。而實際上而回表之後用其他條件過濾後的行數
僅約2萬行（這是評估的資料，實際的資料遠遠比這個少）。很顯然，如果我們建立一個複合索引，那麼索引掃描返回的行數將大大減少。這裡STAFF_ID這
個欄位是與CREATED_DATE建立複合索引最好的欄位。

在STAFF_ID和CREATE_DATE列上建立複合索引後，系統馬上恢複正常。不過這裡把STAFF_ID做為複合索引的前置列，有兩個方面
的考慮，一個是根據這個SQL來看，這會大大減少索引葉塊的邏輯讀數量；二是會減少索引葉塊的熱點競爭（CREATE_DATE是單向增長的欄位）。至於
深層次的分析，則要結合應用，必竟做為故障處理，首要的目標是解決當前的問題。

總結：
利用合適的複合索引，能夠有效地減少索引掃描返回的行數，提高效能
熟悉應用系統的業務，可以更清楚地知道問題的根源，減少故障處理時間

 

 

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

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

老熊水平高，許多基礎知識沒說。現整理一下老熊解決問題的基本流程：

1.會話的等待事件可以通過v$session_wait來尋找，然後通過v$session和v$sql即可找到相對應的sql語句，樣本：

select<br /> u.sid,<br /> substr(u.username,1,12) user_name,<br /> s.sql_text<br />from<br /> v$sql s,<br /> v$session u<br />where<br /> s.hash_value = u.sql_hash_value<br />and<br /> sql_text not like '%from v$sql s, v$session u%'<br />order by<br /> u.sid;

 剩下的老熊就講的蠻仔細的了。

向老熊致敬！