通过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列，由数据量多少决定耗时时长。