[20171113]修改表結構刪除列相關問題4.txt
[20171113]修改表結構刪除列相關問題4.txt
--//連續寫了3篇修改表結構刪除列的相關問題,連結如下:
http://blog.itpub.net/267265/viewspace-2147158/
http://blog.itpub.net/267265/viewspace-2147163/
http://blog.itpub.net/267265/viewspace-2147196/
--//從redo記錄日誌內容看,日誌僅僅記錄偏移位置 (piece relative column number),這樣日誌量還是相對較小,而且與刪除欄位的位置無關.
--//參考連結:
http://blog.itpub.net/267265/viewspace-2147163/
--//摘取其中1段,注意看下劃線部分:
REDO RECORD - Thread:1 RBA: 0x000317.0000009c.0084 LEN: 0x017c VLD: 0x01
SCN: 0x0003.176a162e SUBSCN: 1 11/13/2017 10:13:57
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0003.176a1592 SEQ:1 OP:5.2 ENC:0 RBL:0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ktudh redo: slt: 0x0009 sqn: 0x0000532a flg: 0x0012 siz: 132 fbi: 0
uba: 0x00c1cd3c.10c6.10 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:36 AFN:3 DBA:0x00c1cd3c OBJ:4294967295 SCN:0x0003.176a1591 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 132 spc: 3916 flg: 0x0012 seq: 0x10c6 rec: 0x10
xid: 0x000a.009.0000532a
ktubl redo: slt: 9 rci: 0 opc: 11.1 [objn: 90617 objd: 90617 tsn: 7]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c1cd3c.10c6.0f
prev ctl max cmt scn: 0x0003.176a1392 prev tx cmt scn: 0x0003.176a1396
txn start scn: 0xffff.ffffffff logon user: 83 prev brb: 12700985 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: DSC row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01800081 hdba: 0x01800080
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0x0)
piece relative column number: 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CHANGE #3 TYP:2 CLS:1 AFN:6 DBA:0x01800081 OBJ:90617 SCN:0x0003.176a14f9 SEQ:5 OP:11.14 ENC:0 RBL:0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x000a.009.0000532a uba: 0x00c1cd3c.10c6.10
Block cleanout record, scn: 0x0003.176a162e ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0003.176a14f9
KDO Op code: DSC row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01800081 hdba: 0x01800080
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0x0)
piece relative column number: 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//還是透過了例子來說明:
0.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//建立測試指令碼:
$ cat test1.sql
column name format a54
alter table t1 SET UNUSED (v1,v2,v3);
@ &r/viewredo
set timing on
ALTER TABLE t1 DROP UNUSED COLUMNS ;
set timing off
@ &r/viewredo
1.測試1:
--//drop table t1 purge;
create table t1 (id number,v0 varchar2(50),v1 varchar2(10),v2 varchar2(10),v3 varchar2(10));
insert into t1 select rownum,lpad('a',50,'a'),lpad('b',10,'b'),lpad('c',10,'c'),lpad('d',10,'d') from xmltable('1 to 1000000');
commit;
--//執行test1.sql:
SCOTT@book> @ test1.sql
Table altered.
NAME STATISTIC# VALUE
------------------------------------------------------ ---------- ----------
user commits 6 1
redo size 194 8124
redo wastage 199 0
data blocks consistent reads - undo records applied 326 0
Table altered.
Elapsed: 00:00:41.41
NAME STATISTIC# VALUE
------------------------------------------------------ ---------- ----------
user commits 6 2
redo size 194 687554932
redo wastage 199 0
data blocks consistent reads - undo records applied 326 0
--//redo=687554932-8124=687546808
2.測試2:
--//drop table t1 purge;
create table t1 (id number,v1 varchar2(10),v0 varchar2(50),v2 varchar2(10),v3 varchar2(10));
insert into t1 select rownum,lpad('a',10,'a'),lpad('b',50,'b'),lpad('c',10,'c'),lpad('d',10,'d') from xmltable('1 to 1000000');
commit;
--//執行test1.sql:
SCOTT@book> @ test1.sql
Table altered.
NAME STATISTIC# VALUE
------------------------------------------------------ ---------- ----------
user commits 6 1
redo size 194 8736
redo wastage 199 0
data blocks consistent reads - undo records applied 326 0
Table altered.
Elapsed: 00:00:40.31
NAME STATISTIC# VALUE
------------------------------------------------------ ---------- ----------
user commits 6 2
redo size 194 687540264
redo wastage 199 0
data blocks consistent reads - undo records applied 326 0
--//redo=687540264-8736=687531528.
3.測試3:
--//drop table t1 purge;
create table t1 (id number,v1 varchar2(10),v2 varchar2(10),v0 varchar2(50),v3 varchar2(10));
insert into t1 select rownum,lpad('a',10,'a'),lpad('b',10,'b'),lpad('c',50,'c'),lpad('d',10,'d') from xmltable('1 to 1000000');
commit;
--//執行test1.sql:
SCOTT@book> @ test1.sql
Table altered.
NAME STATISTIC# VALUE
------------------------------------------------------ ---------- ----------
user commits 6 1
redo size 194 8736
redo wastage 199 0
data blocks consistent reads - undo records applied 326 0
Table altered.
Elapsed: 00:00:41.57
NAME STATISTIC# VALUE
------------------------------------------------------ ---------- ----------
user commits 6 2
redo size 194 687620720
redo wastage 199 0
data blocks consistent reads - undo records applied 326 0
--//redo = 687620720-8736=687611984
4.測試4:
--//drop table t1 purge;
create table t1 (id number,v1 varchar2(10),v2 varchar2(10),v3 varchar2(10),v0 varchar2(50));
insert into t1 select rownum,lpad('a',10,'a'),lpad('b',10,'b'),lpad('c',10,'c'),lpad('d',50,'d') from xmltable('1 to 1000000');
commit;
--//執行test1.sql:
SCOTT@book> @ test1.sql
Table altered.
NAME STATISTIC# VALUE
------------------------------------------------------ ---------- ----------
user commits 6 1
redo size 194 8852
redo wastage 199 0
data blocks consistent reads - undo records applied 326 0
Table altered.
Elapsed: 00:00:40.99
NAME STATISTIC# VALUE
------------------------------------------------------ ---------- ----------
user commits 6 2
redo size 194 687650544
redo wastage 199 0
data blocks consistent reads - undo records applied 326 0
--//redo = 687650544 - 8852=687641692
5.分析:
--//刪除v1,v2,v2列. v0列在測試中處在表的位置依次變化.而oracle的改變向量每次操作一個欄位,因為日誌中僅僅piece relative column number.
--//大小相對穩定,這樣操作日誌量大小不存在變化,而且相差很小.
--------------------------
v0出現位置 redo量 時間
---------------------------
2 687546808 41.41
3 687531528 40.31
4 687611984 41.57
5 687641692 40.99
--------------------------
--// 可以看出redo與時間消耗基本差不多.
6.總結:
--1.刪除列不存在相關性.不是越靠前或者後面記錄很大,而導致事務發生變化.
--2.感覺採用ctas或者採用線上表重定義也許更好.業務停頓可能更小.事務量還是偏大.
7.附上viewredo.sql指令碼:
SELECT b.NAME, a.statistic#, a.VALUE
FROM v$mystat a, v$statname b
WHERE b.NAME IN ('redo size', 'redo wastage','user commits','data blocks consistent reads - undo records applied') AND a.statistic# = b.statistic#;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2147216/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20171113]修改表結構刪除列相關問題.txt
- [20171113]修改表結構刪除列相關問題2.txt
- [20171113]修改表結構刪除列相關問題3.txt
- oracle修改表增加列刪除列修改列Oracle
- iptables刪除命令中的相關問題
- Oracle 增加 修改 刪除 列Oracle
- 關於修改分割槽表的問題總結
- 常見問題--oracle10g修改表結構Oracle
- [MYSQL][1]建立,修改,刪除表MySql
- 關於javascript中陣列元素刪除問題的討論 (轉)JavaScript陣列
- mysql相關問題總結MySql
- Laravel佇列相關問題記錄Laravel佇列
- 線性表的相關操作-初始化、增添、刪除
- 在Oracle 9i中修改表的結構的相關sql語句OracleSQL
- iOS上架4.3相關問題修改建議iOS
- 臨時表空間資料刪除問題
- ORACLE表空間的建立修改刪除Oracle
- 【體系結構】與Checkpoint相關的問題解決思路
- 資料結構C語言|佇列相關資料結構C語言佇列
- 刪除 AP 發票相關指令碼指令碼
- 結構體相關結構體
- Oracle表 列欄位的增加、刪除、修改以及重新命名操作sqlOracleSQL
- SQL SERVER 不能修改表結構、列型別 解決方法SQLServer型別
- Hadoop/Spark相關面試問題總結HadoopSpark面試
- HTTPS總結+相關面試問題解答HTTP面試
- 關於刪除資料的快慢問題的分析
- 【專案問題總結】5:樹形結構節點的級聯刪除邏輯
- 演算法問題總結-連結串列相關演算法
- 表管理之二:表資料的插入修改與刪除
- Docker 相關問題Docker
- django相關問題Django
- electron相關問題
- 【實戰】使用“基表修改法”恢復被“set unused”方式誤刪除的列
- 在迴圈陣列時使用splice()方法刪除陣列遇到的問題陣列
- 使用全表掃描快取大表的相關問題快取
- 資料結構——RMQ(ST表)問題資料結構MQ
- [文件教程]解決SAE下本地除錯相關問題除錯
- sql 多表關聯刪除表資料SQL