Re: sql monitor

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
> <mailto: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
>

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