Re: Cursor Issue

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

--
http://www.freelists.org/webpage/oracle-l