_oracle system _ with the church note 005-database buffer cache

This chapter mainly describes the database buffer cache in the SGA. Buffer Cache {1.pin (state of Read and write) 2.clean3.dirty4.unused}--database buffer cacheOverview:1. Concept of segment, area and block:CREATE TABLE t2 (id int, name VARCHAR2 (30)), create a table, create a segment at the same time, create a segment, and assign a region to the segment, which is a number of physically contiguous blocks. Area is the smallest unit that Oracle assigns to a segment. The block is the smallest unit of Oracle I/O. INSERT into T2 values (1, ' Xiaobei '); Access: accesses a block, in general, a block with multiple rows (except row links, row migrations).the meaning of the 2.buffer cache:① reduced I/o: physical I/O, logic I/o② construction CR block Introduction Undo:oracle writes the data to undo before modifying the data. Undo Action {Roll back uncommitted things; construct CR block. This leads to two more in-depth questions: How does the ① session 2 know that session 1 is not committed? How the ②CR block finds the deleted row from the undo space.3.buffer Cache memory Organizational StructureCBC, LRU, Lruw, CHECKPOINT QUEUECBC the buffer chain in accordance with the block address. LRU is the least recently used order chain. Available Blocks ~ Lru,mru (most recently used) Lruw dirty blocks, dbwn the least recently used dirty block as a standard. The CHECKPOINT QUEUE is also about dirty blocks, linked by the first dirty time of the block. Note: In Oracle, a two-way chain is generally used.4.db_writer_processes

Sql> Show parameter writer NAME                                 TYPE        VALUE------------------------------------------------------------- ----------------db_writer_processes                  integer     1sql> alter system set db_writer_processes = 2 scope=spfile;-- The number of DBWN is typically set to server cores/8.

important parameter configuration for 5.buffer cache

Alter system set db_cache_size=200m Scope=both; Set order: Sga_max_sizesga_targetdb_cache_size in an OLTP system, buffer The size of the CACHE is generally set to sga_max_size 2/3.db_cache_size = SGA_MAX_SIZE/2 ~ SGA_MAX_SIZE*2/3 Use advice to confirm buffer The size of the cache is based on the statement query buffer cache This setting is appropriate. Reduce I/O (physical reads) weekdays pay attention to collect and accumulate some commonly used statements.

6.Block Status

Buffer header:sql> desc X$BH     State:0~80,free1,xcur2,scur3,cr4,read The    process of writing buffer from Block 5,MREC6,IREC7, WRITE8,PI sql> SELECT distinct state from X$BH; in a non-RAC environment, current is always equal to xcur. Under RAC, there are scur

Q:current Block and CR block difference? A:

When the CR block is constructed and read out, it immediately becomes meaningless. It will be covered immediately. To modify a block, you can modify only the current block.

Q: See how many different states of buffer an object occupies?

A:

Selecto.object_name,decode (state,0, ' free ', 1, ' Xcur ', 2, ' Scur ', 3, ' Cr ', 4, ' read ', 5, ' Mrec ', 6, ' Irec ', 7, ' write ', 8, ' pi ' ) State,count (*) blocksfrom x$bh B, dba_objects owhere b.obj = O.data_object_idand o.object_name = ' T2 ' GROUP by O.object_n Ame, stateorder by blocks Desc

Note: The experiment can be performed first

alter system flush Buffer_cache;

7. Use various SQL to understand buffer cache usage

Select distinct object_name, Dbarfil, dbablk from X$bh A, dba_objects bwhere a.obj=b.object_id and object_name= ' T2 ';

object_name dbarfil dbablk----------------------------------------T2 1 8 6184t2 1 86185

Select Object_name, Dbarfil, dbablk from X$bh A, dba_objects bwhere a.obj=b.object_id and object_name= ' T2 ';

Q: Find the object that occupies the most buffer in buffer cache? A:

Selecto.object_name,decode (state,0, ' free ', 1, ' Xcur ', 2, ' Scur ', 3, ' Cr ', 4, ' read ', 5, ' Mrec ', 6, ' Irec ', 7, ' write ', 8, ' pi ' ) State,count (*) blocksfrom x$bh B, dba_objects owhere b.obj = o.data_object_id and State<>0group by O.object_name, Stateorder by blocks ASC;

Q: Looking for hot blocksA:

Select obj object,dbarfil file#,dbablk block#,tch touchesfrom x$bhwhere tch>10 order bytch ASC;

    OBJECT      file#     block#    touches------------------------------- ---------      5847          1      11752       &NBS P 29      5834          1      11664         29&N Bsp     5834          1      11665         29  &nbs P   5832          1      11649         30    &NBS P 5833          1      11657         30      5834          1      11666         31       28 7          1               45       2         1       $         45 96 rows selected. Note: Blocks are logically read many times, which is The hot end is judged according to the Tch property of the block.

Select Object_name, Dbarfil, dbablk from X$bh A, dba_objects bwhere a.obj=b.object_id and Dbarfil=1 and dbablk=287

Q: Total number of blocksA:

Select SUM (blocks) from Dba_data_files;

SUM (BLOCKS)-----------176640Q: Check the ratio of the current free space, preferably within 10% controlA:

Select Decode (state,0, ' Free ', 1,decode (lrba_seq,0, ' AVAILABLE ', ' BEING used '), 3, ' BEING used ', state) "BLOCK STATUS", Count (*) from X$bhgroup by Decode (state,0, "Free", 1,decode (lrba_seq,0, ' AVAILABLE ', ' BEING used '), 3, ' BEING used ', state) ;

BLOCK STATUS COUNT (*)

--------------------------------------------------BEING used 74AVAILABLE 5385FREE 10696Q: The most wasted memory of the first 10 statements accounted for all the proportion, the proposed control within 5%A:

Select SUM (pct_bufgets) "Percent" from (select Rank () up (order by buffer_gets Desc) as Rank_bufgets, To_char (Rati O_to_report (Buffer_gets) over (), ' 999.99 ') pct_bufgets from V$sqlarea) where Rank_bufgets < 11;

Q: Find the SQL statement that consumes the most physical I/O resourcesA:

Select Disk_reads, substr (sql_text,1,4000) from V$sqlarea ORDER by disk_reads ASC;

Q: Common Operating system commands, viewingA:

Iostat 1 10vmstat 1 10mpstat 1 10mpstat-p 0 1mpstat-p 1 1topfree

Memory read: Consumes CPU resources physical reads: Consumes I/O resources