晶晶实验十六 详述逻辑读

    这篇实验讨论下数据的读写过程.
    我们都知道,数据块是oracle最基本的读写单位,但用户所需要的数据,并不是整个块,而是块中的行,或列.当用户发出SQL语句时,此语句被解析执行完毕，就开始了数据的抓取阶段,在此阶段,服务器进程会先将行所在的数据块从数据文件中读入buffer cache,这个过程叫做物理读.物理读,每读取一个块,就算一次物理读.当块被送进buffer cache后,并不能立即将块传给用户,因为用户所需要的并不整个块,而是块中的行.从buffer cache的块中读取行的过程,就是逻辑读.为了完成一次逻辑读,服务器进程先要在hash表中查找块所在的cache buffer 链.找到之后,需要在这个链上加一个cache buffer chains 闩,加闩成功之后,就在这个链中寻找指定的块,并在块上加一个pin锁.并释放cache buffer chains闩.然后就可以访问块中的行了.服务器进程不会将块中所有满足条件的行一次取出,而是根据你的抓取命令,每次取一定数量的行.这些行取出之后,会经由PGA传给客户端用户.行一旦从buffer cache中取出,会话要释放掉在块上所加的PIN.本次逻辑读就算结束.如果还要再抓取块中剩余的行,服务器进程要再次申请获得cache bufffer链闩.再次在块上加PIN.这就算是另外一次逻辑读咯.也就是说,服务器进程每申请一次cache buffer链闩,就是一次逻辑读.而每次逻辑读所读取的行的数量,可以在抓取命令中进行设置.
    逻辑读和Cache buffer chains闩关系密切，TOM曾有文章提到，进程每申请一次Cache buffer chains闩，就是一次逻辑读。但是，逻辑读并不等同于Cache buffer chains闩，每次逻辑读，在9i中至少需要获得两Cache buffer chains闩。逻辑读是指在Hash表中定位块的这个过程。

下面是我的测试：
步1：建立测试表：
create table jj_one(id number(5),name char(40));

步2：插入100行
begin
   for i in 1..100 loop
     insert into jj_one values(i,'aaa');
   end loop;
end;
/

或：insert into jj_one select rownum,'aaa' from DBA_objects where rownum<=100;

步3：显示一下表中行的分布
sid=10 pid=11> select bk,max(id),min(id) from (select dbms_rowid.rowid_block_number(rowid) bk,id from jj_one) group by bk;

        BK    MAX(ID)    MIN(ID)
---------- ---------- ----------
     42594         81          1
     42595        100         82

可以看到，表共占两个块，ID从1到81的行在块42594中，ID从82到100的行在42595中。

步4：设备批量读取参数为15
sid=10 pid=11> set arraysize 15
因为9i或10g中的默认值都是15，如果并没有更改过这个设置，此步也可省去。

步5：查看1行：
sid=11 pid=12> set autot trace stat
sid=11 pid=12> select * from jj_one where id<=1;


统计信息
----------------------------------------------------------
          0  recursive calls
          6  consistent gets
          0  physical reads
        458  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  rows processed
（省略无关行）

逻辑读为6

步6：查询15行以内：
sid=11 pid=12> select * from jj_one where id<=2;

统计信息
----------------------------------------------------------
          0  recursive calls
          6  consistent gets
          0  physical reads
        493  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  rows processed

在抓取行数小于15的情况下，逻辑读始终为6。

步7：查询16行以上：
sid=11 pid=12> select * from jj_one where id<=16;

已选择16行。

统计信息
----------------------------------------------------------
          0  recursive calls
          7  consistent gets
          0  physical reads
        699  bytes sent via SQL*Net to client
        383  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         16  rows processed

逻辑读已经变成7次。

注意，在10G中，对块读的算法有改进。以同样的谓词条件，访问同样的行时，第一次访问时的逻辑读要比以后再行访问时多的多。因此，在10G中，同样的命令，多执行几次，这样看到的结果比较全面。

