Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to understand Oracle Buffer". In daily operation, I believe many people have doubts about how to understand Oracle Buffer. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "how to understand Oracle Buffer"! Next, please follow the editor to study!

An Oracle Buffer is an Oracle segment object cache block. An Oracle buffer initially contains the same information as in the Oracle block. The content of a buffer depends on the segment type and whether it is lagging to be a segment header block. Buffer has many states represented by the state column of v$bh, which may be grouped into modes: free (available), dirty (dirty), and pinned (fixed).

Free Buffers

When a buffer matches a block of data on disk, its state is free. A free buffer can be thought of as a mirrored buffer because it mirrors blocks of data on disk. The following query simply shows how to determine the number of free buffers in buffer cache. A free buffer may indeed be empty (for example, after the instance is restarted), but it will most likely contain real block information, such as line records. A free buffer can be replaced without any damage because a copy is stored on disk. Of course, if a transaction commits, at least the modified buffer must be recorded in the online redo log file.

SQL > select count (*) from v$bh where status='free'; COUNT (*)-24

A free buffer may not be visited frequently. Maybe a query needs to access a single row of data, so it needs to put a block of data into a buffer cache, and that buffer has never been accessed since. On the other hand, a free buffer can also be accessed frequently. For example, if a particular data block is queried repeatedly, it will be accessed frequently, but its state is still free because the buffer has not been changed. If your definition of freebuffer is simple and clear, then many Oracle algorithms will also become clear, which will make it easier to understand, detect, and resolve competition.

Dirty Buffers

When a buffer cannot match the relevant blocks on disk, its state is dirty. Any changes made to a buffer will use its state to become dirty, because the buffer will no longer match the block on disk. Dirty blocks cannot be replaced when changes in memory are not written to disk and are to be overwritten. Once the database write process writes a dirty buffer to disk, the buffer will match the block on the disk again, and the state of the buffer will become free.

A dirty buffer may not be accessed frequently. Suppose a row of records is modified but other processes do not need to access this buffer. Because the row record is changed, this block is indeed dirty, but it is not accessed frequently. Of course, there are also frequently visited dirty buffers. Simply updating a row of records repeatedly ensures that its buffer status is dirty and is accessed frequently.

The following query shows that the status of dirty buffers may be xcur or write. Buffers for current and consistent modes will be described in detail in cache buffer chains. The xcur state means that a process has changed the state of a current mode buffer to this state, and the process may now update row records in buffer, although row records are still subject to other conditions, such as row-level locks. Exclusive mode does not prevent multiple users from changing multi-line records in the same buffer, it simply means that the buffer of current mode can be changed. This is critical in a RAC environment where there may be multiple shared current schema buffers (scur), but only one exclusive current schema buffer exists for each block in the entire RAC database.

SQL > select status, count (*) from v$bh where dirty='Y' group by status;STATUS COUNT (*)-- xcur 20792scur 919pi 2567

Pinned Buffers

When a buffer is pinned, it cannot be replaced. Another way to look at pinning is an unofficial lock on buffer. Because an buffer is not a relational structure, the standard locking mechanism cannot be applied. Pinning A specific buffer,latches or mutexes can control access to the entire set of buffers. Pinning can be used in conjunction with latch and lock to ensure proper serialization, protection, and parallel control are implemented.

Suppose a server process is about to read a row of records in a buffer. While you are still accessing this line of records, it is extremely rude for someone to replace the buffer you are visiting with another buffer. It's like when you're reading a book, someone says, "Let me see," and takes it away from you. Many processes can pin the same buffer (reading the same block), but only one process can pinned the buffer, which cannot be replaced. When the row records of a free buffer are being queried, its state changes from free to pinned and back to free again. When the row record in free buffer is modified, its buffer state changes from free to pinned, and then to dirty.

Oracle does not display pinned buffers through the v$bh view, but any buffer that is touched is pinned. Oracle will also pin the buffer when a buffer is being moved to the write list and the touch count is being updated.

The role of Buffer Headers

When buffers is built into buffer cache and buffers has indeed been changed, list management actually works on buffer headers, not the actual buffers. A buffer header is an optimized memory structure that contains information about an buffer and its associated blocks, but does not contain block data such as row records.

Why there is no view for buffer cache? this is because the metadata of a buffer and a block is stored in buffer header, and its metadata is needed for our performance analysis. So the view is named v$bh, and there are three key lists or chains for buffer header:

The .Cache buffers chains (CBCs) is used to quickly determine whether an Oracle block is built into the buffer cache.

. The most recently used (LRU) column is used to retain the frequently accessed buffers in the cache and find the free buffers.

. The write list contains the dirty buffers that will soon be written to disk.

It is important to understand these three lists of buffer headers rather than the actual buffers. A single buffer header is always built into a CBC and a LRU chain or a write list.

