Re: 10g shared pool requirements

Which exact error messages do you get?

If you get ORA-4031's, then given the fact you actually have free memory in shared pool (as stated by shared pool free memory statistic), you might have too many shared pool subpools used.

What does the following query show?

select count(*) from x$kghlu where kghlushrpool > 0 ;

If it shows more than 1 then your shared pool has been partitioned into several subpools and you might be able to fix the issue by setting _kghdsidx_count to 1 (or lower value from your current one) if it's ok with Oracle support..

Tanel.

• Original Message ----- From: "Christo Kutrovsky" <kutrovsky.oracle_at_gmail.com> To: <Rich.Jesse_at_quadtechworld.com> Cc: "oracle-l" <oracle-l_at_freelists.org> Sent: Wednesday, May 04, 2005 7:06 PM Subject: Re: 10g shared pool requirements

> The evidence I have is the shared pool errors we have :)
>
> The other evidence i have is my v$sgastat output:
>
> select pool, name,round(bytes/1024/1024) as mb from v$sgastat order by
> pool,bytes desc;
>
> java pool free memory 16
> large pool free memory 15
> large pool PX msg pool 1
> shared pool sql area 370
> shared pool library cache 223
> shared pool miscellaneous 148
> shared pool free memory 87
> shared pool partitioning d 60
> shared pool KGLS heap 34
> shared pool KQR M PO 30
> shared pool KQR M SO 24
> shared pool KQR L PO 17
> shared pool PL/SQL MPCODE 6
> shared pool PL/SQL DIANA 3
> shared pool type object de 3
> shared pool KUPP subheap 1
> shared pool KQR L SO 1
> shared pool repository 0
> shared pool parameters 0
> shared pool PX subheap 0
> shared pool STREAMS messag 0
> shared pool pl/sql source 0
> shared pool PLS non-lib hp 0
> shared pool table definiti 0
> shared pool PL/SQL PPCODE 0
> shared pool trigger defini 0
> shared pool evaluation con 0
> shared pool KQR S SO 0
> shared pool joxs heap 0
> shared pool kwqbsinfy:bqgc 0
> shared pool fixed allocation callback 0
> shared pool trigger inform 0
> shared pool kwqbsinfy:stat 0
> shared pool kwqbsinfy:cco 0
> shared pool image handles: 0
> shared pool kwqbsinfy:bmsg 0
> buffer_cache 992
> fixed_sga 1
> log_buffer 1
>
>
>
> On 5/4/05, Jesse, Rich <Rich.Jesse_at_quadtechworld.com> wrote:
>> As Moe Syzslak would say, "WHAAAA??!??!"
>>=20
>> 1) What evidence do you have that you require 600MB of shared pool?
>>=20
>> 2) Are you using ASMM?
>>=20
>> I've got a small-ish 10.1.0.3.0 DB dedicated to Oracle's Ultra Search =3D
>> and it runs very happy with a 250MB total SGA on Solaris 9. Currently, =
> =3D
>> ASMM has 145MB of that for the shared pool, 60 for Java, and 40 for =3D
>> buffers.
>>=20
>> Rich
>>=20
>> Rich Jesse System/Database Administrator
>> rich.jesse_at_quadtechworld.com QuadTech, Sussex, WI USA
>> =3D20
>>=20
>>=20
>> -----Original Message-----
>> From: oracle-l-bounce_at_freelists.org
>> Sent: Wednesday, May 04, 2005 9:43 AM
>> To: oracle-l
>> Subject: 10g shared pool requirements
>>=20
>> Hello,
>>=20
>> We've been testing some of our systems with 10g. We frequenly run (on
>> multiple different environments) into shared pool issues.
>>=20
>> The solution so far has been to increase the shared pool size.
>> Databases that used to have < 100 Mb shared pool, now require 600 Mb
>> of shared pool to run. And those are small developement boxes.
>>=20
>> What are your observation on 10g shared pool usage?=3D3D20
>>=20
>> --=3D3D20
>> Christo Kutrovsky
>> --
>> http://www.freelists.org/webpage/oracle-l
>>=20
>
>
> --=20
> Christo Kutrovsky
> Database/System Administrator
> The Pythian Group
> --
> http://www.freelists.org/webpage/oracle-l

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