还有一点，访问15行以内时，为什么会有6次逻辑读？不应该是1次吗？这里，我相信Set autot trace stat命令本身有一定的原因，如果用下面的静态游标：

sid=10 pid=11> alter session set events '10046 trace name context forever ,level 14';
会话已更改。

declare
  type mid is table of jj_one.id%type;
  mid1 mid;
  cursor c is select id from jj_one where id>=1 and id<=15;
begin
  open c;
  fetch c bulk collect into mid1 limit 15;
  dbms_output.put_line(c%rowcount);
  close c;
end;
/

sid=10 pid=11> alter session set events '10046 trace name context off';
会话已更改。

用Tkprof格式化跟踪结果：
E:\oracle\admin\mytwo\udump>tkprof mytwo_ora_756.trc m3.txt

查看M3.txt文件：
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0          15
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0          15

逻辑读只有3次。这3次逻辑读，有一次是针对行所在块的，其余两次是针对段头的。


实验完毕

从上面的实验中可以看出，“成批读取”中，批大小的设定，可以影响逻辑读的数量。批大小越大，读相同数量的行，逻辑读就越少。而且服务端和客户端交互的次数也越少，由网络传输的数据也可以减少，下面看一下测试：
批大小为1：
sid=11 pid=12> set arraysize 1
sid=11 pid=12> select * from jj_one;
已选择100行。
统计信息
----------------------------------------------------------
         54  consistent gets
       7206  bytes sent via SQL*Net to client
        911  bytes received via SQL*Net from client
         51  SQL*Net roundtrips to/from client
        100  rows processed
批大小为100：
sid=11 pid=12> set arraysize 100
sid=11 pid=12> select * from jj_one;
已选择100行。
统计信息
----------------------------------------------------------
          6  consistent gets
       1277  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
        100  rows processed

差别是很明显的，bytes sent via SQL*Net to client的数值，相差了6倍左右。

但这并不代表将批大小设置的越高，速度就越快，否则，Oracle直接将它设置为一个最大的值，不就行了，干吗还要让我们去自己调节呢！
行从Buffer cache中读出来后，会先缓存在PGA中（具体是在游标的运行时区），然后再传给客户端。如果批大小过大，在PGA、客户端占用的内存也会增大。而且，如果渐歇性的在网络上传输大量数据，对网络也会有一定影响。下面来观察一下批大小对PGA的影响：
在会话11中执行如下过程：
declare
  type mid is table of t1.id%type;
  mid1 mid;
  cursor c is select id from t1;
begin
  open c;
  loop
    fetch c bulk collect into mid1 limit 5000;
  exit when c%notfound;
  end loop;
  dbms_output.put_line(c%rowcount);
  close c;
end;
/

在另一会话中观察会话11的内存占用情况：
sid=10 pid=11> @pga   --此脚本下面有说明
输入 user 的值:  11
原值    7: and b.sid= &user
新值    7: and b.sid= 11

  PGA Used  PGA Alloc    PGA Max
---------- ---------- ----------
    561508     779492     779492


然后将会话11中过程的批大小改为1：fetch c bulk collect into mid1 limit 5000; 再试一次

在另一会话观察会话11的PGA占用情况：
sid=10 pid=11> @pga
输入 user 的值:  11
原值    7: and b.sid= &user
新值    7: and b.sid= 11

  PGA Used  PGA Alloc    PGA Max
---------- ---------- ----------
    184388     250668     250668

批大小为5000时的内存占用，是批大小为1时的3倍左右。另外，测试表一定要大一些，我的测试表是1000000行，否则不容易看到结果。在10G中，可以得到基本相同的结果。

PGA.SQL脚本如下：
--pga_by_process.sql：
SELECT
a.pga_used_mem "PGA Used",
a.pga_alloc_mem "PGA Alloc",
a.pga_max_mem "PGA Max"
FROM v$process a,v$session b
where a.addr = b.paddr
and b.sid= &user
/

