RE: sql monitor

I hope no one wrote this yet. Is your plan at all parallel? If not can you make it parallel and see if that changes anything? If it is parallel can you make it first force local, and second, serial to see if either our those makes monitor show up?  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Henry Poras Sent: Friday, May 07, 2021 5:06 PM
To: Sayan Malakshinov
Cc: Lothar Flatz; ORACLE-L
Subject: 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