方案实施过程及分析来啦：

越看越激动是吧，修改前后，从数倍，数十倍直到数百倍的性能差距，这究竟是怎么实现的呢，要回答这个问题，我们还是要从源头说起~~

我个人非常认同这样的观点：大多数的性能优化方案，最终都是要落实到SQL优化的层面来！！这里，我们进行的调优操作，也是要从优化SQL语句着手！

我曾经提到过一种观点：所谓调优，就是尽可能少读并且尽可能少写！！能否少写很多时候DBA不能直接掌控，但让它少读，就本次案例来说，还是有办法的。

从TOP等待事件来看，主要等待也是磁盘文件读，如果我们能够让它少读一点(严重强调，"一点"也很重要，一条SQL语句每次如果能少产生几百次IO，那该语句执行个几万次后，算下来也相当于节省了近千万次IO呢)，对于系统整体负载就能起到重要的作用。

A>. 单列索引变复合索引

从之前的ORACLE报表中可以看到，占用资源最多的其实就那么四五条SQL语句，执行了很多次，占用了大量的CPU资源，产生了巨量的逻辑读/物理读，其中最显著的是这两条：

                                                         CPU      Elapsd

      Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value

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

        450,247,504        1,974      228,088.9   32.1  2445.17   2405.51 1551938908

    select count(1) as num from t_wiki_doc_topics where doc_id_encry

    pt=¨BBwReRlFZV0RRXlkG¨ and topic_state=1

        446,599,884        1,959      227,973.4   31.8  2450.06   2406.25 1250588569

    select b.* from (select rownum as r,a.* from (select t.user_id,t

    .last_post_user_id,t.doc_title,t.topic_title,t.click_count+1 as

    click_count,t.posts_count-1 as posts_count,t.user_nick,to_char(t

    .last_post_time,¨yy-mm-dd hh24:mi:ss¨) last_post_time,t.last_pos

    t_user_nick,t.last_post_user_id_encrypt,t.user_id_encrypt,t.onto

    提示：

    第二条语句由于report脚本的限制，没有完全显示，可以通过查询v$sql获取完整SQL语句，以协助分析。

这两语句占用了一半以上的系统资源，访问的对象相同都是t_wiki_doc_topics表，该表的查询列之一doc_id_encrypt上建有索引，单条语句执行效率亦可接受，基本都是在s即可得到结果，但，由于查询涉及数据量和查询次数，仍然产生了大量的逻辑IO和CPU资源的占用。

首先查看第1条语句的执行计划：

    SQL> explain plan for

      2  select count(1) as num from t_wiki_doc_topics where doc_id_encrypt=¨BBwReRlFZV0RRXlkG¨ and topic_state=1;

     

    Explained

     

    SQL> select * from table(dbms_xplan.display);

     

    PLAN_TABLE_OUTPUT

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

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

    | Id  | Operation                    |  Name                         | Rows  | B

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

    |   0 | SELECT STATEMENT             |                               |     1 |

    |   1 |  SORT AGGREGATE              |                               |     1 |

    |*  2 |   TABLE ACCESS BY INDEX ROWID| T_WIKI_DOC_TOPICS             |     1 |

    |*  3 |    INDEX RANGE SCAN          | IDX_DOC_TOPIC_DOC_ID_ENCRYPT  |     1 |

    --------------------------------------------ex------------------------------------

    Predicate Information (identified by operation id):

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

       2 - filter("T_WIKI_DOC_TOPICS"."TOPIC_STATE"=1)

       3 - access("T_WIKI_DOC_TOPICS"."DOC_ID_ENCRYPT"=¨BBwReRlFZV0RRXlkG¨)

    Note: cpu costing is off

     

    17 rows selected

从上述计划可以看出，虽然有索引，由于还需要返回表中读取数据，以过滤topic_state，因此一旦执行次数频繁，就会迭代产生更多IO。接下来我们再看看第2条语句：

    SQL> explain plan for

      2  

      2   select b.*

      3     from (select rownum as r, a.*

      4             from (select t.user_id,

      5                          t.last_post_user_id,

      6                          t.doc_title,

      7                          t.topic_title,

      8                          t.click_count + 1 as click_count,

      9                          t.posts_count - 1 as posts_count,

     10                          t.user_nick,

     11                          to_char(t.last_post_time, ¨yy-mm-dd hh24:mi:ss¨) last_post_time,

     12                          t.last_post_user_nick,

     13                          t.last_post_user_id_encrypt,

     14                          t.user_id_encrypt,

     15                          t.ontop_sort,

     16                          t.is_valuable,

     17                          t.doc_topics_id_encrypt,

     18                          t.is_ontop,

     19                          t.user_ip,

     20                          t.last_post_user_ip,

     21                          t.topic_type

     22                     from t_wiki_doc_topics t

     23                    where t.doc_id_encrypt = ¨BBwReRlFZV0RRXlkG¨

     24                      and t.topic_state = 1

     25                    order by t.is_ontop       desc,

     26                             t.ontop_sort     desc,

     27                             t.last_post_time desc) a

     28            where rownum <= 40) b

     29   where b.r >= 1

     30  /

     

    Explained

     

    SQL> select * from table(dbms_xplan.display);

     

    PLAN_TABLE_OUTPUT

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

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

    | Id  | Operation                       |  Name                         | Rows

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

    |   0 | SELECT STATEMENT                |                               |     1

    |*  1 |  VIEW                           |                               |     1

    |*  2 |   COUNT STOPKEY                 |                               |

    |   3 |    VIEW                         |                               |     1

    |*  4 |     SORT ORDER BY STOPKEY       |                               |     1

    |*  5 |      TABLE ACCESS BY INDEX ROWID| T_WIKI_DOC_TOPICS             |     1

    |*  6 |       INDEX RANGE SCAN          | IDX_DOC_TOPIC_DOC_ID_ENCRYPT  |     1

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

    Predicate Information (identified by operation id):

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

       1 - filter("B"."R">=1)

       2 - filter(ROWNUM<=40)

       4 - filter(ROWNUM<=40)

       5 - filter("T"."TOPIC_STATE"=1)

     

    PLAN_TABLE_OUTPUT

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

       6 - access("T"."DOC_ID_ENCRYPT"=¨BBwReRlFZV0RRXlkG¨)

    Note: cpu costing is off

     

    23 rows selected

经过分析可以看到基本过滤条件与第一条相同：from t_wiki_doc_topics t where t.doc_id_encrypt = ¨BBwReRlFZV0RRXlkG¨ and t.topic_state = 1。

对于这种类型的语句，我个人认为最简单的办法，就是删除原doc_id_encrypt的旧索引，并创建新的复合索引(doc_id_encrypt+topic_state)即可。

执行创建脚本如下：

    SQL> drop index IDX_DOC_TOPIC_DOC_ID_ENCRYPT;

     

    Index dropped

     

    SQL> create index ind_t_wiki_doc_topics_id_stat on t_wiki_doc_topics (doc_id_encrypt,topic_state);

    Index created

创建完新索引之后，重新查看执行计划：

    SQL> explain plan for

      2  select count(1) as num from t_wiki_doc_topics where doc_id_encrypt=¨BBwReRlFZV0RRXlkG¨ and topic_state=1;

     

    Explained

     

    SQL> select * from table(dbms_xplan.display);

     

    PLAN_TABLE_OUTPUT

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

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

    | Id  | Operation            |  Name                          | Rows  | Bytes |

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

    |   0 | SELECT STATEMENT     |                                |     1 |    19 |

    |   1 |  SORT AGGREGATE      |                                |     1 |    19 |

    |*  2 |   INDEX RANGE SCAN   | IND_T_WIKI_DOC_TOPICS_ID_STAT  |     1 |    19 |

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

    Predicate Information (identified by operation id):

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

       2 - access("T_WIKI_DOC_TOPICS"."DOC_ID_ENCRYPT"=¨BBwReRlFZV0RRXlkG¨ AND

                  "T_WIKI_DOC_TOPICS"."TOPIC_STATE"=1)

    Note: cpu costing is off

     

    16 rows selected

    SQL> explain plan for

      2  

      2   select b.*

      3     from (select rownum as r, a.*

      4             from (select t.user_id,

      5                          t.last_post_user_id,

      6                          t.doc_title,

      7                          t.topic_title,

      8                          t.click_count + 1 as click_count,

      9                          t.posts_count - 1 as posts_count,

     10                          t.user_nick,

     11                          to_char(t.last_post_time, ¨yy-mm-dd hh24:mi:ss¨) last_post_time,

     12                          t.last_post_user_nick,

     13                          t.last_post_user_id_encrypt,

     14                          t.user_id_encrypt,

     15                          t.ontop_sort,

     16                          t.is_valuable,

     17                          t.doc_topics_id_encrypt,

     18                          t.is_ontop,

     19                          t.user_ip,

     20                          t.last_post_user_ip,

     21                          t.topic_type

     22                     from t_wiki_doc_topics t

     23                    where t.doc_id_encrypt = ¨BBwReRlFZV0RRXlkG¨

     24                      and t.topic_state = 1

     25                    order by t.is_ontop       desc,

     26                             t.ontop_sort     desc,

     27                             t.last_post_time desc) a

     28            where rownum <= 40) b

     29   where b.r >= 1

     30  ;

     

    Explained

     

    SQL> select * from table(dbms_xplan.display);

     

    PLAN_TABLE_OUTPUT

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

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

    | Id  | Operation                       |  Name                          | Rows

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

    |   0 | SELECT STATEMENT                |                                |     1

    |*  1 |  VIEW                           |                                |     1

    |*  2 |   COUNT STOPKEY                 |                                |

    |   3 |    VIEW                         |                                |     1

    |*  4 |     SORT ORDER BY STOPKEY       |                                |     1

    |   5 |      TABLE ACCESS BY INDEX ROWID| T_WIKI_DOC_TOPICS              |     1

    |*  6 |       INDEX RANGE SCAN          | IND_T_WIKI_DOC_TOPICS_ID_STAT  |     1

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

    Predicate Information (identified by operation id):

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

       1 - filter("B"."R">=1)

       2 - filter(ROWNUM<=40)

       4 - filter(ROWNUM<=40)

       6 - access("T"."DOC_ID_ENCRYPT"=¨BBwReRlFZV0RRXlkG¨ AND "T"."TOPIC_STATE"=1)

     

    PLAN_TABLE_OUTPUT

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

    Note: cpu costing is off

     

    22 rows selected

对于第1条语句只需要通过索引，即可以得到数据，避免了TABLE ACCESS BY INDEX ROWID的操作；对于第2条语句，虽然索引不能提供所有要访问的列，TABLE ACCESS BY INDEX ROWID不可避免，但是我们要想到，索引本身也是过滤，应用复合索引后得到的数据就是符合条件的记录，这时只需要返回基表获得其它列的信息即可，而无须再做数据的filter，再加上COUNT STOPKEY的作用，仍然可以实现只需要很少的读即可实现需求。