2013年（350）
分类： Oracle
2013-04-10 13:34:08
越看越激动是吧，修改前后，从数倍，数十倍直到数百倍的性能差距，这究竟是怎么实现的呢，要回答这个问题，我们还是要从源头说起~~
我个人非常认同这样的观点：大多数的性能优化方案，最终都是要落实到优化的层面来！！这里，我们进行的调优操作，也是要从优化SQL语句着手！
我曾经提到过一种观点：所谓调优，就是尽可能少读并且尽可能少写！！能否少写很多时候DBA不能直接掌控，但让它少读，就本次案例来说，还是有办法的。
从TOP等待事件来看，主要等待也是磁盘文件读，如果我们能够让它少读一点(严重强调，"一点"也很重要，一条SQL语句每次如果能少产生几百次IO，那该语句执行个几万次后，算下来也相当于节省了近千万次IO呢)，对于系统整体负载就能起到重要的作用。
从之前的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
从上述计划可以看出，虽然有索引，由于还需要返回表中读取数据，以过滤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
经过分析可以看到基本过滤条件与第一条相同：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);
创建完新索引之后，重新查看执行计划：
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
对于第1条语句只需要通过索引，即可以得到数据，避免了TABLE ACCESS BY INDEX ROWID的操作；对于第2条语句，虽然索引不能提供所有要访问的列，TABLE ACCESS BY INDEX ROWID不可避免，但是我们要想到，索引本身也是过滤，应用复合索引后得到的数据就是符合条件的记录，这时只需要返回基表获得其它列的信息即可，而无须再做数据的filter，再加上COUNT STOPKEY的作用，仍然可以实现只需要很少的读即可实现需求。
