hOW to find most resource consuming statement in Oracle

1. SQL> select s.sid, s.serial# "Session", p.serial# "Process" from v$session s, v$process p where s.paddr = p.addr and p.spid="pid of that process using top in unix";

2. By the above command you get the sid of that long running statement.

3. Next you have to find the event

4. SQL> select SID,EVENT,SECONDS_IN_WAIT,STATE from v$session_wait order by SECONDS_IN_WAIT desc;

5. Next find the "hash value" to get the text of sql

6. SQL> select sid,sql_hash_value from v$session where sid="above got sid"

7. SQL> select SQL_FULLTEXT from v$sql where hash_value="above got hash value";