RE: Cursor Issue

  • From: "Coleman, Kelley (HAC)" <Kelley.Coleman@xxxxxx>
  • To: "Paul Drake" <bdbafh@xxxxxxxxx>
  • Date: Tue, 21 Mar 2006 10:59:15 -0700

That article looks to be exactly what I was looking for.  Thank you
very, very much.



From: Paul Drake [mailto:bdbafh@xxxxxxxxx] 
Sent: Tuesday, March 21, 2006 10:46 AM
To: Coleman, Kelley (HAC)
Cc: Oracle-L@Freelists
Subject: Re: Cursor Issue


On 3/21/06, Coleman, Kelley (HAC) <Kelley.Coleman@xxxxxx> 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


        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

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



Other related posts: