Shulou(Shulou.com)06/01 Report--

A set of LRU linked lists includes LRU main chain, LRU auxiliary chain, LRUW main chain, LRUW auxiliary chain, called a WorkSet (working group), as shown in the following figure:

Sys@ZMDB > selectCNUM_SET,CNUM_REPL,ANUM_REPL,CNUM_WRITE,ANUM_WRITE from x$kcbwds whereCNUM_SET > 0

CNUM_SET CNUM_REPL ANUM_REPL CNUM_WRITE ANUM_WRITE

--

15221 15221 3796 0 0

15221 15221 3783 0 0

CNUM_SET: total number of buffer for the workgroup

CNUM_REPL: total number of buffer of LRU in the workgroup (primary LRU+ and secondary LRU)

ANUM_REPL: the total number of secondary LRU BUFFER in the workgroup

Through the implicit parameters, it is found that the total number of BUFFER is 30442, which is exactly the same as the CNUM_SET=15221+15221 above.

Sys@ZMDB > @? / rdbms/admin/show_para

Enter value for p: _ db_block_buffers

Old 12: AND upper (i.ksppinm) LIKEupper ('% & p%')

New 12: AND upper (i.ksppinm) LIKEupper ('% _ db_block_buffers%')

P_NAME P_DESCRIPTION P_VALUE ISDEFAULT ISMODIFIEDISADJ

- -

_ db_block_buffers Number of database blocks cached inmemory: hidden 30442 TRUE FALSE FALSE

Parameter

Let's check the status of the LRU where buffer resides in the database with the following statement

Sys@ZMDB > select lru_flag,count (*) from x$bh group by lru_flag

LRU_FLAG COUNT (*)

--

6 208

2 10

4 7122

8 15199

0 7646

Let's explain to LRU_FLAG=6,2,4,8,0 et al., for example, what does it mean for 6?

The first step is to find any BUFFER of lru_flag=6 in x$bh

Sys@ZMDB > select LRU_FLAG,LOWER (BA) from x$bh where lru_flag=6 andrownum=1

LRU_FLAG LOWER (BA)

--

6 0000000081dae000

BH information in DUMP buffer_cache, the following command:

Sys@ZMDB > alter session set events'immediate trace name buffers level 1'

Session altered.

Ys@ZMDB > col value for A85

Sys@ZMDB > select * from v$diag_info where name='Default TraceFile'

INST_ID NAME VALUE

-- -

1 Default Trace File / u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13235.trc

Search for trace files through BA=81dae000

/ u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13235.trc

Get the following content:

BH (0x81fe7e38) file#: 1 rdba: 0x0040ace1 (1gamma 44257) class: 1 ba:0x81dae000

Set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc:0,25

Dbwrid: 0 obj: 421 objn: 423 tsn: 0 afn: 1hint: f

Hash: [0x9ef9d710,0x853f8da8] lru: [0x81fe7df0,0x81fe8050]

Lru-flags: moved_to_tail on_auxiliary_list

Ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]

St: CR md: NULL fpin: 'kdswh06: kdscgr' tch:1

Cr: [scn: 0x0.80350f4d], [xid: 0x0.0.0], [uba:0x0.0.0], [cls: 0x0.80350f4d], [sfl: 0x0], [lc: 0x0.8034c532]

Flags: block_written_once redo_since_read

LRU_FLAG=6 means lru-flags: moved_to_tail on_auxiliary_list, which means to move to the tail of the auxiliary linked list of LRU. This may be the non-dirty block on the main linked list of LRU and the head of the cold end of the main chain of TCHLRU-. This is quite special. It does not show LRU_FLAG in DUMP.

Tail of cold end of LRU- main chain, lru-flags:moved_to_tail

4 assistant = > LRU- auxiliary chain, lru-flags:on_auxiliary_list

6legs = > tail of LRU- auxiliary chain, lru-flags:moved_to_tail on_auxiliary_list

8 steps = > LUR- main chain hot end, lru-flags:hot_buffer

When a physical read occurs, Oracle will find the free BUFFER from the LRU auxiliary list, and then connect the BUFFER on the LRU auxiliary chain to the cold end of the LRU main chain. The experiment is as follows:

