Hi Cary,
Long time no see! Welcome back! Boy am I glad to see you back on the list.
Regards
On 08/24/2017 02:56 PM, Cary Millsap wrote:
exec dbms_monitor.session_trace_enable(:sid,:serial)
...where :sid and :serial are the session_id and serial# of the Oracle session you're curious about. The resulting trace file will show you all the database calls and system calls that the session has executed. If you're curious about your trace file, feel free to send it to me, and I'll be glad to have a look.
Cary Millsap
Method R Corporation
Author of /Optimizing Oracle Performance <http://amzn.to/OM0q75>/ and /The Method R Guide to Mastering Oracle Trace Data, 2nd edition <http://amzn.to/1U7q8X1>/
On Fri, Aug 18, 2017 at 2:49 PM, Sanjay Mishra <dmarc-noreply@xxxxxxxxxxxxx <mailto:dmarc-noreply@xxxxxxxxxxxxx>> wrote:
After going thru patch details with Oracle it was found patch was
doing the following and so until got downtime to test patch thru
all dev/test, the following changes at database level resolve the
issue
_optimizer_dsdir_usage_control=0 -- disable use of directives
_sql_plan_directive_mgmt_control=0 -- disable creation of directives
Tx for all suggestion and updates
Sanjay
On Friday, August 18, 2017 11:41 AM, Sanjay Mishra
<dmarc-noreply@xxxxxxxxxxxxx <mailto:dmarc-noreply@xxxxxxxxxxxxx>>
wrote:
Jonathan
Thanks for your input. After having long session with Oracle
Support yesterday where SQL plan management and other options
tried to stick with one of the good execution but issue remains
and finally Oracle provided that it is due to bug and need to
apply Patch 16470650
Bug 16470650
<https://support.oracle.com/epmos/faces/BugDisplay?id=16470650&parent=SrDetailText&sourceId=3-15564872561>
- Plans missing after loading from AWR to a SQL Tuning Set ( Doc
ID 16470650.8
<https://support.oracle.com/epmos/faces/DocumentDisplay?parent=SrDetailText&sourceId=3-15564872561&id=16470650.8>
)
Working to get downtime to apply patch and will try the SQL again.
Tx
Sanjay
On Friday, August 18, 2017 2:37 AM, Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx <mailto:jonathan@xxxxxxxxxxxxxxxxxx>>
wrote:
If it's taking a lot of CPU it's not hanging.
The most likely explanation - in the absence of any detailed
information - is that the query has changed it's execution path.
If the select is taking seconds while the insert is taking hours
this may mean the insert path is not allowed to take the access
path of the select statement (e.g. it's a distributed query which
is allowed to use a "driving_site (remote)" strategy while the
insert has to drive off the local site).
Alternatively the query started at a point in time when it had to
do a huge amount of read-consistency work, but the query doesn't
have to do any because it started at a much later point in time.
(This one is a little unlikely given the difference in scale, but
a technical possibility).
Are your licensed to use the AWR, or have you installed Statspack.
If the insert has taken hours then its execution plan will have
been captured in AWR and you can check the plan and compare it
with the "seconds" query. You could query the
dba_hist_active_sess_history to see where the insert spent most of
it's time (Randolf Geist has some excellent "XPLAN_ASH" material
to do this for you, but essentially it means pulling ASH rows for
the SQL_ID and picking out the plan operation details.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf of Sanjay Mishra
<dmarc-noreply@xxxxxxxxxxxxx <mailto:dmarc-noreply@xxxxxxxxxxxxx>>
Sent: 18 August 2017 04:20
To: Oracle-L Freelists
Subject: Insert statement hanging
Hi
I had insert statement which is selecting database from 3-4 table
and hanging for several hours. Some main points are
- The Target Table where insert is going is empty table
- Select statement as itself is working geting 10K records in few sec
- Tried to create new table to insert but still not worked
- Insert session is showing very high wait on CPU and taking big
CPU time
- Bouncing database and running insert worked first few min but as
App is started , it is again hanged and never complete
Opened Oracle SR now but they ask for Trace analyzer and so want
to check experts as what can be other thing to check. This was
working fine and suddenly started in last few days where no
patching on Oracle/OS or major changes to involved table are done.
Environment is Linux with Non-RAC using ASM as storage and Oracle
12c(12.1.0.2)
TIA
Sanjay
--
//www.freelists.org/webpage/oracle-l
<//www.freelists.org/webpage/oracle-l>
