Undo 세미나
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 언두 테스트[편집]
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
1.1 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초
1.2 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초
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# 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 ./
3 CTAS NOLOGGING MODE[편집]
3.1 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
3.2 ctas nolooging 처리후[편집]
bash-4.2# du -h
1.2G .
==> nolooging 인데도 사이즈가 크게 줄지 않음......
--- 테이블 drop 시
drop table tb_big2 purge;
bash-4.2# du -h
1.3G .
3.3 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#
6 테이블스페이스 사이즈 확인[편집]
select *
from dba_tablespaces
order by 1,2;
select owner,count(*)
from dba_segments
where 1=1 -- owner='SYS'
group by owner
order by 1,2
;
-- 테이블 생성
create table dbcafe.TB_T1(A number, B char(1),C varchar2(10))
tablespace TS_DBCAFE;
select * from dbcafe.TB_T1;
-- 세그먼트에는 테이블에 데이터가 입력된적이 없으므로 조회되지 않음. 데이터 입력시 적용됨.
select *
from dba_segments
where segment_name like 'TB_T%'
;
-- 데이터 1건 입력
INSERT INTO dbcafe.TB_T1 VALUES (1,'1','11111');
-- commit 하지 말고 세그먼트 조회
select *
from dba_segments
where segment_name like 'TB_T%'
;
-- header_file 13
-- header_block 130
-- bytes 65536
-- blocks 8
-- extents 1
-- initial_extent 65536
-- next_extent 1048576
-- relative_fno 13
select * from dba_data_files where file_id = 13; -- 테이블스페이 데이터파일 번호 // relative_fno 13
-- [데이터 입력후 아직 커밋하지 않음]
-- 13 130 65536 8 1 65536 1048576
select * from v$session
where status ='ACTIVE'
;
select rowid
, dbms_rowid.rowid_relative_fno(rowid) as file_no -- data file number
, dbms_rowid.rowid_block_number(rowid) as block_no
from dbcafe.TB_T1 where rownum = 1;
-- block info
-- rowid file_no block_no
AAAR/wAANAAAACEAAA 13 132
;
-- 트레이스 파일 식별자 설정 (트레이스파일을 쉽게 찾기 위해서 설정 )
alter session set tracefile_identifier ='DBCAFE_Block_Dump';
alter system dump datafile 13 block 132;
-- 트레이스 폴더로 이동
cd $ORACE_BASE/diag/rdbms/{mongo/MONGO}/trace -- DB마다 {---} 위치가 다름
-- 트레이스 파일 찾기
ls |grep DBCAFE_Block_Dump;
MONGO_ora_43565_DBCAFE_Block_Dump.trc <== 트레이스 파일
MONGO_ora_43565_DBCAFE_Block_Dump.trm <== trace meta file,트레이스파일 메타정보를 보관하는 파일 , adrci같은 툴에서 활용함
-- 트레이스 파일 열기
vi MONGO_ora_43565_DBCAFE_Block_Dump.trc
-- 12c 이상 부터는 sql로 조회 가능
SELECT *
FROM V$DIAG_TRACE_FILE
where trace_filename like '%DBCAFE%'
ORDER BY CHANGE_TIME DESC
;
SELECT replace(replace(payload,chr(13)),chr(10)) as payload
FROM V$DIAG_TRACE_FILE_CONTENTS
WHERE TRACE_FILENAME = 'MONGO_ora_23904_DBCAFE_Block_Dump.trc'
ORDER BY LINE_NUMBER
;
Trace file /u01/app/oracle/diag/rdbms/mongo/MONGO/trace/MONGO_ora_23904_DBCAFE_Block_Dump.trc
... 생략 ....
*** 2024-06-23T03:22:44.434510+00:00 (CDB$ROOT(1))
*** SESSION ID:(21.6605) 2024-06-23T03:22:44.434570+00:00
-- 21은 sid
-- 6605는 serial#
..... 생략 ....
-----------------
Start dump data blocks tsn: 6 file#:13 minblk 132 maxblk 132
-- 1. 버퍼캐시 덤프
Block dump from cache:
Dump of buffer cache at level 3 for pdb=1 tsn=6 rdba=54526084
BH (0x7cf64db8) file#: 13 rdba: 0x03400084 (13/132) class: 1 ba: 0x7c20a000
set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 73712 objn: 73712 tsn: [1/6] afn: 13 hint: f
hash: [0x75181db8,0x75181db8] lru: [0x9afaa2c0,0x8cf53998]
ckptq: [NULL] fileq: [NULL]
objq: [0x6667add0,0x6667add0] objaq: [0x6667adc0,0x6667adc0]
st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' fscn: 0x273bb5 tch: 2
flags: block_written_once
LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x273bb5] HSUB: [3]
Printing buffer operation history (latest change first):
cnt: 3
01. sid:00 L192:kcbbic2:bic:FBD 02. sid:00 L191:kcbbic2:bic:FBW
03. sid:00 L602:bic1_int:bis:FWC 04. sid:00 L822:bic1_int:ent:rtn
05. sid:00 L832:oswmqbg1:clr:WRT 06. sid:00 L930:kubc:sw:mq
07. sid:00 L913:bxsv:sw:objq 08. sid:00 L608:bxsv:bis:FBW
09. sid:00 L607:bxsv:bis:FFW 10. sid:05 L464:chg1_mn:bic:FMS
11. sid:05 L778:chg1_mn:bis:FMS 12. sid:05 L353:gcur:set:MEXCL
13. sid:05 L464:chg1_mn:bic:FMS 14. sid:05 L614:chg1_mn:bis:FBD
15. sid:05 L922:klbc:sw:cq 16. sid:05 L778:chg1_mn:bis:FMS
buffer tsn: 6 rdba: 0x03400084 (13/132)
scn: 0x273bb5 seq: 0x03 flg: 0x04 tail: 0x3bb50603
frmt: 0x02 chkval: 0x0e55 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000007C20A000 to 0x000000007C20C000
........... 생략 ..............
-- 블럭 헤더 덤프
-- Itl 로우 입력한 트랜젝션 이 itl 0x01 슬롯에 저장됨 , 0x01은 1번째
select xidusn,xidslot,xidsqn from v$transaction
where addr = (select taddr from v$session where sid = 21);
-- 세션 정보 조회는 TM 락
SELECT A.SID
, A.SERIAL#
, C.object_name
, A.SID || ', ' || A.SERIAL# AS KILL_TASK
FROM V$SESSION A
INNER JOIN V$LOCK B
ON A.SID = B.SID
INNER JOIN DBA_OBJECTS C
ON B.ID1 = C.OBJECT_ID
WHERE B.TYPE = 'TM'
;
-- xidusn xidslot xidsqn
4 8 1059
-- 16진수 -> 10진수로 변환
4 8 4185
;
-- 16진수 -> 10진수로 변환
select to_number('1059', 'xxxx') from dual;
;
-- 10진수 -> 16진수로 변환
select to_char(1059, 'xxxx') from dual;
;
Block header dump: 0x03400084
Object id on Block? Y
seg/obj: 0x11ff0 csc: 0x0000000000273bb5 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3400080 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.008.00000423 0x01000614.017b.1f ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x03400084
data_block_dump,data header at 0x7c20a064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x7c20a064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8a
avsp=0x1f76
tosp=0x1f76
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f8a
block_row_dump:
tab 0, row 0, @0x1f8a
tl: 14 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [ 1] 31
col 2: [ 5] 31 31 31 31 31
end_of_block_dump
Block dump from disk:
buffer tsn: 6 rdba: 0x03400084 (13/132)
scn: 0x273bb5 seq: 0x03 flg: 0x04 tail: 0x3bb50603
frmt: 0x02 chkval: 0x0e55 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F628C33A000 to 0x00007F628C33C000
........... 생략 ............
-- commit;
select rowid
, dbms_rowid.rowid_relative_fno(rowid) as file_no
, dbms_rowid.rowid_block_number(rowid) as block_no
from dbcafe.TB_T1 where rownum = 1;
6.1 블럭 디버깅[편집]
-- trace file 구분자 입력
alter session set tracefile_identifier = 'DBCAFE';
-- sql 실행
SQL> @core_demo_02.sql
6.1.1 trace file 분석[편집]
---------------------------
-- session info
*** SESSION ID:(395.49059) 2024-06-24T13:04:35.468088+00:00
6.1.2 Block dump from cache[편집]
Start dump data blocks tsn: 0 file#:1 minblk 443673 maxblk 443673
Block dump from cache:
Dump of buffer cache at level 3 for pdb=1 tsn=0 rdba=4637977
BH (0xb1f596e8) file#: 1 rdba: 0x0046c519 (1/443673) class: 1 ba: 0xb1106000
set: 11 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 73789 objn: 73789 tsn: [1/0] afn: 1 hint: f
hash: [0x9ff8d278,0x752a2038] lru: [0xb9fbc740,0xaefe2990]
ckptq: [NULL] fileq: [NULL]
objq: [0x6ccce6b8,0x6ccce6b8] objaq: [0x6ccce6a8,0x6ccce6a8]
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x28c8fa tch: 1
flags: only_sequential_access
LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x0] HSUB: [65535]
Printing buffer operation history (latest change first):
cnt: 3
01. sid:11 L338:zibmlt:set:MSHR 02. sid:11 L144:zibmlt:mk:EXCL
03. sid:11 L122:zgb:set:st 04. sid:11 L830:olq1:clr:WRT+CKT
05. sid:11 L951:zgb:lnk:objq 06. sid:11 L372:zgb:set:MEXCL
07. sid:11 L123:zgb:no:FEN 08. sid:11 L083:zgb:ent:fn
09. sid:11 L154:z_sw_cur:bic:FPB 10. sid:11 L940:z_sw_cur:sw:cq
11. sid:11 L070:zswcu:ent:ob 12. sid:11 L471:bpostapl:bic:FMS
13. sid:11 L786:pre_apl:bis:FMS 14. sid:11 L353:gcur:set:MEXCL
15. sid:11 L471:bpostapl:bic:FMS 16. sid:11 L786:pre_apl:bis:FMS
buffer tsn: 0 rdba: 0x0046c519 (1/443673)
scn: 0x28c8f2 seq: 0x02 flg: 0x04 tail: 0xc8f20602
frmt: 0x02 chkval: 0x9c7f type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000B1106000 to 0x00000000B1108000
..... 생략 ......
6.1.3 Block header dump[편집]
Block header dump: 0x0046c519
Object id on Block? Y
seg/obj: 0x1203d csc: 0x000000000028c8f1 itc: 3 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x000000000028c8f1
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0046c519
data_block_dump,data header at 0xb1106074
===============
tsiz: 0x1f88
hsiz: 0x8a
pbl: 0xb1106074
76543210
flag=--------
ntab=1
nrow=60
frre=-1
fsbo=0x8a
fseo=0x412
avsp=0x388
tosp=0x388
0xe:pti[0] nrow=60 offs=0
.... 생략 ....
block_row_dump:
tab 0, row 0, @0x1f13
tl: 117 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 2] c1 02
col 1: [ 2] c1 02
col 2: [ 6] 78 78 78 78 78 78
col 3: [100]
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
tab 0, row 1, @0x1e9e
tl: 117 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 2] c1 04
col 1: [ 2] c1 03
col 2: [ 6] 78 78 78 78 78 78
col 3: [100]
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
... 생략 ...
end_of_block_dump
Block dump from disk:
buffer tsn: 0 rdba: 0x0046c519 (1/443673)
scn: 0x28c8f2 seq: 0x02 flg: 0x04 tail: 0xc8f20602
frmt: 0x02 chkval: 0x9c7f type: 0x06=trans data
Hex dump of block: st=0, typ_found=1