Saturday, May 9, 2009

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";

1 comment:

  1. Hi
    I'm happy to find numerous useful info here in the post. Thanks for sharing this important information.

    Oracle DBA Corporate Training



About Me

My photo
N.Delhi, Delhi, India
I am an Oracle Certified Professional, Oracle 9i/10G DBA, having 4+ years of core DBA experience.