RE: sga

From: Zelli, Brian <>
Date: Fri, 12 Jun 2009 10:40:30 -0400
Message-ID: <>

Yes. This application has the ability to build queries with drop down selects and then builds this one huge sql to execute. There are several forms that do this so it's not just one but they are complaining most about this one form.

If I need to change the library cache, which parameter would that be? Again, I haven't really tuned much in 10g. I was a 7 - 9 junkie and it just seems like a lot of these parms are hands off.....


Brian J. Zelli, Ed.M.
Sr. Database Administrator
Enterprise Application/Systems Integration Information Technology - Roswell Park Cancer Institute phone: (716) 845-4460 email:<>

From: Martin Berger [] Sent: Friday, June 12, 2009 10:27 AM
To: Zelli, Brian
Cc: Andrew Kerber; Mathias Magnusson; Subject: Re: sga


I read this as 'all the SQLs are unique, and really big' - am I right?

if they are unique, they MUST be hard parsed every time. In such a case, it might be worth reducing the Library Cache, to reduce the time every process needs to search through it until it does not find a preparsed object and parses itselve.

just an idea, totally untested, still without enough understanding of the setup.


On Fri, Jun 12, 2009 at 15:37, Zelli, Brian <<>> wrote: The form for the application does build some really crappy dynamic sql, 2 pages worth. The vendor said "oh you have to add some indexes" but that doesn't look to be the case. I seem to be leaning toward the cursor issue myself. I'll see what it is set to.

This email message may contain legally privileged and/or confidential information. If you are not the intended recipient(s), or the employee or agent responsible for the delivery of this message to the intended recipient(s), you are hereby notified that any disclosure, copying, distribution, or use of this email message is prohibited. If you have received this message in error, please notify the sender immediately by e-mail and delete this email message from your computer. Thank you.

Received on Fri Jun 12 2009 - 09:40:30 CDT

Original text of this message