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 <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Sanjay Mishra <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
