Oracle: finding a query's sql_id, plan and history
Find the sql_id using a comment tag, then show the text, plan and AWR history of a query.
How to find the sql_id of an Oracle query, then display its full text, execution plan and performance history. The approach is based on this article.
Finding the sql_id with a comment tag
Add a recognisable comment to the query, then search for it in v$sql.
select /* MYCOMMENT */ name,age,salary
from user.mytable
where age > 78 order by name;
COL SQL_TEXT format a45
select /* MYCOMMENT1 */ sql_id, substr(sql_text,1,200) sql_text
from v$sql
where upper(sql_text) like '%MYCOMMENT%'
and sql_text not like '%/* MYCOMMENT1 */%' ;
Full query text
select SQL_TEXT fulltext
from v$sqltext
where sql_id='&sql_id'
order by PIECE;
Cursor execution plan
select plan_table_output from table(dbms_xplan.display_cursor('&sql_id',null,'basic'));
Plan and elapsed time history (AWR)
set lines 500
set pages 500
col Snap for a25
col SQL_PROFILE for a40
select distinct b.BEGIN_INTERVAL_TIME as Snap,a.PLAN_HASH_VALUE as Plan,a.EXECUTIONS_DELTA as EXECUTIONS,
a.ELAPSED_TIME_DELTA/1000000 as ELAPSED_sec,a.ROWS_PROCESSED_DELTA as "Rows",a.ROWS_PROCESSED_DELTA/CASE WHEN a.EXECUTIONS_DELTA = 0
THEN -1 ELSE a.EXECUTIONS_DELTA END "Avg Rows",a.ELAPSED_TIME_DELTA/1000000/CASE WHEN a.EXECUTIONS_DELTA = 0 THEN -1
ELSE a.EXECUTIONS_DELTA END "Avg Elapsed",a.optimizer_cost,a.SQL_PROFILE
from DBA_HIST_SQLSTAT a,DBA_HIST_SNAPSHOT b where a.SQL_ID ='&sqlid' and
a.snap_id = b.snap_id order by b.BEGIN_INTERVAL_TIME;