Over at the ITToolbox blogs there’s a great entry on the Oracle shared pool advisory in 9iR2. This type of article is great as it presents a tool, describes it, and walks you right through applying the knowledge.
Here’s their description of the advisory:
The shared pool advisory is an Oracle9i feature that keeps track of the library cache’s use of shared pool memory. While doing this it keeps statistics to determine the behavior of differently sized shared pools. Typically, the advisory will keep a bucket of statistics for shared pool sizes that range from 50% below your current setting to 200% of your current setting. It is then up to us as database administrators to use these statistics to determine what the size of the shared pool should be through the use of the view V$SHARED_POOL_ADVICE.
Check out the article for more information on how to enable and use the advisory.