通过10046 trace观察变更字段类型所需要时间及对表的影响
需求:变更字段varchar to cob:
步骤一:rename 原列
alter table tableA rename column description to description_bak;
步骤二:在表中增加该列,并指定改列类型为clob
alter table tableA add description clob;
步骤三:对此列包含数据的需要包数据从步骤一重命名列中拷出(对于此列没有数据的此步骤省略)
update tableA set description=description_bak;
commit;
步骤四:删除步骤一中的备份列
alter table tableA drop column description_bak;
测试流程:
session 1 :
conn userA/userA
SQL> select pid, spid from v$process p, v$session s where p.addr = s.paddr and s.sid=(select sid from v$mystat where rownum=1);
PID SPID
---------- ------------------------
163 16715
session 2:
对session1进行10046 trace
conn / as sysdba
sys@MAA> oradebug setospid 16715
sys@MAA> oradebug unlimit
sys@MAA> oradebug event 10046 trace name context forever, level 12
session 1 :
SQL> alter table temp_20220425 rename column SOFTVERLIST to SOFTVERLIST_bak;
alter table temp_20220425 add SOFTVERLIST_1 clob;
update temp_20220425 set SOFTVERLIST_1=SOFTVERLIST_bak;
commit;
alter table temp_20220425 rename column SOFTVERLIST_1 to SOFTVERLIST;
Table altered.
SQL> SQL>
Table altered.
SQL> SQL>
5766208 rows updated.
SQL>
Commit complete.
SQL>
Table altered.
SQL> SQL> SQL> SQL>
SQL>
SQL>
SQL> desc temp_20220425
Name Null? Type
----------------------------------------- -------- ----------------------------
EATTRIBUTE9 VARCHAR2(240)
EATTRIBUTE10 VARCHAR2(240)
SOFTVERLISTNEW CLOB
SOFTVERLIST CLOB
SQL>
session 2:
conn / as sysdba
sys@MAA> oradebug event 10046 trace name context off
sys@MAA> oradebug tracefile_name
**10046 trace**:
tkprof mesuat_ora_16715.trc ./mesuat_ora_16715.tkf
cat mesuat_ora_16715.tkf
********************************************************************************
SQL ID: dcpf5nqs0v1yj Plan Hash: 1766460942
update temp_20220425 set SOFTVERLIST_1=SOFTVERLIST_bak
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 5 0 0
Execute 1 193.10 261.04 774661 9952290 42455567 5766208
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 193.10 261.04 774661 9952295 42455567 5766208
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 UPDATE temp_20220425 (cr=9952529 pr=774662 pw=8 time=261090808 us)
5766208 5766208 5766208 TABLE ACCESS FULL temp_20220425 (cr=774462 pr=774423 pw=0 time=57795783 us cost=210282 size=22825302500 card=5700625)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 6082 0.13 42.95
Disk file operations I/O 2 0.00 0.00
log buffer space 645 0.25 23.67
db file sequential read 239 0.02 0.78
log file switch (private strand flush incomplete)
13 0.22 0.58
log file sync 5 0.04 0.17
latch: redo allocation 8 0.00 0.00
latch: object queue header operation 28 0.00 0.00
latch free 3 0.00 0.00
latch: cache buffers lru chain 6 0.00 0.00
log file switch completion 6 0.04 0.19
latch: checkpoint queue latch 1 0.00 0.00
direct path write 8 0.01 0.02
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
********************************************************************************
SQL ID: 3r0knacktrsd5 Plan Hash: 0
LOCK TABLE "temp_20220425" IN EXCLUSIVE MODE NOWAIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 1 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 1 0 0
Misses in library cache during parse: 3
Optimizer mode: ALL_ROWS
Parsing user id: 88 (recursive depth: 1)
************************************
测试结果:
- 1.在alter 期间会加execlude lock,瞬间完成。
- 2.在update clob列,由数据量多少决定耗时时长。