晶晶实验十六 详述逻辑读

    这篇实验讨论下数据的读写过程.
    我们都知道,数据块是oracle最基本的读写单位,但用户所需要的数据,并不是整个块,而是块中的行,或列.当用户发出SQL语句时,此语句被解析执行完毕，就开始了数据的抓取阶段,在此阶段,服务器进程会先将行所在的数据块从数据文件中读入buffer cache,这个过程叫做物理读.物理读,每读取一个块,就算一次物理读.当块被送进buffer cache后,并不能立即将块传给用户,因为用户所需要的并不整个块,而是块中的行.从buffer cache的块中读取行的过程,就是逻辑读.为了完成一次逻辑读,服务器进程先要在hash表中查找块所在的cache buffer 链.找到之后,需要在这个链上加一个cache buffer chains 闩,加闩成功之后,就在这个链中寻找指定的块,并在块上加一个pin锁.并释放cache buffer chains闩.然后就可以访问块中的行了.服务器进程不会将块中所有满足条件的行一次取出,而是根据你的抓取命令,每次取一定数量的行.这些行取出之后,会经由PGA传给客户端用户.行一旦从buffer cache中取出,会话要释放掉在块上所加的PIN.本次逻辑读就算结束.如果还要再抓取块中剩余的行,服务器进程要再次申请获得cache bufffer链闩.再次在块上加PIN.这就算是另外一次逻辑读咯.也就是说,服务器进程每申请一次cache buffer链闩,就是一次逻辑读.而每次逻辑读所读取的行的数量,可以在抓取命令中进行设置.
    逻辑读和Cache buffer chains闩关系密切，TOM曾有文章提到，进程每申请一次Cache buffer chains闩，就是一次逻辑读。但是，逻辑读并不等同于Cache buffer chains闩，每次逻辑读，在9i中至少需要获得两Cache buffer chains闩。逻辑读是指在Hash表中定位块的这个过程。

下面是我的测试：
步1：建立测试表：
create table jj_one(id number(5),name char(40));

步2：插入100行
begin
   for i in 1..100 loop
     insert into jj_one values(i,'aaa');
   end loop;
end;
/

或：insert into jj_one select rownum,'aaa' from DBA_objects where rownum<=100;

步3：显示一下表中行的分布
sid=10 pid=11> select bk,max(id),min(id) from (select dbms_rowid.rowid_block_number(rowid) bk,id from jj_one) group by bk;

        BK    MAX(ID)    MIN(ID)
---------- ---------- ----------
     42594         81          1
     42595        100         82

可以看到，表共占两个块，ID从1到81的行在块42594中，ID从82到100的行在42595中。

步4：设备批量读取参数为15
sid=10 pid=11> set arraysize 15
因为9i或10g中的默认值都是15，如果并没有更改过这个设置，此步也可省去。

步5：查看1行：
sid=11 pid=12> set autot trace stat
sid=11 pid=12> select * from jj_one where id<=1;


统计信息
----------------------------------------------------------
          0  recursive calls
          6  consistent gets
          0  physical reads
        458  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  rows processed
（省略无关行）

逻辑读为6

步6：查询15行以内：
sid=11 pid=12> select * from jj_one where id<=2;

统计信息
----------------------------------------------------------
          0  recursive calls
          6  consistent gets
          0  physical reads
        493  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  rows processed

在抓取行数小于15的情况下，逻辑读始终为6。

步7：查询16行以上：
sid=11 pid=12> select * from jj_one where id<=16;

已选择16行。

统计信息
----------------------------------------------------------
          0  recursive calls
          7  consistent gets
          0  physical reads
        699  bytes sent via SQL*Net to client
        383  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         16  rows processed

逻辑读已经变成7次。

注意，在10G中，对块读的算法有改进。以同样的谓词条件，访问同样的行时，第一次访问时的逻辑读要比以后再行访问时多的多。因此，在10G中，同样的命令，多执行几次，这样看到的结果比较全面。

