Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Cursor Issue

Re: Cursor Issue

From: Paul Drake <>
Date: Tue, 21 Mar 2006 12:45:36 -0500
Message-ID: <>

On 3/21/06, Coleman, Kelley (HAC) <> wrote:
> I have a 3rd part app that collects message strings from various external
> sites, turns each string into an Oracle insert statement, then sends those
> inserts to my database. It's been working well for a couple of years, but
> as the number of messages is growing, I'm find that I have a cursor
> problem. I don't have the ability to change much about the 3rd party app,
> so I'm focused right now on providing what I can on the db side. Is there a
> way to have a unique cursor setting for a user or session?
> What I'm thinking is that, this process usually runs in the late evening
> when there aren't many users on the system, but the 1800 cursor limitation
> we have in the init.ora just doesn't seem to be enough. Can I up it just
> for this process? Or am I really forced to work with the app tech support
> (and I use that term loosely) to figure out why it's using so many cursors?
> It's not even like the numbers are all that huge. I can't see why it would
> take so many cursors to process less than 1,000 records.
> Any thoughts?
> Kelley Coleman
> Database Administrator
> VA Health Administration Center
> Denver, Colorado
> 303-331-7521-o
> Confidentiality Note: This e-mail is intended only for the person or
> entity to which it is addressed, and may contain information that is
> privileged, confidential, or otherwise protected from disclosure.
> Dissemination, distribution, or copying of this e-mail or the information
> herein by anyone other than the intended recipient is prohibited. If you
> have received this e-mail in error, please notify the sender by reply
> e-mail, phone, or fax, and destroy the original message and all copies.
> Thank you


You're on the right track.
The use of a logon trigger will support altering a specific user's sessions for parameters such as cursor_sharing. I used to do the same thing for load processes, setting the pga_workarea_policy to manual, hiking up the db_file_multiblock_read_count and sort_area_size to values suitable for a batch process.

Here is an example of such a script that Mike Ault published:

You'll likely want to go with cursor_sharing='FORCE'.



Received on Tue Mar 21 2006 - 11:45:36 CST

Original text of this message