Have you checked whether your query is still in the parse phase?
column in_exec format a15
column in_hard_parse format a15
column in_parse format a15
column cnt format 9999
compute sum label 'Total Elapsed' of cnt on report
break on report
select
sql_exec_id
,in_sql_execution in_exec
,in_parse
,in_hard_parse
,count(1) cnt
from
gv$active_session_history
where
sql_id = '&sql_id'
group by
sql_exec_id
,in_sql_execution
,in_parse
,in_hard_parse
order by 5 desc
;
The SQL monitor does not include the parse phase.
Best regards
Mohamed Houri
Le ven. 7 mai 2021 à 18:50, Henry Poras <henry.poras@xxxxxxxxx> a écrit :
I think I'm missing something silly here, but I don't know what.
I'm having an issue with getting a sql_monitor report and I'm running
(well, ran) out of ideas on what to try next. I have a long running query
(~2 hours on 12.1.0.2) for which I am trying to get a report using
dbms_sqltune.report_sql_monitor. The result returned is always empty. So I
tried:
- setting _sqlmon_max_planlines to 1000 (execution plan is ~600 lines
using display_cursor('adaptive'))
- checked statistics_level which is correct. It is TYPICAL
- control_management_pack_access is DIAGNOSTIC+TUNING (also correct)
I thought it might be aging out of memory since it ran long, but running
the report and looking in gv$sql_monitor after 2, and 10-15 minutes still
showed nothing. Adding a MONITOR hint to the CTE and body of the sql didn't
help. Neither did running an ALTER SYSTEM SET EVENTS 'sql_monitor [sql:...]
force=true'.
I can't figure out why I am getting nothing. Anybody have any ideas?
Thanks.
Henry
