由讀一致性分析undo,讀一致性undo
下面通過undo的一致性讀分析undo:
[oracle@localhost ~]$ lsb_release -a
LSB Version: :core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch
Distributor ID: EnterpriseEnterpriseServer
Description: Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Release: 5.5
Codename: Carthage
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> create table t(id number,name varchar2(10));表已建立。
經過時間: 00: 00: 00.15
SQL> set timing off;
SQL> show user;
USER 為 "HR"
SQL> insert into t values(1,'a');
已建立 1 行。
SQL> insert into t values(2,'b');
已建立 1 行。
SQL> commit;
提交完成。
SQL> update t set name='c' where id=1;
已更新 1 行。
SQL> select * from t;
ID NAME
---------- ----------
1 c
2 b
注意沒提交。
重新開啟一個session:SQL> select * from t;
ID NAME
---------- ----------
1 a
2 b
此時還是讀取到修改之前的資料,這裡的a是重undo裡讀取的,下面dump分析這個過程:
SQL> select t.*,rowid from t;
ID NAME ROWID
---------- ---------- ------------------
1 c AAASunAAEAAABuuAAA
2 b AAASunAAEAAABuuAAB
我們利用oracle提供的包,可以獲得第一條資料所在的資料檔案號及塊號:
SQL> show user;
USER 為 "SYS"
SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) bno from hr.t;
FNO BNO
---------- ----------
4 7086
4 7086
此時我們可以dump 4號檔案的第7086塊:
SQL> alter system dump datafile 4 block 7086;
系統已更改。
下面是摘自部分轉儲檔案: *** 2015-04-20 15:26:34.151
Block header dump: 0x01001bae
Object id on Block? Y
seg/obj: 0x12ba7 csc: 0x00.6658e2 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1001ba8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.017.0000098a 0x00c009ac.02f0.24 C--- 0 scn 0x0000.0066582c
0x02 0x0008.00d.00000a84 0x00c02e17.0258.28 ----(表示事務鎖定) 1(鎖定一條資料) fsc 0x0000.00000000 =>0x2列已經被鎖定了
bdba: 0x01001bae uba:undo block address
data_block_dump,data header at 0xa18264
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x00a18264
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f88
avsp=0x1f70
tosp=0x1f70
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f90
0x14:pri[1] offs=0x1f88
block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb(鎖標記): 0x2 cc: 2 第一行第二列已經被上鎖了,被一個事務鎖定。 lb:lock byte
col 0: [ 2] c1 02 =>這列是從4號檔案7086塊中讀取的
col 1: [ 1] 63 63代表是c,實際已經被改了
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 1] 62
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 7086 maxblk 7086
第二個session我們查出的第一行第二列卻是a,也是從4號檔案7086個塊上讀取得,但是發現第二列已經被上鎖,不能讀取。
我們通過uba提供的地址,可以擷取修改之前的資料a存放的位置:
uba:
0x00c02e17.0258.28
先把這個十六進位數轉換成十進位數,oracle提供的十進位包可以擷取檔案號及塊號:
SQL> select to_number('00c02e17','XXXXXXXXXXXXXXXXXX') from dual;
TO_NUMBER('00C02E17','XXXXXXXXXXXXXXXXXX')
------------------------------------------
12594711
SQL> select dbms_utility.data_block_address_file(12594711) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(12594711)
----------------------------------------------
3
SQL> select dbms_utility.data_block_address_block(12594711) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(12594711)
-----------------------------------------------
11799
在第二個session中,oracle提示讀取第一行第二列需要到3號檔案上第11799號塊上讀取:
SQL> alter system dump datafile 3 block 11799;
系統已更改。
下面宅在部分轉儲檔案:
uba: 0x00c02e17.0258.25 ctl max scn: 0x0000.00665307 prv tx scn: 0x0000.00665325
txn start scn: scn: 0x0000.006658a2 logon user: 91
prev brb: 12594708 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 191
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01001bae hdba: 0x01001baa
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 1] 61
End dump data blocks tsn: 2 file#: 3 minblk 11799 maxblk 11799
所以通過undo讀取了61(代表a)
我們查看資料檔案3是什麼檔案類型:
select * from dba_data_files where file_id=3;
是undo資料檔案。
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
預設的是undotbs1資料表空間,可以建立很多undo資料表空間,但是一個執行個體只用一個undo資料表空間。
undo資料表空間是有undo segments 組成,查看有多少undo段:
select * from dba_rollback_segs;
注意上面的owner列,如果是public,則該執行個體建立的undo段可以被資料庫其他執行個體使用,但是sys表示的是私人undo段,只可以被該undo段建立者使用。
注意到undo段的狀態了沒,現在預設的undo資料表空間是undotbs1,所以該undo段都是線上,undo_w資料表空間的undo段都是離線。
我們可以通過修改參數undo_tablespace設定預設undo資料表空間。
oracle對於處於online的undo段進行監視,通過視圖v$rollstat查看:
上面總共有11條,usn是undo段編號
一個事務使用一個undo段
下面執行一個事物:
SQL> update t set name='c' where id=1;
已更新 1 行。
select * from v$transaction;
注意XIDUSN列表示的是undo段編號,此時該事務使用的是10號undo段
查看10號undo段:
select * from v$rollstat;
XACTS列表示的是該10號undo段上具有活動的事務數量
此時修改預設undo資料表空間:
SQL> alter system set undo_tablespace=undo_w;
系統已更改。
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO_W
此時查看undo段狀態:
select * from dba_rollback_segs;
undotbs1裡的undo段除了10號,其他的都處於offline,因為它任被使用,事務結束後,自動變為offline
通過查看視圖:
select * from v$rollstat;
發現10號undo段狀態是pending offline(pending在等待…期間)
會疑問,每個復原段段上到底可以被幾個事務使用呢?
SQL> show parameter roll
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5
可以被5個事務使用,但是這是在undo資料表空間沒有自動管理之前,自從undo資料表空間自動管理後,該parameter不起作用。
一個undo段只能被一個事務使用,若undo被事務用完後,則oracle background process smon 自動建立undo段.
如果一個復原段被多個事務使用的話,undo段頭會有等待,影響並發性,我們可以通過視圖V$WAITSTAT查看等待事件:
select * from v$waitstat;
可以通過執行多個事務,類比smon自動建立undo段,自行類比實驗。(smon建立的undo段不會因為事務結束而回收)
下面看一下一個很重要的參數:undo_retention單位是秒(表示的是事務提交以後,放在undo裡的資料保留的時間)
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO_W
SQL> show parameter roll
很有名的oracle一個錯誤:ORA-01555(快照太舊)
實際情況下查詢發生在修改之前,比較少。
出現這個錯的可能情況:
undo資料表空間太小
查詢資料的時間過長(sql查詢效能差)
undo_rentention太小
我們通過視圖dba_tablespaces,引出一個參數:
SELECT TABLESPACE_NAME,RETENTION FROM DBA_TABLESPACES;
retention這列,undo資料表空間預設的是NOGUARANTEE,但是我們可以修改這個參數,強制保留。
SQL> alter tablespace undotbs1 retention GUARANTEE;
資料表空間已更改。
一定會保留900秒。
當讓可以改undo_rentention
SQL> alter system set undo_retention=1200;
系統已更改。
上面列出的出現ORA-01555的三種情況,這三者之間有關係,比如增加了undo_rentention,從而需要的undo資料表空間要更多。
oracle裡提供了undo advisor(顧問),我們可以根據這個,來平衡這幾者關係,進入到OEM:
[oracle@localhost ~]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://diy_os:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control .......
進入em後,在自動管理介面,提供了這一張圖:
根據undo_rentention的大小,來確定所需的undo資料表空間的大小,可以更改分析時段,會提供建議圖形介面。
圖形是如何作出的呢?
有一個公式:
UndoSpace = [UR * (UPS * DBS)] + (DBS*24)
UR:undo_rentention in seconds
UPS:number of undo data blocks generated per second
DBS:overhead varies based on extent and file size(db_block_size)實際就是塊大小:show parameter db_block;
我們可以根據oracle的一個視圖:v$undostat;
每十分鐘計算undo的數量。可以根據這個視圖可以畫出上述圖形。