怎么找出性能低的SQL,

1.先在操作系统上面运行top,查看消耗大量cpu的进程ID
2、查看进程：
oracle >ps -ef | grep 22605
oracle 22605 1 31 5月 31 ? 58752:11 ora9 (LOCAL=NO)
3.查询v$session,v$processes 找到繁忙spid的一些信息
select a.sid,a.serial#,a.username,a.machine,a.program
from v$session a,v$process b
where a.paddr = b.addr
and b.spid = '&pid';
输入 pid 的值: 22605
原值 4: and b.spid = '&pid'
新值 4: and b.spid = '22605'

SID SERIAL# USERNAME PROGRAM
--------------- --------------- ------------ --------------------
110 43585 sxitismg JDBC Thin Client

4、得到SQL:

SQL> select /*+ordered*/
sql_text
from v$sqltext a
where (a.hash_value, a.address) in (
select decode (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
decode (sql_hash_value, 0, prev_sql_addr, sql_address)
from v$session b, v$process c
where b.paddr = c.addr and c.spid = '&pid')
order by piece asc;
输入 pid 的值: 22605
原值 13: WHERE c.spid = '&pid'))
新值 13: WHERE c.spid = '22605'))


top10的sql语句：
SELECT * FROM
　　 (
　　 SELECT PARSING_USER_ID
　　　　 EXECUTIONS,
　　　　 SORTS,
　　　　 COMMAND_TYPE,
　　　　 DISK_READS,
　　　　 sql_text
　　 FROM v$sqlarea
　　 ORDER BY disk_reads DESC
　　 )
　　 WHERE ROWNUM<10 ;
select /*+ordered */ spid,s.sid,s.serial#,s.program, event , p1,p2 , sql_text
from v$process p,v$session s,v$session_wait w,v$sql sql
where 1=1
and p.spid in (159778,116992 , ... )
and s.paddr = p.addr
and s.sid = w.sid
and s.sql_hash_value = sql.hash_value(+);



select a.SID,a.SERIAL#,a.STATUS,b.SPID
from v$session a,v$process b
where a.PADDR=b.ADDR
and spid in (159778,116992 , 74066,119932,159778,198836,202152,96924,125106)
/
Rem 查看等待事件
select /*+ordered */ spid,s.sid,s.serial#,s.program, event , p1,p2
from v$process p,v$session s,v$session_wait w
where 1=1
and p.spid in (159778,116992 , 74066,119932,159778,198836,202152,96924,125106)
and s.paddr = p.addr
and s.sid = w.sid


select * from (SELECT SQL_TEXT Text,
buffer_gets,
executions,
buffer_gets/executions as AVG
FROM v$sqlarea
where executions>0
ORDER BY AVG desc ) where rownum<10;

至于如何调整就要看语句是什么语句了,数据的分布情况如何?查询方式如何?连接方式如何?索引使用情况如何....................

select address,sql_text,rows_processed,disk_reads,disk_reads/rows_processed avg
from v$sqlarea where rows_processed>0
and disk_reads/rows_processed>500
order by 5

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

转载于:http://blog.itpub.net/266281/viewspace-495462/