Re: Insert statement hanging

From: Sanjay Mishra <"Sanjay>
Date: Fri, 25 Aug 2017 18:06:36 +0000 (UTC)
Message-ID: <1529000468.3019011.1503684396920_at_mail.yahoo.com>



Paul
As if the organization is big with hundred of the database, it will be difficult for DBA team who are also not Application DBA to keep track of all critical statement and plan. So how do you keep track while working with multiple environments and with Less of Application knowledge but aware of list of the critical database to business?  What I am meant to say is1. Is there any way to automate the process that can be done to get a list of Sqlid/Hashplan/execution plan details based on the number of executions or so?2. Is there a way to get this details from ASH/AWR history or Oracle Auto job which also analyze the environment to help SQL plan management to make changes in the query execution plan or so? Any suggestion or approach that can help in collecting the data on regular basis and consult when such issue arises beside using available repository when issue arises Thanks for your comment
Sanjay  

    On Friday, August 25, 2017 1:28 PM, Paul Drake <bdbafh_at_gmail.com> wrote:  

 Having an inventory of typically used and business critical statements as well as their execution plans and resource usage might be the kind of thing to have around when response time issues occur. Knowing the before part makes it a whole lot easier to diff. Paul

On Aug 25, 2017 1:18 PM, "Sanjay Mishra" <dmarc-noreply_at_freelists.org> wrote:

Thanks Cary. After making the required parameter change, application query is back to normal. Will keep the suggested command for future use. RgdSanjay

    On Thursday, August 24, 2017 2:57 PM, Cary Millsap <cary.millsap_at_method-r.com> 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 CorporationAuthor of Optimizing Oracle Performance and The Method R Guide to Mastering Oracle Trace Data, 2nd edition

On Fri, Aug 18, 2017 at 2:49 PM, Sanjay Mishra <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> 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 - Plans missing after loading from AWR to a SQL Tuning Set ( Doc ID 16470650.8 ) 

Working to get downtime to apply patch and will try the SQL again. TxSanjay

    On Friday, August 18, 2017 2:37 AM, Jonathan Lewis <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 <oracle-l-bounce_at_freelists.org > on behalf of Sanjay Mishra <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
Received on Fri Aug 25 2017 - 20:06:36 CEST

Original text of this message