Shared Pool Size threshold

[sathidevi]

Hi,

Currently the shared pool size is 168 MB and the hit ratio is 78%. Can u advice me on how to increase the shared pool size to arrive >90% hit ratio


Thanks
Sathidevi

[davey23uk]

dont tune by ration, is there a problem you are trying to solve here or just playing with numbers?

[DaPi]

Are you using bind variables?

If you're always parsing something new, you'll never get a high ratios.

[dhar_kiran]

Either increase shared_pool or Tune SQL.Shared pool can be increased dynamically using alter system set shared_pool_size=,but first you need to check, if there is a free space available using v$sga_dynamic_free_memory.

Better first check, where problem is:
1) Misses (reload/pin ratio)
2) gethit/gets ratio, pinhit/pins ratio
3) Invalidations
4) Large Anonymous blocks
5) shared pool reserved

[davey23uk]

no you cant change shared_pool dynamically

[dhar_kiran]

but there is no error, when i give

alter system set shared_pool_size=;

Also, if is also displayed in
V$SGA_DYNAMIC_COMPONENTS;