一個缺乏索引和統計資訊的優化過程

案例描述：一張表缺乏統計資訊，且缺乏索引導致正常業務無法正常完成，以至於影響生產。
問題描述：本人接到問題通知，遂前往發現前端操作的確是很慢，但是該系統開發商無法提供該模組的具體sql，而客戶端又反應該問題已經持續有半年之久，現已經開始籌劃系統遷移事宜。（由於系統是win server 2003 2GB記憶體 9i的庫）

問題關鍵就是，無法精確確認該模組牽扯到的sql，遂只能在該業務執行期間進行收集相關資訊。
/*分析處理過程*/
--資料庫版本
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

--業務執行期間發現當前活動會話很少，也就下邊這個sql可疑，遂進行分析
select IMSI from CDMA_CARDMANAGE_USER.res_imsi_discrete where task_rec_id = 24958 order by IMSI;

--檢視該sql執行計劃，發現該sql執行時間長約13mins，執行計劃也比較異常（極為不正常）
SQL> select IMSI from CDMA_CARDMANAGE_USER.res_imsi_discrete where task_rec_id = 24958 order by IMSI;

已選擇8503行。

已用時間:  00: 13: 32.60

執行計劃
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'RES_IMSI_DISCRETE'
   2    1     INDEX (FULL SCAN) OF 'IDX_RES_IMSI_DISC_IMSI' (NON-UNIQU
          E)





統計資訊
----------------------------------------------------------
        103  recursive calls
          0  db block gets
    6058144  consistent gets
     155448  physical reads
          0  redo size
     215190  bytes sent via SQL*Net to client
       6613  bytes received via SQL*Net from client
        568  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       8503  rows processed


--檢視該表大小
SQL> select segment_name,sum(bytes)/1024/1024 mb from dba_segments where lower(segment_name)='res_imsi_discrete' group by segment_name;

SEGMENT_NAME                 MB
-------------------- ----------
RES_IMSI_DISCRETE           462

發現在列task_rec_id無索引
檢視錶上次分析時間，發現該表沒有統計資訊


在業務期間我們進行了statspack報告的抓取
對其中的sql相關分析如下：

SQL ordered by Gets for DB: PIMDB  Instance: pimdb  Snaps: 4 -5
-> End Buffer Gets Threshold:   10000
-> Note that resources reported for PL/SQL includes the resources used by
   all SQL statements called within the PL/SQL code.  As individual SQL
   statements are also reported, it is possible and valid for the summed
   total % to exceed 100

                                                     CPU      Elapsd
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
      1,016,124        1,156          879.0    3.5    44.17    107.11 1717596217
select count(recycle_id) c from recycle_info where oper_id =:1 a
nd status<>'001' and notified = 0

        449,795       23,147           19.4    1.6     3.20      7.93 1500762665
UPDATE res_imsi_discrete t              SET t.res_stat    = :b4,
                  t.task_rec_id = :b3,                  t.modify
_date = SYSDATE            WHERE t.imsi = :b2              AND t
.res_stat = :b1

        431,448       23,147           18.6    1.5     2.70      8.70 4289263526
UPDATE res_imsi_discrete t            SET t.res_stat = :b3, t.mo
dify_date = SYSDATE          WHERE t.imsi = :b2            AND t
.res_stat = :b1

        151,621            8       18,952.6    0.5     0.95      2.70 2991408070
SELECT t2.imsi, t2.res_stat         FROM (SELECT t.imsi, t.res_s
tat                 FROM res_imsi_discrete t                WHER
E t.city_code = :b4                  AND t.msisdn_para = :b3
              AND t.hlr_code = :b2                  AND t.res_st
at = :b1                ORDER BY t.imsi) t2        WHERE rownum

        107,116            4       26,779.0    0.4     0.64      7.52 2526016603
SELECT COUNT(*)         FROM res_imsi_discrete t        WHERE t.
city_code = :b4          AND t.hlr_code = :b3          AND t.msi
sdn_para = :b2          AND t.res_stat = :b1

         99,003            4       24,750.8    0.3     0.72      1.09 3154251665
SELECT COUNT(*)         FROM res_imsi_discrete t        WHERE t.
city_code = :b3          AND t.hlr_code = :b2          AND t.res
_stat = :b1


從以上發現問題sql也基本都和表res_imsi_discrete相關。

具體實施過程如下：
SQL> select count(*) from  CDMA_CARDMANAGE_USER.res_imsi_discrete;

  COUNT(*)
----------
   6341031

SQL> select count(distinct task_rec_id) from  CDMA_CARDMANAGE_USER.res_imsi_discrete;

COUNT(DISTINCTTASK_REC_ID)
--------------------------
                      2463
發現該列的可選性還是很高的。
--建立索引
create index CDMA_CARDMANAGE_USER.idx_res_imsi_disc_tsk_rec_id on CDMA_CARDMANAGE_USER.res_imsi_discrete(task_rec_id) tablespace CDMA_CARDMANAGE_DATA nologging parallel 2;
 
--收集表統計資訊
begin
DBMS_STATS.GATHER_TABLE_STATS (
   wnname => 'CDMA_CARDMANAGE_USER',
   tabname => 'RES_IMSI_DISCRETE',
   estimate_percent => 100,
   degree => 2,
   cascade => true);
end;
/

優化後效果
SQL> select IMSI from CDMA_CARDMANAGE_USER.res_imsi_discrete where task_rec_id = 24958 order by IMSI;

已選擇8503行。

已用時間:  00: 00: 00.79

執行計劃
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=129 Card=2030 Bytes=
          42630)

   1    0   SORT (ORDER BY) (Cost=129 Card=2030 Bytes=42630)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'RES_IMSI_DISCRETE' (Co
          st=119 Card=2030 Bytes=42630)

   3    2       INDEX (RANGE SCAN) OF 'IDX_RES_IMSI_DISC_TASKID' (NON-
          UNIQUE) (Cost=6 Card=2030)





統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1218  consistent gets
          4  physical reads
          0  redo size
     215190  bytes sent via SQL*Net to client
       6613  bytes received via SQL*Net from client
        568  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       8503  rows processed

在新增相關索引並收集統計資訊後，之前發現的問題sql執行效率得到大幅提高，從之前的13mins多提高到1s之內，整個業務也從之前的3個小時縮短到20mins之內。可見表的索引和統計資訊是很重要的。

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