Shared Pool Questions

[webids]

I'm trying to determine if the shared pool on one of my production Oracle 7.3.4 databases is, in fact, to large. This database has been online for over 4 years and has never exhibited performance issues. The environment on the server (HP UNIX 10.20), however, is changing and I need to determine if any Oracle resources can be freed.

Here are the facts concerning the database in question:
- The server, as well as the database are reboot every 30 days.
- The library cache hit ratio is consistantly better than 99%
- The data dictionary hit ratio typically is between 88 and 90%.
- The SGA free memory typically falls between 1 to 15 MB.

I'm aware I can run UTLBSTAT and UTLESTAT for defined periods of time. Will these scripts, however, allow me to determine if the shared pool is sized to large or will they only tell me if it is sized to small?

Is there a way, short of changing the vaule of the shared pool and monitoring the results, to determine if the shared pool is sized to large? All ideas and comments welcome.

Thanks,
Bill

[gpsingh]

Check for:

1) Regular free available memory in SGA

2) Too many Shared pool latch sleeps as compared
to other latch sleeps/waits.

[Raminder]

Issue

Select VALUE from V$SGASTAT 
where POOL = 'shared pool'
and 
NAME = 'free memory'

If this value is consistently high over time and another area needs memory, you can decrease the SHARED_POOL_SIZE.