Oracle邏輯讀詳解

us_yunleiwang發表於2014-12-22
Oracle

Oracle邏輯讀詳解

1.物理讀(physical read)

當資料塊第一次讀取到,就會快取到buffer cache 中,而第二次讀取和修改該資料塊時就在記憶體buffer cache 了 以下是例子:

1.1 第一次讀取:

C:"Documents and Settings"Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Feb 28 09:32:04 2008 

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> set autotrace traceonly
SQL> select * from test;


Execution Plan
----------------------------------------------------------
  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
  1   0  TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)

 


Statistics
----------------------------------------------------------
       175 recursive calls
         0 db block gets
        24 consistent gets
         9 physical reads            --9個物理讀
         0 redo size
       373 bytes sent via SQL*Net to client
       503 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         2 sorts (memory)
         0 sorts (disk)
         1 rows processed

1.2 第二次讀取

SQL> select * from test;


Execution Plan
----------------------------------------------------------
  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
  1   0  TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)

 


Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
         7 consistent gets
         0 physical reads    --沒有發生物理讀了,直接從buffer cache 中讀取了
         0 redo size
       373 bytes sent via SQL*Net to client
       503 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

1.3 資料塊被重新讀入buffer cache ,這種發生在

如果有新的資料需要被讀入Buffer Cache中，而Buffer Cache又沒有足夠的空閒空間，Oracle就根據LRU演算法將LRU連結串列中LRU端的資料置換出去。當這些資料被再次訪問到時，需要重新從磁碟讀入。

SQL> alter session set events 'immediate trace name flush_cache';--清空資料緩衝區

Session altered.

SQL> select * from test;


Execution Plan
----------------------------------------------------------
  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
  1   0  TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)

 


Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
         7 consistent gets
         6 physical reads  --又重新發生了物理讀
         0 redo size
       373 bytes sent via SQL*Net to client
       503 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

2.邏輯讀(buffer read)

邏輯讀指的就是從（或者檢視從）Buffer Cache中讀取資料塊。按照訪問資料塊的模式不同，可以分為即時讀（Current Read）和一致性讀（Consistent Read）。注意：邏輯IO只有邏輯讀，沒有邏輯寫。

  • 即時讀

即時讀即讀取資料塊當前的最新資料。任何時候在Buffer Cache中都只有一份當前資料塊。即時讀通常發生在對資料進行修改、刪除操作時。這時，程式會給資料加上行級鎖，並且標識資料為“髒”資料。

SQL> select * from test for update;


Execution Plan
----------------------------------------------------------
  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
  1   0  FOR UPDATE
  2   1    TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)

 


Statistics
----------------------------------------------------------
         0 recursive calls
         1 db block gets
        14 consistent gets
         0 physical reads
       252 redo size
       386 bytes sent via SQL*Net to client
       503 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

SQL>

  • 一致性讀

Oracle 是一個多使用者系統。當一個會話開始讀取資料還未結束讀取之前，可能會有其他會話修改它將要讀取的資料。如果會話讀取到修改後的資料，就會造成資料的不一 致。一致性讀就是為了保證資料的一致性。在Buffer Cache中的資料塊上都會有最後一次修改資料塊時的SCN。如果一個事務需要修改資料塊中資料，會先在回滾段中儲存一份修改前資料和SCN的資料塊，然 後再更新Buffer Cache中的資料塊的資料及其SCN，並標識其為“髒”資料。當其他程式讀取資料塊時，會先比較資料塊上的SCN和自己的SCN。如果資料塊上的SCN 小於等於程式本身的SCN，則直接讀取資料塊上的資料；如果資料塊上的SCN大於程式本身的SCN，則會從回滾段中找出修改前的資料塊讀取資料。通常，普 通查詢都是一致性讀。

下面這個例子幫助大家理解一下一致性讀：

會話1中：

SQL> select * from test;

       ID
       ----------
         1000

SQL> update test set id=2000;

1 row updated.

會話2中:

SQL> set autotrace on
  SQL> select * from test;

       ID
      ----------
         1000


Execution Plan
----------------------------------------------------------
  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
  1   0  TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)

 


Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
         9 consistent gets   沒有事物做update時 是 7 consistent gets 說明多了2個 consistent gets    這2個是要從回滾段中獲取的     
         0 physical reads
        52 redo size
       373 bytes sent via SQL*Net to client
       503 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

SQL>

 

Oracle效能調優中，邏輯讀是個很重要的度量值，它不僅容易收集，而且能夠告訴我們許多關於資料庫引擎工作量的資訊。邏輯讀是在執行SQL語句的時候從快取記憶體中讀取的塊數。

 邏輯讀在Oracle調優中有四個好處：

（1）邏輯讀是受制於CPU能力的操作，因而，很好的反映了CPU的使用情況。

（2）邏輯讀可能導致物理讀，因而，透過減少邏輯讀的數量，很可能會降低I/O操作次數。

（3）邏輯讀是受制於序列的操作，既然經常要考慮多使用者負載的最佳化，最小化邏輯讀將有利於避免擴充套件性問題。

（4）邏輯讀的數量可以透過SQL跟蹤檔案和動態效能檢視在SQL語句以及執行計劃級別獲得。

 下面就來詳細的講述下邏輯讀相關的知識，以作為自己學習的一個總結。
     我們都知道,資料塊是oracle最基本的讀寫單位,但使用者所需要的資料,並不是整個塊,而是塊中的行,或列.當使用者發出SQL語句時,此語句被解析執行完畢，就開始了資料的抓取階段,在此階段,伺服器程式會先將行所在的資料塊從資料檔案中讀入buffer cache,這個過程叫做物理讀.物理讀,每讀取一個塊,就算一次物理讀.當塊被送進buffer cache後,並不能立即將塊傳給使用者,因為使用者所需要的並不是整個塊,而是塊中的行.從buffer cache的塊中讀取行的過程,就是邏輯讀.為了完成一次邏輯讀,伺服器程式先要在hash表中查詢塊所在的buffer cache  鏈.找到之後,需要在這個鏈上加一個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/23490154/viewspace-1375798/，如需轉載，請註明出處，否則將追究法律責任。

相關文章