Find SQL statement with help SQL ID in Oracle

In following query you can directly paste the SQL ID and you will get the SQL Text plus all other detail related to that SQL which used to fixed the performance issue.


set verify off
set pagesize 999
col username format a13
col prog format a22
col sql_text format a41
col sid format 999
col child_number format 99999 heading CHILD
col ocategory format a10
col avg_etime format 9,999,999.99
col etime format 9,999,999.99

select sql_id, child_number, plan_hash_value plan_hash, executions execs, elapsed_time/1000000 etime,(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, u.username,
sql_text from v$sql s, dba_users u
where
sql_id = '&sql_id'
and sql_text not like '%from v$sql s, dba_users u'
and u.user_id = s.parsing_user_id;


Example

SQL> set verify off
SQL> set pagesize 999
SQL> col username format a13
SQL> col prog format a22
SQL> col sql_text format a41
SQL> col sid format 999
SQL> col child_number format 99999 heading CHILD
SQL> col ocategory format a10
SQL> col avg_etime format 9,999,999.99
SQL> col etime format 9,999,999.99
SQL>
SQL> select sql_id, child_number, plan_hash_value plan_hash, executions execs, elapsed_time/1000000
etime,(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, u.username,
2 sql_text from v$sql s, dba_users u
3 where
4 sql_id = '&sql_id'
5 and sql_text not like '%from v$sql s, dba_users u'
6 and u.user_id = s.parsing_user_id
7 /
Enter value for sql_id: 7h35uxf5uhmm1

SQL_ID        CHILD  PLAN_HASH  EXECS  ETIME AVG_ETIME USERNAME SQL_TEXT
------------- ------ ---------- ------ ----- --------- -------- ----------------
7h35uxf5uhmm1      0 1388734953 1      .00   .00       SYS      select sysdate
                                                                from dual