Oracle : retrouver le sql_id, le plan et l'historique d'une requête
Retrouver le sql_id via un commentaire, afficher le texte, le plan et l'historique AWR d'une requête.
Comment retrouver le sql_id d'une requête Oracle, afficher son texte complet, son plan d'exécution et son historique de performance. Principe inspiré de cet article.
Retrouver le sql_id grâce à un commentaire
On ajoute un commentaire repérable dans la requête, puis on le recherche dans 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 */%' ;
Texte complet de la requête
select SQL_TEXT fulltext
from v$sqltext
where sql_id='&sql_id'
order by PIECE;
Plan d'exécution du curseur
select plan_table_output from table(dbms_xplan.display_cursor('&sql_id',null,'basic'));
Historique des plans et des temps d'exécution (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;