Hi,

We are upgrading from oracle 8 to oracle 10g. We have about 30 VB6
applications which connect to our oracle 8 database.

When connecting to Oracle 9 and 10g and the app tries to retrieve a
recordset (50,000+ records), it seems to load the whole recordset into
memory (RAM), and then into virtual memory when this fills up. (I can
show you the system performance graphs.)

I know this is not a VB forum but my code works with oracle 8 and not 9
or 10 so I think maybe someone here could help.

FYI:

Using ADO2.5 (or 2.8) to connect and the "OraOLEDB.Oracle" provider.

I can post some example code if required.

Thanks,

Colm

"CollyMitch" <colmjmitchell@gmail.com> wrote in message
news:1147256377.459005.47340@i40g2000cwc.googlegro ups.com...
> Hi,
>
> We are upgrading from oracle 8 to oracle 10g. We have about 30 VB6
> applications which connect to our oracle 8 database.
>
> When connecting to Oracle 9 and 10g and the app tries to retrieve a
> recordset (50,000+ records), it seems to load the whole recordset into
> memory (RAM), and then into virtual memory when this fills up. (I can
> show you the system performance graphs.)
>
> I know this is not a VB forum but my code works with oracle 8 and not 9
> or 10 so I think maybe someone here could help.
>
> FYI:
>
> Using ADO2.5 (or 2.8) to connect and the "OraOLEDB.Oracle" provider.
>
> I can post some example code if required.
>
> Thanks,
>
> Colm
>

Not sure what you want from us. Clearly the application needs to be fixed.
It shouldn't retrieve 50K records and cache them on the client. It would be
a rare situation where that would be proper and desirable feature. I
suspect that the developer did this because they are used to MS SQLServer
where reading data can block other readers. So they grab the whole dataset
and then disconnect from SQLServer (thus freeing locks). In Oracle there is
no knead to do that. (readers don't lock writers)
Jim

Hey,

Thanks for your reply.

We retrieve 50 thousand rows from oracle (we only use oracle) because
we want to download from the remote Oracle db to a local MS Access
database, removing the need for a live oracle connection and allowing
production to continue if the network is down. This may not be the most
ideal way, but it was required when the network was prone to failures.

We don't *want* to cache them on the client but load, record by record,
into the access db - the virtual memory caching is the issue we need
fixed! The issue is that it loads all the records into memory and the
PC (Win2k) soon runs out of virtual memory.

With Oracle 8 it doesn't seem to cache all the records on the client. I
guess it caches one record at a time, does some work then removes it
from cache, but not with 9 or 10.

Could this be an issue with ADO or the Provider not being compatible
with Oracle 9 or 10?

Thanks again,

Colm

"CollyMitch" <colmjmitchell@gmail.com> wrote in message
news:1147271598.236262.272930@j73g2000cwa.googlegr oups.com...
> Hey,
>
> Thanks for your reply.
>
> We retrieve 50 thousand rows from oracle (we only use oracle) because
> we want to download from the remote Oracle db to a local MS Access
> database, removing the need for a live oracle connection and allowing
> production to continue if the network is down. This may not be the most
> ideal way, but it was required when the network was prone to failures.
>
> We don't *want* to cache them on the client but load, record by record,
> into the access db - the virtual memory caching is the issue we need
> fixed! The issue is that it loads all the records into memory and the
> PC (Win2k) soon runs out of virtual memory.
>
> With Oracle 8 it doesn't seem to cache all the records on the client. I
> guess it caches one record at a time, does some work then removes it
> from cache, but not with 9 or 10.
>
> Could this be an issue with ADO or the Provider not being compatible
> with Oracle 9 or 10?
>
> Thanks again,
>
> Colm
>
I would look at the ADO. I have used Oracle Ole Objects in VB and never
encountered this type of problem. The Oracle middleware doesn't drag the
whole result set across unless you tell it you want it to. Then it doesn't
do any caching. ADO is just an API on top of the OCI (Oracle Call
Interface).
Jim

Ok,

Thanks for your help.

Colm