find the SQL Text

SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value) hash FROM v$session WHERE sid = &SID;

please supply SID, you will get HASH Value




SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE HASH_VALUE = TO_NUMBER (&HASH) ORDER BY PIECE;

Please supply HASH Value, you will get SQL text which is consuming more cpu load.

SQL> SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value) hash FROM v$session WHERE sid = &SID;
Enter value for sid: 1526
old   1: SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value) hash FROM v$session WHERE sid = &SID
new   1: SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value) hash FROM v$session WHERE sid = 1526

      HASH
----------
1784552006

SQL> SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE HASH_VALUE = TO_NUMBER (&HASH) ORDER BY PIECE;
Enter value for hash: 1784552006
old   1: SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE HASH_VALUE = TO_NUMBER (&HASH) ORDER BY PIECE
new   1: SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE HASH_VALUE = TO_NUMBER (1784552006) ORDER BY PIECE

SQL_TEXT
----------------------------------------------------------------
select null from wf_notifications where notification_id = :1 for
 update nowait

SQL> set lines 400
set pages 200
col MODULE for a20
col ACTION for a20
col MACHINE for a20
col OSUSER for a20
select sid,serial#,STATUS,SQL_HASH_VALUE,MODULE,ACTION,LAST_CALL_ET/60,PROCESS,PADDR,OSUSER,MACHINE from v$session
where sid=SQL> SQL> SQL> SQL> SQL> SQL>   2  1526;