Diving Into the Shared Pool - An In Depth Look at Tuning the Shared Pool (Part 2) Oracle Tips by Mike Ault

The Shared Pool and MTS

The use of the multi-threaded server option (MTS) in Oracle requires a sometimes dramatic increase in the size of the shared pool. This increase in the size of the shared pool caused by MTS is due to the addition of the user global areas required for sorting and message queues. If you are using MTS you should monitor the V$SGASTAT values for MTS related memory areas and adjust the shared pool memory allocations accordingly.

Note that in Oracle 8 you should make use of the large pool feature to pull the user global areas (UGA) and multi-threaded server queues out of the shared pool area if MTS is being used. This prevents the fragmentation problems which have been reported in shared pools when MTS is used without allocating the large pool.

Guideline 5: In Oracle7when using MTS increase the shared pool size to accommodate MTS messaging and queuing as well as UGA requirements. In Oracle8 use the Large Pool to prevent MTS from effecting the shared pool areas.

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.