Find SQL using 100% CPU

[rshivagami]

One of my DB CPU usage was 100%. Now after 30 minutes, can I find out which SQL was causing that overkill ? STATSPACK lists only the first few lines of the SQL .

Thanks.

[chduarte]

If you are using UNIX system you can use following:

get the pid process using TOP.

Connected to SQL*Plus run:

SQL> select sid,serial#,username,program,osuser,terminal,sql_hash_value
from v$session
where paddr = (Select addr from v$process where spid = PID_YOU_GOT_ON_TOP);

Now to get the query:

SQL> select sql_text
from v$sql
where hash_value = HASH_VALUE_FROM_ABOVE_QUERY;

Regards

[rshivagami]

That process is not running anymore, so doing TOP does not being it up.

[rshivagami]

Just found out that the Statspack report has a column HashValue for every SQL. Checking for that hashvalue from v$sqltext gives me the complete SQL. Thanks.