Monitoring and Tuning the Shared Pool

The script in Figure 2 should be run periodically during times of normal and high usage of your database. The results will be similar to Figure 3. If your shared_pool_pct figures stay in the high nineties then you may need to increase the size of your shared pool, however, this isn't always the case.

Figure 3: Example Output From Script In Figure 2.

To often all that is monitored is how much of the shared pool is filled, no one looks how is it filled; with good reusable SQL or bad throw away SQL. You must examine how the space is being used before you can decide whether the shared pool should be increased in size, decreased in size or perhaps a periodic flush schedule set up with the size remaining the same. So how can we determine what is in the shared pool  and whether it is being properly reused or not? Let's look at a few more reports.

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.