[20181116]18c DML 日誌優化.txt
[20181116]18c DML 日誌優化.txt
1.環境:
xxxxxxxx> select banner_full from v$version;
BANNER_FULL
-----------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
2.建立測試指令碼:
xxxxxxxx> create table t as select rownum id ,lpad('y',200,'y') v1,lpad('z',100,'z') v2 from dual connect by level<=100;
Table created.
xxxxxxxx> select dump('x',16),dump('y',16),dump('z',16) from dual ;
DUMP('X',16) DUMP('Y',16) DUMP('Z',16)
-------------------------------- -------------------------------- --------------------------------
Typ=96 Len=1: 78 Typ=96 Len=1: 79 Typ=96 Len=1: 7a
xxxxxxxx> @ viewsess redo%size
NAME STATISTIC# VALUE SID
---------------------------------- ---------- -------------- ----------
redo size 293 684 258
redo size for lost write detection 295 0 258
redo size for direct writes 296 0 258
redo write size count ( 4KB) 321 0 258
redo write size count ( 8KB) 322 0 258
redo write size count ( 16KB) 323 0 258
redo write size count ( 32KB) 324 0 258
redo write size count ( 64KB) 325 0 258
redo write size count ( 128KB) 326 0 258
redo write size count ( 256KB) 327 0 258
redo write size count ( 512KB) 328 0 258
redo write size count (1024KB) 329 0 258
redo write size count (inf) 330 0 258
IMU Redo allocation size 736 0 258
14 rows selected.
--//建立測試指令碼a.txt
column member new_value v_member
column member noprint
set numw 12
alter system switch logfile ;
--//alter system archive log current;
--//12c以上不允許在pluggable database執行這條命令.注這個庫沒有開啟歸檔,alter system archive log current;會報錯.
--//採用alter system switch logfile ;.
SELECT member FROM v$log a, v$logfile b WHERE a.group#(+) = b.group# and a.STATUS='CURRENT' and rownum=1;
column curr1 new_value v_curr1
select current_scn curr1 from v$database;
--//以下操作DML內容:
update t set v1=lpad('y',200,'y') ,v2=lpad('z',100,'z');
commit ;
exec dbms_session.sleep(3);
column curr2 new_value v_curr2
select current_scn curr2 from v$database;
prompt exec DBMS_LOGMNR.START_LOGMNR(STARTSCN => &&v_curr1 ,ENDSCN => &&v_curr2 ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
prompt alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2;
alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2;
--//執行指令碼a.txt:
xxxxxxxx> @ a.txt
System altered.
CURR1
------------
1336662128
100 rows updated.
Commit complete.
PL/SQL procedure successfully completed.
CURR2
------------
1336662131
exec DBMS_LOGMNR.START_LOGMNR(STARTSCN => 1336662128 ,ENDSCN => 1336662131 ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE)
alter system dump logfile '+DATA/ORCLCDB/ONLINELOG/group_2.264.985030477' scn min 1336662128 scn max 1336662131
System altered.
xxxxxxxx> @ viewsess redo%size
NAME STATISTIC# VALUE SID
---------------------------------- ---------- -------------- ------------
redo size 293 23692 258
redo size for lost write detection 295 0 258
redo size for direct writes 296 0 258
redo write size count ( 4KB) 321 0 258
redo write size count ( 8KB) 322 0 258
redo write size count ( 16KB) 323 0 258
redo write size count ( 32KB) 324 0 258
redo write size count ( 64KB) 325 0 258
redo write size count ( 128KB) 326 0 258
redo write size count ( 256KB) 327 0 258
redo write size count ( 512KB) 328 0 258
redo write size count (1024KB) 329 0 258
redo write size count (inf) 330 0 258
IMU Redo allocation size 736 49012 258
14 rows selected.
--//日誌大小 23692-648 = 23044 ,大約22K.
--//如果你查詢轉儲檔案,你可以發現一個奇特的資訊,你根本找不到yyyy,zzzz相關的字串資訊.
--//也就是如果dml欄位前後兩者一樣,日誌根本沒有記錄.
3.修改dml語句重複測試:
$ cat a.txt
..
update t set v1=lpad('z',200,'z') ,v2=lpad('y',100,'y');
commit ;
...
--//執行a.txt
xxxxxxxx> @ viewsess redo%size
NAME STATISTIC# VALUE SID
---------------------------------- ---------- -------------- ----------
redo size 293 684 258
redo size for lost write detection 295 0 258
redo size for direct writes 296 0 258
redo write size count ( 4KB) 321 0 258
redo write size count ( 8KB) 322 0 258
redo write size count ( 16KB) 323 0 258
redo write size count ( 32KB) 324 0 258
redo write size count ( 64KB) 325 0 258
redo write size count ( 128KB) 326 0 258
redo write size count ( 256KB) 327 0 258
redo write size count ( 512KB) 328 0 258
redo write size count (1024KB) 329 0 258
redo write size count (inf) 330 0 258
IMU Redo allocation size 736 0 258
14 rows selected.
xxxxxxxx> @ a.txt
System altered.
CURR1
------------
1336662429
100 rows updated.
Commit complete.
PL/SQL procedure successfully completed.
CURR2
------------
1336662432
exec DBMS_LOGMNR.START_LOGMNR(STARTSCN => 1336662429 ,ENDSCN => 1336662432 ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE)
alter system dump logfile '+DATA/ORCLCDB/ONLINELOG/group_3.262.985030477' scn min 1336662429 scn max 1336662432
System altered.
xxxxxxxx> @ viewsess redo%size
NAME STATISTIC# VALUE SID
---------------------------------- ---------- -------------- ------------
redo size 293 73948 258
redo size for lost write detection 295 0 258
redo size for direct writes 296 0 258
redo write size count ( 4KB) 321 0 258
redo write size count ( 8KB) 322 0 258
redo write size count ( 16KB) 323 0 258
redo write size count ( 32KB) 324 0 258
redo write size count ( 64KB) 325 0 258
redo write size count ( 128KB) 326 0 258
redo write size count ( 256KB) 327 0 258
redo write size count ( 512KB) 328 0 258
redo write size count (1024KB) 329 0 258
redo write size count (inf) 330 0 258
IMU Redo allocation size 736 36236 258
14 rows selected.
--//日誌大小73948-648 = 73300,至少72K,比原來增加不少.
--//你可以看到如下:
REDO RECORD - Thread:1 RBA: 0x0008b3.0000002c.0134 LEN: 0x00e0 VLD: 0x01 CON_UID: 1
SCN: 0x000000004fabd870 SUBSCN: 2 11/16/2018 10:30:36
CHANGE #1 CON_ID:1 TYP:0 CLS:24 AFN:4 DBA:0x010008de OBJ:4294967295 SCN:0x000000004fabd870 SEQ:96 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 68 spc: 866 flg: 0x0022 seq: 0x0ba1 rec: 0x69
xid: 0x0004.004.00002086
ktubu redo: slt: 4 rci: 104 opc: 11.1 objn: 104890 objd: 104890 tsn: 0
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x010008de.0ba1.68
KDO Op code: LKR row dependencies Disabled
ktudb redo: siz: 68 spc: 656 flg: 0x0022 seq: 0x0ba1 rec: 0x6c
op: F xid: 0x0006.00d.00002131 uba: 0x010000c8.0b0c.01
Block cleanout record, scn: 0x000000004fabd99d ver: 0x01 opt: 0x02 bigscn: Y compact: Y spare: 00000000, entries follow...
itli: 3 flg: (opt=2 whr=1) scn: 0x000000004fabd871
Array Update of 20 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 0
ncol: 3 nnew: 2 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0041af39 hdba: 0x0041af38
itli: 1 ispac: 0 maxfr: 4863
vect = 3
col 1: [200]
7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a 7a
col 2: [100]
79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 1 ckix: 0
4.修改dml語句重複測試:
$ cat a.txt
..
update t set v1=lpad('z',200,'z') ,v2=lpad('x',100,'x');
commit ;
...
--//v1欄位修改前後一樣,v2欄位前後不同,開始是lpad('y',100,'y')後面變成lpad('x',100,'x').
xxxxxxxx> @ viewsess redo%size
NAME STATISTIC# VALUE SID
----------- ---------- -------------- ----------
redo size 293 684 258
....
xxxxxxxx> @ a.txt
System altered.
CURR1
------------
1336662656
100 rows updated.
Commit complete.
PL/SQL procedure successfully completed.
CURR2
------------
1336662659
exec DBMS_LOGMNR.START_LOGMNR(STARTSCN => 1336662656 ,ENDSCN => 1336662659 ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE)
alter system dump logfile '+DATA/ORCLCDB/ONLINELOG/group_1.265.985030477' scn min 1336662656 scn max 1336662659
System altered.
xxxxxxxx> @ viewsess redo%size
NAME STATISTIC# VALUE SID
--------- ---------- -------------- ------------
redo size 293 48340 258
....
--//日誌大小48340-648 = 47692,47K上下.
--//檢查跟蹤檔案你可以發現:
REDO RECORD - Thread:1 RBA: 0x0008b5.0000005f.0040 LEN: 0x01d0 VLD: 0x01 CON_UID: 1
SCN: 0x000000004fabda80 SUBSCN:1176 11/16/2018 10:44:53
CHANGE #1 CON_ID:1 TYP:0 CLS:18 AFN:4 DBA:0x01000087 OBJ:4294967295 SCN:0x000000004fabda80 SEQ:27 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 188 spc: 3282 flg: 0x0022 seq: 0x0b4d rec: 0x1b
xid: 0x0001.002.000020c9
ktubu redo: slt: 2 rci: 26 opc: 11.1 objn: 104890 objd: 104890 tsn: 0
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x01000087.0b4d.1a
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0041af3d hdba: 0x0041af38
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 0
col 2: [100]
79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79 79
CHANGE #2 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0041af3d OBJ:104890 SCN:0x000000004fabda80 SEQ:8 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x01000087.0b4d.1b
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0041af3d hdba: 0x0041af38
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 2 ckix: 0
ncol: 3 nnew: 1 size: 0
col 2: [100]
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
--//沒有字元z相關內容(7a 7a).
5.總結:
--//你可以發現oracle 18c在dml下修改欄位前後如果資訊不變,日誌根本沒有記錄,這樣一定程度減少日誌大小.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2220303/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- dml操作重做日誌分析
- MySQL慢日誌優化MySql優化
- 日誌挖掘-對於DML操作的挖掘
- go開發屬於自己的日誌庫-日誌庫優化Go優化
- SEO優化之淺談蜘蛛日誌優化
- Android優化系列一:日誌清理Android優化
- Oracle10g DML錯誤日誌表Oracle
- 測試DML 時產生歸檔日誌和閃回日誌的比
- Oracle歸檔日誌暴增排查優化Oracle優化
- [20190911]12c dml redo優化2.txt優化
- 使用Logminer來分析具體的DML操作日誌
- [20170412]分析重做日誌.txt
- MySQL慢日誌功能分析及優化增強MySql優化
- 網站蜘蛛日誌如何分析?對優化有用嗎?網站優化
- Monolog 優化及打造 ELK 友好的日誌格式Mono優化
- 如何利用NLog輸出結構化日誌,並在Kibana優雅分析日誌?
- Oracle10g新特性——記錄DML錯誤日誌(三)Oracle
- Oracle10g新特性——記錄DML錯誤日誌(二)Oracle
- Oracle10g新特性——記錄DML錯誤日誌(一)Oracle
- PHP實現日誌寫入log.txtPHP
- 10g release 2的新特性--記錄DML錯誤日誌
- 淺談融雲即時通訊服務「日誌優化」優化
- mysql 關於大資料量日誌表的優化過程MySql大資料優化
- TXT文字Log日誌分割工具(附工具連結)
- Redis持久化——AOF日誌Redis持久化
- Crash 日誌符號化符號
- tinylog簡化日誌
- java 日誌脫敏框架 sensitive,優雅的列印脫敏日誌Java框架
- 關於Oracle LOGMNR找不到dml操作及補充日誌相關Oracle
- Oracle10g新特性——LOG ERRORS記錄DML錯誤日誌(三)OracleError
- Oracle10g新特性——LOG ERRORS記錄DML錯誤日誌(二)OracleError
- Oracle10g新特性——LOG ERRORS 記錄DML錯誤日誌(一)OracleError
- python dictConfig封裝自定義日誌器優化(三)Python封裝優化
- [20181217]ogg抽取日誌分析.txt
- SEO優化-robots.txt解讀優化
- nginx1.10.3一鍵安裝/系統核心優化/配置檔案優化/https/日誌切割Nginx優化HTTP
- 如何優雅地記錄操作日誌?
- 如何優雅地記錄操作日誌