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

Several weeks ago I did a tip on use of the PGA_AGGREGATE_TARGET, The PGA_AGGREGATE_TARGET or Memory Management Made Easy(ier) (pdf). Oracle has also provided new ways of evaluating the effectiveness of the shared pool and the data cache with two new "advice" tables.

This week let's look at the data cache. The "advice" table providing information on the data cache is v$db_cache_advice. Below is a query of that table and the results from one of the databases I am managing.

SELECT name, size_for_estimate, size_factor, estd_physical_reads
FROM   v$db_cache_advice;

NAME                 SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READS
-------------------- ----------------- ----------- -------------------
DEFAULT                             32       .1053             1222657
DEFAULT                             64       .2105             1109756
DEFAULT                             96       .3158              976119
DEFAULT                            128       .4211              923853
DEFAULT                            160       .5263              896285
DEFAULT                            192       .6316              857879
DEFAULT                            224       .7368              820841
DEFAULT                            256       .8421              799795
DEFAULT                            288       .9474              781780
DEFAULT                            304           1              775526
DEFAULT                            320      1.0526              768642
DEFAULT                            352      1.1579              758132
DEFAULT                            384      1.2632              748394
DEFAULT                            416      1.3684              739151
DEFAULT                            448      1.4737              644383
DEFAULT                            480      1.5789              628819
DEFAULT                            512      1.6842              588339
DEFAULT                            544      1.7895              572859
DEFAULT                            576      1.8947              555499
DEFAULT                            608           2              536921
DEFAULT                            640      2.1053              525849

21 rows selected.

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

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

• NAME - The buffer pool for which the data applies
• SIZE_FOR_ESTIMATE - Buffer cache size in MB
• SIZE_FACTOR - Indicates how the SIZE_FOR_ESTIMATE relates to the current cache size. The size factor for the current cache size is 1.
• ESTD_PHYSICAL_READS - The estimated number of physical reads for the given cache size. We assume more physical reads will hurt performance, while fewer will improve it.

Oracle provides estimates for cache sizes from 10% to 200% of the current cache size, tracking up to 20 different estimated values.

Use the ESTD_PHYSICAL_READS column to determine if a change would benefit the database. In this case, the value for the current cache size is 775,526. Based on these estimates, increasing the cache will result in fewer physical reads, with a significant improvement occurring at 448MB. After first ensuring that the SGA_MAX_SIZE is set high enough to allow the change, I would recommend making that change, with the following command:

ALTER SYSTEM SET db_cache_size=448M SCOPE=BOTH;

After allowing the database workload to adjust to the new cache, the above query can be rerun to determine the effectiveness of the change and if any other adjustments are necessary.

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.