08. 비즈니스 인텔리전트와 데이터 웨어하우징 (by feelie) [2017.01.19]
8장 비즈니스 인텔리전트와 데이터 웨어하우징
* 주요 내용
1. 분석 기능 향상
2. 온라인 분석 프로세싱
3. ILM (Information LifeCycle Management)
4. TH (Temporal History )
5. 퍼포먼스에 개선된 기능
3. ILM (Information LifeCycle Management)
1. In-Database 아카이빙(IDA)
- 레코드가 보이거나 보이지 않도록 함
2. Temporal Validity
- 데이터가 표시되는 것을 결정하는 Time 디멘전 컬럼을 추가하는 기능
- 레코드가 특정 기간 동안만 보임
3. 자동 데이터 최적화 (ADO Automatic Data Optimizition )
- 세그먼트 레벨이나 로우 레벨에서 데이터에 어떤 작업이 일어나는지를 인식
- 데이터의 액세스나 수정은 Heat Map으로 추적
- Heat Map 사용하고, 데이터의 액세스나 수정을 기반으로 언제 어떤 작업을 할지 결정
1. In-Database 아카이빙(IDA)
CREATE TABLE EMPLOYEES_ARCHIVE_TEST AS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEE
WHERE EMPLOYEE_ID IN (200,201,202,203,204,205);
ALTER TABLE EMPLOYEE_ARCHIVE_TEST ROW ARCHIVAL;
** 컬럼 정보 조회
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_ID, HIDDEN_COLUMN
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'EMPLOYEE_ARCHIVE_TEST'
ORDER BY COLUMN_ID;
TABLE_NAME COLUMN_NAME COLUMN_ID HID
------------------------- -------------------- ---------- ---
EMPLOYEE_ARCHIVE_TEST EMPLOYEE_ID 1 NO
EMPLOYEE_ARCHIVE_TEST FIRST_NAME 2 NO
EMPLOYEE_ARCHIVE_TEST LAST_NAME 3 NO
EMPLOYEE_ARCHIVE_TEST ORA_ARCHIVE_STATE YES
EMPLOYEE_ARCHIVE_TEST SYS_NC00004$ YES
** 히든 컬럼(ORA_ARCHIVE_STATE) 조회
SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME, ORA_ARCHIVE_STATE
FROM EMPLOYEE_ARCHIVE_TEST
ORDER BY EMPLOYEE_ID;
EMPLOYEE_ID FULL_NAME ORA_ARCHIV
----------- ------------------------------ ----------
200 Jennifer Whalen 0
201 Michael Hartstein 0
202 Pat Fay 0
203 Susan Mavris 0
204 Hermann Baer 0
205 Shelley Higgins 0
6 행이 선택되었습니다.
** 히든 컬럼(ORA_ARCHIVE_STATE) 업데이트
UPDATE EMPLOYEE_ARCHIVE_TEST
SET ORA_ARCHIVE_STATE = DBMS_ILM.ARCHIVESTATENAME(1)
WHERE EMPLOYEE_ID IN (200, 201)
----------------------------------------------------------
UPDATE EMPLOYEE_ARCHIVE_TEST
SET ORA_ARCHIVE_STATE = 1
WHERE EMPLOYEE_ID IN (200, 201)
COMMIT;
** 테이블 정보 조회 시
SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME, ORA_ARCHIVE_STATE
FROM EMPLOYEE_ARCHIVE_TEST
ORDER BY EMPLOYEE_ID;
EMPLOYEE_ID FULL_NAME ORA_ARCHIV
----------- ------------------------------ ----------
202 Pat Fay 0
203 Susan Mavris 0
204 Hermann Baer 0
205 Shelley Higgins 0
4 행이 선택되었습니다.
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME, ORA_ARCHIVE_STATE
FROM EMPLOYEE_ARCHIVE_TEST
ORDER BY EMPLOYEE_ID;
EMPLOYEE_ID FULL_NAME ORA_ARCHIVE_STATE
----------- ------------------------------ ------------------------------
200 Jennifer Whalen 1
201 Michael Hartstein 1
202 Pat Fay 0
203 Susan Mavris 0
204 Hermann Baer 0
205 Shelley Higgins 0
6 행이 선택되었습니다.
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE
SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME, ORA_ARCHIVE_STATE
FROM EMPLOYEE_ARCHIVE_TEST
ORDER BY EMPLOYEE_ID;
EMPLOYEE_ID FULL_NAME ORA_ARCHIVE_STATE
----------- ------------------------------ ------------------------------
202 Pat Fay 0
203 Susan Mavris 0
204 Hermann Baer 0
205 Shelley Higgins 0
** 로우가 아카이브되었다는 것은 실제로 로우가 이동하거나 없어진 것을 의미하진 않는다
ALTER TABLE EMPLOYEE_ARCHIVE_TEST ADD PRIMARY KEY (EMPLOYEE_ID)
INSERT INTO EMPLOYEE_ARCHIVE_TEST VALUES(200,'I Want', ' in ');
1행에 오류:
ORA-00001: 무결성 제약 조건(SYS.SYS_C0010074)에 위배됩니다
** 적절한 데이터가 archived로 표시될 수 있고 이후에 archived 로 표시된 데이터를 저가의 스토리지쪽에
생성된 테이블 스페이스로 이동시킬 수 있다
2. Temporal Validity
* 테이블 생성
CREATE TABLE EMPLOYEES_TEMPORAL_TEST AS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEE
WHERE EMPLOYEE_ID IN (200,201,202,203,204,205);
ALTER TABLE EMPLOYEES_TEMPORAL_TEST ADD PERIOD FOR VALID_TIME;
SELECT COLUMN_NAME, HIDDEN_COLUMN, DATA_TYPE
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'EMPLOYEES_TEMPORAL_TEST'
ORDER BY COLUMN_ID, COLUMN_NAME DESC;
COLUMN_NAME HIDDE DATA_TYPE
------------------------------ ----- ----------------------------------------
EMPLOYEE_ID NO NUMBER
FIRST_NAME NO VARCHAR2
LAST_NAME NO VARCHAR2
VALID_TIME_START YES TIMESTAMP(6) WITH TIME ZONE
VALID_TIME_END YES TIMESTAMP(6) WITH TIME ZONE
VALID_TIME YES NUMBER
6 행이 선택되었습니다.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
TO_CHAR(VALID_TIME_START, 'DDMONYY') "Start",
TO_CHAR(VALID_TIME_END, 'DDMONYY') "End"
FROM EMPLOYEES_TEMPORAL_TEST
EMPLOYEE_ID FIRST_NAME LAST_NAME Start End
----------- -------------------- --------------- ------------ ------------
200 Jennifer Whalen
201 Michael Hartstein
202 Pat Fay
203 Susan Mavris
204 Hermann Baer
205 Shelley Higgins
6 행이 선택되었습니다.
UPDATE EMPLOYEES_TEMPORAL_TEST
SET VALID_TIME_START = TO_DATE('20120101','YYYYMMDD'),
VALID_TIME_END = TO_DATE('20130215','YYYYMMDD')
WHERE EMPLOYEE_ID IN (200,201);
UPDATE EMPLOYEES_TEMPORAL_TEST
SET VALID_TIME_START = TO_DATE('20130216','YYYYMMDD'),
VALID_TIME_END = TO_DATE('20130321','YYYYMMDD')
WHERE EMPLOYEE_ID IN (202,203);
UPDATE EMPLOYEES_TEMPORAL_TEST
SET VALID_TIME_START = TO_DATE('20130420','YYYYMMDD')
WHERE EMPLOYEE_ID IN (204,205);
COMMIT;
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
TO_CHAR(VALID_TIME_START, 'YYYYMMDD') "Start",
TO_CHAR(VALID_TIME_END, 'YYYYMMDD') "End"
FROM EMPLOYEES_TEMPORAL_TEST;
EMPLOYEE_ID FIRST_NAME LAST_NAME Start End
----------- -------------------- --------------- -------- --------
200 Jennifer Whalen 20120101 20130215
201 Michael Hartstein 20120101 20130215
202 Pat Fay 20130216 20130321
203 Susan Mavris 20130216 20130321
204 Hermann Baer 20130420
205 Shelley Higgins 20130420
6 행이 선택되었습니다.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
TO_CHAR(VALID_TIME_START, 'YYYYMMDD') "Start",
TO_CHAR(VALID_TIME_END, 'YYYYMMDD') "End"
FROM EMPLOYEES_TEMPORAL_TEST
AS OF PERIOD FOR VALID_TIME TO_DATE('20130110')
ORDER BY 2;
** 참고 (10053 trace file)
WHERE ("T"."VALID_TIME_START" IS NULL OR SYS_EXTRACT_UTC("T"."VALID_TIME_START") <= SYS_EXTRACT_UTC(TO_DATE('20130110')))
AND ("T"."VALID_TIME_END" IS NULL OR SYS_EXTRACT_UTC("T"."VALID_TIME_END") > SYS_EXTRACT_UTC(TO_DATE('20130110')))
EMPLOYEE_ID FIRST_NAME LAST_NAME Start End
----------- -------------------- --------------- -------- --------
200 Jennifer Whalen 20120101 20130215
201 Michael Hartstein 20120101 20130215
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
TO_CHAR(VALID_TIME_START, 'YYYYMMDD') "Start",
TO_CHAR(VALID_TIME_END, 'YYYYMMDD') "End"
FROM EMPLOYEES_TEMPORAL_TEST
VERSIONS PERIOD FOR VALID_TIME
BETWEEN TO_DATE('20130217') AND TO_DATE('20130224')
ORDER BY 1;
EMPLOYEE_ID FIRST_NAME LAST_NAME Start End
----------- -------------------- --------------- -------- --------
202 Pat Fay 20130216 20130321
203 Susan Mavris 20130216 20130321
** 참고 (10053 trace file)
("T"."VALID_TIME_START" IS NULL OR
SYS_EXTRACT_UTC("T"."VALID_TIME_START")<=SYS_EXTRACT_UTC(TO_DATE('20130224')))
AND ("T"."VALID_TIME_END" IS NULL OR
SYS_EXTRACT_UTC("T"."VALID_TIME_END")>SYS_EXTRACT_UTC(TO_DATE('20130217')))
** 현재 날짜에 적합한 사원 조회
EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('CURRENT');
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
TO_CHAR(VALID_TIME_START, 'YYYYMMDD') "Start",
TO_CHAR(VALID_TIME_END, 'YYYYMMDD') "End"
FROM EMPLOYEES_TEMPORAL_TEST
EMPLOYEE_ID FIRST_NAME LAST_NAME Start End
----------- -------------------- --------------- -------- --------
204 Hermann Baer 20130420
205 Shelley Higgins 20130420
EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ALL');
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
TO_CHAR(VALID_TIME_START, 'YYYYMMDD') "Start",
TO_CHAR(VALID_TIME_END, 'YYYYMMDD') "End"
FROM EMPLOYEES_TEMPORAL_TEST;
EMPLOYEE_ID FIRST_NAME LAST_NAME Start End
----------- -------------------- --------------- -------- --------
200 Jennifer Whalen 20120101 20130215
201 Michael Hartstein 20120101 20130215
202 Pat Fay 20130216 20130321
203 Susan Mavris 20130216 20130321
204 Hermann Baer 20130420
205 Shelley Higgins 20130420
6 행이 선택되었습니다.
EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ASOF', TO_DATE('20120102'));
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
TO_CHAR(VALID_TIME_START, 'YYYYMMDD') "Start",
TO_CHAR(VALID_TIME_END, 'YYYYMMDD') "End"
FROM EMPLOYEES_TEMPORAL_TEST;
EMPLOYEE_ID FIRST_NAME LAST_NAME Start End
----------- -------------------- --------------- -------- --------
200 Jennifer Whalen 20120101 20130215
201 Michael Hartstein 20120101 20130215
3. 자동 데이터 최정화 (ADO Automatic Data Optimizition )
1. ADO 테이블 이동
- 특정 테이블스페이스로부터 지정된 임계값을 초과하면 다른 테이블 스페이스로 이동
ALTER SYSTEM SET HEAT_MAP = ON SCOPE=BOTH;
SELECT NAME, VALUE FROM V$PARAMETER
WHERE NAME LIKE 'heat%';
NAME VALUE
------------------------------ ------------------------------
heat_map ON
** 테이블 스페이스 생성
CREATE TABLESPACE T1_ILM_TBS
DATAFILE 'C:\ORACLEXE\ORADATA\ORCL\T1_ILM_TBS.DBF'
SIZE 10M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE T2_ILM_TBS
DATAFILE 'C:\ORACLEXE\ORADATA\ORCL\T2_ILM_TBS.DBF'
SIZE 10M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL;
SELECT TABLESPACE_NAME, FILE_NAME
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE '%ILM%';
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
T1_ILM_TBS C:\ORACLEXE\ORADATA\ORCL\T1_ILM_TBS.DBF
T2_ILM_TBS C:\ORACLEXE\ORADATA\ORCL\T2_ILM_TBS.DBF
SELECT DF.TABLESPACE_NAME "TABLESPACE",
DF.BYTES / (1024*1024) "SIZE (MB)",
SUM(FS.BYTES) / (1024*1024) "FREE (MB)",
NVL(ROUND(SUM(FS.BYTES) *100 /DF.BYTES),1) "% FREE",
ROUND((DF.BYTES - SUM(FS.BYTES)) * 100 / DF.BYTES) "% USED"
FROM DBA_FREE_SPACE FS,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) DF
WHERE FS.TABLESPACE_NAME(+) = DF.TABLESPACE_NAME
AND DF.TABLESPACE_NAME LIKE '%ILM%TBS'
GROUP BY DF.TABLESPACE_NAME, DF.BYTES
ORDER BY 1;
TABLESPACE SIZE (MB) FREE (MB) % FREE % USED
------------------------------ ---------- ---------- ---------- ----------
T1_ILM_TBS 10 9 90 10
T2_ILM_TBS 10 9 90 10
ALTER USER HR IDENTIFIED BY HR;
ALTER USER HR ACCOUNT UNLOCK;
/
** 테이블 생성
CREATE TABLE HR.ILM_MOVE_TEST TABLESPACE T1_ILM_TBS
AS SELECT * FROM HR.EMPLOYEES;
** 데이터 생성
DECLARE
STOP_LOOP PLS_INTEGER := 6;
SQL_TEST CLOB;
BEGIN FOR I IN 1 .. STOP_LOOP
LOOP
SQL_TEST := 'INSERT /*+ APPEND */ INTO HR.ILM_MOVE_TEST
SELECT * FROM HR.ILM_MOVE_TEST';
EXECUTE IMMEDIATE SQL_TEST;
COMMIT;
END LOOP;
END;
/
** 통계정보 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','ILM_MOVE_TEST');
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
FROM DBA_TABLES
WHERE OWNER = 'HR' AND TABLE_NAME = 'ILM_MOVE_TEST'
OWNER TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ ------------------------------
HR ILM_MOVE_TEST T1_ILM_TBS
SELECT COUNT(*) FROM HR.ILM_MOVE_TEST;
** SEGMENT_READ_TIME : 세그먼트가 읽기가 수행된 최종 타임 스템프
SELECT OBJECT_NAME, SEGMENT_READ_TIME
FROM DBA_HEAT_MAP_SEGMENT
WHERE OWNER = 'HR'
AND OBJECT_NAME = 'ILM_MOVE_TEST';
** 테이블에 POLICY 추가
ALTER TABLE HR.ILM_MOVE_TEST
ILM ADD POLICY TIER TO T2_ILM_TBS;
SELECT CAST(POLICY_NAME AS VARCHAR2(30)) POLICY_NAME, ACTION_TYPE,
SCOPE, COMPRESSION_LEVEL,
CAST(TIER_TABLESPACE AS VARCHAR2(30)) TIER_TBS,
CONDITION_TYPE, CONDITION_DAYS
FROM DBA_ILMDATAMOVEMENTPOLICIES
ORDER BY POLICY_NAME;
POLIC ACTION_TYPE SCOPE COMPR TIER_TBS CONDITION_TYPE CONDITION_DAYS
----- ----------- ------- ----- ---------------------------------------------------- --------------
P1 STORAGE SEGMENT T2_ILM_TBS 0
SELECT * FROM DBA_ILMOBJECTS;
SELECT * FROM DBA_ILMPARAMETERS
WHERE NAME LIKE 'TBS%PERCENT%';
NAME VALUE
------------------------------ ----------
TBS PERCENT USED 85
TBS PERCENT FREE 25
EXEC DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,95);
EXEC DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_USED,5);
SELECT * FROM DBA_ILMPARAMETERS
WHERE NAME LIKE 'TBS%PERCENT%';
NAME VALUE
------------------------------ ----------
TBS PERCENT USED 5
TBS PERCENT FREE 95
** HR 계정 사용 **
** DBMS_ILM.EXECUTE_ILM 페키지 수행으로 바로 확인 가능
DECLARE
V_EXECUTIONID NUMBER;
BEGIN
DBMS_ILM.EXECUTE_ILM(ILM_SCOPE=>DBMS_ILM.SCOPE_SCHEMA,
EXECUTION_MODE => DBMS_ILM.ILM_EXECUTION_OFFLINE,
TASK_ID => V_EXECUTIONID);
END;
/
SELECT UIT.TASK_ID, UIR.JOB_NAME, UIR.JOB_STATE,
UIT.CREATION_TIME,
UIT.START_TIME,
UIR.COMPLETION_TIME,
UIR.COMPLETION_TIME-UIT.START_TIME
FROM USER_ILMTASKS UIT, USER_ILMRESULTS UIR
WHERE UIT.TASK_ID = UIR.TASK_ID
ORDER BY UIT.CREATION_TIME DESC;
SELECT TABLE_NAME, TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME = 'ILM_MOVE_TEST';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
ILM_MOVE_TEST T2_ILM_TBS
2. ADO Table Compression
CREATE OR REPLACE PROCEDURE ADJ_TIME (OBJECT_ID NUMBER, DATA_OBJECT_ID NUMBER,
N_DAYS NUMBER,
P_TS# NUMBER,
P_SEGMENT_ACCESS NUMBER)
AS
BEGIN
INSERT INTO SYS.HEAT_MAP_STAT$(OBJ#,DATAOBJ#,TRACK_TIME,SEGMENT_ACCESS, TS#)
VALUES (OBJECT_ID, DATA_OBJECT_ID, SYSDATE - N_DAYS, P_SEGMENT_ACCESS, P_TS#);
COMMIT;
END;
/
** HEAT_MAP 활성화
ALTER SYSTEM SET HEAT_MAP =ON SCOPE=BOTH;
** 테이블 생성
CREATE TABLE HR.ILM_COMPRESSION_TEST TABLESPACE USERS
AS SELECT * FROM HR.EMPLOYEES;
DECLARE
STOP_LOOP PLS_INTEGER := 6;
SQL_TEST CLOB;
BEGIN FOR I IN 1 .. STOP_LOOP
LOOP
SQL_TEST := 'INSERT /*+ APPEND */ INTO HR.ILM_COMPRESSION_TEST
SELECT * FROM HR.ILM_COMPRESSION_TEST';
EXECUTE IMMEDIATE SQL_TEST;
COMMIT;
END LOOP;
END;
/
SELECT COUNT(*) FROM HR.ILM_COMPRESSION_TEST
SELECT SEGMENT_NAME, BYTES/1024/1024 MB
FROM DBA_SEGMENTS
WHERE OWNER = 'HR'
AND SEGMENT_NAME = 'ILM_COMPRESSION_TEST';
SEGMENT_NAME MB
-------------------- ----------
ILM_COMPRESSION_TEST .6875
** HEAT_MAP 에 의해서 추적되는 테이블인지 확인
SELECT OBJECT_NAME, FULL_SCAN
FROM DBA_HEAT_MAP_SEGMENT
WHERE OWNER = 'HR'
AND OBJECT_NAME = 'ILM_COMPRESSION_TEST';
OBJECT_NAME FULL_SCA
-------------------- --------
ILM_COMPRESSION_TEST
** ACTIVITY : Low Access, No Access, no midification, creation
ALTER TABLE HR.ILM_COMPRESSION_TEST
ILM ADD POLICY ROW STORE COMPRESS
ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;
DECLARE
V_OBJECT_ID NUMBER;
V_DATAOBJ_ID NUMBER;
V_TS_NUM NUMBER;
BEGIN
SELECT OBJECT_ID, DATA_OBJECT_ID INTO V_OBJECT_ID, V_DATAOBJ_ID
FROM ALL_OBJECTS
WHERE OBJECT_NAME = 'ILM_COMPRESSION_TEST'
AND OWNER = 'HR';
SELECT TS# INTO V_TS_NUM
FROM SYS.TS$ A, DBA_SEGMENTS B
WHERE A.NAME = B.TABLESPACE_NAME
AND B.SEGMENT_NAME = 'ILM_COMPRESSION_TEST';
COMMIT;
SYS.ADJ_TIME
(OBJECT_ID => V_OBJECT_ID,
DATA_OBJECT_ID => V_DATAOBJ_ID,
N_DAYS => 30,
P_TS# => V_TS_NUM,
P_SEGMENT_ACCESS => 1);
END;
/
** 테이블에 압축 여부 확인
SELECT COMPRESSION, COMPRESS_FOR
FROM DBA_TABLES
WHERE OWNER = 'HR'
AND TABLE_NAME = 'ILM_COMPRESSION_TEST';
COMPRESS COMPRESS_FOR
-------- ------------------------------
DISABLED
** 업데이트된 통계정보를 바로 보기 위해서 데이터베이스 재시작
SHUTDOWN IMMEDIATE
STARTUP
SELECT OWNER, OBJECT_NAME, SEGMENT_WRITE_TIME
FROM DBA_HEAT_MAP_SEGMENT
WHERE OBJECT_NAME = 'ILM_COMPRESSION_TEST';
OWNER OBJECT_NAME SEGMENT_
------------------------------ -------------------- --------
HR ILM_COMPRESSION_TEST 16/12/19
DECLARE
V_EXECUTIONID NUMBER;
BEGIN
DBMS_ILM.EXECUTE_ILM(ILM_SCOPE => DBMS_ILM.SCOPE_SCHEMA,
EXECUTION_MODE => DBMS_ILM.ILM_EXECUTION_OFFLINE,
TASK_ID => V_EXECUTIONID);
END;
/
SELECT UIT.TASK_ID, UIR.JOB_NAME, UIR.JOB_STATE,
UIT.CREATION_TIME,
UIT.START_TIME,
UIR.COMPLETION_TIME,
UIR.COMPLETION_TIME-UIT.START_TIME
FROM USER_ILMTASKS UIT, USER_ILMRESULTS UIR
WHERE UIT.TASK_ID = UIR.TASK_ID
ORDER BY UIT.CREATION_TIME DESC;
** 통계정보 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'ILM_COMPRESSION_TEST');
** 압축 여부 확인
SELECT COMPRESSION, COMPRESS_FOR
FROM DBA_TABLES
WHERE TABLE_NAME = 'ILM_COMPRESSION_TEST';
COMPRESS COMPRESS_FOR
-------- ------------------------------
DISABLED ADVANCED
SELECT SEGMENT_NAME, BYTES/1024/1024 MB
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'ILM_COMPRESSION_TEST';
SEGMENT_NAME MB
-------------------- ----------
ILM_COMPRESSION_TEST .5
4. Temporal History
- 특정 테이블에 대한 변경 이력을 저장하는 사이즈와 보존 기간을 정의
참고
After the specified retention period expires, data is automatically purged to conserve space
If an archive quota is exceeded, new transactions are blocked.
1. 테이블 스페이스 생성
create tablespace fda_ts datafile
'/u01/app/oracle/oradata/fda_ts_od.dbf' size 15M;
2. Flashback Archive 생성
CREATE FLASHBACK ARCHIVE DEFAULT
FLA01 TABLESPACE FDA_TS
QUOTA 10G RETENTION 1 YEAR;
3. 테이블 생성
CREATE TABLE HR.TEST123(COL1 VARCHAR2(10), COL2 VARCHAR2(10))
FLASHBACK ARCHIVE FLA01;
5.성능 개선 사항
