by Asif Anwar
Data required by oracle user process is loaded into buffer cache, if it is not already present in cache. Proper memory tuning is required to avoid repeated disk access for the same data. This means that there should be enough space in buffer cache to hold required data for long time. If same data is required in very short intervals then such data should be permanently pinned into memory. Oracle allows us to use multiple buffers. Using multiple buffers we can control that how long objects should be kept in memory.
Continue reading Keep Buffer Pool and Recycle Buffer Pool
by Asif Anwar
Database Buffer cache is one of the most important components of System Global Area (SGA). Database Buffer Cache is the place where data blocks are copied from datafiles to perform SQL operations. Buffer Cache is shared memory structure and it is concurrently accessed by all server processes.
Working of Database buffer Cache
Buffer Cache is organized into two lists
Write List
Write list contains dirty buffers. These are the data blocks which contain modified data and needed to be written to datafiles.
Continue reading Database Buffer Cache
by Asif Anwar
When query is submitted to oracle server, various processing steps are performed to execute query. Following main steps are involved in this process.
1- Query Parsing
2- Optimizer determines most efficient way to execute query and generates an execution plan.
3- Finally SQL engine executes query.
In 1st step, SQL server checks syntax of query and outputs error if found.
After parsing of query, optimizer generates execution plan. Execution plan produced by optimizer depends on two main possible goals Continue reading SQL Processing