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

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Wed, 22 May 2002 02:53:28 -0800
Message-ID: <F001.00467441.20020522025328@fatcity.com>

('binary' encoding is not supported, stored as-is)
>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
>

Hmm, I don't think that the problem is with the OCI. Normally (and that what the 'cursor' of old meant) you do at execution any processing (sorts included) which may be required to determine the result set, then move a kind of pointer forward that result set. Not much different from a succession of read() - the size of the buffer, performance questions apart, just means more or less moving a pointer. The behaviour you witness is more typical of a statement which would be executed again and again, skipping 0 lines on the first execution, 5000 on the second one, 10000 on the third one ... so as to always return 5000 lines.  Wanna bet ?

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroul
  INET: sfaroult_at_oriolecorp.com

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).

Received on Wed May 22 2002 - 05:53:28 CDT