How to fnd SQL query of top CPU resoruces

This topic is very usefull for Oracle DBA beginers to find TopSQL which are consuming high CPU resouces on the oracle server.

Here are below steps to know top SQL queries in Oracle 10.2.0.4

1. Give Top command on the server or If top command is not workimg then use  prstat -a command on SUN OS.

Get the top 5 PID no from the  above output.

2, Now you have to fetch what are the queries running from thse TOP 5 PID,So below is SQL to fetch the queries.

select sql_text from v$sqlarea where sql_id in (select sql_id from v$session where paddr in (select
(addr from v$process where spid in (<UNIX TOP 5 PID>))

Hope this helps
Tarun C
02.tarun@gmail.com


;