Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Bolat.

Asked: September 02, 2016 - 12:21 pm UTC

Last updated: August 03, 2017 - 1:06 am UTC

Version: 11.2.0.4.

Viewed 10K+ times! This question is

You Asked

Hi,
We have DB with enabled AMM (Automatic Memory Management)
Version : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

Parameters:
sys@XXX_DB1> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 3600M
memory_target                        big integer 3600M
parallel_servers_target              integer     128
pga_aggregate_target                 big integer 0
sga_target                           big integer 0
sys@XXX_DB1>
sys@XXX_DB1> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 3600M
sga_target                           big integer 0

sys@XXX_DB1>
sys@XXX_DB1> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 0


We want to enable parameter DB_CACHE_SIZE.
Are there any BEST PRACTICES for enabling such option with enabled AMM?
If current parameter is changed or some connected parameters are also changed?
How to define optimal value for DB_CACHE_SIZE parameter?

With regards!

and Connor said...

If sga_target or memory_target are defined, then db_cache_size takes on a differnet meaning. It specifies the *minimum* size that the db cache is allowed to be. It it still allowed to grow past this.

You can use the v$db_cache_advice to determine what you might want to set this to.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DB_CACHE_SIZE is must or not

Satender Dalal, August 02, 2017 - 10:20 am UTC

Hi Tom,

If SGA_TARGET or MEMORY_TARGET are set or either of them is set, then will it be fine if DB_CACHE_SIZE is 0.
Or we must set some value to DB_CACHE_SIZE, for performace improvement.
(I am talking about this for performance improvement)
Connor McDonald
August 03, 2017 - 1:06 am UTC

If they are set, the setting db_cache_size acts as a lower limit. For example:

sga_target = 1000M

means I'll share out that 1000M, and if I need (say) more shared pool, the database can shrink the buffer cache down to whatever it wants to meet that need.

sga_target = 1000M
db_cache_size = 400M

means the same, but no matter what, the database will not shrink the buffer cache to less than 400m.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.