Re: Performance issue , OCI related ? - urgent , please

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 <ORACLE-L_at_fatcity.com> 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:andreyb_at_elrontelesoft.com
|

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).