The three lists are maintained at the buffer header level, not at the buffer level, let alone at the block level. Many of us have been taught that when buffer is built into buffer cache, buffers itself is linked. This is not correct. Each buffer is associated with a buffer header, and buffer header is manipulated in various lists.

Cache Buffer Chains

In short, CBCs is used to answer "whether this buffer is in buffer cache, and if so, where is it?" this is essentially a search type question. Many types of search algorithms may be used to get answers: binary tree, B + tree, B* tree, sequential search, hash algorithm, or some combination of algorithms. Oracle chose to use a hashing algorithm, followed by a fast sequential search.

Hash algorithm

Hashing algorithms can be very fast because the entire structure is usually stored in memory and requires a separate mathematical calculation, while there is some memory access to answer search questions. There are many changes in hash structures, but all hash structures are made up of a hash function, a hash bucket and a hash chain.

Hash function

The hash function receives the input and uses the defined range to produce an output. The input is called a hash. The x mod 10 function can simply be used to ensure that its output is always between 0 and 9, regardless of the positive hash of the input. The hash value is input 11 and the output will be 1. A good hash function will produce uniformly distributed output. When Oracle is about to search for a buffer, a hash value is generated based on the combination of the block's file number and the block number (it is also called the block address DBA). So the hash function is essentially a hash of the block file number and block number of the buffer. This is a very convenient and fast hashing situation.

Hash bucket

The hash input value will be hashed to the bucket, and each output value represents a separate bucket. In many hash cases, the number of hashes that may be entered exceeds the number of barrels. For Oracle, the possible hash output value is the number of Oracle blocks. However, in any case, the number of hash input values will be equal to the number of buffers in buffercache.

When two hashes are hashed to the same bucket, this is called a collision. Collisions are common for hashes. Collisions can be minimized by increasing the number of hash buckets, but it can be a disaster for high-performance programs, such as Oracle databases. For example, if the hash function of x mod 10 has a hash input value of 1000, a collision is certain to occur. To avoid collisions, the hash algorithm will require 1000 hash buckets with a completely uniform output. Use an excellent hash algorithm and a large number of hash buckets to reduce collisions. If the hash algorithm remains the same, you can increase the number of hash buckets.

Hash chain

Each hash bucket has an associated hash chain. When a searched object is hashed to a bucket, the chain of the bucket is searched sequentially to find the object. If the object is not found in the hash chain, we know that the object is not in the entire hash structure. If the hash chain is short, the sequential search will be completed quickly. If the object is not in cache, the chain length should be zero.

The CBC structure of Oracle is a complex memory structure, and Oracle must maintain serialization control. So it uses a serialization structure: latch or mutex.

How to destroy the performance of CBC

The best way to learn how to solve a performance problem is to know how to simulate a problem. There are three typical ways to degrade CBC performance:

. When the number of latches is reduced, the concurrency of the remaining latches will increase.

. If you reduce the number of CBCs, the average length of each CBC will increase, and the concurrency of the remaining chains and the scanning time of CBC will also increase.

. If buffer clones become intense, frequently accessed chain will become very long, increasing the scanning time of concurrency with CBC

Reduce latches to limit concurrency

With a single latch, serialization is guaranteed, but concurrency is severely limited. When another process requests latch and it is held by another process, there is sometimes competition. In this example, simply adding a latch can solve this problem. If there are hundreds of thousands of processes that need to access CBCs, you can see that there are serious concurrency performance limitations. Fortunately, Oracle creates hundreds of CBC latches by default.

Oracle knows that its hash function is not perfect and will produce collisions. One way to reduce collisions is to have a lot of CBCs. But you will first think that more CBCs will consume more memory, but this is not the case. Each buffer header must be built into a CBC chain, regardless of the number and length of CBC chains. When more CBC chains are used, and the number of buffer headers remains the same, the average length of CBC chains decreases. Therefore, although there is some additional memory consumption for each CBC chain, the real memory consumer is the number of buffer headers, not just the number of CBC chains.

Many years ago, the rule defined that the number of latches should not exceed twice the number of CPU cores. Obviously Oracle has changed the rules, and CBC latches is just one of many latches in the Oracle database.

Oracle may handle multiple CBC latches, and some would think that there will be one latch for each CBC, but Oracle believes that this is unnecessary and that a single latch can manage hundreds of CBC chains.

If there are more CBC chains than buffers, this means that some CBC chains will not be associated with buffer header, which will effectively make the length of the CBC chain zero.

