Re: Cursor Issue
- From: "Paul Drake" <bdbafh@xxxxxxxxx>
- To: Kelley.Coleman@xxxxxx
- Date: Tue, 21 Mar 2006 12:45:36 -0500
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 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
>
>
>
Kelley,
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:
http://www.dba-oracle.com/oracle_tips_ault_custom_parameters_users.htm
You'll likely want to go with cursor_sharing='FORCE'.
hth.
Paul
Other related posts:
