Re: sql monitor

Hi,

If a query is running a long time, details might age out. I have a long running query at one of my customers and I can see all details EM type report about 30 minutes. A monitor in Text format is visible a lot longer. You might create a Text format Monitor manually:

set longchunk 50000
set long 50000
set lines 200 pages 0
set trimspool on
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=>'9mmcywzzj5myn',     report_level=>'ALL',Type=>'TEXT') as report from dual;

Always keep in mind though, that in many cases it is not necessary to let a query finish to see its issue.
30 Minutes will often be enough .

Regards

Lothar

Am 07.05.2021 um 18:50 schrieb Henry Poras:
> 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

--
http://www.freelists.org/webpage/oracle-l