晶晶实验十七_逻辑读补疑篇

创建表空间命令如下：
create tablespace jj_ts_1 datafile 'e:\oracle\jj_1.dbf' size 50m extent management local uniform size 20k segment space management auto;
块大小4K，每区5个块。
创建表命令：
create table jj_two(id number(5),name char(50)) tablespace jj_ts_1;
插入记录命令：
begin
   for i in 101..200 loop
insert into jj_two values(i,'abc');
   end loop;
   commit;
end;
/
查看表中行的分布：
select dbms_rowid.rowid_block_number(rowid) bl,min(id),max(id) from jj_two group by dbms_rowid.rowid_block_number(rowid);
        BL    MIN(ID)    MAX(ID)
---------- ---------- ----------
        20         61        100
        21          1         60
查看区的分布
sid=10 pid=11> select extent_id,file_id, block_id, blocks from dba_extents where segment_name='JJ_TWO';
EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0         23         17          5
查看高水点：
sid=10 pid=11> select header_file,header_block from dba_segments where segment_name='JJ_TWO';
HEADER_FILE HEADER_BLOCK
----------- ------------
         23           19
sid=10 pid=11> alter system dump datafile 23 block 19;
系统已更改。
查看转储文件：
Highwater::  0x05c00016
高水点在23号文件22号块。
目前表中块的状态是：
  17（L1块） 18（L2块） 19（段头） 20（数据块） 21（数据块）
查一下Buffer cache中JJ_TWO的相关块：
sid=10 pid=11> select file#, dbablk, state, tch from x$bh where obj=8229; --(JJ_TWO的ID为8829)
     FILE#     DBABLK      STATE        TCH
---------- ---------- ---------- ----------
        23         17          1          2
        23         18          1          2
        23         19          1         16
        23         20          1          1
        23         21          1          1
以索引方式访问大量的行，将JJ_TWO在Buffer cache中的块挤走：
sid=11 pid=12> select /*+index(big_table big_table_id)*/* from big_table where id<=10000;
已选择300003行。
执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=211 Card=300008 Bytes=24900664)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost=211 Card=300008 Bytes=24900 664)
   2    1     INDEX (RANGE SCAN) OF 'BIG_TABLE_ID' (NON-UNIQUE) (Cost=9 Card=30000
再显示一下JJ_TWO在Buffer cache中的块：
sid=10 pid=11> select file#, dbablk, state, tch from x$bh where obj=8229;
未选定行
换个会话，开始测试：
sid=11 pid=12> select * from jj_two where id<=1;
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          3  physical reads
          0  redo size
        473  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
有5次逻辑读，3次物理读。一般来说，物理读是以块的数量来计算的，3次物理读，说明读了三个块，下面，再显示一下Buffer cache中，JJ_TWO的块：
sid=10 pid=11> select file#, dbablk, state, tch from x$bh where obj=8229;
     FILE#     DBABLK      STATE        TCH
---------- ---------- ---------- ----------
        23         17          1          0
        23         18          1          0
        23         19          1          0
只有三个，分别是段头17，普通数据块18和19。逻辑读的次数，两个普通数据块只读一次，段头读三次。再查询一次JJ_TWO表：
sid=11 pid=12> select * from jj_two where id<=1;
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        473  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
物理读为0，逻辑读仍是5次。再显示Buffer cache中的情况：
sid=10 pid=11> select file#, dbablk, state, tch from x$bh where obj=8229;
     FILE#     DBABLK      STATE        TCH
---------- ---------- ---------- ----------
        23         19          1          1
        23         20          1          0
        23         21          1          0
仍是三个块。这里说一点题外话，再次查询JJ_TWO表，段头的TCH加1，而普通数据块的TCH一直为0。这是因为全表扫描的块将一直被放在LRU的冷端。而段头因为被重用的可能性比较大，因此算法上和全表扫描时的普通块不一样。
总结：ASSM下，逻辑读的次数：
17（L1块） 18（L2块） 19（段头） 20（数据块） 21（数据块）
　不访问      不访问  　　  访问三次     一次      　　　   一次
总共为5次。当然，如果高水标记不在22号块，逻辑读的次数仍会增加。段头将会被访问三次，L2、L1块不会被访问。因为在段头中有个区地图，全表扫描只需按区地图中记载的信息扫描全部数据块即可，L2、L1块将会被跳过。还有，每当读完一个区时，会再读一次段头中的区地图，取出下一个区的位置，开始读下一个区。针对这一点，再测试如下：
再向表JJ_TWO中插入100行：
查看当前区的分配情况：
sid=10 pid=11> select extent_id,file_id, block_id, blocks from dba_extents where segment_name='JJ_TWO';
EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0         23         17          5
         1         23         22          5
一个L1块，通常情况下最少容纳16个数据块，上面这两个区，加起来不过10个块，因此，L1块还是17号块，不会增加新的L1块。
查看段头，高水标记目前在27块处：Highwater::  0x05c0001b 
目前JJ_TWO的块状态如下：
___________________第一个区__________________________   ________第二个区__________
17（L1块）　18（L2块）　19（段头）　20（后面都是数据块）21    　　　22　      23   24   25   26
  不访问    　　不访问  　　　　访问3次     1次 　　　　　　　1次 　　　  再访问一  1次 1次  1次  1次
                                                     　　　　　　　　　　　　　　　　　  次段头,
                                                       　　　　　　　　　　　　　　　　还有它本身
　　　　　　　　　　　　　　　　　　　                                                  共2次
此时，如果选择的行数不超过Arraysize的大小，逻辑读是11次，验证如下：
sid=11 pid=12> select * from jj_two where id<=1;
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          8  physical reads
          0  redo size
        473  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
最后要说明的是，Set autot工具并不会影响最终结果，我昨天的考虑不全面。使用游标将得到一模一样的结果。
逻辑读和行的关系，其实每Fetch一次，就是一次逻辑读。因为本次Fetch完成后，服务器进程并不知道程序是否还要进行下次抓取，因此，就将块上的Pin释放了。再次Fetch时，服务器进程需要再次申请Cache buffer chains闩，再次在块上加共享Pin，这又是一次逻辑读了。
全表扫描，段头会被访问3次，这点，我就不详细写的证明过程了，因为不是很严谨，只能说是推测。简单说一下推测过程，我建了个静态游标，只抓取一次就退出。Buffer cache中只有段头块和抓取行所在的数据块，且逻辑读为4。数据块只被抓取一次，因此，剩下的3次只能算是段头的了。

补充一段逻辑读的准确定义：

    我们都知道,数据块是oracle最基本的读写单位,但用户所需要的数据,并不是整个块,而是块中的行,或列.当用户发出SQL语句时,此语句被解析执行完毕，就开始了数据的抓取阶段,在此阶段,服务器进程会先将行所在的数据块从数据文件中读入buffer cache,这个过程叫做物理读.物理读,每读取一个块,就算一次物理读.当块被送进buffer cache后,并不能立即将块传给用户,因为用户所需要的并不整个块,而是块中的行.从buffer cache的块中读取行的过程,就是逻辑读.
   但Oracle并不会在一次逻辑读中，读块中所有的行，而是根据用户的设定，一次读一部分行。
   为了完成一次逻辑读,服务器进程先要在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表中定位块的这个过程。

   如果一次Fetch，访问了N个块，就是N个逻辑读。如果一个块中的行，花费N次Fetch才抓取完，也是N个逻辑读。