高负载SQL优化案例-优快云博客

最近发现公司的主数据库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表是一个几千万条记录的日志表，并且做了分区，并且在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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28419/viewspace-613747/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28419/viewspace-613747/