Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tesla.

Asked: November 15, 2016 - 4:47 am UTC

Last updated: November 19, 2016 - 3:44 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

I would like to know how much amount of data gets stored in the buffer cache? Say, if I have huge amounts of RAM on the server, and say If I increase SGA proportionately, will my buffer cache high amount of data near to that of RAM allocated?

What determines the amount of data that can cache inside buffer cache? Can I set a value manually for that?

Also, I have recently gone through this link https://oracle-base.com/articles/12c/full-database-caching-mode-12cr1 where it mentions about FULL Database Caching which says it will cache the entire database, in this case does it cache the data with respect to amount of RAM allocated?

and Connor said...

All of the caching mechanisms (buffer cache, large table cache, full database cache) are totally under *your* control.

If your server has 128G of RAM, but you set your buffer cache to 2G (db_cache_size)...then we will only use 2G.

If you are using automatic memory management (memory_target or sga_target) then we'll be bound by that as well. SO if you set sga_target to (say) 64G, then the buffer cache could conceivably grow to almost that size (with the rest being kept for shared pool etc) but we wont grow the buffer cache unless needed. So if you've only read 10G of data, we dont need a buffer cache of 60G

Hope this helps.

Rating

  (2 ratings)

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

Comments

Specifically on full DB caching

Tesla AR, November 16, 2016 - 3:14 pm UTC

Can you shed some light on Full Database Caching which is available from 12c onwards.?

1. When should one prefer over the normal usage?
2. Is it like in-memory where it stores all the data in the RAM?
3. When using full-database caching, should I specifically mention the buffer cache size for it to hold the full data?

Thanks in advance!!
Connor McDonald
November 17, 2016 - 2:47 am UTC

1. When should one prefer over the normal usage?

If your I/O subsystem is a significant bottleneck. Even with a large buffer cache, we might do lots of things direct from disk. Full database caching endeavours to avoid any such activity.

2. Is it like in-memory where it stores all the data in the RAM?

No. In-memory is a reformatting/restructuring of existing row data into a new format.

3. When using full-database caching, should I specifically mention the buffer cache size for it to hold the full data?

As a minimum, I would set db_cache_size so I know that there is a minimum size the cache will be kept to.

Reporting and full db caching

Tesla AR, November 19, 2016 - 3:15 am UTC

Say I have an reporting database where only reporting runs, fetching of reports etc. Only selects, in that case, if I enable full database caching and I set a high value to db_cache_size w.r.t the RAM, the response of the queries would be high compared to database without full database caching?
Connor McDonald
November 19, 2016 - 3:44 am UTC

It depends on the server and the usage of it. I have seen queries full based in RAM run *slower* than queries that come off disk, because of increased competition for buffers.

The answer is simple: Benchmark it.
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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.