The Oracle Learning buffer cache

1, block, the concept of buffer
The concept of section block and the concept of the block are introduced.
The concept of buffer
2. The meaning of buffer cache
Reduce IO
Physical IO: Disk read
Logic IO: Memory read
Construction of CR blocks: later speaking
Undo cited: Rollback uncommitted data; construct CR block
As long as it is not committed, it can be rolled back
No other sessions can see changes as long as they are not committed
3. Buffer Cache Memory Organizational structure
Cbc:cache buffer Chain
You need to use the CBC chain when you are looking for a block from the block address

LRU: Least Recently used
LRU, MRU
Lruw

4, Db_writer_processes
5. Important parameter configuration of Buffer cache
SELECT component,current_size,min_size from V$sga_dynamic_components;
Size configuration of the Buffer cache
Alter system set db_cache_size=200m Scope=both;
Sga_max_size

Sga_target
Db_cache_size
In an OLTP system, the recommended configuration for db_cache_size settings is:
Db_cache_size = sga_max_size/2~ SGA_MAX_SIZE*2/3
Use advice to confirm the size of the buffer cache

Select Size_for_estimate "Cache size (MB)", size_factor,buffers_for_estimate "buffers",
Estd_physical_read_factor Est_read_factor,
Estd_physical_reads Estd_phy_red,estd_physical_read_time est_phy_red_t
From V$db_cache_advice
where Name= ' DEFAULT ' and block_size= (SELECT VALUE from V$parameter WHERE name= ' db_block_size ');

6. Block status
X$bh
State

0, free, no valid block image
1, xcur, a current mode block, exclusive to this instance
2, Scur, a current mode block, GKFX with other instances
3, CR, a consistent read (stale) block image
4, READ, buffer is reserved-a block being READ from disk

5, MREC, a block in media recovery mode
6, Irec, a block in instance (crash) Recovery mode

The specifics of an object occupying buffer
Sql>select
O.object_name,
Decode (state,0, ' free ', 1, ' Xcur ', 2, ' Scur ', 3, ' Cr ', 4, ' read ', 5, ' Mrec ',
6, ' Irec ', 7, ' write ', 8, ' pi ') state,
COUNT (*) blocks
From X$bh B, dba_objects o
where b.obj = o.data_object_id
--and o.object_name = ' T2 '
GROUP BY O.object_name, state
ORDER BY blocks Desc;

Select object_name,dbarfil,dbablk from X$BH a,dba_objects b
where a.obj=b.object_id and Object_name= ' T2 '

Select class, Flag, state, Lru_flag from X$BH
where Dbarfil = 1 and dbablk = 61433;

The specifics of the use of the pool by the object (considering the situation of various pools)

Select
O.object_name,
Decode (state,0, ' free ', 1, ' Xcur ', 2, ' Scur ', 3, ' Cr ', 4, ' read ', 5, ' Mrec ',
6, ' Irec ', 7, ' write ', 8, ' pi ') state,
COUNT (*) blocks
From X$bh B, dba_objects o
where b.obj = o.data_object_id and state <> 0
GROUP BY O.object_name, state
Order by blocks ASC;

Select Decode (wbpd.bp_id,
1, ' Keep ',
2, ' recycle ',
3, ' Default ',
4, ' 2k pool ',
5, ' 4k pool ',
6, ' 8k pool ',
7, ' 16k pool ',
8, ' 32k pool ',
' Unknown ') pool,
Bh.owner,
Bh.object_name object_name,
Count (1) numofbuffers
From X$kcbwds WDS,
X$KCBWBPD WBPD,
(select Set_ds, x.addr, O.name object_name, u.name owner
From sys.obj$ O, sys.user$ u, x$bh x
where o.owner# = u.user#
and o.dataobj# = X.obj
and x.state! = 0
and o.owner#! = 0
) BH
where wds.set_id >= Wbpd.bp_lo_sid
and wds.set_id <= Wbpd.bp_hi_sid
and wbpd.bp_size! = 0
and wds.addr = Bh.set_ds
--and object_name= ' T2 '
Group by Decode (WBPD.BP_ID,
1, ' Keep ',
2, ' recycle ',
3, ' Default ',
4, ' 2k pool ',
5, ' 4k pool ',
6, ' 8k pool ',
7, ' 16k pool ',
8, ' 32k pool ',
' Unknown '),
Bh.owner,
Bh.object_name
Order by 1, 4, 3, 2;

