Re: Insert statement hanging

I'm not sure if this was asked already:
can you check, if there is any change in your session? You can do this by running
select event, seq#, state
from v$session
where sid=<your_sessions_sid>

then wait for some seconds and run the statement again.

IF the STATE is WAITING and EVENT and SEQ# stay the same for some time, the session is really "hanging" (e.g. waiting for something).

Otherwise it's doing something, and we can try to identify WHAT it is doing and WHY.

hth
 Martin

2017-08-18 16:50 GMT+02:00 Mark W. Farnham <mwf_at_rsiz.com>:

> All good, and...
>
> Is this the only insert/update/delete currently running? The bit about it
> NOT running quickly into a new table yields both JL's concern about read
> consistency work below, and also the possibility that your log_buffer is
> full and the archiver is stuck (in which case no insert/update/deletes will
> commit and presumably the alternately burning and waiting on CPU would be
> checking that.)
>
> Any clues in the alert.log?
>
> Other than those low probability possibilities, what is preventing you from
> generating a trace?
>
> mwf
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Jonathan Lewis
> Sent: Friday, August 18, 2017 2:36 AM
> To: Oracle-L Freelists
> Subject: Re: Insert statement hanging
>
>
>
> 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