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

How to understand and adjust BUFFER CACHE and DBWR, in view of this problem, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Understanding and Tuning Buffer Cache and DBWR (Doc ID 62172.1)

Understand and adjust BUFFER CACHE and DBWR (database writer)

When there is a wait event in our database, it means that database performance has been affected:

1) Latch contention for the 'cache buffers lru chain' or the "cache buffer chain" latch

-2) Large "Average Write Queue" length

-3) Lots of time spent waiting for "write complete waits"

4) Lots of time spent waiting for "free buffer waits"? Or "Buffer busy waits"

BUFFER CACHE buffer cache

Oracle keeps a copy of the database block in an area of SGA, called the buffer cache.

The cache can save multiple copies of a block from different points in time and may contain "dirty" blocks, that is, blocks that have been updated but have not yet been flushed to disk. The database write/ S (DBWR or DBWn process) is responsible for writing dirty blocks to disk, while any user session can read blocks to write cache.

All blocks in the buffer cache are in a LRU (least recently used) list-when a process needs an idle buffer. It will scan the non-dirty buffer from the LRU side of this list, and it can be used. The 'cache buffers lru chain' latch/es serialize operations on the LRU list/s. (the algorithm used for LRU lists in Oracle8i is different from earlier versions, but the influencing factors remain the same.

Evaluating Buffer? Cache Activity

Activity evaluation of buffer cache

The buffer cache hit ratio measures how many times the block is needed in memory, rather than performing expensive reads on disk to get the block.

Querying the V$SYSSTAT view is possible to get statistics used to adjust the buffer cache. To calculate this ratio, you must consider the version of Oracle you are running. It is recommended that the hit ratio be higher than 80% before increasing the buffer cache size.

How to calculate this ratio on each Oracle version.

How to calculate the hit rate of each Oracle version.

"Cache hit ratio" is a derivative of the most statistical manuals and articles.

There is more than one definition of a cache hit.

The hit rate is used to indicate the frequency of various situations.

The process that accesses the data buffer looks for blocks in the Oracle buffer.

Cache. Accurate value ratio of hit rate

The ability to monitor it over time to pay attention to any major changes

A summary of the activities on the database.

Important:

A high hit rate (close to 100%) is not necessarily good.

The reason was explained later.

Calculation:

~

The most common formula in circulation for the hit ratio for the buffer cache

For Oracle7/8 is:

Hit ratio = 1-(physical reads)

-

(consistent gets + db block gets)

A better formula in Oracle8i/9i is:

Hit ratio =

1-(physical reads-(physical reads direct + physical reads direct (lob)

(db block gets + consistent gets-(physical reads direct + physical reads direct (lob)

The hit ratio for each pool can be seen using V buffer_pool_statistics:

SELECT name, 1-(physical_reads / (consistent_gets + db_block_gets)) "HIT_RATIO"

FROM V$BUFFER_POOL_STATISTICS

WHERE (consistent_gets + db_block_gets)! = 0

The "Miss Ratio"

~

Occasionally you may see reference to the "miss ratio". This is just

Miss ratio = 100%-Hit Ratio (expressed as a percentage)

Notes about the Hit Ratio

~

A good hit ratio is expected for OLTP type systems but decision support type

Systems may have much lower hit ratios. Use of parallel query will make the

Hit ratio less meaningful if using the first form of calculation based on

"physical reads" only.

A hit ratio close to 100% does not mean the application is good. It is quite

Possible to get an excellent hit ratio by using a very unselective index in a

Heavily used SQL statement.

Eg: Consider a statement like:

SELECT * FROM employee WHERE empid=1023 AND gender='MALE'

If EMPLOYEE is a large table and this statement always uses the GENDER index

Rather than the EMPID index then you scan LOTS of blocks (from the GENDER

Index) and find nearly all of them in the cache as everyone is scanning this

Same index over and over again. The hit ratio is very HIGH but performance

Is very BAD. A common 'variation' on an "unselective" index is a heavily

Skewed index where there are a large number of entries with one particular

Value (eg: a workflow status code of CLOSED)-the index may perform well for

Some queries and very poorly for the most common value.

If the employee is a large table, this statement always uses a gender index.

Instead of the work number index, then you scan a lot of blocks (from the gender index) and find almost all the cache in the cache, because everyone is scanning this.

Same index over and over again. The hit rate is high, but the performance is poor. A common "change"selectivity" index is a large number of indexes with a particular entry.

Value (for example: closed workflow status code)-the index may perform well.

Some queries and the most common values are poor.

The following hit ratio formula applies to all versions of Oracle:

A few comments:

~

-The "good" hit ratio is generally considered to be one > 80%

There is probably still scope for tuning if it is

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.