What is Database Buffer Cache in Oracle?
The database buffer cache is the area where SQL commands are executed. When we update data, it does not change directly in the data files on the hard drive. The data blocks that contain the data we are working with are first copied to the buffer cache (if they are not already there). Changes (insertion, modification, or deletion of data) are applied to those copies of the data in the buffer cache. Then these data blocks remain in memory for some time until the space they occupy is needed to work on any other data.
When we want to look at some data, the operation of issuing information also occurs through the cache. First, the info blocks that we requested are found and copied to the cache buffer (again, if they’re not there yet); the specified data is then transferred to the PGA for further processing.
The term block. Data files are data grouped into blocks of fixed length. Data in tables (rows) and other objects like indexes, etc. are stored in these blocks. Buffer cache is additionally grouped into in-memory structures to contain exactly one block. Strings are often of various lengths: the length of a row depends on the amount of columns in it, it doesn’t matter whether these columns contain data or not. counting on the block size (the size is chosen by the database administrator) and therefore the size of the road , there could also be several lines during a block, or a line can span several blocks. we’ll take a better check out the block structure within the topic data organization.
Ideally, the info blocks containing the foremost frequently used data are going to be within the buffer cache, thus reducing the amount of read / write operations. As a typical example of how the cache buffer works, consider an example of a typical operation of a salesman during a store, which receives data a few customer and updates it. The following queries will be used:
SELECT cust_id, cust_name FROM CUSTOMERS;
UPDATE CUSTOMERS SET cust_name = 'JAMES' WHERE cust_id = 7752;
In order to execute the SQL command select that came from the user, the server process of this session first let’s see if there is already a data block in the buffer that contains the necessary information. If this information is found, the data will be read (hit) from the buffer. Assuming the data is not in the buffer (miss), then the server process reads blocks from the data files and places them in the buffer before returning the data to the user.
Then the user runs UPDATE and COMMIT commands, which will be processed by the server process. Assuming data is still available in the buffer cache at the time this query is executed, the row will be updated to the buffer cache. In this example, we get a buffer efficiency of 50%. 2 times access to data in the buffer cache, once reading data from disk. Calculation formula: 1- “number of reads from the disk” / number of reads from the buffer “). In well-optimized databases, this figure can reach 90%.
A buffer in which the data in the blocks differs from the data in these blocks on disk space is called dirty buffers. The buffer will be clean when the block has just been copied from disk to memory: at this point in time, the data is the same in memory and on disk. As a result, dirty blocks of data must be written back to disk and then the buffer will become empty again. But even after writing to disk, blocks remain in memory until they are overwritten with other blocks.
It is important that there’s no direct relationship between the amount of knowledge updates within the buffer (COMMIT commands) and therefore the number of operations for writing data back to data files. Writing to data files is performed by the background process – the database writer.
The size of the buffer cache is extremely important to performance. the worth should be adequately calculated, not minimal – in order that all frequently used blocks (no matter clean or dirty) are within the buffer, but not so large that even rarely used data is additionally within the buffer. Insufficient space will cause excessive use of read / write from disk, since blocks are going to be read from disk, immediately overwritten and skim again. overlarge a size isn’t as bad as too small (as long because it is a smaller amount than the dimensions of the particular available RAM), but it also can cause certain problems: for instance , starting the database will take longer, since formatting an outsized amount of memory is required.
Memory for the buffer cache is allocated at the stage of starting the database instance. Prior to version 9i, you cannot resize the cache buffer without restarting the instance. Starting from version 10g – the cache buffer size can be changed both automatically (enable the automatic control mechanism) and manually.
How the Database Buffer works:
The buffer is organized in two lists..
The writing list contains dirty swabs. These are data blocks containing modified data and must be written to data files.
Least Recent Use List (LRU):
The buffers on the LRU list are divided into Nailed, Clean, Free or Unused and Dirty Buffers. Currently, pinned buffers are used, while clean buffers are available for use. Although clean buffers contain some data but are synchronized with the content of the blocks stored in the data files, there is no need to write these buffers to disk. What are free buffers? We can explain that free buffers are empty and have not yet been used. The dirty buffers are the ones that had to be moved to write the list.
When the Oracle server process needs a particular block of data, it first looks at the buffer cache. If it finds the required block, it accesses it directly, and this event is called a cache hit. If the buffer cache lookup fails, it reads from the data file on disk and the event is called Cache Miss. If the requested block is not in the buffer cache, the process needs a free buffer to read the data from the disk. If the user process finds a dirty block in the LRU list during the search, it moves it to the write list. If the process cannot find the free buffers for a certain time, it processes the signals from the DBWn process to write the dirty buffers to disk.
By default, accessible buffers are moved to the end of the last use of the LRU list. The search for free buffers is started from the oldest end of the LRU list, i.e., newly accessible buffers are stored for a longer period of time. However, if a full scan of the table is performed, this means that the oracle process moves the table blocks to the oldest used end of the LRU list when SQL scans the entire table. This means that the oracle process quickly recognizes them again. During new table creation if you are providing a storage parameter Cache | NoCache| then it is best option. When a table is created with the Cache parameter, the data blocks of the table are added to the last used table at the end of the full scan.
When you want to make strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals , Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to be keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.