Re: Sometimes query in waiting status and taking more than 30 mins to execute

On Wednesday, November 13, 2013 5:23:46 AM UTC-8, oracle_user wrote:
> I hava a query. Most of the times it executes very fast 15 -20 seconds but at times it takes more than 30 mins to execute.In V$session it shows in 'WAITING' status and event is 'db file sequential read' or 'direct path read'. Wait class is 'User I/O'. Plz suggest why does this happens and how to improve this.

The major reason this happens is a plan change. Typically, the fast one uses some index to get a small number of rows, while the slow one does nested loop or full object scans (or both). There are a number of reasons this can happen, such as statistics that don't accurately reflect the data, or bind peeking (where a previous use of the sql came up with a plan that is wrong for this query).

So the first thing to do is get the good and bad plans. This is a common request on the Oracle forums, so there is a suggested methodology: http://oracle-randolf.blogspot.com/2009/02/basic-sql-statement-performance.html Often, merely following the steps shows the answer. It is perfectly relevant for posting here too.

jg

-- 
_at_home.com is bogus.
http://www.theregister.co.uk/2013/11/13/oracle_to_pour_hot_steaming_java_into_heterogeneous_haven/