Mark, thanks for the suggestion. The execution plan I got shows no
parallelism. I'll try making it parallel and see if that makes a
difference. Hoping to get to it later today. This has the feel of an old
movie with a prop plane where you kick the engine a few times to get it
going. Ugh.
Henry
On Fri, May 7, 2021 at 7:25 PM Mark W. Farnham <mwf@xxxxxxxx> wrote:
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
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org
