2013年（350）
分类： Oracle
2013-04-10 13:34:46
接下来要攻克的这条语句CPU时间和BUFFER GETS和与之前两条比稍嫌逊色，但影响力不可小觑，毕竟执行次数高了一倍，而且其各项参数也只是相比前面的两条稍低，对比其它SQL语句在资源占用方面仍然有数倍甚至数十倍的差距，因此也是我们必须攻克的关口。
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
216,979,036 3,602 60,238.5 15.4 1878.72 1850.94 52310006
select e.*, rownum from T_WIKI_EVENT e where (rownum <= :1) and
NEXT_SEND_TIME < SYSDATE and EVENT_TYPE>=0 order by NEXT_SEND_TIME该表由于频繁更新，物理读也是非常恐怖的(相比其它语句)：
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
104,960,046 3,602 29,139.4 96.9 1878.72 1850.94 52310006
select e.*, rownum from T_WIKI_EVENT e where (rownum <= :1) and
NEXT_SEND_TIME < SYSDATE and EVENT_TYPE>=0 order by NEXT_SEND_TIME
SQL语句看起来还是比较简单的，首先看看执行计划怎么样：
SQL> explain plan for
2 select e.*, rownum from T_WIKI_EVENT e where (rownum <= :1) and NEXT_SEND_TIME < SYSDATE and EVENT_TYPE>=0 order by NEXT_SEND_TIME;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT ORDER BY | | | | |
|* 2 | COUNT STOPKEY | | | | |
|* 3 | TABLE ACCESS FULL | T_WIKI_EVENT | | | |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=TO_NUMBER(:Z))
3 - filter("E"."EVENT_TYPE">=0 AND "E"."NEXT_SEND_TIME"
Note: rule based optimization
全表扫，怪不得要占用这么多资源啊，看起来如果想少读的话，必须从索引上考虑了，从过滤列上来看，我们的索引将在event_type和next_send_time两列上打主意，先来分析一下表吧：
SQL> select count(0) from t_wiki_event where next_send_time>sysdate;
COUNT(0)
----------
1561
SQL> select event_type,count(0) from t_wiki_event group by event_type;
EVENT_TYPE COUNT(0)
---------- ----------
-1 589566
1 6基于这样一个结果，很多人都会下意识的认为event_type这种状态列，属于低相异值，不建议在其中创建索引。应该说，这种说法并没有问题，但是具体情况要具体分析，对于性能调优来说从来没有什么铁则。
考虑到该表大多数查询的过滤条件都是event_type>0，而表中符合这一条件的记录非常之少，因此我感觉将索引建在event_type列上将会有更好的查询性能，而且event_type列非常之小，创建索引的话相比date类型的next_send_time也能节省更多的空间。
创建索引如下：
SQL> create index ind_t_wiki_event_event_type on t_wiki_event (event_type);
再次查看执行计划：
SQL> explain plan for
2 select e.*, rownum from T_WIKI_EVENT e where (rownum <= :1) and NEXT_SEND_TIME < SYSDATE and EVENT_TYPE>=0 order by NEXT_SEND_TIME;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | B
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT ORDER BY | | 1 |
|* 2 | COUNT STOPKEY | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| T_WIKI_EVENT | 1 |
|* 4 | INDEX RANGE SCAN | IND_T_WIKI_EVENT_EVENT_TYPE | 1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=TO_NUMBER(:Z))
3 - filter("E"."NEXT_SEND_TIME"
4 - access("E"."EVENT_TYPE">=0 AND "E"."EVENT_TYPE" IS NOT NULL)
Note: cpu costing is off
这样的执行计划好看了很多，cost极底(虽然cost不代表一切)，我们根据当前的具体情况分析，这样走索引方式取数据的效率也是非常高的。
