Buffer Cache :
What is the Buffer Cache?
The buffer cache is part of the SGA. It holds copies of data blocks so as they can be accessed quicker by oracle than by reading them off disk.
The purpose of the buffer cache is to minimize physical io. When a block is read by Oracle, it places this block into the buffer cache, because there is a chance that this block is needed again. Reading a block from the buffer cache is less costly (in terms of time) than reading it from the disk.
Different Pool within Cache :
The keep pool’s purpose is to take small objects that should always be cached, for example Look Up Tables.
The recycle pool is for larger objects.
The default pool is for everything else.
Cold Area/Hot Area in Buffer Cache :
Each pool’s LRU is divided into a hot area and a cold area. Accordingly, buffers with in the hot area are hot buffers (and buffers in the cold are are called cold buffers).
By default, 50% of the buffers belong to the cold area and the other 50% belong to the hot area. This behaviour can be changed with _db_percent_hot_default (for the default pool) _db_percent_hot_recycle (for the recycle pool) and _db_percent_hot_keep (for the keep pool).
A newly read db block will be inserted between the cold and the hot area such that it belongs to the hot area. This is called midpoint insertion. However, this is only true for single block reads, multi block reads will be placed at the LRU end.
how to Flush Buffer Cache :
In 10g you can Flush Buffer using :
alter system flush buffer_cache.
Some common wisdom says that the larger the buffer cache is, the better the performance of the database becomes. However, this claim is not always true.
To begin with, the cache needs to be managed. The bigger the cache, the larger the LRU and dirty list becomes. That results in longer search times for a free buffer (buffer busy waits.
Also, the bigger the cache, the greater the burden on the DBWn process.
What is Shared Pool
the shared pool is the part of the SGA where (among others) the following things are stored:
- Optimized query plans
- Security checks
- Parsed SQL statements
- Object informatio.
Shared Pool Latch :
used when memory is allocated or freed in the shared pool.
Library Cache latch :
this latch protects operations within the library cache.
Flush Shared Pool
alter system flush shared_pool
Allocation in memory
Memory in the shared pool is managed and freed in a LRU fashion, that is, Memory that isn’t used for long gets aged out first. This is in contrast to the large pool, where memory is managed in a heap (using allocate and free).
Check Shared Pool Size :
select name, bytes/1024/1024 "MB" from v$sgastat where pool = 'shared pool'
order by bytes desc;
Refence Link :
Published by Osama Mustafa
Osama considered as one of the leaders in Cloud technology, DevOps and database in the Middle-East. I have more than ten years of experience within the industry. moreover, certfied 4x AWS , 4x Azure and 6x OCI, have also obtained database certifications for multiple providers.
In addition to having experience with Oracle database and Oracle products, such as middle-ware, OID, OAM and OIM, I have gained substantial knowledge with different databases.
Currently, I am architecting and implementing Cloud and DevOps. On top of that, I'm providing solutions for companies that allow them to implement the solutions and to follow the best practices.
View all posts by Osama Mustafa
2 thoughts on “buffer cache and Shared Pool”
Thank you osama ;-).
Welcome … Hope it was useful