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

From: joel garry <>
Date: Wed, 13 Nov 2013 09:07:26 -0800 (PST)
Message-ID: <>

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: Often, merely following the steps shows the answer. It is perfectly relevant for posting here too.


-- is bogus.
Received on Wed Nov 13 2013 - 18:07:26 CET

Original text of this message