First of all, make sure that there is a BUFFER on the LRU auxiliary chain, that is, there is LRU_FLAG=6 or LRU_FLAG=4. If the database has just started, there may be no LRU_FLAG=6 or LRU_FLAG=4, then you need to do a lot of physical read operations before there will be LRU_FLAG=6 or LRU_FLAG=4.

Sys@ZMDB > alter system flush buffer_cache

System altered.

Sys@ZMDB > selectlru_flag,count (*) from x$bh group by lru_flag

LRU_FLAG COUNT (*)

--

6 208

4 30009

0 2

DUMP the entire BUFFER CACHE for the first time:

Sys@ZMDB > alter session set events'immediate trace name bufferslevel 1'

/ u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13480.trc

Physical reading occurs

Gyj@ZMDB > conn gyj/gyj

Connected.

Gyj@ZMDB > set autot on

Gyj@ZMDB > select id,name,dbms_rowid.rowid_relative_fno (rowid) file#,dbms_rowid.rowid_block_number (rowid) block# from gyj_t1 where id=1

ID NAME FILE# BLOCK#

1 gyj1 7 139

Execution Plan

Plan hash value: 59758809

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 14 | 68 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | GYJ_T1 | 1 | 14 | 68 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("ID" = 1)

Statistics

1 recursive calls

1 db block gets

254 consistent gets

248 physical reads

0 redo size

733 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Sys@ZMDB > selectLRU_FLAG,lower (BA), TCH from x$bh where file#=7 and dbablk=139

LRU_FLAG LOWER (BA) TCH

0 000000007d1b2000 1

4 0000000078558000 0

4 0000000085f68000 0

When the physical read is complete, dump the entire buffer cache again

Sys@ZMDB > alter session set events'immediate trace name buffers level 1'

/ u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13511.trc

Take BA=7d1b2000 and search the trace file of the first DUMP.

/ u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13480.trc

BH (0x7d3e8098) file#: 3 rdba:0x00c0586b (3max 22635) class: 34 ba: 0x7d1b2000

Set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc:0,25

Dbwrid:0 obj:-1 objn: 0 tsn: 2 afn: 3 hint: F

Hash: [0x9efa7570,0x9efa7570] lru: [0x7f7f5d30,0x7d3e8050]

Lru-flags: on_auxiliary_list

Ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]

St: FREE md: NULL fpin: 'ktuwh03: ktugnb'tch: 0 lfb: 33

Flags:

Take BA=7d1b2000 and search the trace file of the second DUMP.

/ u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13511.trc

BH (0x7d3e8098) file#: 7 rdba:0x01c0008b (7max 139) class: 1 ba: 0x7d1b2000

Set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc:0,25

Dbwrid: 0 obj: 22919 objn: 19567 tsn: 7 afn:7 hint: f

Hash: [0x787e4bd8,0x9e4cda50] lru: [0x7f7f5d30,0x7d3e8050]

Ckptq: [NULL] fileq: [NULL] objq: [0x9a88e518,0x7d3e8078] objaq: [0x9a88e508,0x7d3e8088]

St: XCURRENT md: NULL fpin: 'kdswh21:kdst_fetch' tch: 1

Flags: only_sequential_access

LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]

From the above two trace, we can draw a conclusion: ba: 0x7d1b2000

From lru-flags:on_auxiliary_list (LRU_FLAG=4) to the head of the cold end of the LRU- main chain, this is special in that DUMP does not show LRU_FLAG (LRU_FLAG=0)

Observe that the cold end moves to the hot end when LRUTCH > = 2

1. Set BUFFER to 100m manually

ALTER SYSTEM SETmemory_max_target=0 scope=spfile

ALTER SYSTEM SET memory_target=0

Alter system set sga_target=0

Create table gyj1_t80 (idint,name char (2000))

Create table gyj2_t80 (idint,name char (2000))

Begin

For i in 1.. 30000

Loop

