在Oracle 11g中，全表扫描可能使用direct path read方式，绕过buffer cache，这样的全表扫描就是物理读了。 在10g中，都是通过gc buffer来读的，所以不存在direct path read的问题。
direct path read较高的可能原因有：
1. 大量的磁盘排序操作，order by, group by, union, distinct, rollup, 无法在PGA中完成排序，需要利用temp表空间进行排序。 当从临时表空间中读取排序结果时，会产生direct path read.
2. 大量的Hash Join操作，利用temp表空间保存hash区。
3. SQL语句的并行处理
4. 大表的全表扫描，在中，全表扫描的算法有新的变化，根据表的大小、高速缓存的大小等信息，决定是否绕过SGA直接从磁盘读Oracle11g取数据。而10g则是全部通过高速缓存读取数据，称为table scan(large)。11g认为大表全表时使用直接路径读，可能比10g中的数据文件散列读(db file scattered reads)速度更快，使用的latch也更少。
大量的direct path read等待时间最可能是一个应用程序问题。 direct path read事件由SQL语句驱动，这些SQL语句执行来自临时的或常规的表空间的直接读取操作。 当输入的内容大于PGA中的工作区域时，带有需要排序的函数的SQL语句将排序结果写入到临时表空间中，临时表空间中的排序顺序串随后被合并，用于提供最终的结果。读取排序结果时，Oracle会话在direct path read等待事件上等待。DB_FILE_DIRECT_IO_COUNT初始化参数可能影响direct path read的性能。
一个隐含参数：
_serial_direct_read = false 禁用direct path read
_serial_direct_read = true 启用direct path read
alter sytem set "_serial_direct_read"=never scope=both sid='*'; 可以显着减少 direct path read
https://www.doczj.com/doc/0417666949.html,/guxueliang/article/details/9088269,这篇文章中采用禁用direct path read得出read by other session的测试结果。
高级OWI与ORACLE性能调整读书笔记之Select/Select引起的read by other session
在抽象层次上，为了修改一个行，获得锁存器或者锁的过程如下
1) 为了查找要修改行相对应的块存在的位置(hash chain),请求cache buffers chains锁存器
2) 对载入块的缓冲区请求buffer lock,并释放cache buffers chains锁存器
3) 请求TX锁，修改相应行。若在此过程中，没有获得TX锁。则释放buffer lock并进入等待状态。之后重复过程1
4) 修改相应的行后，释放buffer lock。
为获得buffer lock而等待的期间内，一般会等待buffer busy waits事件。
下面进行相关的测试。
Select/Select引起的read by other session
SELECT / SELECT引起的buffer lock争用，发生在将相同块载入到缓冲区的过程中，通过read by other session等待时间观察。
测试方
案
创建>15w行的表RBOS_TEST
多个进程同时对RBOS_TEST表执行操作
在此过程中，多个进程同时相对同一个块获得buffer lock的过程中，则发生buffer lock竞争。
SQL> create table rbos_test(id char(1000));
表已创建。
SQL> insert into rbos_test select ' ' from dba_objects where rownum<100000;
已创建76542行。
SQL> insert into rbos_test select ' ' from dba_objects where rownum<100000;
已创建76542行。
SQL> create or replace procedure rbos_do_select
2 is
3 begin
4 for x in (select * from rbos_test) loop
5 null;
6 end loop;
7 end;
8 /
过程已创建。
SQL> var job_no number;
SQL> begin
2 for idx in 1 .. 50 loop
3 dbms_job.submit(:job_no,'rbos_do_select;');
4 end loop;
5 commit;
6 end;
7 /
新开一个Session
执行存储过程
SQL> exec rbos_do_select
PL/SQL 过程已成功完成。
SQL> select event from v$session_event where sid=(select sid from v$mystat wher
e rownum=1);
EVENT
-----------------------------------
Disk file operations I/O
latch: cache buffers chains
buffer busy waits
read by other session
enq: KO - fast object checkpoint
log file sync
db file sequential read
db file scattered read
db file parallel read
direct path read
SQL*Net message to client
read by other session是由于物理IO的原因引起，同时会会伴随db file sequential read，db file scattered read等物理读现象的出现。若以相同的方式再次测试的话，此现象会消失，因为所需要的数据块已经全部在缓冲区了，无需物理读。
减少read by other session的等待方法如下:
l 优化SQL,减少物理IO
l 增加SGA的大小(对于目前的硬件水平，大多数情况下不是太大问题了）
在ORACLE 11G下测试的同学可能最后看不出read by other session的结果，这是oracle11g的算法改变所致，详细请看Oracle 11g direct path read 等待事件的理解https://www.doczj.com/doc/0417666949.html,/guxueliang/article/details/9088315
高级OWI与ORACLE性能调整读书笔记之Latch:cache buffers chains争用情形
分类： Oracle 2013-06-07 17:48 162人阅读 评论(0) 收藏 举报
模拟Latch:cache buffers chains的两种情况。
Oracle Latch:cache buffers chains的出现一般情况下是由于低效的SQL(并发的、大范围的的索引扫描和全表扫描)和Hot Block引起。下面就模拟这两种情况。
并发的全表扫描
1. 首先建立测试Table,数据量在25W左右.
sajet@FDK> drop table cbc_full_scan_table;
表已删除。
sajet@FDK> create table cbc_full_scan_table (no int,object_name varchar2(50));
表已创建。
sajet@FDK> declare i int;
2 begin
3 for i in 1..5 loop
4 insert into cbc_full_scan_table select row
num as no,object_name from dba_objects;
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
sajet@FDK> commit;
提交完成。
sajet@FDK> select count(*) from cbc_full_scan_table;
COUNT(*)
----------
253881
2. 模拟并发全表扫描cbc_full_scan_table。
sajet@FDK> create or replace procedure cbc_full_scan_test is
2 i int;
3 icount int;
4 begin
5 for i in 1..1000 loop
6 select count(*) into icount from cbc_full_scan_table;
7 end loop;
8 end;
9 /
过程已创建。
3.模拟20个session执行以上的procedure；
sajet@FDK> var job_no number;
sajet@FDK> begin
2 for idx in 1..20 loop
3 dbms_job.submit(:job_no,'cbc_full_scan_test;');
4 commit;
5 end loop ;
6 end;
7 /
PL/SQL 过程已成功完成。
4.查看争用情况
sys@FDK> select sid,event,p1text,p1raw from v$session_wait where event='latch: cache buffers chains';
SID EVENT P1TEXT P1RAW
---------- ---------------------------------------- ---------- --------
129 latch: cache buffers chains address 6C1E8A80
130 latch: cache buffers chains address 6C1BBA80
133 latch: cache buffers chains address 6C1E8A80
134 latch: cache buffers chains address 6C18C380
137 latch: cache buffers chains address 6C1D9480
139 latch: cache buffers chains address 6C1E8A80
140 latch: cache buffers chains address 6C1CDC00
141 latch: cache buffers chains address 6C1E8A80
142 latch: cache buffers chains address 6C1B1B80
144 latch: cache buffers chains address 6C1D3F00
sys@FDK> col name for a30;
sys@FDK> select ADDR,LATCH#,CHILD#,NAME,GETS,MISSES,SLEEPS from
2 (select * from v$latch_children
3 where name ='cache buffers chains'
4 order by sleeps desc)
5 where rownum<=20;
ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS
-------- ---------- ---------- -------------------- ---------- ---------- ----------
6C197000 122 142 cache buffers chains 324019 31005 28
6C1C8C80 122 673 cache buffers chains 243026 23509 28
6C1A0780 122 243 cache buffers chains 323927 26931 27
6C1AD980 122 383 cache buffers chains 326615 27496 27
6C1C0A00 122 586 cache buffers chains 242895 16893 27
6C1A6300 122 304 cache buffers chains 650548 58148 27
6C1C6B80 122 651 cache buffers chains 242908 23467 27
6C1B5D80 122 471 cache buffers chains 242838 23343
26
6C196880 122 137 cache buffers chains 242793 19774 25
6C1AA500 122 348 cache buffers chains 323657 26331 25
6C1ABA00 122 362 cache buffers chains 242891 16885 25
6C1B1580 122 423 cache buffers chains 243322 24847 25
6C1E5300 122 976 cache buffers chains 163967 15485 25
6C1A9900 122 340 cache buffers chains 243391 23500 24
6C1CEF80 122 739 cache buffers chains 323591 27873 24
6C1E2D80 122 951 cache buffers chains 162042 16985 24
6C1D5880 122 809 cache buffers chains 161924 16069 24
6C198380 122 155 cache buffers chains 404665 34464 23
6C1D6D80 122 823 cache buffers chains 404445 33667 23
6C1AB280 122 357 cache buffers chains 243117 22308 23
已选择20行。
根据以上的查询，基本可以得知，cache buffers chains锁存器并没有集中在一两个锁存器上，可以判定为低效的SQL并发引起。
最后查询低效sql，改进sql。
热块
sys@FDK> create index cbc_full_scan_table_idx on sajet.cbc_full_scan_table(no);
索引已创建。
为了模拟只读一个块，我们保留no=2只有一条记录，确保只读这一个块
sys@FDK> delete cbc_full_scan_table where no=2 and rownum<=4；
创建Procedure
sajet@FDK> create procedure cbc_hot_block_test is
2 v_no int;
3 i int;
4 begin
5 for i in 1..20000 loop
6 select no into v_no from cbc_full_scan_table where no=2;
7 end loop;
8 end;
9 /
过程已创建。
20session同时执行以上procedure
sajet@FDK> var job_no number;
sajet@FDK> begin
2 for idx in 1..20 loop
3 dbms_job.submit(:job_no,'cbc_hot_block_test;');
4 commit;
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
此时查看latch争用情况。
sys@FDK> select sid,event,p1text,p1raw from v$session_wait where event='latch: cache buffers chains'
SID EVENT P1TEXT P1RAW
---------- ------------------------------ ---------- --------
131 latch: cache buffers chains address 6C1D3300
140 latch: cache buffers chains address 6C1D3300
141 latch: cache buffers chains address 6C1D3300
143 latch: cache buffers chains address 6C1D3300
147 latch: cache buffers chains address 6C1D3300
158 latch: cache buffers chains address 6C1D3300
已选择6行。
Latch的地址都是一样，表明Hot Block的存在。
sys@FDK> col name for a30;
sys@FDK> select ADDR,LATCH#,CHILD#,NAME,GETS,MISSES,SLEEPS from
2 (select
* from v$latch_children
3 where name ='cache buffers chains'
4 order by sleeps desc)
5 where rownum<=20;
ADDR LATCH# CHILD# NAME GETS MISSES SLEEPS
-------- ---------- ---------- ------------------------------ ---------- ---------- ----------
6C1D3300 122 784 cache buffers chains 10162666 355639 1663
6C197D80 122 151 cache buffers chains 5080225 428883 7
6C1E5000 122 974 cache buffers chains 17531 126 1
6C189C80 122 1 cache buffers chains 180 0 0
6C189E00 122 2 cache buffers chains 136 0 0
6C189F80 122 3 cache buffers chains 78 0 0
6C18A100 122 4 cache buffers chains 28 0 0
6C18A280 122 5 cache buffers chains 54 0 0
6C18A400 122 6 cache buffers chains 63 0 0
6C18A580 122 7 cache buffers chains 2371 0 0
6C18A700 122 8 cache buffers chains 759 0 0
6C18A880 122 9 cache buffers chains 106 0 0
6C18AA00 122 10 cache buffers chains 340 0 0
6C18AB80 122 11 cache buffers chains 114 0 0
6C18AD00 122 12 cache buffers chains 56 0 0
6C18AE80 122 13 cache buffers chains 91 0 0
6C18B000 122 14 cache buffers chains 101 0 0
6C18B180 122 15 cache buffers chains 957 0 0
6C18B300 122 16 cache buffers chains 47 0 0
6C18B480 122 17 cache buffers chains 90 0 0
已选择20行。
发现Sleep集中在地址为6C1D3300的子latch上，表明产生了热块。
现在我们根据以上提供的信息来查明究竟是那些段有热块。
根据X$BH,和DBA_OBJECTS关联查询
OBJECT_NAME HLADDR TCH DBARFIL DBABLK
---------------------------------------- -------- ---------- ---------- ----------
CBC_FULL_SCAN_TABLE_IDX 6C1D3300 22 1 60963
C_OBJ# 6C1D3300 8 1 56625
I_OBJ2 6C1D3300 5 1 231
ACCESS$ 6C1D3300 3
1 697
COL_USAGE$ 6C1D3300 2 1 4103
SYN$ 6C1D3300 2 1 35401
WRH$_PARAMETER_NAME 6C1D3300 1 3 2938
C_TOID_VERSION# 6C1D3300 1 1 8674
I_DEPENDENCY1 6C1D3300 1 1 8907
IDL_UB2$ 6C1D3300 1 1 9140
C_COBJ# 6C1D3300 1 1 56858
C_TOID_VERSION# 6C1D3300 1 1 13711
CBC_FULL_SCAN_TABLE 6C1D3300 0 5 694544
CBC_FULL_SCAN_TABLE 6C1D3300 0 5 695010
CBC_FULL_SCAN_TABLE 6C1D3300 0 5 694777
CBC_FULL_SCAN_TABLE_IDX 6C1D3300 0 1 60963
已选择16行。
可以很明显的看到CBC_FULL_SCAN_TABLE_IDX这个object的60963块的产生了热块。
以上模拟了Latch:cache buffers chains的两种情况，以及如何去查找引起的原因。
Oracle 锁
SELECT * FROM V$LOCK --所有拥有和等待锁的信息
SELECT * FROM V$ENQUEUE_LOCK --被阻塞等待者的信息
SELECT * FROM V$LOCKED_OBJECT --锁对象
SELECT * FROM DBA_WAITERS --发生阻塞时，拥有锁和等待锁的相互关系
--latch保护的是链表结构
--锁保护的是数据库对象
enqueue锁 --通过V$LOCK等去观察
普通锁 --通过等待事件去观察
row cache lock
library cache lock
library cache pin
buffer lock