对oracle大表字段进行变更的方法及影响
测试环境:oracle 11.2.0.4
添加字段
a.当添加允许null值的字段时
1. 避免使用alter table aa add column_1 varchar2(2) default 'Y';
原理：
这种加默认值方式，（内部执行时由ddl+dml组成）。会刷新存量数据,未完成返回前，表在library cache中invalidation。
当其他会话对此表进dml操作需要硬解释，但发现表对象在library cache中被锁住，于是被阻塞，library cache lock [Concurrency]等待事件。
表数量量越大，此操作需要时间越长,可能还会导致undo空间暴涨。因此特别是大表，生产环境，业务产生期间就应该禁止此操作。
2. 应采取两段式：
alter table aa add column_1 varchar2(2); --add不带缺省值。获取一个row exclusive锁，ddl操作短暂锁住表，毫秒级，修改的是数据字典。
alter table aa modify column_1 varchar2(2) default 'Y'; --毫秒级，不会更新之前的数据，该默认值是存储于数据字典表中的。b.当添加not null字段时
alter table aa add column_1 varchar2(2) default 'Y' not null; ---获取一个row exclusive锁，毫秒级，修改的是数据字典。该默认值是存储于数据字典表中的。
删除字段
1. 大表不要采取直接drop column
select不受影响。
insert 语句在drop column完成之前无法执行，等待事件为enq:TM-contention。
在执行drop column的过程中，需要修改每一行数据，运行时间往往特别长，这会消耗大量的undo表空间，如果表特别大，操作时间足够长，undo表空间会全部耗尽
2. 考虑rename 列或set unused，以后再找维护窗口删除列.
alter table tableA set unused column QUERY_HIS_DAY_old; （wow 列在物理上仍然存在，但是相对于用户来说，该列就像真的被删除了一样。）
alter table tableA drop unused column QUERY_HIS_DAY_old ；真正的物理删除了。( 不忙的时候经行)
alter table tableA drop unused column QUERY_HIS_DAY_old checkpoint 2000; 节省了undo 资源的使用。
创建模拟环境
创建测试表
create table insert_test(
id number,
address varchar2(20),
QRcode varchar2(20),
password varchar2(30)
);创建一个自用序列
create sequence wyl_seq
start with 1
increment by 1
nomaxvalue
nominvalue
nocycle
nocache;插入12000000行数据，47分钟。。。。。
begin
for i in 1 .. 12000000 loop
insert into insert_test values(WYL_SEQ.NEXTVAL,'www.baidu.com',dbms_random.string('x', 20),dbms_random.string('p',30));
end loop;
commit;
end;
/
模拟持续有业务插入或查询数据
declare v_a int;
BEGIN
FOR i IN 1 .. 1200000000
LOOP
IF MOD (i, 2) = 0
THEN
INSERT INTO userA.insert_test(id,address,QRcode,password)
VALUES (userA.WYL_SEQ.NEXTVAL,
'www.baidu.com',
DBMS_RANDOM.string ('x', 20),
DBMS_RANDOM.string ('p', 30));
ELSE
SELECT count(*) into v_a
FROM userA.insert_test SAMPLE (1)
WHERE ROWNUM = 1;
COMMIT;
END IF;
DBMS_LOCK.sleep (0.1);
END LOOP;
END;
/现在进行加字段并设置默认值测试.
主要有两种方法（一是直接添加并设置默认值，二是先添加，然后modify默认值）
我们先测试一下第二种方法
Adding Table Columns
If a new column is added to a table, the column is initially NULL unless you specify the DEFAULT clause.
When you specify a default value, the database immediately updates each row with the default value.
Note that this can take some time, and that during the update, there is an exclusive DML lock on the table.
For some types of tables (for example, tables without LOB columns),if you specify both a NOT NULL constraint and a default value,
the database can optimize the column add operation and greatly reduce the amount of time that the table is locked for DML.
You can add a column with a NOT NULL constraint only if the table does not contain any rows, or you specify a default value.
说的很明白了，如果新增一个含有默认值的字段，那么会立即更新每一行，在更新过程中，会有一个EXCLUSIVE级别的锁在该表上。
如果指定NOT NULL和默认值，则会进行优化，降低阻止DML操作的时间。
如果增加一个仅有NOT NULL的约束字段，那么需要表不能包含任何记录，否则就需要必须指定一个默认值，这也好理解，如果执行之前有记录，又要求NOT NULL，那么之前的记录字段默认值是什么就需要指定才行。
>>>10046 trace
场景 1：当添加允许null值的字段时，测试"先添加，然后modify默认值"
测试"先添加，然后modify默认值",
alter table insert_test add col2 varchar2(10);
alter table insert_test modify col2 default '0';
获取到EXCLUSIVE MODE锁后，瞬间完成，ddl操作，从10046trace也可以看到，两步操作都没有对rows有操作记录。
Session 1
set timing on;
SQL> select count(*) from insert_test;
COUNT(*)
----------
12000000
Elapsed: 00:00:02.01
SQL>
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
---------- ------------------------
25 16879
SQL>
session 2
[vivoadmin@dgvxl6632 ~]$ sudo su - oracle
Last login: Fri Jul 22 17:49:55 CST 2022 on pts/1
[oracle@dgvxl6632 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 22 17:50:37 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> oradebug setospid 16879
Oracle pid: 25, Unix process pid: 16879, image: oracle@dgvxl6632 (TNS V1-V3)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.Session 1
SQL> alter table insert_test add col varchar2(10);
Table altered.
Elapsed: 00:00:00.04
SQL> alter table insert_test modify cola default '0';
Table altered.
Elapsed: 00:00:00.01session 2
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL> oradebug tracefile_name
/data01/u01/app/oracle/diag/rdbms/orcls1/orcl/trace/orcl_ora_16879.trc
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dgvxl6632 ~]$ tkprof /data01/u01/app/oracle/diag/rdbms/orcls1/orcl/trace/orcl_ora_16879.trc orcl_ora_16879.tkf
TKPROF: Release 11.2.0.4.0 - Development on Fri Jul 22 17:54:13 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@dgvxl6632 ~]$ ls -ltrh orcl_ora_16879.tkf
-rw-r--r-- 1 oracle oinstall 100K Jul 22 17:54 orcl_ora_16879.tkf
[oracle@dgvxl6632 ~]$ 查看trace文件,抽取其中与表相关部分
获得的是一个ROW EXCLUSIVE模式锁
********************************************************************************
SQL ID: ctj96yuqf2w1q Plan Hash: 0
LOCK TABLE "INSERT_TEST" IN EXCLUSIVE MODE NOWAIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 0
Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: 101 (recursive depth: 1)
********************************************************************************
增加字段
********************************************************************************
SQL ID: fytqn3177zjkf Plan Hash: 0
alter table insert_test a
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 205 2 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 207 2 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 101
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message from client 3 84.95 90.65
SQL*Net break/reset to client 1 0.00 0.00
SQL*Net message to client 2 0.00 0.00
log file sync 1 0.00 0.00
********************************************************************************
********************************************************************************
SQL ID: 99z4jw5aravpd Plan Hash: 0
变更字段默认值
alter table insert_test m
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 2 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 2 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 101
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
********************************************************************************场景 2：当添加允许null值的字段时，测试"直接添加并设置默认值"
测试"直接添加并设置默认值"
alter table insert_test add col25 varchar2(10) default '25';
从10046 trace看到，被分成了两步，一步是add column,第二步是update "INSERT_TEST" set "COL25"='25',
时间花在update这一步，数据量越大，花时间越长。
所有dml操作受影响，产生library cache lock [Concurrency]等待事件。
Session 1
SQL> select count(*) from insert_test;
COUNT(*)
----------
12000000
Elapsed: 00:00:02.01
SQL>
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
---------- ------------------------
34 27086
Elapsed: 00:00:00.01
session 2
[vivoadmin@dgvxl6632 ~]$ sudo su - oracle
Last login: Fri Jul 22 17:49:55 CST 2022 on pts/1
[oracle@dgvxl6632 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 22 17:50:37 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> oradebug setospid 27086
Oracle pid: 25, Unix process pid: 16879, image: oracle@dgvxl6632 (TNS V1-V3)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.Session 1
SQL> alter table insert_test add col25 varchar2(10) default '25';
Table altered.
Elapsed: 00:01:09.76
SQL> session 2
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL> oradebug tracefile_name
/data01/u01/app/oracle/diag/rdbms/orcls1/orcl/trace/orcl_ora_27086.trc
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dgvxl6632 ~]$ tkprof /data01/u01/app/oracle/diag/rdbms/orcls1/orcl/trace/orcl_ora_27086.trc orcl_ora_27086.tkf
TKPROF: Release 11.2.0.4.0 - Development on Fri Jul 22 17:54:13 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
[oracle@dgvxl6632 ~]$ ls -ltrh orcl_ora_27086.tkf
-rw-r--r-- 1 oracle oinstall 63K Jul 25 10:52 orcl_ora_27086.tkf查看trace文件,抽取其中与表相关部分
获得的是一个ROW EXCLUSIVE模式锁
********************************************************************************
SQL ID: ctj96yuqf2w1q Plan Hash: 0
LOCK TABLE "INSERT_TEST" IN EXCLUSIVE MODE NOWAIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 101 (recursive depth: 1)
********************************************************************************
SQL ID: df3bam3zu2fn7 Plan Hash: 0增加字段
alter table insert_test a
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.06 0.06 0 356168 2 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.06 0.06 0 356170 2 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 101
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message from client 1 83.60 83.60
SQL*Net message to client 1 0.00 0.00
********************************************************************************
变更字段默认值
********************************************************************************
SQL ID: 7d3v687uxmvdn Plan Hash: 1736109780
update "INSERT_TEST" set "COL25"='25'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 63.41 69.67 128253 356039 12964310 12000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 63.41 69.67 128253 356039 12964310 12000000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 101 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 UPDATE INSERT_TEST (cr=356042 pr=128253 pw=0 time=69672806 us)
12000000 12000000 12000000 TABLE ACCESS FULL INSERT_TEST (cr=165195 pr=127566 pw=0 time=8278596 us cost=34823 size=108000000 card=12000000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 1032 0.03 4.48
Disk file operations I/O 2 0.00 0.00
db file sequential read 687 0.02 1.00
log file switch completion 8 0.02 0.09
log buffer space 26 0.10 0.74
latch: checkpoint queue latch 4 0.00 0.00
log file switch (checkpoint incomplete) 4 0.07 0.12
latch: object queue header operation 1 0.00 0.00
********************************************************************************期间-查看当前数据库阻塞会话,被阻塞，产生等待事件library cache lock
LEVEL USERNAME INST_ID OSUSER SID SERIAL# TYPE LOCKWAIT MACHINE PROGRAM LOGON_TIME SQL_ID SQL_EXEC_ID SQL_EXEC_START STATUS EVENT WAIT_CLASS SECONDS_IN_WAIT PREV_SQL_ID PREV_EXEC_ID PREV_EXEC_START BLOCKING_SESSION BLOCKING_SESSION_STATUS KILL_SESSION_SCRIPT
----- -------------------- ---------- ---------- ---------- ---------- ---------- ---------------- ------------------------- ------------------------------ -------------------- -------------------- ----------- -------------- ---------- ---------------------------------------- -------------------- --------------- ------------- ------------ --------------- ---------------- ----------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 userA 1 oracle 1139 7061 USER dgvxl6632 sqlplus@dgvxl6632 (TNS V1-V3) 2022-07-25 15:38:04 0dg6qtp9qbb45 16777216 25-7月-22 ACTIVE log buffer space Configuration 3 11rr7brbn8z19 16777216 25-7月-22 NOT IN WAIT alter system kill session '1139,7061,@1' immediate;
2 userA 1 oracle 1142 49231 USER dgvxl6632 sqlplus@dgvxl6632 (TNS V1-V3) 2022-07-25 16:02:18 033rfyt7gzupc ACTIVE library cache lock Concurrency 4 dyk4dprp70d74 16777227 25-7月-22 1139 VALID alter system kill session '1142,49231,@1' immediate;
2 userA 1 oracle 1702 18537 USER dgvxl6632 sqlplus@dgvxl6632 (TNS V1-V3) 2022-07-25 11:38:53 dw8nnh7jnt93d ACTIVE library cache lock Concurrency 66 a5ucgf0gf75ky 16825790 25-7月-22 1139 VALID alter system kill session '1702,18537,@1' immediate;
已选择 3 行。场景 3：当添加not null字段时
alter table insert_test add column_1 varchar2(2) default 'Y' not null;
瞬间完成，ddl操作，从10046trace也可以看到，两步操作都没有对rows有操作记录。
********************************************************************************
SQL ID: ctj96yuqf2w1q Plan Hash: 0
LOCK TABLE "INSERT_TEST" IN EXCLUSIVE MODE NOWAIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 101 (recursive depth: 1)
********************************************************************************
SQL ID: 4qaa62xwxugw3 Plan Hash: 0
alter table insert_test a
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 251 25 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 253 25 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 101
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
********************************************************************************参考：
https://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247483684&idx=1&sn=c1b8f87b14ddbc33b89e1ef6891867d1&scene=21#wechat_redirect
https://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247484420&idx=1&sn=a104389cdb0d73a18f131520cc16c921&chksm=ea8141f7ddf6c8e121431ea669e238a3db7250c18db34a7c15a3e25b440ec93073ee967a19a3&scene=21#wechat_redirect关于latch:library cache lock等待事件：
This event controls the concurrency between clients of the library cache.
It acquires a lock on the object handle so that either: One client can prevent other clients from accessing the same object The client can maintain a dependency for a long time (for example, no other client can change the object)
This lock is also obtained to locate an object in the library cache.
Oracle needs Library Cache Lock and Library Cache Pin for the compilation and parsing of packages, procedures, functions, and other objects.
This is to ensure that no one is using the object during compilation/parsing since changes are being made to the definition of the object.
>> Problem This wait can occur when DDL is being executed on an object or one of its dependant objects. This causes a database object invalidation.
>>Typically these are the object maintenance operations such as: ALTER, GRANT, and REVOKE.
>>After object invalidation, Oracle tries to recompile the object at the time of the next access attempt.
>>This may be a problem in a case where other sessions have pinned the object in the library cache because they are actively accessing the object.
This is more likely to occur with more active users and with more complex dependencies (e.g. many cross-dependent packages or package bodies).
In some cases, waiting for object recompilation may take hours, blocking all the sessions trying to access it.
>>Typical Scenarios A DML operation that is hanging because the table which is accessed is currently undergoing changes (ALTER TABLE).
>>This may take quite a long time depending on the size of the table and the type of the modification (e.g. ALTER TABLE MODIFY col1 CHAR(200) on millions of records).
The compilation of package will hang on Library Cache Lock and Library Cache Pin if some users are executing any procedure or function defined in the same package.
Sessions trying to access objects that have recently been modified in some way may have to wait while objects are recompiled.
>>Solution If a session is hanging on this wait, it is possible to find its blocker and kill it.
Refer to MetaLink Note: 122793.1 for details on how to find the blocker.
For a long-term fix, look at any processes or applications that might cause object invalidations and limit or time their execution for minimal impact.
Be very careful with altering, granting or revoking privileges on database objects that frequently used PL/SQL is dependent on.
-----------------------------------
