OracleBufferCache optimization ideas

I recently read several pages of books, take notes, and copy a paragraph from the official oracle documents. Of course, BufferCache is far more than that, but reading English is really slow. Now I want

I have read several pages of books recently, take notes, and copy a paragraph from the official oracle documents. Of course, Buffer Cache is far more than that, but reading English is really slow. Now I want

I recently read several pages of books, made notes, and copied a paragraph from the official Oracle documents. Of course, the Buffer Cache is far more than that, but it is really slow to read English, what I want to say most now is: I need more time. There have been too many chores recently, and there is not much time at your disposal. When can I be fluent in English? Step by step, too many details can only be learned from the official documentation. It is indeed a vault, but you cannot get any gold. step by step!
Database Buffer Cache
The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. all user processes concurrently connected to the instance share access to the database buffer cache. database buffer cache is a part of sga and is used to keep copies of data blocks read from data files. All user processes access the database buffer cache.
Database read/write operations should be performed in the memory as much as possible. Reducing the number of I/O operations is the basic policy for database performance optimization.

How to optimize buffer cache:

1) set the db_cache_size parameter based on experience, for example, db_cache_size = SGA_TARGET * 80%.
2) analyze the Buffer Hit value (Instance Efficiency Percentages (Target 100%) in the AWR report )).

Wait events to be concerned (if these wait events appear in Top-5, the Buffer Cache is inefficient ):
Latch: cache buffer chains
Latch: cache buffer LRU chains
Buffer busy waits
Read waits
Free buffer waits

Latch: cache buffer chains and Latch: cache buffer LRU chains
Indicates that some data blocks in the database are frequently read, that is, hot block data. For example, frequently read code tables (?), UNDO header data (?) And monotonically increasing indexes.
You can query the hot block data as follows:
Select * from (select owner, object_name, object_type, statistic_name, sum (value)
From v $ segment_statistics
Group by owner, object_name, object_type, statistic_name
Order by sum (value) desc)
Where rownum <10;
Optimization Methods: for example, reduce the number of code data reads, use the reverse key index, and 10 Gb Global Hash-patition partition index.

Buffer busy waits
It indicates that a waiting event occurs when multiple applications concurrently access a Buffer Cache data block. This data block may be an application table or index, or UNDO (?) , Segment Header (?) And other system data.
You can query the objects involved in the wait event as follows:
Select object_name, statistic_name, value
From v $ segment_statistics
Where statistic_name = 'buffer busy waits 'and value> 2000;
Optimization Methods: for example, whether full table scan is performed, whether there are too many indexes, or the index increases monotonically. In the final analysis, it is still an analysis application.

Read waits
Including db file sequential read, db file parallel read, and db file scattered read. Generally, as long as db file scattered read is not the most important wait event, or the proportion is not high, the above events are normal even if they occur in Top-5 wait events.
If these wait events are very high, or the proportion is very high. Optimization Method: 1. Similarly, first optimize the application, such as whether there are too many full table scans and whether the index fragmentation is serious. 2. Expand db_cache_size appropriately. 3. Analyze disk I/O efficiency.

Free buffer waits
The speed at which the Buffer Cache content is written to the disk, which cannot meet the requirements of other applications for idle Buffer Cache. Optimization Method: Similarly, first optimize the application, such as whether there are too many full table scans and whether the index efficiency is high. 2. Expand db_cache_size appropriately. 3. Expand the db_writer_processes parameter, increase the number of DBWn processes, and speed up writing Buffer Cache content to the disk.
Db_writer_processes is 1 by default or cpu_count/8. The default value is usually enough. If you need to set db_writer_processes, do not exceed cpu_count to avoid unnecessary cpu resource consumption.

Other optimization methods are used to design a table as a cache table.
In general, the first task is to optimize the application!

Segment-Level Statistics in oracle10gR2)
Retrieving segment-level statistics can help us identify performance problems related to individual segments and collect and view them to quickly identify hot tables and indexes. After viewing wait events and system statistics to identify performance problems, you can use segment-level statistics to find the tables or indexes that cause performance problems. For example, V $ SYSTEM_EVENT implies that buffer busy waits causes a certain amount of wait time. At this time, you can query V $ SEGMENT_STATISTICS to obtain the top segments resulting in buffer busy waits. Therefore, you can move the focus to these segments to eliminate performance problems.
Dynamic Performance view related to segment-level statistics:
1. V $ SEGSTAT_NAME This view lists the segment statistics being collected, as well as the properties of each statistic (for instance, if it is a sampled statistic ). (the collected segment statistical information contains an attribute that identifies whether it is a sampling statistical information)
2. V $ SEGSTAT This is a highly efficient, real-time monitoring view that shows the statistic value, statistic name, and other basic information. (High-Efficiency Real-time Monitoring view, displaying statistical information values, statistical names, and other basic information)
3. V $ SEGMENT_STATISTICS This is a user-friendly view of statistic values. in addition to all the columns of V $ SEGSTAT, it has information about such things as the segment owner and table space name. it makes the statistics easy to understand, but it is more costly. (display a more friendly view of statistical information values, including not only all fields of V $ SEGSTAT, but also segment owner and tablespace name. this makes it easier to query statistics at the same cost .)