Re: sql monitor

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Fri, 7 May 2021 19:00:41 +0200
Message-ID: <CAJu8R6h+2Co67myMtTw4GSGV3Jja4=6K7LgZj=Ma7j4nq-eAeA_at_mail.gmail.com>

Have you checked whether your query is still in the parse phase?

column  in_exec       format a15
column  in_hard_parse format a15
column  in_parse      format a15

column cnt format 9999
compute sum label 'Total Elapsed' of cnt on report break on report
select

    sql_exec_id

   ,in_sql_execution in_exec
   ,in_parse
   ,in_hard_parse

  ,count(1) cnt
from

   gv$active_session_history
 where
   sql_id = '&sql_id'
group by

    sql_exec_id

   ,in_sql_execution
   ,in_parse
   ,in_hard_parse

order by 5 desc
;
 The SQL monitor does not include the parse phase.

Best regards
Mohamed Houri

Le ven. 7 mai 2021 à 18:50, Henry Poras <henry.poras_at_gmail.com> a écrit :

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

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

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

Received on Fri May 07 2021 - 19:00:41 CEST