Re: Select statement tuning - help required

On Nov 7, 10:46 pm, barraboombarrabin <barraboombarrab..._at_yahoo.com> wrote:
> There is no difference in the plan on the remote site or the local
> site or in the trace.
> When I run the query in SQL Plus, I have observed that the query
> starts returning rows very quickly i.e. in a few seconds, however as
> the records spool, the rate at which they returned slows down and then
> stops. After some time, I get an ORA-1555.
> Can some one tell me how do I analyze this and if I have to make a
> case of changing the retention period for UNDO how do I make that
> case ?

You noted that the query spools in SQL*PLUS what sounds like a lot of rows, but the plan says only one will be returned at the end? If you actually expect what sounds like thousands of rows, I would say your statistics are messed up.

How many rows are in WSH.WSH_DELIVERY_DETAILS? That index lookup is the most expensive part of the plan (using questionable stats, as noted). It is doing an index lookup by rowid on each of 281,000 rows? Does that number sound right?

TABLE ACCESS BY INDEX ROWID WSH.WSH_DELIVERY_DETAILS Cost:30,933 Bytes: 4,778,564 Cardinality: 281,092 Received on Thu Nov 08 2007 - 20:29:39 CST