Oracle ODBC driver Pre-fetch problem with 8.1.78 Driver

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Tue, 6 May 2003 09:25:22 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA703A34E48@lnewton.leeds.lfs.co.uk>

Hi Nick,

Oracle's own ODBC driver for 8i and 9i relaese 2 both use pre-fetch counters, so you pre-fetch a number of rows from all tables and not a buffer size worth of rows. Microsoft's (broken) ODBC driver does it by setting up a buffer to read as many rows into as possible. Are you sure you have the Oracle driver in use ?

The default for Oracle's driver is now (9i) 10 rows, but I've seen it being set to 1 row - which isn't much of a pre-fetch. I'm pretty certain that it all maps down to the OCI attributes for a statement OCI_ATTR_PREFETCH_ROWS and OCI_ATTR_PREFETCH_MEMORY according to whichever ODBC driver you are using. I've not used the 8178 driver myself so I cannot comment on whether it has changed from prefetch rows to a prefetch buffer, however, the 9i one has gone back/stayed at prefetch rows.

The 64000 default you mention is almost 64K, but not quite. 65536 would be 64Kb.

Cheers,
Norman.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

-----Original Message-----
From: Nick Palmer [mailto:nick_at_kcicorp.com] Posted At: Saturday, May 03, 2003 12:50 AM Posted To: server
Conversation: Oracle ODBC driver Pre-fetch problem with 8.1.78 Driver Subject: Oracle ODBC driver Pre-fetch problem with 8.1.78 Driver

Hi all,

I'm having a strange problem with my Oracle ODBC Driver. I had the Oracle
8.1.74 ODBC driver on my system, and I upgraded to the 8.1.78 driver. After
doing this, I noticed that my network packet count has gone up doing simple
queries. It seems with this driver, that the driver is not pre-fetching any
data. I also noted that in the 8.1.78 driver that the prefetch count has
been replace by a prefetch buffer size. Is there a bug with the 8.1.78 ODBC
driver ?
Or do I have to configure the Pre-fetch buffer size differently than the Pre-fetch count ? I've left the size at the default of 64000 - I assuming
that means 64000 bytes = 64K.

Thanks,
Nick. Received on Tue May 06 2003 - 03:25:22 CDT