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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxx> 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@xxxxxxxxxx> 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
