持續近7個小時的索引掃描的查詢優化分析

昨天客戶的DBA反映有一個資料抽取的任務持續了很長時間最後超時退出了，讓我看看有什麼地方可以調優一下。
找到了對應的日誌，發現在一個大表抽取的時候，抽取持續了將近7個小時，最後超時退出了。對於這個問題，有以下幾個方面需要考慮一下。
1）為什麼這個問題之前沒有發現過
2）是否是由某些變化導致了這個問題
3）這個問題的調優方向
這個資料抽取的服務之前一直沒有問題，抽取速度都是比較快的，結果這次竟然持續了7個小時還沒有抽取完。首先抓取到了對應的日誌，把相關的sql語句也抓取到了。
同時從系統負載的角度進行分析，檢視資料庫層，系統級是否發生了某些變化導致了這個問題，結果抓取了詳細的awr報告，同時結合系統命令分析檢視系統負載，都沒有發現任何的異常，而且這些天來一直沒有任何資料庫層面的引數變更。
所以問題的關注點還是到了sql語句上。
檢視sql語句的執行計劃，也沒有發現異常，可以很明顯看到走的是索引掃描。
語句是類似下面的樣子，使用了閃回查詢，查詢條件只有一個customer_id
select * FROM "RATED_EVENT" AS OF TIMESTAMP (TIMESTAMP' 2015-05-21 07:33:23.000000000') "RATED_EVENT" WHERE "CUSTOMER_ID"=:1 
Plan hash value: 3695503463
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |   432 |   369K|   320   (0)| 00:00:04 |       |       |
|   1 |  PARTITION RANGE ALL               |                 |   432 |   369K|   320   (0)| 00:00:04 |     1 |   961 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| RATED_EVENT     |   432 |   369K|   320   (0)| 00:00:04 |     1 |   961 |
|*  3 |    INDEX RANGE SCAN                | RATED_EVENT_1UQ |   432 |       |   289   (0)| 00:00:04 |     1 |   961 |
----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / RATED_EVENT@SEL$1
   3 - SEL$1 / RATED_EVENT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("CUSTOMER_ID"=11727713)

這樣一來，問題就顯得更加奇怪了。走了索引掃描，條件也很簡單，怎麼就查詢了那麼長的時間呢。
這條語句有一個亮點就是看看pstart,pstop的部分，顯示為1和961，即表示這個分割槽表在查詢中掃描的分割槽為1~961個，這個規模還是相當大的。
但這個還不是最終的問題原因。
這個時候需要結合一下業務來進行診斷。
customer對應有多個subscriber,一般的三戶模型中都是一個customer可能就對應一個subscriber，當然一個customer也可以對應多個subscriber,比如一些大客戶就是如此。
我們來看看這個場景裡的customer和subscriber的對應比例。
SQL> select count(*)from subscriber where customer_id=11727713;
  COUNT(*)
----------
      6168
裡面有6000多個subscriber，在近10億條記錄中進行這麼大範圍的資料掃描，而且掃描的分割槽是1~961個，難度可想而知。
這個資料抽取的部分程式碼都是靈活配置的，怎麼能儘快的提升效率呢。
自己嘗試了幾個方法，一個是使用exp/expdp匯出資料，結合使用query條件，這個時候算是脫離了原有的資料抽取工具，因為這個場景裡抽取邏輯相對簡單，所以不妨一試。
exp xxxxx/xxxxx@xxxxfile=test.dmp tables=rated_event query=\' where  customer_id= 11727713 \'   grants=n indexes=n statistics=none buffer=9102000
可以看到這個時候掃描的分割槽少了很多，從241~481，相比原來只掃描了四分之一的分割槽。效能的提升還是很高的。
這個時候簡單對比一下，結合了分割槽欄位，掃描的速度也快了不少。用了大概4分鐘就能夠有結果了。
SQL> select count(*)from rated_event where customer_id=11727713 and cycle_code=2
    /
  COUNT(*)
----------
  11757084
Elapsed: 00:04:16.40

而對於select count(*)的操作在之前卻要花費將近15分鐘。
SQL>  select count(*) FROM "RATED_EVENT" "RATED_EVENT" WHERE "CUSTOMER_ID"=11727713 ;
  COUNT(*)
----------
  11751975
Elapsed: 00:15:05.54

這個時候如果觀察足夠細緻，會發現兩個查詢的資料條數還是有一些出入，這是因為某些客戶做了修改賬期的操作，在這個資料抽取中只關注當前賬期的操作，所以可以暫時放過。
由此可見，看似簡單的語句走了索引掃描，看起來合理，但是問題突然發生的時候還得結合具體的場景來分析，不能把問題孤立起來看，在明白了問題的瓶頸之後，如果單純從資料庫層面所做的工作有限時，可以考慮從業務上進行進一步的優化，輔助資料庫優化的方向。這個時候DBA的效能調優工作就不單單是一個資料層面的工作了，可能結合業務場景更有針對性，調優的方向也更明確。

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