Re: Insert statement hanging

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_at_freelists.org <mailto:dmarc-noreply_at_freelists.org>> 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_at_freelists.org <mailto:dmarc-noreply_at_freelists.org>>
> 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_at_jlcomp.demon.co.uk <mailto:jonathan_at_jlcomp.demon.co.uk>>
> 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_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> <oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>> on behalf of Sanjay Mishra
> <dmarc-noreply_at_freelists.org <mailto:dmarc-noreply_at_freelists.org>>
> 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
>
> --
> http://www.freelists.org/webpage/oracle-l
> <http://www.freelists.org/webpage/oracle-l>
>
>
>
>
>
>
>

-- 
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217


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