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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle邏輯讀過程Oracle
- 在Oracle中，什麼是物理讀和邏輯讀？Oracle
- oracle 邏輯結構Oracle
- 深度解讀GaussDB邏輯解碼技術原理
- Oracle OCP(41)：邏輯結構Oracle
- MySQL調優篇 | 邏輯架構解讀（1）MySql架構
- Oracle SCN機制詳細解讀Oracle
- 《底層邏輯》讀後感
- oracle邏輯備份之--資料泵Oracle
- sql生成可讀性邏輯圖SQL
- Oracle 9i升級19C 邏輯遷移詳細方法（一）Oracle
- Oracle 9i升級19C 邏輯遷移詳細方法（二）Oracle
- 【SpringSecurity系列02】SpringSecurity 表單認證邏輯原始碼解讀SpringGse原始碼
- 3：Oracle體系結構（邏輯結構）Oracle
- 《金子塔原理》讀書筆記之解決問題的邏輯筆記
- 基於UNIX系統，邏輯故障的資料災難解讀
- 賽靈思（Xilinx）BlockRam（Bram）的結構與讀邏輯解釋BloC
- [20190311]關於oracle物理與邏輯壞塊.txtOracle
- [20231124]奇怪的高邏輯讀4.txt
- [20230216]奇怪的高邏輯讀3.txt
- 適當調大arraysize減少邏輯讀
- [20210224]fetch r=0算邏輯讀嗎.txt
- [20210220]gdb跟蹤邏輯讀2.txt
- sed編輯器的使用以及詳細解讀
- 從《英雄聯盟》手遊成績看解讀資料的邏輯
- 電商促銷後臺邏輯詳述
- [ 邏輯鍛鍊] 用 JavaScript 做一個小遊戲 ——2048 （詳解版）JavaScript遊戲
- Oracle邏輯備份與恢復選項說明Oracle
- 【ASK_ORACLE】Oracle Data Guard（三）邏輯備庫的概念和優勢Oracle
- 一次調整arraysize減少邏輯讀
- [20210301]為什麼邏輯讀這麼多.txt
- [20210219]全表掃描邏輯讀問題.txt
- PostgreSQL 原始碼解讀（120）- MVCC#5(獲取事務號-主邏輯)SQL原始碼MVCC#
- Oracle SCN詳解Oracle
- oracle rowid詳解Oracle
- ORACLE -詳解SCNOracle
- Jmeter(十) - 從入門到精通 - JMeter邏輯控制器 - 中篇（詳解教程）JMeter
- Jmeter(十一) - 從入門到精通 - JMeter邏輯控制器 - 下篇（詳解教程）JMeter
- Jmeter(九) - 從入門到精通 - JMeter邏輯控制器 - 上篇（詳解教程）JMeter