Re: sql monitor

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

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