Oracle 12c, big table caching

In Oracle 11g,  there has been annoying change which has frequently had a seriously detrimental effect to the application performance. All tables, larger than 2% of SGA, were considered big tables and were read using direct reads into PGA, instead of SGA. That has had two undesired effects:

  • Increased paging, as PGA had to be expanded, in order to receive all those rows
  • No sharing, since the PGA is not visible from other processes (or threads).

Good news about it was preventing a full table scan of a big table (see the definition of big tables above) from flooding the SGA and throwing potentially useful blocks out of it. This mechanism is described here:

http://afatkulin.blogspot.co.uk/2012/07/serial-direct-path-reads-in-11gr2-and.html

However, this story has a sequel, in Oracle 12c. Direct reads were detrimental to performance, so Oracle decided to allow creating of an area withing db cache, dedicated for the large tables. That is so called “big table cache” which, when activated, houses blocks from big tables. Those blocks can no longer flood the SGA and throw the useful blocks out of the cache, as they are now stored in separate pool.  Also, the detrimental direct reads are greatly reduced by using the big table cache. I wrote about the mechanism before, here::

https://dbwhisperer.wordpress.com/2014/10/06/poor-mans-in-memory-caching/

However, now I got to test it and the results were very beneficial, across the board. Bad news is that Oracle 12c requires more memory than Oracle 11G. That should have been expected. Each new version ended up using significantly more memory than the previous one, so this is not really surprising. However, the prices of memory are falling so rapidly that  they more than offset increased hunger for memory by new versions of Oracle RDBMS, so that shouldn’t be a problem.

PS:

Also worth reading is the following article by Tanel Poder:

Optimizer statistics-driven direct path read decision for full table scans (_direct_read_decision_statistics_driven)

 

Advertisements

About mgogala

I am a consultant with an extensive Oracle DBA experience. I have worked on very large databases. I have worked with OPS and RAC since its inception.I am also a published book writer, having published two books about PHP. This blog is about the challenges and adventures in my professional life. Sorry, no family pictures here.
This entry was posted in Miscellaneous Oracle. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s