Home | Tip of the Week | Tip of the Week Archive | Partners | Clients | History | Friends

Tip of the Week (Archived)
Advice on Improving Your Database's Memory II

So far we have covered the PGA_AGGREGATE_TARGET, The PGA_AGGREGATE_TARGET or Memory Management Made Easy(ier) (pdf), and the data cache, Advice on Improving Your Database's Memory. To complete the Oracle memory trilogy, this week I want to look at the shared pool.

The "advice" table providing information on the shared pool is v$shared_pool_advice. Below is a query of that table and the results from one of the databases I am managing.

SELECT shared_pool_size_for_estimate, shared_pool_size_factor, estd_lc_time_saved,
       estd_lc_time_saved_factor
FROM   v$shared_pool_advice;

SHARED_POOL_SIZE_FOR_ESTIMATE SHARED_POOL_SIZE_FACTOR ESTD_LC_TIME_SAVED ESTD_LC_TIME_SAVED_FACTOR
----------------------------- ----------------------- ------------------ -------------------------
                          176                   .5789             446426                     .9975
                          208                   .6842             446831                     .9984
                          240                   .7895             447128                      .999
                          272                   .8947             447366                     .9996
                          304                       1             447561                         1
                          336                  1.1053             447736                    1.0004
                          368                  1.2105             447871                    1.0007
                          400                  1.3158             447991                     1.001
                          432                  1.4211             448112                    1.0012
                          464                  1.5263             448238                    1.0015
                          496                  1.6316             448338                    1.0017
                          528                  1.7368             448428                    1.0019
                          560                  1.8421             448533                    1.0022
                          592                  1.9474             448633                    1.0024
                          624                  2.0526             448716                    1.0026

15 rows selected.

Note: The initialization parameter statistics_level must be set to either TYPICAL or ALL for this information to be available.

This information is based on statistics collected during database operation. The columns contain the following information:

• SHARED_POOL_SIZE_FOR_ESTIMATE - Shared pool size in MB
• SHARED_POOL_SIZE_FACTOR - Indicates how the SHARED_POOL_SIZE_FOR_ESTIMATE relates to the current shared pool. The size factor for the current shared pool size is 1.
• ESTD_LC_TIME_SAVED - (From Oracle documentation) Estimated elapsed parse time saved (in seconds), owing to library cache memory objects being found in a shared pool of the specified size. This is the time that would have been spent in reloading the required objects in the shared pool had they been aged out due to insufficient amount of available free memory.
• ESTD_LC_TIME_SAVED_FACTOR - indicates how the ESTD_LC_TIME_SAVED relates to the current shared pool. The size factor for the current shared pool size is 1.

Use the ESTD_LC_TIME_SAVED or  ESTD_LC_TIME_SAVED_FACTOR column to determine if a change would benefit the database. In both cases, a higher value indicates where adding memory would save parse time, so higher values show shared pool settings that should improve performance. I like ESTD_LC_TIME_SAVED_FACTOR, just because I think it provides a clearer picture of the results.

In this case, the values in both ESTD_LC* columns are slowly, steadily increasing, with no particular value indicating a point where performance would markedly improve. That fits this particular database environment, in that the application generates a significant amount of SQL that does not use bind variables, resulting in reparsing and little statement reuse. We tried to address this issue by setting the CURSOR_SHARING parameter to FORCE, but found this resulted in errors in the use of LONG columns.

The recommendation at this point is that we will leave the shared pool size where it is. If we wanted to change the shared pool size, the statement to change it would be:

ALTER SYSTEM SET shared_pool_size=448M SCOPE=BOTH;

Note: This tip was tested on Oracle9i.

Was this tip useful? Did you find any errors? Do you have any suggestions? Do you care? Click here for the tip feedback page. Thank you.

2000 Turnberry Circle, Glenmoore, PA 19343
Voice: (610) 942-1979
Fax: (610) 942-1990
Email

© 2005 Alydan Consulting, Inc.