一个缺乏索引和统计信息的优化过程

案例描述：一张表缺乏统计信息，且缺乏索引导致正常业务无法正常完成，以至于影响生产。
问题描述：本人接到问题通知，遂前往发现前端操作的确是很慢，但是该系统开发商无法提供该模块的具体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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26143577/viewspace-748592/