Oracle adjusts the buffer cache speed

For more information about what the Oracle high-speed cache is and how it works, see:

Http://www.php100.com/html/webkaifa/database/oracle/2008/0709/1348.html

1. We can configure shared
Pool (to ensure that users find cached statements in the memory) to improve performance. Another important method is to enable users to find the data they requested in the memory! This requires the database
Buffer cache (the cache area of the database buffer.
Buffer cache is a component of SGA used to cache copies of data blocks recently accessed by users in the database. These copies are synchronized with the corresponding blocks on the disk! If it is not synchronized, It is the so-called dirty buffer! Writing from a cache to a disk is called a writing disk. The mechanism for managing dirty buffers is dirty.
List (Dirty List) or write
List (write list ). This list traces the statements that have been inserted, updated, and deleted but have not been written to the disk. The final disk write work is performed by the Oracle background process database
Writer (dbw0) is complete.
Buffer cache is also managed by a LRU algorithm. * During a full table scan, the table buffer is directly placed at least the last point of LRU.

Ii. measurement database
Buffer cache Performance
Select 1-(physical. Value-direct. Value-lobs. Value)/logical. value)
"Buffer cache hit ratio"
From v $ sysstat physical, V $ sysstat direct, V $ sysstat lobs, V $ sysstat logical
Where physical. Name = 'physical reads'
And direct. Name = 'physical reads direct'
And lobs. Name = 'physical reads direct (LOB )'
And logical. Name = 'session logical reads ';
When the preceding statement is greater than 90%, the statement is fully adjusted. The hit rate is high!

Iii. database improvement
Buffer cache Performance
1. The simplest method is to increase its size. 45% of SGA is suitable! Parameters
(1). db_block_size the size of the primary database block is 8 KB by default, which cannot be changed after the database is created.
(2). db_cache_size (default buffer pool) The default value is 48 m. We mainly modify this parameter to improve performance.
Alter system set db_cache_advice = on; Use the Statistical Advisor recommended by Oracle, after half an hour of normal database operation
Select name, size_for_estimate, V $ db_cache_advice.estd_physical_reads from V $ db_cache_advice where block_size = '2016' and advice_status = 'on ';
Recommended size
Select obj. Owner, obj. object_name, obj. object_type, count (distinct BH. Block #) "num. buffers"
From dba_objects OBJ, V $ BH
Where obj. object_id = BH. objd
And owner! = 'Sys'
Group by obj. Owner, obj. object_name, obj. object_type;
The preceding statement can be used to check which objects are being cached and then buffer cache, and how many buffers are being used.
Cache buffer.
(3). Use multiple buffer pools
Alter system set db_cache_size = 300 m;
Alter system set db_keep_cache_size = 150 m; persistence Zone
Alter system set db_recycle_cache_size = 50 m; recycle Zone
Allocate a table to a partition
Alter table col_cust storage (buffer_pool keep );
Statement
Select owner, segment_type, segment_name, buffer_pool
From dba_segments
Where buffer_pool! = 'Default ';
You can view the allocation information.
Select name, block_size, current_size
From v $ buffer_pool;
You can view the size of each buffer pool.
Select name "buffer pool", 1-(physical_reads/(db_block_gets + consistent_gets) "buffer pool hit ratio"
From v $ buffer_pool_statistics order by name;
You can check that the higher the hit rate of each buffer pool, the better the Keep. The smaller the recycle, the better.
(4) cache tables in memory
Although tables can be placed in the persistence area, the keep pool is also controlled by LRU. Therefore, when the FTS (full table scan) is performed, the table is least recently used by LRU. In this way, it is possible to be removed. So we can use
Alter table col_cust cache;
Select owner, table_name from dba_tables where ltrim (cache) = 'y'
You can view the table information in the cache area.

Remarks: This article Reprinted from: http://blog.csdn.net/huanghui22/article/details/1256358