대용량 테이블에서 컬럼 삭제 시 redo 사용량, 소요시간 등 테스트
- drop column
- CTAS
- unused column
DROP COLUMN 테스트
-- 7400만건 테이블
SQL> select count(*) from sk.tab ;
COUNT(*)
----------
74361856
Elapsed: 00:00:21.23
-- 테이블 사이즈 11GB
SQL> SELECT segment_name, bytes/1024/1024 MB
FROM dba_segments
WHERE segment_name = 'TAB'
;
SEGMENT_NAME MB
-------------------------------------------------------- ----------
TAB 11326
Elapsed: 00:00:00.08
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
;
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 10
redo size 968
Elapsed: 00:00:00.01
-- drop column
SQL> ALTER TABLE sk.tab DROP COLUMN object_name ;
Table altered.
Elapsed: 00:22:05.27
-- redo 사용량은 18GB
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
;
NAME VALUE
---------------------------------------------------------------- ------------------
redo entries 75452630
redo size 20021659448
Elapsed: 00:00:00.01
--# drop columns 중 모니터링
-- 언두 사용량 모니터링
####################################
Sat Jan 15 10:44:00 KST 2022
SID SERIAL# USERNAME SEGMENT_NAME Extent Count KB USED_UREC PROGRAM
---------- ---------- ---------------------------------------- ------------------------------ ------------ ---------- ---------- ------------------------------------------------
47 59270 SYS _SYSSMU10_930580995$ 353 8279392 74361856 sqlplus@svr1 (TNS V1-V3)
####################################
Sat Jan 15 10:44:04 KST 2022
no rows selected
-- 테이블 LOCK 발생
####################################
Sat Jan 15 10:43:57 KST 2022
INST_ID SESSION_ID SERIAL# ORACLE_USERNAME OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE MODULE STATE STATUS OS_USER_NAME MACHINE SQL_ID EVENT LMO LAST_CALL_ET HOLDER
---------- ---------- ---------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------------------------------- ------------------- -------- -------------------- ---------------------------------------------------------------- ------------- ---------------------------------------------------------------- --- ------------ ----------
1 47 59270 SYS WRI$_OPTSTAT_HISTHEA TABLE sqlplus@svr1 (TNS V1-V3) WAITING ACTIVE oracle svr1 58rmg9b74y4pp free buffer waits RX 1320 9
D_HISTORY
1 47 59270 SYS WRI$_OPTSTAT_HISTGRM TABLE sqlplus@svr1 (TNS V1-V3) WAITING ACTIVE oracle svr1 58rmg9b74y4pp free buffer waits RX 1320 9
_HISTORY
1 47 59270 SYS TAB TABLE sqlplus@svr1 (TNS V1-V3) WAITING ACTIVE oracle svr1 58rmg9b74y4pp free buffer waits X 1320 9
CTAS 테스트
삭제 컬럼 제외 후 신규 테이블 생성 시 테스트
SQL> drop table sk.tab purge
;
Table dropped.
Elapsed: 00:00:00.29
-- nologging 테이블 생성
SQL> create table sk.tab as select * from sk.tab1 where 1= 0 ;
Table created.
Elapsed: 00:00:00.10
SQL> alter table sk.tab nologging ;
Table altered.
Elapsed: 00:00:00.02
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
;
NAME VALUE
---------------------------------------------------------------- ------------------
redo entries 76243994
redo size 26482252916
Elapsed: 00:00:00.00
-- 동일한 7400만건 insert
SQL> insert /*+ append */ into sk.tab select * from sk.tab1 ;
74361856 rows created.
Elapsed: 00:01:27.10
SQL> commit ;
Commit complete.
Elapsed: 00:00:00.06
SQL> select count(*) from sk.tab ;
COUNT(*)
----------
74361856
Elapsed: 00:00:21.86
-- redo 사용량은 918KB
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
;
NAME VALUE
---------------------------------------------------------------- ------------------
redo entries 76257073
redo size 26483193108
Elapsed: 00:00:00.01
컬럼 unused 후 실제 drop은 나중에 진행 테스트
unused 는 금방되지만 실제 삭제시에는 drop column과 동일
-- unused column
SQL> ALTER TABLE sk.tab SET UNUSED(object_name) ;
Table altered.
Elapsed: 00:00:00.03
SQL> desc sk.tab
Name Null? Type
--------------------------------------------------------------------------------------
OWNER VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(18)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
APPLICATION VARCHAR2(1)
DEFAULT_COLLATION VARCHAR2(100)
DUPLICATED VARCHAR2(1)
SHARDED VARCHAR2(1)
CREATED_APPID NUMBER
CREATED_VSNID NUMBER
MODIFIED_APPID NUMBER
MODIFIED_VSNID NUMBER
-- unused column 조회, 실패
SQL> select object_name from sk.tab ;
select object_name from sk.tab
*
ERROR at line 1:
ORA-00904: "OBJECT_NAME": invalid identifier
Elapsed: 00:00:00.01
SQL>
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
;
NAME VALUE
------------------------------ ------------------
redo entries 76261032
redo size 26483858028
Elapsed: 00:00:00.00
SQL> SELECT * FROM DBA_UNUSED_COL_TABS ;
OWNER TABLE_NAME COUNT
------------------------------------------------------- -------------------------------------------------- ----------
SK TAB 1
Elapsed: 00:00:00.16
-- drop unused column
SQL> ALTER TABLE sk.tab DROP UNUSED COLUMNS ;
Table altered.
Elapsed: 00:10:54.15
SQL>
-- redo 사용량은 18GB
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
;
NAME VALUE
------------------------------ ------------------
redo entries 151715680
redo size 46505718532
Elapsed: 00:00:00.01
SQL>
댓글
댓글 쓰기