Monitoring and Tuning the Shared Pool

Let me begin this section by stating that the default values for the shared pool size initialization parameters are almost always too small by at least a factor of four. Unless your database is limited to the basic scott/tiger type schema and your overall physical data size is less than a couple of hundred megabytes, even the "large" parameters are far too small. What parameters control the size of the shared pool? Essentially only one, SHARED_POOL_SIZE. The other shared pool parameters control how the variable space areas in the shared pool are parsed out, but not overall shared pool size. In Oracle8 a new area, the large pool, controlled by the LARGE_POOL_SIZE parameter is also present. Generally speaking I suggest you start at a shared pool size of 40 megabytes and move up from there. The large pool size will depend on the number of concurrent users, number of multi-threaded server servers and dispatchers and the sort requirements for the application.

What should be monitored to determine if the shared pool is too small? For this you need to wade into the data dictionary tables, specifically the V$SGASTAT and V$SQLAREA views. Figure 2 shows a report that shows how much of the shared pool is in use at any given time the script is run.

SEE CODE DEPOT FOR FULL SCRIPTS

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.