Re: sql monitor

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

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