Re: sql monitor

Mark, thanks for the suggestion. The execution plan I got shows no parallelism. I'll try making it parallel and see if that makes a difference. Hoping to get to it later today. This has the feel of an old movie with a prop plane where you kick the engine a few times to get it going. Ugh.

Henry

On Fri, May 7, 2021 at 7:25 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> I hope no one wrote this yet. Is your plan at all parallel? If not can you
> make it parallel and see if that changes anything? If it is parallel can
> you make it first force local, and second, serial to see if either our
> those makes monitor show up?
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Henry Poras
> *Sent:* Friday, May 07, 2021 5:06 PM
> *To:* Sayan Malakshinov
> *Cc:* Lothar Flatz; ORACLE-L
> *Subject:* Re: sql monitor
>
>
>
> Hmmm... one question on this trace.
>
>
>
> Since the trace is enabled using an 'alter session' command, I'm thinking
> that I need to follow this 'enable' with my sql including a MONITOR hint.
> This should guarantee at least some hiccough in the monitor trace. BTW, is
> the correct place for the hint in the CTE select, the main body select, or
> both?
>
>
>
> Thanks again.
>
> Henry
>
>
>
>
>
> On Fri, May 7, 2021 at 4:56 PM Henry Poras <henry.poras_at_gmail.com> wrote:
>
> Sayan,
>
> I have checked SQL Monitor both for new executions of the query and within
> an ongoing execution.
>
>
>
> The trace is a good ideal. Don't think I'll get to it today, but I'll post
> once I have some output.
>
>
>
> Thanks again.
>
> Henry
>
>
>
>
>
> On Fri, May 7, 2021 at 3:51 PM Sayan Malakshinov <xt.and.r_at_gmail.com>
> wrote:
>
> Hi Henry,
>
>
>
> Are you restarting that query? Or do you have just one still running
> execution?
>
> Also please try to trace sqlmonitor:
> http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc
>
>
>
> Trace in-memory the SQL_MONITOR component (the target) and all its
>
> sub-components at level high. Get high resolution time for each
>
> trace:
>
> alter session set events 'trace[sql_mon.*] memory=high,
>
> get_time=highres';
>
>
>
> On Fri, May 7, 2021 at 10:44 PM Henry Poras <henry.poras_at_gmail.com> wrote:
>
> Nothing in gv$sql_plan_monitor for my sql_id.
>
> Henry
>
>
>
>
>
> On Fri, May 7, 2021 at 3:38 PM Henry Poras <henry.poras_at_gmail.com> wrote:
>
> I have looked in ash and it helps, but sql monitor would be better. Also,
> this got me wondering what is going on with the Monitor. I have checked
> v$sql_monitor and nothing shows up with my sql_id. I have not looked in
> v$sql_plan_monitor. I'll give it a shot, but will be surprised if it is the
> outlier.
>
>
>
> Henry
>
>
>
>
>
> On Fri, May 7, 2021 at 2:12 PM Sayan Malakshinov <xt.and.r_at_gmail.com>
> wrote:
>
> Hi,
>
>
>
> Have you checked v$sql_monitor? V$sql_plan_monitor?
>
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning expert
> Oracle Database Developer Choice Award winner
> Oracle ACE Associate
> http://orasql.org
>
>
>
> On Fri, May 7, 2021, 21:06 Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>
> I have seen queries that just won't show up in sql monitor. I do not know
> why.
> One idea is to increase the shared_pool.
> Alternatively you might query ash, which helps to some extend.
>
> At least you can group by sql_plan_line_id to find out where the time is
> spent.
> I guess you have a parallel query. You can still use runtime stats as
> shown here: https://jonathanlewis.wordpress.com/2016/05/11/dbms_xplan/
>
> Good luck
>
> Lothar
>
>
> Am 07.05.2021 um 19:50 schrieb Henry Poras:
>
> Thanks Lothar and Mohamed. I've checked for both of those possibilities.
> The query is not still parsing. I see, for example, multiple entries on
> multiple lines of the execution plan in v$active_session_history (and
> in_sql_execution is 'Y'). I've also been looking in gv$sql_monitor for this
> sql_id and running dbms_sqltune.report_sql_monitor while the query is still
> running (at ~5-10 minutes, 30 minutes, ...) with no luck.
>
>
>
> Henry
>
>
>
> On Fri, May 7, 2021 at 1:19 PM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>
> Hi,
>
> If a query is running a long time, details might age out.
> I have a long running query at one of my customers and I can see all
> details EM type report about 30 minutes.
> A monitor in Text format is visible a lot longer.
> You might create a Text format Monitor manually:
>
> set longchunk 50000
> set long 50000
> set lines 200 pages 0
> set trimspool on
> select DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=>'9mmcywzzj5myn',
> report_level=>'ALL',Type=>'TEXT') as report
> from dual;
>
> Always keep in mind though, that in many cases it is not necessary to
> let a query finish to see its issue.
> 30 Minutes will often be enough .
>
> Regards
>
> Lothar
>
>
> Am 07.05.2021 um 18:50 schrieb Henry Poras:
> > 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