Re: sql monitor

Hi Henry

Sometimes events are not propogated immeditely to other existing sessions: https://tanelpoder.com/2013/10/07/why-doesnt-alter-system-set-events-set-the-events-or-tracing-immediately/

So you can try to enable it before that session connected or try to repeat your query few times, or even enable that event for only needed session in logon trigger.

BTW, could you show please what exactly did you run, full command please?

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE Associate
http://orasql.org

On Thu, May 13, 2021, 15:26 David Barbour <david.barbour1_at_gmail.com> wrote:

> sqlt works on 12c. Easy install, very detailed output. 19c is really
> 12.2.0.3 and the download is the same for 12/18/19 (and some others like 10
> and 11 I think). Check out Doc ID 215187.1
>
>
>
> On Wed, May 12, 2021 at 11:57 PM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>
>> if it is a serial execution, I do prefer runtime stats most of the time
>> over sql monitor.
>> It is simpler and easier to get the main points.
>> Besides, does your query contain some unusual sql code, a special clause,
>> anything like that?
>>
>> Am 13.05.2021 um 06:00 schrieb Henry Poras:
>>
>> 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.
>>
>> Henry
>>
>> brought to you by: Larry as Lucy and rsm as the football.
>>
>>
>>
>> On Wed, May 12, 2021 at 8:50 AM David Barbour <david.barbour1_at_gmail.com>
>> wrote:
>>
>>> 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