建站
咨詢
售后
建站咨詢
新聞中心
本篇文章為大家展示了如何理解Oracle邏輯讀和物理讀，內(nèi)容簡(jiǎn)明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過(guò)這篇文章的詳細(xì)介紹希望你能有所收獲。
成都創(chuàng)新互聯(lián)公司是一家專(zhuān)業(yè)提供盤(pán)錦企業(yè)網(wǎng)站建設(shè),專(zhuān)注與成都網(wǎng)站建設(shè)、網(wǎng)站建設(shè)、HTML5、小程序制作等業(yè)務(wù)。10年已為盤(pán)錦眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專(zhuān)業(yè)網(wǎng)絡(luò)公司優(yōu)惠進(jìn)行中。
1.物理讀(physical read)
物理讀即是把數(shù)據(jù)從磁盤(pán)讀入到buffer catch的過(guò)程。 通常情況下是，如果需要數(shù)據(jù)的時(shí)候發(fā)現(xiàn)不存在于buffer catch當(dāng)中，即oracle就會(huì)執(zhí)行物理讀。
當(dāng)數(shù)據(jù)塊第一次讀取到,就會(huì)緩存到buffer cache 中,而第二次讀取和修改該數(shù)據(jù)塊時(shí)就在內(nèi)存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個(gè)物理讀
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 --沒(méi)有發(fā)生物理讀了,直接從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 數(shù)據(jù)塊被重新讀入buffer cache ,這種發(fā)生在
如果有新的數(shù)據(jù)需要被讀入Buffer Cache中，而B(niǎo)uffer Cache又沒(méi)有足夠的空閑空間，Oracle就根據(jù)LRU算法將LRU鏈表中LRU端的數(shù)據(jù)置換出去。當(dāng)這些數(shù)據(jù)被再次訪問(wèn)到時(shí)，需要重新從磁盤(pán)讀入。
SQL> alter session set events 'immediate trace name flush_cache'; --清空數(shù)據(jù)緩沖區(qū)
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 --又重新發(fā)生了物理讀
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中讀取數(shù)據(jù)塊。按照訪問(wèn)數(shù)據(jù)塊的模式不同，可以分為即時(shí)讀(Current Read)和一致性讀(Consistent Read)。注意：邏輯IO只有邏輯讀，沒(méi)有邏輯寫(xiě)。
即時(shí)讀
即時(shí)讀即讀取數(shù)據(jù)塊當(dāng)前的***數(shù)據(jù)。任何時(shí)候在Buffer Cache中都只有一份當(dāng)前數(shù)據(jù)塊。即時(shí)讀通常發(fā)生在對(duì)數(shù)據(jù)進(jìn)行修改、刪除操作時(shí)。這時(shí)，進(jìn)程會(huì)給數(shù)據(jù)加上行級(jí)鎖，并且標(biāo)識(shí)數(shù)據(jù)為“臟”數(shù)據(jù)。
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是一個(gè)多用戶系統(tǒng)。當(dāng)一個(gè)會(huì)話開(kāi)始讀取數(shù)據(jù)還未結(jié)束讀取之前，可能會(huì)有其他會(huì)話修改它將要讀取的數(shù)據(jù)。如果會(huì)話讀取到修改后的數(shù)據(jù)，就會(huì)造成數(shù)據(jù)的不一致。一致性讀就是為了保證數(shù)據(jù)的一致性。在Buffer Cache中的數(shù)據(jù)塊上都會(huì)有***一次修改數(shù)據(jù)塊時(shí)的SCN。如果一個(gè)事務(wù)需要修改數(shù)據(jù)塊中數(shù)據(jù)，會(huì)先在回滾段中保存一份修改前數(shù)據(jù)和SCN的數(shù)據(jù)塊，然后再更新Buffer Cache中的數(shù)據(jù)塊的數(shù)據(jù)及其SCN，并標(biāo)識(shí)其為“臟”數(shù)據(jù)。當(dāng)其他進(jìn)程讀取數(shù)據(jù)塊時(shí)，會(huì)先比較數(shù)據(jù)塊上的SCN和自己的SCN。如果數(shù)據(jù)塊上的SCN小于等于進(jìn)程本身的SCN，則直接讀取數(shù)據(jù)塊上的數(shù)據(jù);如果數(shù)據(jù)塊上的SCN大于進(jìn)程本身的SCN，則會(huì)從回滾段中找出修改前的數(shù)據(jù)塊讀取數(shù)據(jù)。通常，普通查詢都是一致性讀。
下面這個(gè)例子幫助大家理解一下一致性讀：
會(huì)話1中：
SQL> select * from test;
ID
----------
1000
SQL> update test set id=2000;
1 row updated.
會(huì)話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 沒(méi)有事物做update時(shí) 是 7 consistent gets 說(shuō)明多了2個(gè) consistent gets 這2個(gè)是要從回滾段中獲取的
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>
上述內(nèi)容就是如何理解Oracle邏輯讀和物理讀，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
網(wǎng)頁(yè)題目：如何理解Oracle邏輯讀和物理讀
文章起源：http://www.dlmjj.cn/article/ghpoho.html
