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:
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::
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.
Also worth reading is the following article by Tanel Poder: