Re: sql monitor

Let's see. I tried:

1. v$sql_monitor on both nodes and also gv$sql_monitor. My sql_id did not appear
2. ran repot_sql_monitor from both nodes. Nothing
3. ran the alter system set events 'trace[sql_mon.*] prior to executing my sql. Didn't see any trc file in diag which looked useful. I also did a grep there for keswx which diid not show up in any recent traces.
4. I did not yet force parallel
5. David, this is 12.1.0.2, not 19c, but I will give sqlt a look. I remember checking it out a while back, but don't remember much.

> 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