다른 명령
언두 테스트
SELECT SUBSTR(SEGMENT_NAME, 1, 20) TB , BYTES/1024/1024 "MB", BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ( 'TB_BIG','TB_BIG2'); -- SHARED_POOL/BUFFER_POOL/FLASH_CACHE/BUFFER_CACHE/GLOBAL CONTEXT/PASSWORDFILE_METADATA_CACHE 키워드 alter system flush FLASH_CACHE; alter system flush BUFFER_CACHE; alter table tb_big rename to tb_big2; drop table tb_big2 purge; alter table tb_big rename to tb_big2; SELECT A.TABLESPACE_NAME,A.STATUS, B.TOTAL_MB, A.USE_MB, ROUND(RATIO_TO_REPORT(A.USE_MB) OVER(PARTITION BY A.TABLESPACE_NAME) * 100)||'%' AS PCT FROM (SELECT TABLESPACE_NAME, STATUS, ROUND(SUM(BYTES/1024/1024)) USE_MB FROM DBA_UNDO_EXTENTS GROUP BY TABLESPACE_NAME,STATUS )A, (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024) TOTAL_MB FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE 'UNDO%' GROUP BY TABLESPACE_NAME )B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME ORDER BY 1,2 ; SELECT SUBSTRB(A.SEGMENT_NAME, 1, 10) AS SEGMENT_NAME , SUBSTRB(A.TABLESPACE_NAME, 1, 10) AS TABLESPACE_NAME , TO_CHAR(A.SEGMENT_ID, '99,999') AS SEG_ID , TO_CHAR(A.MAX_EXTENTS, '999,999') AS MAX_EXT , TO_CHAR(B.EXTENTS, '999,999') AS EXTENTS , TO_CHAR(B.EXTENDS, '999,999') AS EXTENDS , TO_CHAR((A.INITIAL_EXTENT + (B.EXTENTS - 1) * A.NEXT_EXTENT) / 1000000, '9,999.999') AS "ALLOC(MB)" , TO_CHAR(XACTS, '9,999') AS XACTS FROM DBA_ROLLBACK_SEGS A , V$ROLLSTAT B WHERE A.SEGMENT_ID = B.USN(+) ORDER BY 1; SELECT group# , status , member FROM v$logfile; select * from V$ARCHIVED_LOG; select * from v$parameter where name like '%log%'; alter system set log_archive_format='%t_%s_%r.arc' scope=spfile; drop table tb_big3 purge; create table tb_big3 as select cola,colb,22 as colc,cold,cole,colf,colg,colh,coli from tb_big; select count(*) from tb_big; alter system switch logfile;
-- 테이블 생성 CREATE TABLE TB_BIG( COLA VARCHAR2(20), COLB NUMBER , COLC NUMBER, COLD VARCHAR2(30), COLE VARCHAR2(30), COLF VARCHAR2(30), COLG NUMBER , COLH VARCHAR2(30), COLI VARCHAR2(30) ); -- DATA 입력 DECLARE TYPE tbl_ins IS TABLE OF TB_BIG%ROWTYPE INDEX BY BINARY_INTEGER; w_ins tbl_ins; BEGIN FOR i IN 1..8960000 LOOP w_ins(i).COLA :=i; w_ins(i).COLB :=300000; w_ins(i).COLC :=99; -- <== 변경 테스트 대상 컬럼 99를 11로 변경 w_ins(i).COLD :='ABC'||dbms_random.string('x',10); w_ins(i).COLE :='EEEEEEEEEEEEEEEE'; w_ins(i).COLF :='FFFFFFFFFFFFFFFF'; w_ins(i).COLG :=9999999; w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH'; w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII'; END LOOP; FORALL i in 1..8960000 INSERT INTO TB_BIG VALUES w_ins(i); COMMIT; END; / -- 8,960,000 건
TB MB BLOCKS -------------------- ---------- ---------- TB_BIG 1216 155648
UPDATE 테스트
sh-4.2$ cat update_tb_big.sql update tb_big set colc=11; commit; ====== 시작 ====== 15:46:35 SQL> @update_tb_big.sql 8960000 rows updated. Commit complete. 15:54:14 SQL> ====== 종료 ====== 총 7분 40초 , 460초
CTAS 테스트
sh-4.2$ cat ctas_tb_big.sql -- rename old alter table tb_big rename to tb_big_old; -- ctas create table tb_big as select cola,colb,22 as colc , cold,cole,colf,colg,colh,coli from tb_big_old; ====== 시작 ====== 15:58:56 SQL> @ctas_tb_big.sql Table created. 15:59:22 SQL> ====== 종료 ======= 총 26초
archive log mode 인경우 테스트
16:28:03 SQL> @update_tb_big.sql 8960000 rows updated. Commit complete. 16:35:46 SQL> ====== 종료 ====== 총 7분 43초 , 463초
UPDATE 테스트
- 아카이빙 로그 사이즈
- 로그 테스트 전
bash-4.2# du -h 16K .
- 로그 테스트 후
-rw-r----- 1 oracle oinstall 196845056 Jun 16 16:28 arch1_37_1171284405.dbf -rw-r----- 1 oracle oinstall 195198976 Jun 16 16:29 arch1_38_1171284405.dbf -rw-r----- 1 oracle oinstall 194925056 Jun 16 16:29 arch1_39_1171284405.dbf -rw-r----- 1 oracle oinstall 195596288 Jun 16 16:30 arch1_40_1171284405.dbf -rw-r----- 1 oracle oinstall 195552256 Jun 16 16:30 arch1_41_1171284405.dbf -rw-r----- 1 oracle oinstall 195438592 Jun 16 16:31 arch1_42_1171284405.dbf -rw-r----- 1 oracle oinstall 189432320 Jun 16 16:31 arch1_43_1171284405.dbf -rw-r----- 1 oracle oinstall 183622656 Jun 16 16:32 arch1_44_1171284405.dbf -rw-r----- 1 oracle oinstall 190867456 Jun 16 16:32 arch1_45_1171284405.dbf -rw-r----- 1 oracle oinstall 190482432 Jun 16 16:33 arch1_46_1171284405.dbf -rw-r----- 1 oracle oinstall 190621696 Jun 16 16:33 arch1_47_1171284405.dbf -rw-r----- 1 oracle oinstall 191492608 Jun 16 16:34 arch1_48_1171284405.dbf -rw-r----- 1 oracle oinstall 185030144 Jun 16 16:34 arch1_49_1171284405.dbf -rw-r----- 1 oracle oinstall 188132864 Jun 16 16:35 arch1_50_1171284405.dbf drwxr-xr-x 1 oracle dba 4096 Jun 16 16:35 . -rw-r----- 1 oracle oinstall 179443200 Jun 16 16:35 arch1_51_1171284405.dbf bash-4.2# date Sun Jun 16 16:41:50 UTC 2024 bash-4.2# du -k 2795632 . bash-4.2# du -h 2.7G .
CTAS 테스트
bash-4.2# ls -altr total 1358964 -rw-r--r-- 1 oracle dba 3079 May 14 2015 init.ora drwxr-xr-x 1 oracle dba 4096 Jun 10 12:44 .. -rw-r----- 1 oracle oinstall 24 Jun 10 12:44 lkMONGO lrwxrwxrwx 1 oracle oinstall 54 Jun 10 13:07 spfileMONGO.ora -> /u01/app/oracle/oradata/dbconfig/MONGO/spfileMONGO.ora lrwxrwxrwx 1 oracle oinstall 49 Jun 10 13:07 orapwMONGO -> /u01/app/oracle/oradata/dbconfig/MONGO/orapwMONGO -rw-rw---- 1 oracle oinstall 1544 Jun 16 16:27 hc_MONGO.dat -rw-r----- 1 oracle oinstall 198791168 Jun 16 16:49 arch1_52_1171284405.dbf -rw-r----- 1 oracle oinstall 198788096 Jun 16 16:49 arch1_53_1171284405.dbf -rw-r----- 1 oracle oinstall 198794240 Jun 16 16:49 arch1_54_1171284405.dbf -rw-r----- 1 oracle oinstall 198791680 Jun 16 16:49 arch1_55_1171284405.dbf -rw-r----- 1 oracle oinstall 198794240 Jun 16 16:49 arch1_56_1171284405.dbf -rw-r----- 1 oracle oinstall 198794240 Jun 16 16:49 arch1_57_1171284405.dbf drwxr-xr-x 1 oracle dba 4096 Jun 16 16:49 . -rw-r----- 1 oracle oinstall 198789120 Jun 16 16:49 arch1_58_1171284405.dbf bash-4.2# du -k ./ 1358956 ./ bash-4.2# du -h ./ 1.3G ./
CTAS NOLOGGING MODE
ctas nologging mode
bash-4.2# du -h 16K .
- 00:19.21 소요
-- UNRECOVERABLE 옵션 사용 SQL> @ctas_tb_big_nolog.sql Table altered. Elapsed: 00:00:00.03 Table created. Elapsed: 00:00:19.21
ctas nolooging 처리후
bash-4.2# du -h 1.2G . ==> nolooging 인데도 사이즈가 크게 줄지 않음...... --- 테이블 drop 시 drop table tb_big2 purge; bash-4.2# du -h 1.3G .
insert ~ select nologging
- 01:19 초 소요
SQL> @ins_sel_nologging.sql Table altered. Elapsed: 00:00:00.07 Table created. Elapsed: 00:00:00.10 Table altered. Elapsed: 00:00:00.04 8960000 rows created. Elapsed: 00:01:19.94
bash-4.2# du -h 1.2G . bash-4.2#