Looking for hot blocks

SELECT
Obj object,
Dbarfil file#,
Dbablk block#,
TCH touches
From
X$bh
WHERE
tch > 10
ORDER by
TCH ASC;

Select object_name,dbarfil,dbablk from X$BH a,dba_objects b
where a.obj=b.object_id and Dbarfil=1 and dbablk=338

The sum of blocks in all files in the entire database
Select
SUM (blocks)
From
Dba_data_files;

The ratio of free space is best controlled within 10%
Select Decode (state,0, ' Free ', 1,decode (lrba_seq,0, ' AVAILABLE ', ' BEING used '), 3, ' BEING used ', state) "BLOCK STATUS", COUNT (*)
From X$BH
Group by Decode (state,0, "Free", 1,decode (lrba_seq,0, ' AVAILABLE ', ' BEING used '), 3, ' BEING used ', state);

The most wasted memory of the first 10 statements accounted for the proportion of all statements, it is recommended to control within 5%

Select SUM (pct_bufgets) "Percent"
From (select Rank () over (order by buffer_gets Desc) as
Rank_bufgets,to_char (Ratio_to_report (buffer_gets) over (), ' 999.99 ') pct_bufgets from V$sqlarea)
where Rank_bufgets < 11;

Find the SQL statement that consumes the most physical IO resources
Select Disk_reads, substr (sql_text,1,4000) from V$sqlarea ORDER by disk_reads ASC;

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

Specific usage of buffer cache

SET linesize PAGESIZE 1400

