Re: sql monitor

Hi Henry,

Did you get an RTSM trace? It should contain keswx* functions

On Mon, May 10, 2021 at 11:36 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

> Two possibilities, though neither seems likely.
>
> a) gv$sql_plan_monitor (and gv$sql_monitor) may be one of those (rare)
> views that DOESN'T run cross-instance, so if the query is running on
> instance but the session you're using to look at the monitor report is
> connect to another instance you wouldn't see it.
> b) there is another hidden parameter that limits the number of plans that
> can be monitored at any one time. This is _sqlmon_max_plan, default 80, and
> is the number of plans PER CPU. Maybe something has jammed the counter of
> v$sql_monitor at the maximum so your query can't be captured.
>
> Regards
> Jonathan Lewis
>
>
>
> On Fri, 7 May 2021 at 17:50, Henry Poras <henry.poras_at_gmail.com> wrote:
>
>> 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
>>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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