Re: Insert statement hanging

Hello Sanjay,

> Select statement as itself is working geting 10K records in few sec

This does not mean anything as there are limitations that are related to DMLs which work with plain SELECTs - it all depends :)

> Insert session is showing very high wait on CPU and taking big CPU time

What do you mean? Is it waiting on CPU (CPU starvation) or running on CPU? It can't be both.

Can you please run Snapper (http://blog.tanelpoder.com/files/scripts/snapper.sql) for your session while the INSERT statement is running and provide a SQLd360 report (https://mauro-pagano.com/2015/02/16/sqld360-sql-diagnostics-collection-made-faster/) if you have licensed the diagnostic (or/and tuning) pack?

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Sanjay Mishra <dmarc-noreply_at_freelists.org> hat am 18. August 2017 um 05:20 geschrieben:
>
> 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