Re: sql monitor

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 <mailto: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
> <mailto: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 <mailto: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 <mailto: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 <mailto: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://orasql.org>
>

--
http://www.freelists.org/webpage/oracle-l