还有一点，访问15行以内时，为什么会有6次逻辑读？不应该是1次吗？这里，我相信Set autot trace stat命令本身有一定的原因，如果用下面的静态游标：

sid=10 pid=11> alter session set events '10046 trace name context forever ,level 14';
会话已更改。

declare
  type mid is table of jj_one.id%type;
  mid1 mid;
  cursor c is select id from jj_one where id>=1 and id<=15;
begin
  open c;
  fetch c bulk collect into mid1 limit 15;
  dbms_output.put_line(c%rowcount);
  close c;
end;
/

sid=10 pid=11> alter session set events '10046 trace name context off';
会话已更改。

用Tkprof格式化跟踪结果：
E:\oracle\admin\mytwo\udump>tkprof mytwo_ora_756.trc m3.txt

查看M3.txt文件：
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0          15
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0          15

逻辑读只有3次。这3次逻辑读，有一次是针对行所在块的，其余两次是针对段头的。


实验完毕

从上面的实验中可以看出，“成批读取”中，批大小的设定，可以影响逻辑读的数量。批大小越大，读相同数量的行，逻辑读就越少。而且服务端和客户端交互的次数也越少，由网络传输的数据也可以减少，下面看一下测试：
批大小为1：
sid=11 pid=12> set arraysize 1
sid=11 pid=12> select * from jj_one;
已选择100行。
统计信息
----------------------------------------------------------
         54  consistent gets
       7206  bytes sent via SQL*Net to client
        911  bytes received via SQL*Net from client
         51  SQL*Net roundtrips to/from client
        100  rows processed
批大小为100：
sid=11 pid=12> set arraysize 100
sid=11 pid=12> select * from jj_one;
已选择100行。
统计信息
----------------------------------------------------------
          6  consistent gets
       1277  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
        100  rows processed

差别是很明显的，bytes sent via SQL*Net to client的数值，相差了6倍左右。

但这并不代表将批大小设置的越高，速度就越快，否则，Oracle直接将它设置为一个最大的值，不就行了，干吗还要让我们去自己调节呢！
行从Buffer cache中读出来后，会先缓存在PGA中（具体是在游标的运行时区），然后再传给客户端。如果批大小过大，在PGA、客户端占用的内存也会增大。而且，如果渐歇性的在网络上传输大量数据，对网络也会有一定影响。下面来观察一下批大小对PGA的影响：
在会话11中执行如下过程：
declare
  type mid is table of t1.id%type;
  mid1 mid;
  cursor c is select id from t1;
begin
  open c;
  loop
    fetch c bulk collect into mid1 limit 5000;
  exit when c%notfound;
  end loop;
  dbms_output.put_line(c%rowcount);
  close c;
end;
/

在另一会话中观察会话11的内存占用情况：
sid=10 pid=11> @pga   --此脚本下面有说明
输入 user 的值:  11
原值    7: and b.sid= &user
新值    7: and b.sid= 11

  PGA Used  PGA Alloc    PGA Max
---------- ---------- ----------
    561508     779492     779492


然后将会话11中过程的批大小改为1：fetch c bulk collect into mid1 limit 5000; 再试一次

在另一会话观察会话11的PGA占用情况：
sid=10 pid=11> @pga
输入 user 的值:  11
原值    7: and b.sid= &user
新值    7: and b.sid= 11

  PGA Used  PGA Alloc    PGA Max
---------- ---------- ----------
    184388     250668     250668

批大小为5000时的内存占用，是批大小为1时的3倍左右。另外，测试表一定要大一些，我的测试表是1000000行，否则不容易看到结果。在10G中，可以得到基本相同的结果。

PGA.SQL脚本如下：
--pga_by_process.sql：
SELECT
a.pga_used_mem "PGA Used",
a.pga_alloc_mem "PGA Alloc",
a.pga_max_mem "PGA Max"
FROM v$process a,v$session b
where a.addr = b.paddr
and b.sid= &user
/