Re: sql monitor

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