Re: sql monitor

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