Re: sql monitor

Hi Sayan,
Thanks for checking back. I've been busy with some other stuff, but hope to have some more information on this either tonight or tomorrow.

Henry

On Mon, May 10, 2021 at 6:17 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> 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