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@xxxxxxxxx> 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@xxxxxxxxx>* 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@xxxxxxxxx > 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@xxxxxxxxx> *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@xxxxxxxxx>
> To: oracle-l@xxxxxxxxxxxxx
> 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

Other related posts: