Re: 8.1.7.3 and the shared pool

It may be do to more and larger DBMS and UTL packages. Have you looked at a comparison of v$sgastat listings from before and after? usually shared pool sizes should range from 50-150 megabytes above the base needed for pinned code. If you get much more than this you get excssive latch thrashing and performance degrades (over about 5000 listings in V$SQLAREA which usually corresponds to over 130,000 in the underling structures.)

I would institute a temporary flush procedure and begin code fixes. I have an example procedure that I use in this type of situation. Another thing to remember about CURSOR_SHARING is it only affects SELECT, not IUD. If you do a lot of dynamic IUD activity then CURSOR_SHARING may not help.

I have a paper and some scripts on tuning the shared pool, email me if you would like it.

Mike Ault
Senior Technical Management Consultant
TUSC Consulting

"Anton Buijs" <aammbuijs_at_xs4all.nl> wrote in message news:<aeo1jb$rst$1_at_news1.xs4all.nl>...
> That's true, no explanation. Must say I don't have that experience. Recently
> upgrade a small database from V8.0.6 to V8.1.7.3. Some to do on VMS from
> V8.0.5. But will try to find out.
> And also can't imagine why. You did not change any other parameters (like
> cursor_space_for_time or so)? Or changed defaults? Just guessing.
>
> Scott Gamble <zifnab_at_reddragon.org> schreef in berichtnieuws
> VnLP8.5619$Bz4.30905_at_petpeeve.ziplink.net...
> | "Anton Buijs" <aammbuijs_at_xs4all.nl> wrote in
> | news:aentb1$hm1$1_at_news1.xs4all.nl:
> |
> | > Sounds like your database is a candidate for using the (new) init.ora
> | > parameter CURSOR_SHARING=force.
> | > Literal strings are replaced by bind variables before the statement is
> | > loaded in the shared pool. Later the literal values are binded. This
> | > could significantly reduce the number of different statements in the
> | > shared pool in your case.
> | >
> | > Read Metalink note 94036.1 " Init.ora Parameter "CURSOR_SHARING"
> | > Reference Note".
> | > Bugs are reported too! (It's a new feature you know).
> | >
> |
> | It is a perfect candidate for it and is in the plans to do at some point
> | down the road (doubtful on 8.1.7.3 we have hit enough bugs with it in 1.5
> | months of using it that management is going to be very leary of the new
> | features). This still doesn't explain why 8.1.7.3 needs so much more
> | space.
> |
> | Scott
Received on Wed Jun 19 2002 - 06:42:03 CDT