Insert into gyj1_t80 values (iJournal gyj' | | I)

Commit

End loop

End

/

SQL > SQL > selectbytes/1024/1024 | |'M' from dba_segments where segment_name='GYJ1_T80' andowner='GYJ'

BYTES/1024/1024 | |'M'

-

80M

Begin

For i in 1.. 30000

Loop

Insert into gyj2_t80 values (iJournal gyj' | | I)

Commit

End loop

End

/

Create index idx_gyj1_t80m ongyj1_t80 (id)

Create index idx_gyj2_t80m ongyj2_t80 (id)

SQL > show user

USER is "GYJ"

SQL > conn / as sysdba

Connected.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup

ORACLE instance started.

The first dump

SQL > alter session set events'immediate trace name buffers level1'

Session altered.

SQL > select * fromv$diag_info where name='Default Trace File'

INST_ID NAME

--

VALUE

1 Default Trace File

/ u01/app/oracle/diag/rdbms/jfdb/jfdb/trace/jfdb_ora_7210.trc

A physical read-out index occurred

Set autot on

Selectid,name,dbms_rowid.rowid_relative_fno (rowid) file#,dbms_rowid.rowid_block_number (rowid) block# from gyj1_t80 where id=1

SQL > selectid,name,dbms_rowid.rowid_relative_fno (rowid) file#,dbms_rowid.rowid_block_number (rowid) block# from gyj1_t80 where id=1

ID NAME FILE# BLOCK#

1 gyj1 5 581

Select LRU_FLAG,lower (BA), TCHfrom x$bh where file#=5 and dbablk=581

SQL > select LRU_FLAG,lower (BA), TCH,decode (state,0,'free',1,'xcur',2,'scur')

2, 3, 3, 4, 4, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 4, 4, 4, 4, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,

3 'donated', 12, 13, 13, 14, 15, 15, 16,' flashf

4 ree', 17, 'flashcur', 18 retro Flashna') from x$bh where file#=5 anddbablk=581

LRU_FLAG LOWER (BA) TCH DECODE (STA

0 000000009fca8000 1 xcur

SQL > selectLRU_FLAG,lower (BA), TCH from x$bh where file#=5 and dbablk=581

LRU_FLAG LOWER (BA) TCH

0 000000009fca8000 5

SQL > set autot traceonly

SQL > select / * + index (G) * / count (name) fromgyj1_t80 G where id selectLRU_FLAG,lower (BA), TCH from x$bh where file#=5 and dbablk=581

LRU_FLAG LOWER (BA) TCH

0 000000009fca8000 6

The physical read occurs again, with LRU_FLAG=0 changing to 8 and TCH=8 reset to 0

SQL > select LRU_FLAG,lower (BA), TCH from x$bh where file#=5 and dbablk=581

LRU_FLAG LOWER (BA) TCH

0000000009fca8000 8

SQL > select LRU_FLAG,lower (BA), TCH from x$bh where file#=5 anddbablk=581

LRU_FLAG LOWER (BA) TCH

8000000009fca8000 0

BH (0x9ffe02a8) file#: 5 rdba: 0x01400245 (5Compact 581) class: 1 ba:0x9fca8000

Set: 5 pool: 3 bsz: 8192bsi: 0 sflg: 2 pwc: 15,19

Dbwrid: 0 obj: 13537 objn:13537 tsn: 5 afn: 5 hint: f

Hash: [0xb6a86de0,0xb6a86de0] lru: [0x9ffe0260,0x9ffe9a60]

Lru-flags: hot_buffer

Ckptq: [NULL] fileq: [NULL] objq: [0x9ffe0618,0x9ffe0028] objaq: [0x9ffe0628,0x9ffe0038]

St: XCURRENT md: NULL fpin:'kdswh05: kdsgrp' tch: 0

Flags:

LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]

When TCH=0 occurs, a large number of physical reads occur, and the BUFFER with the address 9fca8000 is reused and completely disappeared from the BUFFER.

SQL > selectLRU_FLAG,lower (BA), TCH from x$bh where file#=5 and dbablk=581

LRU_FLAG LOWER (BA) TCH

8 000000009fca8000 0

SQL > select LRU_FLAG,lower (BA), TCH from x$bh wherefile#=5 and dbablk=581

No rows selected

Through experiments, we have a clearer understanding of the basic flow of physical reading LRU, and we can further understand the internal LRU algorithm of physical reading.

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.