Re: sql monitor

Is the query using a global temp or private temp (19c) table? Since these are populated at runtime, the optimizer can (and has in my experience) create a totally off-the-wall plan.

Something else you might consider for analysis is sqlt. I found if OEM and the native tables/research functionality aren't providing answers, sqlt can really help in pinpointing issues. It's easy to install and you'd be amazed at the detail.

On Tue, May 11, 2021 at 4:53 PM Henry Poras <henry.poras_at_gmail.com> wrote:

> 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