[oracle@jytest2] $sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 21 10:28:02 2019Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit ProductionSQL > col param format a50 heading "Instance Param and Value" word_wrappedSQL > col description format A20 heading "Description" word_wrappedSQL > col dflt format a5 heading "Dflt?" Word_wrappedSQL > select rpad (i.ksppinm, 35) | |'='| | v.ksppstvl param, 2 i.ksppdesc description, 3 v.ksppstdf dflt 4 from x$ksppi I, 5 x$ksppcv v 6 where v.indx = i.indx 7 and v.inst_id = i.inst_id 8 and i.ksppinm in 9 ('db_block_buffers','_db_block_buffers','db_block_size', 10' _ db_block_hash_buckets' '_ db_block_hash_latches' 11) 12 order by i.ksppinm 13 / Instance Param and Value Description Dflt? -_ db_block_buffers = 97136 Number of database TRUE blocks cached in memory: hidden parameter_db_block_hash_buckets = 262144 Number of database TRUE block hash buckets_db_block_hash_latches = 8192 Number of database TRUE block hash latchesdb_block_buffers = 0 Number of database TRUE Blocks cached in memorydb_block_size = 8192 Size of database FALSE block in bytes

One of the best and easiest ways to compete with CBC latch is to create a large buffer cache to cache more blocks, and then reduce the number of CBC latches to one. Oracle does not allow less than 1024 CBC latches from 10g, but even with 1024 CBC latches and enough logical IO capabilities, you can often see CBC latch competition.

Increase the scanning time of CBC by reducing the number of CBC

If the CBCs is long, the time it takes to scan it will cause significant competition. In addition, the time for other processes to obtain CBC latch will also be significantly increased. One obvious way is to increase the average length of each CBC to reduce the number of CBC, which can be done by reducing the number of hash buckets. Simply reduce the instance parameter _ db_block_hash_buckets to 50, make sure that the block you query is built into buffer cache, and you will quickly get CBC latch competition. Because Oracle has to ensure at least 64 hash buckets to ignore your settings, but there will still be a lot of competition.

In reality, one way to solve CBC latch competition is to increase the number of hash buckets, which will reduce the average length of each CBC. If a particular CBC is long and frequently written, then this solution will not improve performance. In addition, Oracle creates a large number of CBC, so increasing the number of hash buckets does not improve performance as significantly as increasing CBC, but it has an effective approach that should be considered.

Use clone Buffers to increase the scanning time of CBC

Although the problem of long CBC is rare, if it does, the situation is very serious. Understanding how this happens can not only help you solve the problem, but also give you a deeper understanding of CBCs,latch,undo and read consistency. It relates to the RAC system.

Long CBC represents a very challenging problem. First of all, the hash structure is fast because there are few scans, so long CBC quickly reduces the benefits of using the hash algorithm. Second, a scanning process must deal with a CBC latch, not any CBC latch, which protects a specific CBC. A long CBC means that the CBC latch will be held longer and more CPU will be used when scanning the list. In addition, because CBC latch is held for longer, this increases the likelihood that other processes will compete for latch. The process that competes for latch consumes CPU when both spinning and wait events are published at sleeping. But the problem is much more than that.

Normally, Oracle's hashing algorithm uses more than twice as many CBC as buffers, so the length of CBC is very short. The only way to appear a long CBC is if multiple buffers are hashed to the same CBC. Usually this is not a problem, but it can also arise. In order to understand this situation, first understand block cloning and hashing. When an Oracle block is cached, only a single current mode buffer can be modified. If a line in the buffer needs to be modified, a single current mode buffer must be available. The current mode buffers is sometimes called CU buffers. In RAC systems, if the current mode buffer you need is built into another instance, it must be sent to the instance you are using before you can modify the buffer.

Suppose a server process is running a query at time T100. The process accesses the data dictionary and knows that it will have to access a specific block, so it will be hashed to the appropriate CBC, get the appropriate CBC latch, scan the CBC, and find the buffer header of the current schema buffer. However, when checking the buffer header, it is found that the current mode buffer has been modified at time T200, after the server process starts to execute the query. This means that the row records required after the query is executed have been modified. The default read consistency mode of Oracle requires that the information returned is the same as when the query starts execution. Therefore, Oracle must take action to ensure that the information returned is correct for time T100.

Oracle now either finds a copy of buffer or builds a copy of the current schema buffer, so this buffer represents time T100 everywhere. A copy of buffer is usually called a buffer clone. Cloning a buffer is a relatively expensive process. First, a free buffer must be found, and then the buffer header must be properly connected to the CBC structure and the LRU chain structure.

The key to understanding the potentially significant performance impact is to understand where the cloned buffer's buffer header will be built into the CBC structure. Because the cloned buffer is a legitimate buffer, it takes up space in the buffer cache, can be shared and must be located. This means that it must be properly built into the CBC structure. The file number and block number of the cloned buffer are the same as its current mode buffer, which means that it must be hashed to the same CBC. Therefore, if a buffer has 50 clones, the CBC associated with it will be at least 50 buffer header long, and it may be longer if it collides with other buffer. There is nothing Oracle can do about this, because the ha algorithm is based on file numbers and block numbers.