SELECT/*+ ORDERED Use_hash (o u) MERGE */
DECODE (obj#,
NULL,
To_char (bh.obj),
U.name | | '. ' | | O.name) name,
COUNT (*) Total,
SUM (DECODE ((DECODE (Lru_flag, 8, 1, 0) + DECODE (sign (tch-2), 1, 1, 0)),
2,
1,
1,
1,
0)) Hot,
SUM (DECODE (DECODE (lru_flag-8), 1, 0, 0, 0, 1) +
DECODE (tch, 2, 1, 1, 1, 0, 1, 0),
2,
1,
1,
0,
0) cold,
sum (DECODE (Bitand (Flag, POWER (2,)), 0, 0, 1) fts,
sum (tch) Tota L_tch,
ROUND (AVG (TCH), 2) Avg_tch,
MAX (tch) Max_tch,
MIN (tch) min_tch
from X$bh BH, sys.obj$ o, sys.user$ U
WHERE
bh.obj <> 4294967295
and Bh.state in (1, 2, 3)
and bh.obj = o.dataobj# (+)
and bh.inst_id = USERENV (' INSTANCE ')
and o.owner# = u.user# (+)
--and o.owner# > 5
and U.name not like ' aurora$% '
Grou P by DECODE (obj#,
NULL,
To_char (bh.obj),
U.name | | '. ' | | O.name)
ORDER by total desc
/

Use of each object in buffer cache

SELECT T.name as Tablespace_name,
O.object_name,
SUM (DECODE (bh.status, ' free ', 1, 0)) as free,
SUM (DECODE (bh.status, ' xcur ', 1, 0)) as Xcur,
SUM (DECODE (bh.status, ' scur ', 1, 0)) as Scur,
SUM (DECODE (bh.status, ' Cr ', 1, 0)) as CR,
SUM (DECODE (bh.status, ' read ', 1, 0)) as read,
SUM (DECODE (bh.status, ' Mrec ', 1, 0)) as Mrec,
SUM (DECODE (bh.status, ' Irec ', 1, 0)) as Irec
From V$bh BH
JOIN dba_objects o on o.data_object_id = BH.OBJD
JOIN v$tablespace t on t.ts# = bh.ts#
GROUP by T.name, O.object_name
ORDER BY xcur Desc
/
Column C0 heading "Owner" format A12
Column C1 heading "object| Name "Format A30
Column C2 heading "object| Type "Format A8
Column C3 heading "Number of| Blocks in| buffer| Cache "Format 99,999,999
Column C4 heading "Percentage|of object|blocks in| Buffer "Format 999
Column C5 heading "buffer| Pool "format A7
Column C6 heading "block| Size "Format 99,999

Select
Buffer_map.owner C0,
object_name C1,
case if object_type = ' TABLE PARTITION ' then ' TAB part '
When object_type = ' INDEX PARTITION ' and ' IDX part '
Else Object_type End C2,
Sum (Num_blocks) C3,
(SUM (num_blocks)/greatest (sum (Blocks),. 001)) *100 C4,
Buffer_pool c5,
Sum (bytes)/sum (blocks) C6
From
Buffer_map,
Dba_segments s
where
S.segment_name = Buffer_map.object_name
and
S.owner = Buffer_map.owner
and
S.segment_type = Buffer_map.object_type
and
NVL (S.partition_name, '-') = NVL (Buffer_map.subobject_name, '-')
GROUP BY
Buffer_map.owner,
object_name,
Object_type,
Buffer_pool
Having
SUM (num_blocks) > 10
ORDER BY
SUM (num_blocks) desc
;

REM Dbbuffer

Select Decode (pd.bp_id,1, ' KEEP ', 2, ' RECYCLE ', 3, ' DEFAULT ',
4, ' 2K Subcache ', 5, ' 4K Subcache ', 6, ' 8K Subcache ',
7, ' 16K Subcache ', 8, ' 32KSUBCACHE ', ' UNKNOWN ') Subcache,
Bh.object_name,bh.blocks
From X$kcbwds ds,x$kcbwbpd PD, (select/*+ use_hash (x) */Set_ds,
O.name Object_name,count (*) BLOCKS
From obj$ O, x$bh x where o.dataobj# = X.obj
and X.state!=0 and o.owner#!=0
GROUP by Set_ds,o.name) BH
where ds.set_id >= Pd.bp_lo_sid
and ds.set_id <= Pd.bp_hi_sid
and pd.bp_size! = 0
and Ds.addr=bh.set_ds;

With BH_LC as
(Select/*+ ORDERED */
Lc.addr, lc.child#, Lc.gets, lc.misses, Lc.immediate_gets,
Lc.immediate_misses, Lc.spin_gets, Lc.sleeps,
BH.HLADDR, Bh.tch tch, bh.file#, Bh.dbablk, Bh.class,
Bh.state, Bh.obj
From
X$KSLLD LD,
v$session_wait SW,
V$latch_children LC,
X$BH BH
where Lc.addr =sw.p1raw
and sw.p2= Ld.indx
and ld.kslldnam= ' Cache buffers chains '
and lower (sw.event) like '%latch% '
and sw.state= ' waiting '
and BH.HLADDR=LC.ADDR
)
Select Bh_lc.hladdr, Bh_lc.tch, O.owner, O.object_name, O.object_type,
bh_lc.child#, Bh_lc.gets,
Bh_lc.misses, Bh_lc.immediate_gets,
Bh_lc.immediate_misses, Spin_gets, sleeps
From
BH_LC,
Dba_objects o
where bh_lc.obj = o.object_id (+)
Union
Select Bh_lc.hladdr, Bh_lc.tch, O.owner, O.object_name, O.object_type,
bh_lc.child#, Bh_lc.gets, bh_lc.misses, Bh_lc.immediate_gets,
Bh_lc.immediate_misses, Spin_gets, sleeps
From
BH_LC,
Dba_objects o
where bh_lc.obj = o.data_object_id (+)
ORDER BY-desc
/

7, Dbms_rowid
Dbms_rowid. Rowid_block_number (ROWID)

Sql> set echo on
Sql>
Sql>
Sql>
Sql>
Sql> CREATE TABLE T
2 (a int,
3 b varchar2 (4000) Default Rpad (' * ', 4000, ' * '),
4 c varchar2 (+) Default Rpad (' * ', 3000, ' * ')
5)
6/

Table created.

Sql>
sql> INSERT into t (a) VALUES (1);

1 row created.

sql> INSERT into t (a) values (2);

1 row created.

sql> INSERT into t (a) values (3);

1 row created.

Sql> Delete from t where a = 2;

1 row deleted.

sql> INSERT into t (a) values (4);

1 row created.

Sql> Select a from t;

A
----------
1
4
3

3 Rows selected.

Sql>
Sql>--Example showing the above sort of effect without a delete
Sql>
Sql>
sql> INSERT into T (a) Select rownum from All_users;

Rows created.

Sql> commit;

Commit complete.

sql> Update T set B = null, c = NULL;

Rows updated.

Sql> set Serveroutput on
Sql> commit;

Commit complete.

Sql>
sql> INSERT into T (a) Select rownum+1000 from All_users;

Rows created.

Sql> Select Dbms_rowid.rowid_block_number (ROWID), a from t;

Dbms_rowid. Rowid_block_number (ROWID) A
------------------------------------ ----------
42610 1
42611 4
42612 3
42613 1
42613 1017
42614 2
42614 1016
42615 3
42615 1015
42616 4
42616 1014
43785 5
43785 1013
43786 6
43786 1012
43787 7
43787 1011
43788 8
43788 1010
43789 9
43789 1009
43790 10
43790 1008
43791 11
43791 1007
43792 12
43792 1006
43793 13
43793 1005
43794 14
43794 1004
43795 15
43795 1003
43796 16
43796 1002
43797 17
43797 1001

PNs rows selected.

sql> drop table t;

Table dropped.

Sql>

Create or Replace function Get_rowid
(L_rowid in VARCHAR2)
return VARCHAR2
Is
Ls_my_rowid VARCHAR2 (200);
Rowid_type number;
Object_number number;
RELATIVE_FNO number;
Block_number number;
Row_number number;
Begin
Dbms_rowid.rowid_info (L_rowid,rowid_type,object_number,relative_fno, Block_number, row_number);
Ls_my_rowid: = ' object# is: ' | | To_char (object_number) | | Chr (10) | |
' Relative_fno is: ' | | To_char (RELATIVE_FNO) | | Chr (10) | |
' Block number is: ' | | To_char (block_number) | | Chr (10) | |
' Row number is: ' | | To_char (Row_number);
return ls_my_rowid;
End
/

Select rowid,a.* from T2 A;

Select Get_rowid (' aaam13aabaaao/6aaa ')
ROW_ID from dual;

Select Get_rowid (' Aaam13aabaaao/6aab ')
ROW_ID from dual;

Thu Mar Page 1
Contents of Data Buffers

row_id
--------------------------------------------------------------------------------------------
object# is:52599
Relative_fno is:1
Block number is:61434
Row number is:0

8. Various SQL statements about buffer cache
Buffer hit Ratio:

Select (Sum (decode (name, ' physical reads ', value,0)/(SUM (Decode (name, ' db block gets ', value,0))
+sum (Decode (name, ' consistent gets ', value,0)))) * "Hit Ratio"
From V$sysstat;

SELECT (P1. VALUE + P2. Value-p3. VALUE)/(P1. VALUE + P2. VALUE)
From V$sysstat P1, V$sysstat P2, V$sysstat P3
WHERE P1.name = ' db block gets '
and p2.name = ' consistent gets '
and p3.name = ' physical reads '

--see the buffer cache hit ratio for one specific session
SELECT (P1. VALUE + P2. Value-p3. VALUE)/(P1. VALUE + P2. VALUE)
From V$sesstat P1,
V$statname N1,
V$sesstat P2,
V$statname N2,
V$sesstat P3,
V$statname N3
WHERE N1.name = ' db block gets '
and p1.statistic# = n1.statistic#
and P1.sid =141
and n2.name = ' consistent gets '
and p2.statistic# = n2.statistic#
and P2.sid = P1.sid
and n3.name = ' physical reads '
and p3.statistic# = n3.statistic#
and P3.sid = P1.sid

SELECT A.file_name, B.phyrds, B.PHYBLKRD
From Sys.dba_data_files A, V$filestat B
WHERE b.file# = a.file_id
ORDER by a.file_id

SELECT executions,
Buffer_gets,
Disk_reads,
First_load_time,
Sql_text
From V$sqlarea
ORDER by Disk_reads

9. Empty buffer Cache
alter system flush Buffer_cache;

10, different block size of the pool
11. Different types of ponds

The Oracle Learning buffer cache