Can you switch on SQL trace whilst this is running, or peek into v$sql.
It sounds to me as if your code is "fetching an array" by re-opening the cursor, fetching and discarding all the rows up to the required point, and then returning the required rows. To answer your direct question - when doing array fetching correctly, the time for the Nth fetch should be constant (although the first fetch time may include sorts, hashes etc. and therefore be atypical). You might expect some slow-down as time passes if the fetched data is also subject to update, and increasing amounts of undo have to be applied as the fetch progresses to preserve read-consistency, but I wouldn't expect the effect to be as extreme as your figures indicate. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -----Original Message----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 22 May 2002 10:33 |Dear gurus ! |We are facing a severe performance problems here that i have no idea of how |to address. |There is a C++ program , which uses OCI , that does the following. |It initiates a long running SQL select statement, and then fetches the |result set from the DB in buffers of 5000 records. |Now , the problem is that it takes significantly (exponentially) more time |to fetch each subsequent result set. |I.e. , it takes a second to bring the first 5000 records, it takes 2 |seconds to bring the next 5000 rows , it takes 3 seconds to bring the third |set of 5000 rows and ....it takes a hour to bring the 100th set of 5000 |rows. |My main question is : what actually happens within the DB engine when you |execute a huge select and then fetch the results buffer-by-buffer ? | |Thanks a lot !!! | | |DBAndrey | |* 03-9254520 |* 058-548133 |* mailto:[EMAIL PROTECTED] | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
