转 优化笔记oracle占用90%

优化笔记oracle占用90% 

1. 当时知道的topas查看的cpu 使用90%以上.

2. 查看wait events

SELECT a.EVENT, b.SID , b.SERIAL#,c.HASH_VALUE,c.SQL_TEXT
FROM v$session_wait a , v$session b , v$sqlarea c
WHERE a.SID=b.SID
AND b.SQL_HASH_VALUE=c.HASH_VALUE
AND a.EVENT NOT LIKE 'SQL*Net%'
AND a.EVENT NOT LIKE 'rdbms%'

发现很多 db file scattered read  , 而这个表相对于是个字典表. 记录数160万左右.
sql 大致语句: select * from tab where seg > :1 and seg < :2  , 而把sql 语句copy 到 pl/sql中 使用了index . 但是weit event 反而为scattered  read
怀疑执行计划改变.

SELECT st.SQL_TEXT FROM v$sqltext st WHERE st.HASH_VALUE='&hash_value' ORDER BY piece

3 查看系统使用的执行计划.
SELECT  sp.OPERATION, sp.OPTIONS, sp.OBJECT_OWNER, sp.OBJECT_NAME
  FROM v$sql_plan sp
 WHERE sp.HASH_VALUE = '&hash_value'

这时发现执行计划为full table scan , 可以判断index 出了问题或者统计信息不准确.

4. 了解业务可知这个表每天都需要同步数据, 有大量的insert , delete 操作.
  alter index ind_name rebuild ; (当时也不能确定是否可用. 说原来发生过重建就好了. 估计是改变了执行计划)
 
5. 重建完index 并没有改变执行计划.还是full table scan . 决定先删除统计信息. 因为这个表业务说只有这个地方使用.
 
  analyze table tab delete statistics ;
 
  之后查看v$sql_plan 执行计划改变 . index range scan .