深入瞭解ORACLE的邏輯讀

這篇實驗討論下資料的讀寫過程.
    我們都知道,資料塊是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 
/

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16978544/viewspace-691845/，如需轉載，請註明出處，否則將追究法律責任。