I am attempting to find the execution plan for a statement that executes in split-seconds (or so it seems from viewing the logs).
The MERGE statement is running within a package.procedure, and is preceded by the execution of an UPDATE and succeeded by an INSERT statement.
I would like to display the execution plan using either dbms_xplan.display_cursor or dbms_xplan.display_awr.
But I cannot find the SQL text in either DBA_HIST_SQLTEXT or V$SQLAREA.
Any ideas why, is there some kind of time span cut-off for a statement to appear ?
I am using the following to try and locate the SQL_ID:
select * from DBA_HIST_SQLTEXT
where upper(DBMS_LOB.Substr(sql_text, 4000, 1)) like '%MERGE INTO%'
and upper(DBMS_LOB.Substr(sql_text, 4000, 1)) like '%SOME_TABLE_NAME%'
and sql_id in (
select sql_id from (SELECT sql_id,
sql_exec_id,
session_id,
session_serial#,
port,
sql_plan_hash_value,
MAX (sample_time) - MIN (sql_exec_start) ela,
MIN (sql_exec_start) started,
MAX (sample_time) finished
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN TO_TIMESTAMP ('11/11/2019 03:17:00',
'dd/mm/yyyy hh24:mi:ss')
AND TO_TIMESTAMP ('11/11/2019 23:23:59',
'dd/mm/yyyy hh24:mi:ss')
AND sql_exec_start IS NOT NULL
GROUP BY sql_id,
sql_exec_id,
session_id,
session_serial#,
port,
sql_plan_hash_value
ORDER BY started )
) ;