在平时的工作中,可能需要导入一些数据,有些表可能比较大,对于constraint的操作可能会耗费大量的时间,今天简单做了一些相关的测试,也提出了一些相关的优化方案,对结果进行比较,看看哪种才是比较合理的方法。
首先监控redo,undo的生成量也是衡量的一个标准。本次测试就简单从redo,undo,执行时间这三个方面进行总结。我准备采用shell脚本来进行监控。脚本内容见最后。
首先删除原有的表,重新创建,这个过程中也可以监控redo,undo和执行时间。数据量目前在40万左右,可以看到创建constraint的时候耗费了约70M左右的redo
然后在客户的机器上进行了简单的测试,配置要比本地好很多,测试的数据量是800万的。为了放大某些细节,总结的测试结果如下。
测试场景 | statistics(40万数据量) | statistics(800万数据量) | |||||
redo size | undo change vector size |
Elapsed_time | redo size | undo change vector size |
Elapsed_time | ||
创建 | drop table t purge; | 51444 | 23196 | 00:00:00.12 | |||
create table t nologging as select rownum id, object_id,object_name,object_type from all_objects ; |
103456 | 22476 | 00:00:01.80 | 74348 | 15668 | 00:00:03.81 | |
insert data | |||||||
alter table t logging; | 2648 | 728 | 00:00:00.01 | ||||
alter table t add constraint t_pk primary key(id); | 7170644 | 25044 | 00:00:02.67 | 152900744 | 21640 | 00:00:17.73 | |
Object parallel | alter table t disable constraint t_pk; | 17984 | 3208 | 00:00:00.06 | 21708 | 8536 | 00:00:00.06 |
alter table t parallel 4; | 2204 | 632 | 00:00:00.02 | 2652 | 724 | 00:00:00.01 | |
alter table t enable constraint t_pk; | 7128596 | 10824 | 00:00:03.20 | 152904056 | 20272 | 00:00:17.33 | |
session parallel | alter table t disable constraint t_pk; | 16840 | 3208 | 00:00:00.08 | 21472 | 8540 | 00:00:00.06 |
alter session enable parallel ddl; | |||||||
alter table t enable constraint t_pk; | 7128600 | 10820 | 00:00:02.58 | 152905636 | 20748 | 00:00:17.90 | |
table nologging | alter table t nologging; | 2144 | 632 | 00:00:00.05 | 2656 | 728 | 00:00:00.01 |
alter table t disable constraint t_pk; | 16848 | 3208 | 00:00:00.10 | 21464 | 8540 | 00:00:00.06 | |
alter table t enable constraint t_pk; | 7130000 | 11260 | 00:00:02.73 | 152912236 | 20172 | 00:00:16.74 | |
先enable novalidate constraint, 然后enable constraint |
alter table t disable constraint t_pk; | 16744 | 3208 | 00:00:00.13 | 21516 | 8496 | 00:00:00.07 |
alter table t enable novalidate constraint t_pk; | 7128132 | 10544 | 00:00:02.62 | 152895024 | 19920 | 00:00:17.02 | |
alter table t enable validate constraint t_pk; | 4136 | 1304 | 00:00:00.13 | 5088 | 1600 | 00:00:01.23 | |
尝试重建constraint 采用Nologging |
alter table t drop constraint t_pk; | ||||||
alter table t add constraint t_pk primary key(id) using index(create unique index t_pk on t(id)) nologging; | 7132120 | 11828 | 00:00:02.25 | 152915992 | 21384 | 00:00:17.30 | |
index和constraint分离 | alter table t disable constraint t_pk; | 16864 | 3208 | 00:00:00.16 | 21568 | 8540 | 00:00:00.06 |
create unique index t_pk on t(id) nologging; | 64192 | 10384 | 00:00:02.21 | 304876 | 19208 | 00:00:17.08 | |
alter table t enable constraint t_pk; | 3576 | 1152 | 00:00:01.94 | 4220 | 1388 | 00:00:00.60 | |
alter index t_pk logging; | 2596 | 772 | 00:00:00.09 | 3124 | 916 | 00:00:00.01 |
各种多种场景最终都被否定了,最后可以考虑一下把constraint和index分离,加上parallel来处理。
采用这种方式,响应时间和redo的生成量都大幅度降低,从原来的17秒减少到了8秒
index和constraint分离,parallel | alter table t disable constraint t_pk; | 4160 | 1396 | 00:00:00.02 | |||
drop index t_pk; | 18468 | 7468 | 00:00:00.05 | ||||
create unique index t_pk on t(id) nologging parallel 4; | 1039232 | 142652 | 00:00:08.29 | ||||
alter table t enable constraint t_pk; | 4220 | 1388 | 00:00:00.58 | ||||
alter index t_pk logging; | 3132 | 920 | 00:00:00.01 |
-------------------------------------------------------------------------
sqlplus -s n1/n1 set feedback off
set termout off
set linesize 100
set pages 50
col name format a30
variable redo number
spool test_before_tmp.log
@redo_stat.sql
@undo_stat.sql
spool off;
set timing on
$1
set timing off
--alter session enable parallel ddl;
spool test_after_tmp.log
@redo_stat.sql
@undo_stat.sql
spool off;
EOF
echo '##################################################'
echo 'redo, undo stats generated as below'
echo '##################################################'
sdiff test_before_tmp.log test_after_tmp.log|grep "|"|awk -F"|" '{print $2}'
exit
引用到的sql脚本内容如下:
redo_stat.sql
select a.name ,b.value from v$statname a,v$mystat b
where a.STATISTIC#=b.STATISTIC#
and a.name like '%redo%'
order by a.name;
undo_stat.sql
select a.name ,b.value from v$statname a,v$mystat b
where a.STATISTIC#=b.STATISTIC#
and a.name like '%undo%'
order by a.name;