最近发现公司的主数据库CPU负载严重，通过TOP工具发现占用CPU严重的SPID，
通过如下语句得到该SPID具体执行的SQL：
select ps.spid,se.sid,se.USERNAME,sql_text from
v$sql sql,
v$session se,
v$process ps
where
se.SQL_ADDRESS=sql.ADDRESS and ps.ADDR=se.PADDR and ps.SPID='&id'；
该SQL语句如下：
SELECT snd_phone, title, trade_code, price, rcv_phone,
TO_CHAR (send_time, 'yyyy-mm-dd hh24:mi:ss'),
TO_CHAR (sended_time, 'yyyy-mm-dd hh24:mi:ss'), state
FROM tbl_send_histroy
WHERE snd_phone LIKE '%139XXXXXXXX'
AND send_time BETWEEN TO_DATE ('2004-05-01', 'YYYY-MM-DD')
AND TO_DATE ('2005-05-26', 'YYYY-MM-DD')
ORDER BY send_time DESC
tbl_send_histroy表是一个1000多万条记录的日志表，并且做了分区，并且在send_time+snd_phone上建立了一个复合索引，
跟踪该sql语句的执行计划如下：
SQL> SELECT snd_phone, title, trade_code, price, rcv_phone,
2 TO_CHAR (send_time, 'yyyy-mm-dd hh24:mi:ss'),
3 TO_CHAR (sended_time, 'yyyy-mm-dd hh24:mi:ss'), state
4 FROM tbl_send_histroy
5 WHERE snd_phone LIKE '%13910613104'
6 AND send_time BETWEEN TO_DATE ('2004-05-01', 'YYYY-MM-DD')
7 AND TO_DATE ('2005-05-26', 'YYYY-MM-DD')
8 ORDER BY send_time DESC
9 /
已选择2496行。
已用时间: 00: 00: 09.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=994 Bytes=179
914)
1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TBL_SEND_HISTROY'
(Cost=3 Card=994 Bytes=179914)
2 1 INDEX (RANGE SCAN DESCENDING) OF 'IDX_ST_SP' (NON-UNIQUE
) (Cost=2 Card=1789)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
30304 consistent gets
12232 physical reads
0 redo size
174572 bytes sent via SQL*Net to client
2322 bytes received via SQL*Net from client
168 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2496 rows processed
SQL> /
已选择2496行。
已用时间: 00: 00: 08.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=994 Bytes=179
914)
1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TBL_SEND_HISTROY'
(Cost=3 Card=994 Bytes=179914)
2 1 INDEX (RANGE SCAN DESCENDING) OF 'IDX_ST_SP' (NON-UNIQUE
) (Cost=2 Card=1789)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
30304 consistent gets
2437 physical reads
0 redo size
174572 bytes sent via SQL*Net to client
2322 bytes received via SQL*Net from client
168 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2496 rows processed
通过跟程序员了解，得到信息，对于SND_PHONE字段的值，除了具体的“手机号码”，还有一部分为“前缀+手机号码”，为了得到结果，
程序员写成了snd_phone like '%13XXXXXX'的形式。
通过查询分析该字段的值，确认该字段除了手机号码外，都是86+手机号码的形式，发现通过另一种方法也可以得到正确的值，更改SQL
语句如下：
SELECT snd_phone, title, trade_code, price, rcv_phone,
TO_CHAR (send_time, 'yyyy-mm-dd hh24:mi:ss'),
TO_CHAR (sended_time, 'yyyy-mm-dd hh24:mi:ss'), state
FROM tbl_send_histroy
WHERE (snd_phone ='138XXXXX' OR snd_phone ='86138XXXXXXXXX')
AND send_time BETWEEN TO_DATE ('2004-05-01', 'YYYY-MM-DD')
AND TO_DATE ('2005-05-26', 'YYYY-MM-DD')
ORDER BY send_time DESC
并在snd_phone+send_time上建立了一个复合索引，删除开始send_time+snd_phone的复合索引。
跟踪执行计划：
SQL> SELECT snd_phone, title, trade_code, price, rcv_phone,
2 TO_CHAR (send_time, 'yyyy-mm-dd hh24:mi:ss'),
3 TO_CHAR (sended_time, 'yyyy-mm-dd hh24:mi:ss'), state
4 FROM tbl_send_histroy
5 WHERE (snd_phone ='13910613104' OR snd_phone ='8613910613104')
6 AND send_time BETWEEN TO_DATE ('2004-05-01', 'YYYY-MM-DD')
7 AND TO_DATE ('2005-05-26', 'YYYY-MM-DD')
8 ORDER BY send_time DESC
9 /
已选择2496行。
已用时间: 00: 00: 08.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=199 Bytes=36
019)
1 0 SORT (ORDER BY) (Cost=11 Card=199 Bytes=36019)
2 1 INLIST ITERATOR
3 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TBL_SEND_HIST
ROY' (Cost=3 Card=199 Bytes=36019)
4 3 INDEX (RANGE SCAN) OF 'IDX_ST_SS' (NON-UNIQUE) (Cost
=2 Card=143)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1767 consistent gets
18 physical reads
0 redo size
174646 bytes sent via SQL*Net to client
2322 bytes received via SQL*Net from client
168 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2496 rows processed
SQL> /
已选择2496行。
已用时间: 00: 00: 02.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=199 Bytes=36
019)
1 0 SORT (ORDER BY) (Cost=11 Card=199 Bytes=36019)
2 1 INLIST ITERATOR
3 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TBL_SEND_HIST
ROY' (Cost=3 Card=199 Bytes=36019)
4 3 INDEX (RANGE SCAN) OF 'IDX_ST_SS' (NON-UNIQUE) (Cost
=2 Card=143)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1767 consistent gets
0 physical reads
0 redo size
174646 bytes sent via SQL*Net to client
2322 bytes received via SQL*Net from client
168 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2496 rows processed
SQL> /
已选择2496行。
可以看到使用了刚建立的索引 IDX_ST_SS，并且consistent gets大大建立，执行时间控制在3秒之内。CPU更是一下降了N多
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28419/viewspace-604015/ ，如需转载，请注明出处，否则将追究法律责任。
