Re: Select statement tuning - help required

barraboombarrabin 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 ?.

If have UNDO then we can guess, since you don't say, that you have 10g. So start with using the undo advisor. http://www.psoug.org/reference/dbms_undo_adv.html

Then, if it continues, my guess is you have incremental commits.

Post the statement(s) causing the problem.

PS: We work best when we have something to look at.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org