Re: sql monitor

Henry,

You can enable it on system level with a filter for your sql_id: alter system set events 'trace[sql_mon.*] [SQL: ...] disk=high,memory=high,get_time=highres';

Hint monitor is a statement-level hint, so you just need to put it on top-level select.

On Sat, May 8, 2021 at 12:06 AM Henry Poras <henry.poras_at_gmail.com> wrote:

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

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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