Re: ** keep/pin objects in sga

hi:

A good question.There is no formula for this parameter. Different applications bring different results :)

I think you can build a test environment and estimate how big the shared_pool will be when you set the cursor_space_for_time to true for your applications.

And you can check x$kghlu to see how many chunks have been flushed out. Just for reference.

If you have enough free memory, set cursor_space_for_time to true and set shared_pool_size to a bigger value, 600M may be not enough for 9i in most cases :)

On 10/24/06, A Joshi <ajoshi977_at_yahoo.com> wrote:
>
> Eagle fan,
> Thanks. I tried to look up the REFERENCE manual for 9i but could not
> find explanation for x$ views : x$kghlu, x$ksmss, x$ksmsp
> I did find v$ views listed. Can you tell where I can find the explanation.
> Thanks
> About cursor_space_for_time : My current shared pool is 600MB. If I set
> cursor_space_for_time to true then I assume I will need to increase the
> shared pool. Any way I can compute or guess how much I need to increase.
> Thanks
> Go eagles.
>
> *eagle fan <eagle.f_at_gmail.com>* wrote:
>
> hi:
>
> If you have literal sql issue, I suggest you use bind variables rather
> than cusor_sharing.
>
> The parameter cursor_space_for_time is used to pin shared sql in
> shared_pool. You can find more info about this parameter..
>
> For advance warning, you can check the x$ tables: x$kghlu, x$ksmss,
> x$ksmsp
>
> On 10/22/06, A Joshi <ajoshi977_at_yahoo.com > wrote:
> >
> > Fuad,
> > Thanks. So no short cut there. OK. So I guess we still need to do
> > keeping of objects and sql. And put in bind variables. I assume there is
> > some improvement in 9i in handling all this. Is there anything else that can
> > be done to improve the sga usage and avoid its fragmentation and is there
> > way to get advance warning when it is fragmented and could be heading for
> > problems. Thanks
> >
> >
> > *Fuad Arshad <fuadar_at_yahoo.com> *wrote:
> >
> > cursor sharing still has a lot of bugs.
> > it depends on the environment but tom kyte also asked for the use of
> > this only at session level rather than system level
> >
> > ----- Original Message ----
> > From: A Joshi <ajoshi977_at_yahoo.com>
> > To: oracle-l_at_freelists.org
> > Sent: Friday, October 20, 2006 3:20:15 PM
> > Subject: ** keep/pin objects in sga
> >
> > Hi,
> > For efficient use of shared pool and SGA : On 9i : I assume it is
> > beneficial to keep objects and sql in sga. Or is it not needed on 9i? I
> > think parameter CURSOR SHARING can be set to FORCE as alternative to using
> > bind variables and that will help SGA use but there were issues with it. I
> > assume in 9i it is better. Any body with experience otherwise or any other
> > catch? Thanks for your help.
> > ------------------------------
> > How low will we go? Check out Yahoo! Messengers low PC-to-Phone call
> > rates.<http://us.rd.yahoo.com/mail_us/taglines/postman8/*http://us.rd.yahoo.com/evt=39663/*http://voice.yahoo.com>
> >
> >
> > ------------------------------
> > Want to be your own boss? Learn how on Yahoo! Small Business.
> > <http://us.rd.yahoo.com/evt=41244/*http://smallbusiness.yahoo.com/r-index>
> >
>
>
>
> --
> Eagle Fan
>
> Oracle DBA
>
>
> ------------------------------
> Want to be your own boss? Learn how on Yahoo! Small Business.<http://us.rd.yahoo.com/evt=41244/*http://smallbusiness.yahoo.com/r-index>
>
>

-- 
Eagle Fan

Oracle DBA

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