"Undo 세미나"의 두 판 사이의 차이
DB CAFE
(→archive log mode 인경우 테스트) |
(→archive log mode 인경우 테스트) |
||
88번째 줄: | 88번째 줄: | ||
====== 종료 ====== 총 7분 43초 , 463초 | ====== 종료 ====== 총 7분 43초 , 463초 | ||
</source> | </source> | ||
− | + | ==== UPDATE 테스트 ==== | |
* 아카이빙 로그 사이즈 | * 아카이빙 로그 사이즈 | ||
** 로그 테스트 전 | ** 로그 테스트 전 | ||
119번째 줄: | 119번째 줄: | ||
bash-4.2# du -h | bash-4.2# du -h | ||
2.7G . | 2.7G . | ||
+ | </source> | ||
+ | |||
+ | ==== CTAS 테스트 ==== | ||
+ | <source lang=sql> | ||
+ | bash-4.2# du -h ./ | ||
+ | 1.3G ./ | ||
+ | bash-4.2# | ||
+ | bash-4.2# | ||
+ | bash-4.2# du -k ./ | ||
+ | 1358956 ./ | ||
+ | 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 | ||
</source> | </source> |
2024년 6월 17일 (월) 01:27 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 언두 테스트[편집]
-- 테이블 생성
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..1400000 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..1400000 INSERT INTO TB_BIG VALUES w_ins(i);
COMMIT;
END;
/
TB MB BLOCKS
-------------------- ---------- ----------
TB_BIG 1216 155648
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초
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초
2 archive log mode 인경우 테스트[편집]
16:28:03 SQL> @update_tb_big.sql
8960000 rows updated.
Commit complete.
16:35:46 SQL>
====== 종료 ====== 총 7분 43초 , 463초
2.1 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 .
2.2 CTAS 테스트[편집]
bash-4.2# du -h ./
1.3G ./
bash-4.2#
bash-4.2#
bash-4.2# du -k ./
1358956 ./
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