Re: sql monitor

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