I. we can improve the performance by configuring the Shared Pool to ensure that users find cached statements in the memory. Another important method is: this allows users to find the data they requested in the memory! This requires the database buffer cache (the High-speed 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 or 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 writing is completed by the Oracle background process database writer (dbw0.
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. measure the performance of database buffer cache
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. Improve database 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 buffer cache buffers are being used.
(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.
