Re: Rowcount, OCI and small memory questions

Not sure how you have things written in your application but let me clarify. Oracle has server side cursors so you don't have to issue a query and retrieve ALL the rows of the result set. If you want to you are free to do so, but it is not a requirement. (readers don't block writers and writers don't block readers so you don't have to get the entire result set into memory and close the cursor to work on it.) I would have to carefully read all OCI developer's guide, but basically things work like this:

(assuming you are logged on via oci)
You specify your query (hopefully with bind variables) , parse, describe, and execute it. Execute it does NOT mean you are retrieving any results. Then you fetch your rows. You can fetch them one at a time or you can use an array interface and fetch them N rows at a time (eg 10 rows at a time). For performance reasons it is better to use the array interface instead of fetching 1 row at a time. So you control the fetch via OCI. You control the memory allocations etc. So is it possible to write a program that uses OCI and tries to put 1 billion records into memory of the client. Certainly, but you the application designer has to make that decision.

Jim

"Buffy The Cache Coder" <buffcoder_at_hotmail.com> wrote in message news:e3850c89.0207311324.c142853_at_posting.google.com...
> So then what you are saying, is that there is no such thing as
> the oracle client libraries filling up all of process memory
> and core dumping when it fetches a large result set.
>
> It will fetch as many rows as it can fit in the part of
> process memory that has been assigned to it (the client's buffer),
> which is probably fixed or at least bounded.
>
> Then, the application's will retrieve these rows from the client's buffer.
> The oracle client will then fetch another part of the result set as the
> application 'retrieve-loops'.
>
> In otherwords, what you are saying is that it appears to be an
> application memory bug, not Oracle, right?
>
> thanks in advance!
>
>
> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
news:<K3H19.705637$352.153308_at_sccrnsc02>...
> > You don't have to retrieve all the rows, just retrieve the number you
want.
> > You could have a billion rows and as long as you don't try to display
them
> > etc. in ram on the client you are fine. Just because you issue a query
it
> > does not mean you have to retrieve ALL the rows; just retrieve a smaller
> > number.
> >
> > Jim
> > "Buffy The Cache Coder" <buffcoder_at_hotmail.com> wrote in message
> > news:e3850c89.0207300959.5c73a6c7_at_posting.google.com...
> > > Someone in our company has complained that if an inexperienced
> > > person writes a query which returns a large result set (via OCI)
> > > to a process having limited amount of memory, it will
> > > make the process core dump.
> > >
> > > Is this true? Is there a way to set the maximum memory used
> > > by the oracle client libraries?
> > >
> > > The suggestion at our company was to provide a way to limit
> > > the rows returned in a result set.
> > >
> > > Doing some research, Oracle has a ROWNUM feature, but it really
> > > isn't the same thing as Sybase' set rowcount <NUM>, because
> > > you have to add ROWNUM into the actual query.
> > >
> > > Does Oracle have an >environmental< ROWNUM feature which the
> > > DBA can set, or maybe can be set at the sql prompt? Or perhaps
> > > it can be something set per user, or database, or <something>?
> > >
> > > thanks.
Received on Wed Jul 31 2002 - 21:04:09 CDT