That would be the sga_target and sga_max_size, then let Oracle manage from there. If you dont have plenty of memory, set shared_pool_size to the minimum size. On Fri, Jun 12, 2009 at 9:40 AM, Zelli, Brian <Brian.Zelli@xxxxxxxxxxxxxxx>wrote: > 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..... > > > ciao, > > Brian > > > > *Brian J. Zelli,* Ed.M. > > Sr. Database Administrator > > Enterprise Application/Systems Integration > > Information Technology - Roswell Park Cancer Institute > > phone: (716) 845-4460 email: brian.zelli@xxxxxxxxxxxxxxx > > > > ------------------------------ > *From:* Martin Berger [mailto:martin.a.berger@xxxxxxxxx] > *Sent:* Friday, June 12, 2009 10:27 AM > *To:* Zelli, Brian > *Cc:* Andrew Kerber; Mathias Magnusson; oracle-l@xxxxxxxxxxxxx > *Subject:* Re: sga > > Brian, > 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. > > br > Martin > > > On Fri, Jun 12, 2009 at 15:37, Zelli, Brian > <Brian.Zelli@xxxxxxxxxxxxxxx>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. > -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.'