您好,登錄后才能下訂單哦!
這篇文章給大家分享的是有關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
感謝各位的閱讀!關于“Oracle邏輯讀的示例分析”這篇文章就分享到這里了,希望以上內容可以對大家有一定的幫助,讓大家可以學到更多知識,如果覺得文章不錯,可以把它分享出去讓更多的人看到吧!
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。