Not only does the free buffer search algorithm facilitate replacing cloned buffer, but Oracle attempts to limit the number of clones per buffer. Oracle wants the number of clones per buffer not to exceed the implicit parameter _ db_block_max_cr_dba, which has a default value of 6. However, if cloning becomes intense, it is easy to have more than six copies of a buffer clone.

SQL > col name for a30SQL > col value for a20SQL > col describ for a50SQL > select x.ksppinm NAME,y.ksppstvl value,x.ksppdesc describ 2 from x$ksppi x, x$ksppcv y 3 where x.inst_id=USERENV ('Instance') 4 and y.inst_id=USERENV (' Instance') 5 and x.indx=y.indx 6 and x.ksppinm like'% & par%' Enter value for par: _ db_block_max_cr_dbaNAME VALUE DESCRIB -_ db_block_max_cr_dba 6 Maximum Allowed Number of CR buffers per dba1 row selected.

Buffer with many clones does not necessarily mean that there is a performance problem. If there is a performance problem, the CBC latch competition problem will be very obvious. If this occurs and a problem with cloning buffer is found, consider the following possible remedies:

. Repair the application

This is usually necessary. This is very painful, requires a meeting, will be very professional if the application developer is involved, and usually requires the application to be modified in some way to reduce the frequent access of a single clone buffer.

. Move line record

If you are lucky, there may be multiple lines of records that cause buffer to be accessed frequently. If possible, scatter these lines, so multiple buffer are no longer accessed frequently. When modifying traditional pct_free and pct_used storage parameters is an option, in order to increase control, consider setting the maximum number of records that a block can store. Surprisingly, this is not just a simple execution of a statement like alter table all_status minimizer records_per_block 5

. Balancing workload

If you can control the workload intensity, consider reducing the workload associated with buffer cloning activity during the peak period of cloning activity. Although this is not an exciting solution, workload balancing can also have a positive impact on performance.

CBC competitive Identification and solution

Some solutions can help you solve the problem of CBC competition. Make sure that CBC latch problems exist before you try to resolve them.

SQL > @ swpctxRemember: This report must be run twice so both the initial andfinal values are available. If no output, press ENTER twice.DB/Inst: RLZY/RLZY1 25-Mar 11:24amReport: swpctx.sql OSM by OraPub Inc. Page 1 System Event CHANGE (17 sec interval) Activity By PERCENT Time Waited% Time Avg Time WaitWait Event Display Name (sec) Waited Waited (ms) Count (k)- -- latch: cache buffers chains 10.610 96.28 15.7 1control file parallel write 0.160 1.45 7.6 0log file parallel write 0.030 0.27 15.0 0log file sync 0.000 0.00 0.00

If the database system is a version prior to Oracle 10g, then top wait event will be latch free, and you need to make sure that the latch problem is CBClatch. For Oracle 10g and later, wait event will be latch: cache buffers chains. In most cases, the CPU subsystem will be heavily utilized and overburdened. The following are possible CBC latch solutions:

. Optimize logical IO SQL statement

The CBC structure becomes tense when answering "whether buffer is in buffer cache". The expected answer is always "Yes". If the answer is "No", you will see sequential read or scattered read wait events. So from an application point of view, finding execution activities is mainly buffer gets, that is, the SQL of logical IO, to reduce logical IO consumption as much as you can. This is a typical SQL optimization, including indexing and reducing the execution rate when performance problems occur.

. Increase CPU processing capacity

In most cases, the CPU subsystem will be overutilized and may be an operating system bottleneck. The acquisition of latch and related memory management may consume too much CPU resources. Do whatever it takes to reduce CPU consumption and increase CPU capabilities. Find processes that are not or are being executed during peak hours. Consider adding or using faster CPU. If you are running in a virtual environment, consider ensuring that your Oracle system has added CPU resources. Note, however, that unless the application workload has increased significantly, the increased CPU processing power will usually be consumed quickly. The real solution may be something else. Increasing CPU capabilities may be a quick solution, but it may not really solve the problem.

. Check for buffer cloning issu

No matter what you encounter with CBC latch competition, you need to check if there is a problem with buffer cloning. This is a rare situation, but if encountered, the solution is very different from other solutions.

. Increase the number of CBC latch

This usually brings some comfort, but it's not really optimization logic IO SQL. The implicit parameter _ db_block_hash_latches controls the number of CBC latch

. Add CBC buckets

It is difficult to have an impact on performance because Oracle creates a large number of buckets by default. Unless you have previously reduced the number of CBC buckets, increasing the size of this parameter will significantly affect performance.

At this point, the study of "how to understand Oracle Buffer" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.