RE: Cursor Issue

From: Coleman, Kelley (HAC) <Kelley.Coleman_at_va.gov>
Date: Tue, 21 Mar 2006 10:59:15 -0700
Message-ID: <FA94A8C58E32ED41B749787F4DB158C469B8EA@VHAV19MSGA2.v19.med.va.gov>

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

From: Paul Drake [mailto:bdbafh_at_gmail.com] Sent: Tuesday, March 21, 2006 10:46 AM
To: Coleman, Kelley (HAC)
Cc: Oracle-L_at_Freelists
Subject: 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

Received on Tue Mar 21 2006 - 11:59:15 CST