PK 테이블 관련하여
NO SUPPLEMNETAL LOGGING 과 SUPPLMENTAL LOGGING 테스트
#PK 테이블 NO SUPPLMENTAL LOGGING
CREATE TABLE SCOTT.TEST2
(
NO NUMBER(10) PRIMARY KEY
, NAME VARCHAR2(20)
, BASEDATE VARCHAR2(20)
);
----------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
----------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 0 (0)|
----------------------------------------------------
select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
archive log list;
alter system switch logfile;
archive log list;
데이터베이스 로그 모드 아카이브 로그 모드
자동 아카이브 사용
아카이브 대상 USE_DB_RECOVERY_FILE_DEST
가장 오래된 온라인 리두 시퀀스 169
아카이브할 다음 로그 시퀀스 171
현재 로그 시퀀스 171
-- 데이터삽입
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SET AUTOTRACE ON EXPLAIN;
BEGIN
FOR i in 1..10000 LOOP
INSERT INTO SCOTT.TEST2 VALUES(i,DBMS_RANDOM.STRING('U', 20), (select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')from dual));
COMMIT;
END LOOP;
END;
/
PL/SQL 프로시저가 성공적으로 완료되었습니다.
-- 데이터 변경
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SET AUTOTRACE TARCEONLY EXPLAIN;
UPDATE SCOTT.TEST2 SET NAME='HWP' WHERE NO > 50;
COMMIT;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 10025 | 244K| 22 (0)| 00:00:01 |
| 1 | UPDATE | TEST2 | | | | |
|* 2 | TABLE ACCESS FULL| TEST2 | 10025 | 244K| 22 (0)| 00:00:01 |
----------------------------------------------------------------------------
-- 데이터 삭제
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SET AUTOTRACE TARCEONLY EXPLAIN;
DELETE FROM SCOTT.TEST2;
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 13 | 21 (0)| 00:00:01 |
| 1 | DELETE | TEST2 | | | | |
| 2 | INDEX FULL SCAN| SYS_C0011449 | 1 | 13 | 21 (0)| 00:00:01 |
---------------------------------------------------------------------------------
alter system switch logfile;
archive log list;
exec dbms_logmnr.add_logfile('/home/oracle/arc/171_1_915156033.arc', options=>dbms_logmnr.new);
exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/dict');
PL/SQL 프로시저가 성공적으로 완료되었습니다.
PL/SQL 프로시저가 성공적으로 완료되었습니다.
select * from V$LOGMNR_CONTENTS;
-- 로그마이너 세션 종료
exec dbms_logmnr.end_logmnr();
#PK 테이블 Supplemental Logging PK ON
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
SELECT SUPPLEMENTAL_LOG_DATA_MIN S_MIN, SUPPLEMENTAL_LOG_DATA_PK S_PK, SUPPLEMENTAL_LOG_DATA_UI S_UI, SUPPLEMENTAL_LOG_DATA_FK S_FK, SUPPLEMENTAL_LOG_DATA_ALL S_ALL from v$database ;
S_MIN S_P S_U S_F S_A
-------- --- --- --- ---
IMPLICIT YES NO NO NO
데이터베이스 로그 모드 아카이브 로그 모드
자동 아카이브 사용
아카이브 대상 USE_DB_RECOVERY_FILE_DEST
가장 오래된 온라인 리두 시퀀스 184
아카이브할 다음 로그 시퀀스 186
현재 로그 시퀀스 186
select current_scn from v$database;
8482731
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SET AUTOTRACE ON;
BEGIN
FOR i in 1..10000 LOOP
INSERT INTO SCOTT.TEST4 VALUES(i,DBMS_RANDOM.STRING('U', 20), (select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')from dual));
COMMIT;
END LOOP;
END;
/
SET AUTOTRACE OFF;
PL/SQL 프로시저가 성공적으로 완료되었습니다.
Statistics
-----------------------------------------------------------
323 CPU used by this session
327 CPU used when call started
321 DB time
3 Requests to/from client
2637824 cell physical IO interconnect bytes
3688 consistent gets
1194 consistent gets - examination
3688 consistent gets from cache
2242 consistent gets from cache (fastpath)
51681 db block gets
51681 db block gets from cache
10375 db block gets from cache (fastpath)
20184 enqueue releases
20184 enqueue requests
6567 messages sent
326 non-idle wait count
1 non-idle wait time
20761 opened cursors cumulative
1 opened cursors current
320 physical read total IO requests
2637824 physical read total bytes
1 pinned cursors current
31864 recursive calls
306 recursive cpu usage
55369 session logical reads
2 user I/O wait time
4 user calls
10000 user commits
자동 추적 사용 안함
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SET AUTOTRACE ON;
UPDATE SCOTT.TEST4 SET NAME='HWP' WHERE NO > 50;
COMMIT;
SET AUTOTRACE OFF;
9,950개 행 이(가) 업데이트되었습니다.
Plan hash value: 886624951
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 10017 | 244K| 22 (0)| 00:00:01 |
| 1 | UPDATE | TEST4 | | | | |
|* 2 | TABLE ACCESS FULL| TEST4 | 10017 | 244K| 22 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NO">50)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
-----------------------------------------------------------
7 CPU used by this session
8 CPU used when call started
14 DB time
3 Requests to/from client
1613824 cell physical IO interconnect bytes
1311 consistent gets
497 consistent gets - examination
1311 consistent gets from cache
716 consistent gets from cache (fastpath)
10405 db block gets
10405 db block gets from cache
2 db block gets from cache (fastpath)
16 enqueue releases
18 enqueue requests
4 messages sent
127 non-idle wait count
1 non-idle wait time
354 opened cursors cumulative
1 opened cursors current
124 physical read total IO requests
1613824 physical read total bytes
1 pinned cursors current
544 recursive calls
2 recursive cpu usage
11716 session logical reads
4 user calls
커밋 완료.
Statistics
-----------------------------------------------------------
1 CPU used by this session
1 CPU used when call started
1 DB time
3 Requests to/from client
1 db block gets
1 db block gets from cache
3 enqueue releases
1 messages sent
5 non-idle wait count
1 opened cursors cumulative
1 opened cursors current
1 pinned cursors current
2 recursive calls
1 session logical reads
3 user calls
1 user commits
자동 추적 사용 안함
-- 데이터 삭제
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SET AUTOTRACE ON;
DELETE FROM SCOTT.TEST4;
SET AUTOTRACE OFF;
alter system switch logfile;
archive log list;
실행 계획과 명령문 통계를 표시합니다.
10,000개 행 이(가) 삭제되었습니다.
Plan hash value: 3349578560
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 13 | 21 (0)| 00:00:01 |
| 1 | DELETE | TEST4 | | | | |
| 2 | INDEX FULL SCAN| SYS_C0011452 | 1 | 13 | 21 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
-----------------------------------------------------------
11 CPU used by this session
14 CPU used when call started
16 DB time
3 Requests to/from client
1662976 cell physical IO interconnect bytes
1240 consistent gets
493 consistent gets - examination
1240 consistent gets from cache
639 consistent gets from cache (fastpath)
10791 db block gets
10791 db block gets from cache
59 db block gets from cache (fastpath)
13 enqueue releases
15 enqueue requests
2 messages sent
134 non-idle wait count
3 non-idle wait time
350 opened cursors cumulative
1 opened cursors current
123 physical read total IO requests
1662976 physical read total bytes
1 pinned cursors current
506 recursive calls
4 recursive cpu usage
12031 session logical reads
4 user calls
alter system switch logfile;
archive log list;
System SWITCH이(가) 변경되었습니다.
데이터베이스 로그 모드 아카이브 로그 모드
자동 아카이브 사용
아카이브 대상 USE_DB_RECOVERY_FILE_DEST
가장 오래된 온라인 리두 시퀀스 185
아카이브할 다음 로그 시퀀스 187
현재 로그 시퀀스 187
select current_scn from v$database;
8504154
exec dbms_logmnr.add_logfile('/home/oracle/arc/186_1_915156033.arc', options=>dbms_logmnr.new);
exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/dict');
'Oracle > Oracle DBMS' 카테고리의 다른 글
| Oracle Supplemental Logging 일반테이블 (NO) Supplemental Logging (0) | 2017.11.07 |
|---|---|
| Oracle Supplemental Logging (0) | 2017.11.07 |
| UNDO 재생성 (0) | 2017.10.31 |
댓글