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.running
I'm having an issue with getting a sql_monitor report and I'm
(well, ran) out of ideas on what to try next. I have a longrunning
query (~2 hours on 12.1.0.2) for which I am trying to get a reportalways
using dbms_sqltune.report_sql_monitor. The result returned is
empty. So I tried:lines
- setting _sqlmon_max_planlines to 1000 (execution plan is ~600
using display_cursor('adaptive'))correct)
- checked statistics_level which is correct. It is TYPICAL
- control_management_pack_access is DIAGNOSTIC+TUNING (also
I thought it might be aging out of memory since it ran long, but10-15
running the report and looking in gv$sql_monitor after 2, and
minutes still showed nothing. Adding a MONITOR hint to the CTE andSET
body of the sql didn't help. Neither did running an ALTER SYSTEM
EVENTS 'sql_monitor [sql:...] force=true'.ideas?
I can't figure out why I am getting nothing. Anybody have any
Thanks.
Henry
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org
