'oracle' 태그의 글 목록

'oracle'에 해당되는 글 29건

2009.09.25 | OMF(ORACLE MANAGED FILES) 개념과 사용 예제
2008.10.01 | External Table을 이용 물리적 파일을 처리하는 DML작업 성능개선.
2008.09.25 | [Oracle 10g] UTL_FILE 패키지..
2008.09.25 | [오라클 10g] 플래시백의 설정/관리
2008.09.25 | Oracle10g에서의 Automatic Retention - ORA-1555에러방지
2008.09.25 | Oracle의 메모리 구조 - SGA(DataBase Buffer Cache)
2008.09.25 | [Oracle DataBase 10g] 오라클 데이타베이스 구조
2008.09.24 | ERROR:ORA-12154:TNS:couldnot resolve service name
2007.12.21 | [본문스크랩] PL/SQL (16) - 대용량 오브젝트 타입 - BFILE
2007.12.21 | [본문스크랩] PL/SQL (15) - 대용량 오브젝트 타입 - CLOB, BLOB

OMF(ORACLE MANAGED FILES) 개념과 사용 예제

오라클 | 2009. 9. 25. 11:43

Posted by 시반

OMF(ORACLE MANAGED FILES) 개념과 사용 예제

Space Management와 관련된 Oracle 9i의 새로운 기능은 크게 다음 세 가지이다.

- Oracle Managed Files, Delete Datafiles, Default Temporary Tablespace.

이를 이용하면 개발 중인 혹은 운영 중인 데이터베이스에 대해 DBA의 수고를 덜게 할 수 있다.

Oracle 9i의 새 기능인 OMF에 대하여 개념과 사용 예제를 알아보기로 한다.
DBA가 테이블스페이스를 생성할 때, 혹은 기존의 테이블스페이스에 새로운 데이터 화일을 추가할 때, 실제 OS 화일명(디렉토리 포함)과 크기를 주면 자동으로 해당 화일이 생성된다.
9i의 Oracle Managed Files(OMF) 기능을 이용하면 OS 화일명을 줄 필요도 없다.
즉, 데이터베이스 차원에서 유일한 화일명을 생성하고, 그 이름의 데이터 화일을 생성하는 것이다.
또한 OMF를 이용해 만들어진 데이터 화일의 경우, 나중에 그 데이터 화일이 속한 테이블스페이스를 삭제하면 데이터 화일까지 자동으로 삭제가 된다.

DBA가 필요없는 데이터 화일을 찾아서 OS 상에서 일일이 삭제해야만 하는 수고를 덜어주게 되는 것이다.

1. Oracle Managed Files (OMF)

Overview에서 기술했듯이 OMF를 이용하면 데이터베이스가 사용하는 OS 화일에 대해 DBA가 일일이 관리해야 하는 번거로움을 덜 수 있다. OMF 화일은 Oracle 서버에 의해 자동으로 생성(유일성을 보장하기 위한 Naming Structure 존재)되고 삭제된다. 이 OMF를 이용할 수 있는 화일은 Control File, Online Redo Log File, Data File 등이다.

1) Benefits of Oracle Managed Files

화일과 관련된 DBA 작업이 쉽다. 유일한 화일명이 생성되기 때문에, DBA가 화일명을 잘못 사용할 염려가 없다.
사용되지 않는 데이터 화일이 자동으로 삭제되기 때문에, 사용되지 않는 데이터 화일에 의한 디스크 공간 낭비의 우려가 없다. 테스트 데이터베이스, 개발용 데이터베이스 생성이 단순하다.
SQL 스크립트에 OS의 화일명을 명시하지 않아도 되기 때문에 Portable Application 개발이 용이하다

2) Oracle Managed File Configurations

다음 두 가지의 초기 파라미터를 정의하면 된다.

DB_CREATE_FILE_DEST : data file들이 위치하게 될 기본 디렉토리
DB_CREATE_ONLINE_LOG_DEST_n : online redo log와 control file들이 위치하게 될 기본 디렉토리 (n : 1과 5 사이의 정수 - for multiplexing)

#1 : 위 두 파라미터는 ALTER SYSTEM/SESSION에 의해 동적으로 정의/변경될 수 있다.
#2 : DB_CREATE_FILE_DEST 파라미터는 정의되어 있지만

DB_CREATE_ONLINE_LOG_DEST_n 파라미터가 정의되어 있지 않으면, 모든 화일들(Data Files, Control Files, Online Redo Logs)은 DB_CREATE_FILE_DEST 파라미터에 정의된 디렉토리에 존재하게 된다.

3) Oracle Managed File Name Structure

OMF 화일은 Oracle Flexible Architecture(OFA)를 따르며 플랫폼에 의존적이다.
아래는 Solaris 환경의 OMF Naming Structure의 예이다.

Control Files : ora_%u.ctl
Redo Log Files : ora_%g_%u.log
Data Files : ora_%t_%u.dbf
Temporary Data Files : ora_%t_%u.tmp

#1 : %u is an 8 character string that guarantees uniqueness.
     %t is the tablespace name, truncated if necessary.
     %g is the redo log file group number.
     ora_ identifies the file as an Oracle Managed File.

#2 : 위 Naming Structure를 따르지 않으면 OMF로 인식하지 않는다.
즉, 위 Naming Structure를 따르는 화일을(OMF로 생성) 추후 이 structure 를 따르지 않는 화일 이름으로 rename하게 되면 OMF 기능을 사용하지 못한다.
별 다른 옵션 없이 DROP TABLESPACE를 수행하면 해당 데이터 화일의 OS 화일이 자동으로 삭제되지는 않는다.

#3: 파일 시스템 구성시 Oracle Flexible Architecture (OFA) 의 장점은 다음과 같습니다

     A) 향상된 파일 시스템 구성과 간편한 관리
     B) 확장성 향상 : 간편한 데이타베이스 파일 추가
     C) 성능 향상 : 여러 개의 디스크 드라이버에 I/O 분산
     D) 데이타 보호 : 여러 개의 디스크에 데이타 분산

4) Managing Control Files with OMF

데이터베이스 생성 시 CONTROL_FILES 초기 파라미터를 정의하지 않으면 DB_CREATE_ONLINE_LOG_DEST_n에 정의된 디렉토리에 Control File이 기본적으로 생성된다(OMF).
Control File 이름은 유일하게 생성되며 이 결과는 alert.log에 기록이 된다.
데이터베이스 생성 후에는 CONTROL_FILES 초기 파라미터에 생성된 Control File 이름을 정의해주어야 한다.

5) Managing Online Redo Log Files with OMF

Redo Log File specification 없이 아래와 같이 전체 Redo Log Group을 생성할 수 있다.

SQL> ALTER DATABASE ADD LOGFILE;

#1 : 이 경우 해당 redo log file은 DB_CREATE_ONLINE_LOG_DEST_n에 정의된 디렉토리에 생성된다.

만일 이 파라미터 값이 정의되지 않았으면 DB_CREATE_FILE_DEST 디렉토리에 생성된다.

특정 Redo Log Group을 삭제할 때 관련 OMF 화일들은 OS 레벨에서 자동으로 삭제된다.

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

#2 : Archived Log File은 OMF일 수 없다.

6) Managing Tablespaces with OMF

아래와 같이 테이블스페이스를 생성하면 DB_CREATE_FILE_DEST에 정의된 디렉토리에 크기가 100M인 데이터 화일이 자동으로 생성된다.이 데이터 화일의 이름은 Naming Structure에 의해 유일하게 만들어진다. (Autoextend On, Unlimited Restriction)

SQL> CREATE TABLESPACE TBS1;

데이터 화일의 기본 값 이외의 화일을 생성하려면 다음과 같이 하면 된다.

SQL> CREATE TABLESPACE TBS1 DATAFILE SIZE 400M AUTOEXTEND OFF;

기존 테이블스페이스에 데이터 화일을 추가하려면 다음과 같이 하면 된다.

SQL> ALTER TABLESPACE TBS1 ADD DATAFILE;

아래와 같이 테이블스페이스를 삭제하면 관련 데이터 화일(OMF)들도 OS 레벨에서 삭제된다.

SQL> DROP TABLESPACE TBS1;

#1 : DB_CREATE_FILE_DEST의 값은 다음과 같이 동적으로 변경할 수 있다.

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/disk2/oradata/';

세션 단위의 변경은 ALTER SESSION 구문을 사용하면 된다. 하지만 이와 같은 변경이 이미 생성되어져 있는 데이터 화일의 위치를 바꾸는 것은 아니며, 다만 새로 생성되는 데이터 화일에만 영향이 있다. OMF 기능을 사용하지 않으려면 다음과 같이 하면 된다.

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = '';

Example

테스트 1) OMF를 이용한 테이블스페이스 생성/삭제 및 이에 대한 확인
------------------------------------------------------------------
As SYSTEM
DB_CREATE_FILE_DEST 파라미터 값 정의

SQL> alter session set db_create_file_dest='/d02/ora9i/oracle/dbs/ora9i/tbs_omf';

- OMF를 이용한 테이블스페이스 생성 : 크기가 10M인 두 개의 데이터 화일 자동 생성

SQL> host ls -l /d02/ora9i/oracle/dbs/ora9i/tbs_omf
total 0

SQL> create tablespace tbs_4 datafile size 10M, size 10M;

SQL> host ls -l /d02/ora9i/oracle/dbs/ora9i/tbs_omf
total 41024
-rw-r----- 1 ora9i dba 10489856 Mar 12 17:10 ora_tbs_4_xbs141rr.dbf
-rw-r----- 1 ora9i dba 10489856 Mar 12 17:10 ora_tbs_4_xbs142xv.dbf

- 해당 테이블스페이스에 데이터 화일 추가

SQL> alter tablespace tbs_4 add datafile size 10M;

SQL> host ls -l /d02/ora9i/oracle/dbs/ora9i/tbs_omf
total 61536
-rw-r----- 1 ora9i dba 10489856 Mar 12 17:10 ora_tbs_4_xbs141rr.dbf
-rw-r----- 1 ora9i dba 10489856 Mar 12 17:10 ora_tbs_4_xbs142xv.dbf
-rw-r----- 1 ora9i dba 10489856 Mar 12 17:11 ora_tbs_4_xbs156ff.dbf

- 해당 테이블스페이스 삭제 : 이 테이블스페이스의 모든 OMF 데이터 화일 자동 삭제

SQL> drop tablespace tbs_4;

SQL> host ls -l /d02/ora9i/oracle/dbs/ora9i/tbs_omf
total 0

테스트 2) OMF를 이용한 UNDO Tablespace 생성/삭제 및 이에 대한 확인
------------------------------------------------------------------
As SYSTEM
DB_CREATE_FILE_DEST 파라미터 값 정의

SQL> alter session set db_create_file_dest='/d02/ora9i/oracle/dbs/ora9i/tbs_omf';

- OMF를 이용한 UNDO 테이블스페이스 생성 : 크기가 10M인 데이터 화일 자동 생성

SQL> create undo tablespace undotbs_1 datafile size 10M;

SQL> host ls -l /d02/ora9i/oracle/dbs/ora9i/tbs_omf
total 20512
-rw-r----- 1 ora9i dba 10489856 Mar 12 17:12 ora_undotbs__xbs17brr.dbf

- 해당 테이블스페이스에 데이터 화일 추가

SQL> alter tablespace undotbs_1 add datafile size 10M;

SQL> host ls -l /d02/ora9i/oracle/dbs/ora9i/tbs_omf
total 41024
-rw-r----- 1 ora9i dba 10489856 Mar 12 17:12 ora_undotbs__xbs17brr.dbf
-rw-r----- 1 ora9i dba 10489856 Mar 12 17:13 ora_undotbs__xbs19hr8.dbf

- 해당 테이블스페이스 삭제 : 이 테이블스페이스의 모든 OMF 데이터 화일 자동 삭제

SQL> drop tablespace undotbs_1;

SQL> host ls -l /d02/ora9i/oracle/dbs/ora9i/tbs_omf
total 0

테스트 3) OMF를 이용한 Temporary Tablespace 생성/삭제 및 이에 대한 확인
------------------------------------------------------------------------
As SYSTEM
DB_CREATE_FILE_DEST 파라미터 값 정의

SQL>alter session set db_create_file_dest ='/d02/ora9i/oracle/dbs/ora9i/tbs_omf';

- OMF를 이용한 Locally Managed Temporary 테이블스페이스 생성 : 크기가 10M인
데이터 화일 자동 생성

SQL>create temporary tablespace omf_temp tempfile size 10M extent
management local;

SQL> host ls -l /d02/ora9i/oracle/dbs/ora9i/tbs_omf
total 176
-rw-r----- 1 ora9i dba 10489856 Mar 12 17:14 ora_omf_temp_xbs1cqrv.tmp

- 해당 테이블스페이스에 temp file 추가

SQL> alter tablespace omf_temp add tempfile size 10M;

SQL> host ls -l /d02/ora9i/oracle/dbs/ora9i/tbs_omf
total 352
-rw-r----- 1 ora9i dba 10489856 Mar 12 17:14 ora_omf_temp_xbs1cqrv.tmp
-rw-r----- 1 ora9i dba 10489856 Mar 12 17:15 ora_omf_temp_xbs1d9xp.tmp

- 해당 테이블스페이스 삭제 : 이 테이블스페이스의 모든 OMF 데이터 화일 자동 삭제

SQL> drop tablespace omf_temp;

SQL> host ls -l /d02/ora9i/oracle/dbs/ora9i/tbs_omf
total 0

테스트 4) OMF를 이용한 Online Redo Log 생성/삭제 및 이에 대한 확인
------------------------------------------------------------------

As SYSTEM
DB_CREATE_ONLINE_LOG_DEST_n 파라미터 값 정의

SQL> alter session set db_create_online_log_dest_1 =
'/d02/ora9i/oracle/oradata/ora9i/redo_omf';

- OMF를 이용한 새로운 Online Redo Log Group 생성 : 크기가 10M인 Online Redo
Log File 자동 생성

SQL> host ls -l /d02/ora9i/oracle/oradata/ora9i/redo_omf
total 0

SQL> select group#, member from v$logfile;
GROUP# MEMBER

3 /d02/ora9i/oracle/oradata/ora9i/redo03.log
2 /d02/ora9i/oracle/oradata/ora9i/redo02.log
1 /d02/ora9i/oracle/oradata/ora9i/redo01.log

SQL> alter database add logfile size 10M;

SQL> host ls -l /d02/ora9i/oracle/oradata/ora9i/redo_omf
total 20512
-rw-r----- 1 ora9i dba 10486272 Mar 12 17:15 ora_4_xbs1fv2r.log

SQL> select group#, member from v$logfile;
GROUP# MEMBER

3 /d02/ora9i/oracle/oradata/ora9i/redo03.log
2 /d02/ora9i/oracle/oradata/ora9i/redo02.log
1 /d02/ora9i/oracle/oradata/ora9i/redo01.log
4 /d02/ora9i/oracle/oradata/ora9i/redo_omf/ora_4_xbs1fv2r.log

- 해당 Online Redo Log Group 삭제 : 이 Redo Log Group의 모든 OMF Redo Log Member 화일 자동 삭제

SQL> alter database drop logfile group 4;

SQL> host ls -l /d02/ora9i/oracle/oradata/ora9i/redo_omf
total 0

SQL> select group#, member from v$logfile;
GROUP# MEMBER
------------------------------------------

3 /d02/ora9i/oracle/oradata/ora9i/redo03.log
2 /d02/ora9i/oracle/oradata/ora9i/redo02.log
1 /d02/ora9i/oracle/oradata/ora9i/redo01.log

'오라클' 카테고리의 다른 글

External Table을 이용 물리적 파일을 처리하는 DML작업 성능개선. (0)	2008.10.01
[Oracle 10g] UTL_FILE 패키지.. (0)	2008.09.25
[오라클 10g] 플래시백의 설정/관리 (0)	2008.09.25
Oracle10g에서의 Automatic Retention - ORA-1555에러방지 (0)	2008.09.25
Oracle의 메모리 구조 - SGA(DataBase Buffer Cache) (0)	2008.09.25

External Table을 이용 물리적 파일을 처리하는 DML작업 성능개선.

오라클 | 2008. 10. 1. 16:50

Posted by 시반

일반적으로, OLTP 프로그램(온라인)에 대한 튜닝을 진행하는데 중요한 성능개선방법은 국내에 나와있는 많은 책들에서 이미 소개된 것과 같이

- 인덱스에 대한 재조정

- SQL 재 작성

- 힌트 조정

- 페이징 기법

- Memory Keep

- Parameter 조정

- 기타

등을 활용하여 프로그램의 일량을 최소화 할 수 있도록 효율적인 Access Path로 유도하는 것이 중요한 성능개선 방법이 될 것이다. 온라인 프로그램과 마찬가지로 배치 프로그램에서도 물론 위의 개선방법들을 많이 사용을 하게 된다.

하지만, 배치 프로그램을 계속 진행하면서 익히 알고 있는 다양한 성능개선방법으로만 튜닝을 진행 하는 것은 뭔가 알 수 없는 벽을 만난듯한 그런 느낌을 많이 받았다. 그런 벽의 존재가 기존에 일반적으로 널리 알려진 성능개선방법과 오라클에서 제안하는 다양한 신기술들이 접목이 되었을 때 배치 프로그램에 대한 성능개선이 좀더 잘 이뤄진다라는 그런 사실이 날 가로막고 있었던 거다.

오라클 제품이 버전 업그레이드 되면서 새롭게 등장하는 많은 신기술, 신기능들에 대한 지식이 필요하다라는 생각이확~ 들게 했던 계기가 있었는데

물리 파일을 읽어 조회작업과 트랜잭션 처리(UPDATE)를 수행하는 프로그램 중 성능문제가 심각한 프로그램이 있어 이를 해결하기 위해 조치를 취한 방법으로 배치 프로그램(or DW) 튜닝을 지속적으로 해 오셨던 분들은 모두들 알고 있는 기능이겠지만 실제 적용을 해보지 않았던 분들에게는 생소한 기능들 중(나 역시도 한번도 접하지 않고 기능이 나온 지만 알고 있었던 ) 오라클이 9i 에서 첨 소개한 External Table이 있다. External Table에 대한 정보를 찾고 적용하게 된 것은 아래의 그림과 같은 로직으로 수행이 되는 배치 프로그램이 있었고, 해당 프로그램은 수납관리 프로그램 중 아주 중요한 역할을 수행하고 수행시간이 아주 중요한 배치 프로그램으로 성능개선이 꼭 되어야 하는 핵심 프로그램 중 하나의 내용이다.

위의 배치 프로그램의 로직을 보면, 물리 파일에서 최대 300만 건을 한 건씩 읽어, 300만 건에 대한 한 건씩

UPDATE를 수행하는 프로그램으로 최대 300만 건 수행하는데 3~4시간이 소요되어 수행시간이 길었으며, 고객의 요구는 최대 30분 이내에 처리를 해달라는 것 이여서 상당히 고민에 빠지게 하는 배치 프로그램 이었다.

오라클 9i 이전에는 물리 파일에 있는 내용을 읽어 처리하는 조회작업이나 DML 작업(트랜잭션 처리)의

경우는 Temp(임시 처리용) Table을 생성하고 SQL*Loader를 이용하여 DB에 Insert 한 이후 배치 처리하는

방법이 있을 것이고, 물리 파일(업무적인 병렬처리)을 읽어 배치 프로그램 로직을 수행(조회,DML작업)하는 방법이 일반적인 방법일 것이라 생각이 들었다.

그렇지만, 이 방법들은 식빵에 쨈을 발라서 먹지 않을 때 혹은 저녁을 먹을 때 반찬 중에 김치가 빠져 있을 때 느끼는… 뭔가 허전하고 뭔가 중요한 것이 빠져있는 느낌이 상당히 많이 들게 되었고 또한, 30분 이내에 배치 프로그램의 수행이 완벽히 끝낼 수 있을지 의문이 들었고 기존에 사용되던 방식들을 이용하여 30분 이내에 처리가 되더라도 업무적인 병렬 처리 등등의 처리를 활용하게 되면 프로세스간의 경합과 비효율적인 서버 자원을 사용하게 될 가능성이 많게 되고, 프로그램 수행을 하기 위한 관리적인 요소가 별도로 들어가야 할 것으로 판단되어, 오라클 DW 성능개선 방법들을 오라클 매뉴얼등등을 활용하여 문제의 배치 프로그램의 성능개선을 위한 좋은 방법이 있는지도 꼼꼼히 훑어보기 시작했다.

그러던 와중 “앗~ 이것이다.” 라는 기능이 있는 것이었다.
그것도 Oracle 9i에서 나온 기능 중에.

그것이 바로, External Table 이었다.

External Table은 물리 파일의 처리에 대한 문제점을 해결하기 위해서 Oracle 9i에서 새로 나온 기능으로 문제의 배치 프로그램을 External Table + Merge Into ~ 구문을 활용하면 아주 괜찮은 성능개선이 될 것이라는 확신이 생겨났다. 또한, 이곳 ORACLE 버전이 10.2.0.3으로 10g부터는 Merge Into ~ 구문도 Upsert (UPDATE+INSERT)으로 작성하지 않고 UPDATE문만 처리하는 것도 가능하기에 문제의 배치 프로그램의 성능 개선방법으로 걸림돌이 되지는 않았다. External Table과 Merge Into~구문을 활용하여 배치 프로그램을 개선방법을 적용하여 아래와 같이 개선이 되었다.

물리 파일의 300만 건의 읽는 작업은 External Table을 활용하여 한번 읽는 작업으로 처리를 하고, 300만 건의 UPDATE 처리는 Merge Into ~ 구문에서 PARALLEL DML 처리로 하여 적용하였고, 개선 전에 300만 건 처리 시 3~4시간 소요되는 것이 적용 후 최대 15분 이내에는 처리가 완료되는 것을 확인할 수 있었다.

적용했던 사례는 아래와 같다.

먼저, 물리 파일을 읽기 위하여 UTL DIRECTORY에 물리 파일이 있는 DIRECTORY를 UTL_FILE_DIR 파라미터에 설정을 한다.

STEP1. utl directory에 위치시키기

SQL> show parameter utl

NAME TYPE VALUE

-------------------------- ----------- --------------------------------------------------------

create_stored_outlines string

utl_file_dir string /usr/tmp, /usr/tmp, /app/oracle/product/10.
2.0/db/appsutil/outbound/NDB2_cdb4, /usr/tmp

: /home1/var/lll/projs/if/work 폴더(물리적 파일위치) UTL_FILE_DIR 파라미터에 추가하여야 함.

그리고, 물리 파일을 읽어 들일 External Table을 생성한다.

STEP2. External Table 생성하기

- Directory 지정

CREATE OR REPLACE DIRECTORY dat_dir AS '/home1/var/lll/projs/if/work ';

CREATE OR REPLACE DIRECTORY log_dir AS '/home1/var/lll/projs/if/work ';

CREATE OR REPLACE DIRECTORY bad_dir AS '/home1/var/lll/projs/if/work ';

CREATE OR REPLACE DIRECTORY dat_dir AS '/usr/tmp';

CREATE OR REPLACE DIRECTORY log_dir AS '/usr/tmp';

CREATE OR REPLACE DIRECTORY bad_dir AS '/usr/tmp';

GRANT READ ON DIRECTORY dat_dir TO 배치유저; à 프로그램 수행 유저

GRANT WRITE ON DIRECTORY log_dir TO 배치유저;

GRANT WRITE ON DIRECTORY bad_dir TO 배치유저;

- External Table 생성하기

CREATE TABLE TB_PAYMENT_UPDATE (

payment_data varchar2(180)

) ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY dat_dir

ACCESS PARAMETERS

(

records delimited by newline

badfile bad_dir:'ext_test%ar_%p.bad

logfile log_dir:'ext_test%ar_%p.log'

fields terminated by ','

missing field values are null

(

payment_data

)

LOCATION ('payment_update.dat')

)

PARALLEL 4

REJECT LIMIT UNLIMITED ;

ALTER TABLE 배치유저. TB_PAYMENT_UPDATE NOPARALLEL

/home1/var/lll/projs/if/work/payment_update.dat(물리 파일)을 읽을 준비는 다 되었다.

위에서 물리파일의 하나의 Row가 컬럼 구분자 없이 구성이 되어 있어, 물리 파일을 TB_PAYMENT_UPDATE라는

External Table로 불러들이게 되는데, 물리 파일의 한 Row(한줄)을 Payment_data 라는 하나의 Column으로 매칭을

시켜서 데이터를 가져와서 원하는 데이터를 자리수로 계산하여 잘라 필요한 데이터를 사용하게 된다. 물리 파일을 읽고,

물리 파일의 데이터를 업데이트하는 로직을 Merge Into ~ 구문을 활용하여 처리를 변경하여 최종 반영하게 된다.

STEP3. SQL 변경하기

개선 전 :

- FILE READ : MAX 300만건 à /home1/var/lll/projs/if/work/payment_update.dat (물리 파일)

- UPDATE STATEMENT 수행 : MAX 300만번 수행됨.

UPDATE TB_PAYMENT SET

APPLICATION_ID = :sql_application_id,

WDRW_IMPSB_RSN_CD = :sql_wdrw_impsb_rsn_cd,

ATST_RETN_AMT = to_number(:atst_retn_amt2),

SYS_UPDATE_DATE = SYSDATE

WHERE WORK_YYMM BETWEEN TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')

AND TO_CHAR(SYSDATE,'YYYYMM')

AND BILL_ACNT_GRP_NO = MOD(:sql_bill_acnt_no,20) + 1

AND BILL_ACNT_NO = :sql_bill_acnt_no

AND ATST_BILL_SEQNO = to_number(:atst_bill_seqno2)

AND FILE_PRSS_NO = :sql_file_prss_no

개선 후 :

- FILE READ : 테이블 두번 FTS로 해석 à Merge Into ~

- UPDATE : Merge Into

~

MERGE /+ USE_HASH(T B) / INTO TB_PAYMENT T

USING ( SELECT /+ LEADING(H P U) USE_HASH(H P U) FULL(H) FULL(P) FULL(U) / à File 읽기

P.WORK_YYMM ,

P.BILL_ACNT_GRP_NO,

P.BILL_ACNT_NO ,

H.FILE_PRSS_NO ,

P.ATST_BILL_SEQNO ,

P.WORK_DV_CD ,

U.WDRW_IMPSB_RSN_CD,

TO_NUMBER(U.ATST_RETN_AMT) ATST_RETN_AMT

FROM TB_PAYMENT P,

(

SELECT SUBSTR(PAYMENT_DATA,36,9) BILL_ACNT_NO, à 파일에서 해당 데이터 위치

SUBSTR(PAYMENT _DATA,45,10) ATST_BILL_SEQNO,

SUBSTR(PAYMENT _DATA,9,11) ATST_RETN_AMT,

SUBSTR(PAYMENT _DATA,3,6) WDRW_IMPSB_RSN_CD

FROM TB_PAYMENT_UPDATE

WHERE PYM_DATA LIKE 'RD%' ) U,

( SELECT SUBSTR(PYM_DATA,24,5) FILE_PRSS_NO

FROM TB_PAYMENT_UPDATE

WHERE PYM_DATA LIKE 'RH%'

) H

WHERE P.WORK_YYMM BETWEEN TO_CHAR(ADD_MONTHS(SYSDATE,-1),‘

YYYYMM') AND TO_CHAR(SYSDATE,'YYYYMM')

AND P.BILL_ACNT_GRP_NO = MOD(U.BILL_ACNT_NO,20) + 1

AND P.BILL_ACNT_NO = U.BILL_ACNT_NO

AND P.ATST_BILL_SEQNO = U.ATST_BILL_SEQNO

AND P.FILE_PRSS_NO = H.FILE_PRSS_NO

) B

ON ( -- PK로 해당건만 UPDATE -> ROWID 비교도 가능함.

T.WORK_YYMM = B.WORK_YYMM

AND T.BILL_ACNT_GRP_NO = B.BILL_ACNT_GRP_NO

AND T.BILL_ACNT_NO = B.BILL_ACNT_NO

AND T.FILE_PRSS_NO = B.FILE_PRSS_NO

AND T.ATST_BILL_SEQNO = B.ATST_BILL_SEQNO

AND T.WORK_DV_CD = B.WORK_DV_CD

)

WHEN MATCHED THEN

UPDATE SET T.APPLICATION_ID = :sql_application_id,

T.WDRW_IMPSB_RSN_CD = B.WDRW_IMPSB_RSN_CD ,

T.ATST_RETN_AMT = B.ATST_RETN_AMT,

T.SYS_UPDATE_DATE = SYSDATE

개선내용확인하기 - SELECT 처리 TEST 결과 (FILE READ -> UPDATE 대상 선별)

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.06 0.05 0 663 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 68321 38.02 44.25 0 95293 0 1024792

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 68323 38.08 44.31 0 95956 0 1024792

Misses in library cache during parse: 1

Optimizer mode: FIRST_ROWS

Parsing user id: 427

Rows Row Source Operation

------- ---------------------------------------------------

1024792 FILTER (cr=96701 pr=0 pw=0 time=40230686 us)

1024792 HASH JOIN (cr=96701 pr=0 pw=0 time=40230278 us)

1024792 EXTERNAL TABLE ACCESS FULL TB_PAYMENT_UPDATE (cr=704 pr=0 pw=0 time=7209313 us)

1194024 HASH JOIN (cr=95997 pr=0 pw=0 time=18037752 us)

1 EXTERNAL TABLE ACCESS FULL TB_PAYMENT_UPDATE (cr=704 pr=0 pw=0 time=7288229 us)

1194024 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=95293 pr=0 pw=0 time=8359986 us)

1194024 PARTITION LIST ALL PARTITION: 1 20 (cr=95293 pr=0 pw=0 time=7165527 us)

1194024 TABLE ACCESS FULL TB_PAYMENT PARTITION: KEY KEY (cr=95293 pr=0 pw=0 time=7168107 us)

외부 성능개선 컨설팅 중 발생했던 배치 프로그램의 문제를 접근하는 방향이 익히 알고 있는 성능개선 방법뿐만 아니고 잘 사용되지 않는 오라클 기술들을 잘 활용하면 문제 접근이 의외로 쉬울 수 있고, 성능개선 효과 또한 아주 큰 경우가 많이 있을 것으로 각된다. 오라클 버전 업그레이드 되고, 오라클이 신기술(New Feature)을 발표하면 이러한 기술들을 최소한 그 기능이 어떻게 활용이 되면 좋을지 그리고, 향후 적용할 수 있는 영역이 어딘지 등등에 대해 고민하는 것도 아주 좋을 것같다.

'오라클' 카테고리의 다른 글

OMF(ORACLE MANAGED FILES) 개념과 사용 예제 (0)	2009.09.25
[Oracle 10g] UTL_FILE 패키지.. (0)	2008.09.25
[오라클 10g] 플래시백의 설정/관리 (0)	2008.09.25
Oracle10g에서의 Automatic Retention - ORA-1555에러방지 (0)	2008.09.25
Oracle의 메모리 구조 - SGA(DataBase Buffer Cache) (0)	2008.09.25

[Oracle 10g] UTL_FILE 패키지..

오라클 | 2008. 9. 25. 19:41

Posted by 시반

◆ UTL_FILE Package

UTL_FILE 패키지를 사용하여 PL/SQL에서 파일의 입출력을 수행 할 수 있다.
PL/SQL에는 SQL*Plus의 SPOOL에 해당하는 간략화된 명령어가 없으므로 큰 로그를 출력하고 싶은 경우에는 UTL_FILE 패키지를 사용하든지 혹은 테이블을 사용한다.

표준 출력에는 DBMS_OUTPUT 패키지를 사용한다.

Oracle 8i이전이라면 초기화 파라미터 UTL_FILE_DIR를 설정해야 한다

프로그램의 Input과 output으로서, 서버OS 상의 텍스트파일을 사용하는 것으로,

PL/SQL프로그램 이외의 프로그램 정보를 뿌려주는것이 가능합니다.

UTL_FILE 패키지는 텍스트파일의 Open, Close, Read, Write의 기능을 제공합니다.

Oracle7.3부터 OS파일에 대한 쓰기,읽기를 지원하였으며

Oracle9iR2 부터 파일의 복사, 이동, 삭제 및 디렉토리 오브젝트의 이용도 가능해졌습니다.

주의사항

안전을 보증하기 위해서, 파일에 접근하려면 초기화 파라메타 파일 UTL_FILE_DIR에

접근대상의 폴더(디렉토리)를 지정해 둘 필요가 있습니다.

개행코드는 자동부가/삭제됩니다.

- 한개의 행은 최대 1024바이트(개행코드 포함)가 될 수 있습니다.

- Oracle8.0.5 부터 32767바이트(개행코드 포함) 까지 가능합니다.

◈FOPEN 프로시져로 크기를 지정 할 수 있습니다.

◆ UTL_FILE_DIR 편

.인스턴스를 시작한 OS 사용자가 지정한 디렉토리를 의미하며 파일에 대해서 OS레벨으로 I/O를 허가할 필요가 있다. 또한 모든 디렉토리를 대상으로 하기 위해 「UTL_FILE_DIR = *」으로 지정하는 경우 보안에 취약할 수 있으므로 되도록 특정위치를 지정하도록 한다.

예) UTL_FILE_DIR = C:\TEMP

오라클 패키지중. dbms_logminer_d 라는 패키지를 이용하여 오라클 dictionary를 파일로 받으려는 경우 파일생성 위치를 패키지에서 utl_file_dir에 설정된 경로내에서만 지정할 수 있게 된다.

복수의 디렉토리를 지정할 경우는, 사이에 다른 파라미터가 들어오지 않고 UTL_FILE_DIR 을 복수행 지정합니다.

예)UTL_FILE_DIR = C:\TEMP

UTL_FILE_DIR = C:\TEST

서로 다른 파라미터가 들어 있는 경우 맨 뒤에 지정한 경로만을 지정한 것으로 처리된다.

준비작업

■ 디렉토리 작성

디렉토리를 작성할 경우에 OS 의 DBA 인 유저(통상은 oracle)로 디렉토리를 작성한다.

root 로 작성하는 경우에는, chown, chgrp, chmod 등에 의해 읽고 쓰기의 권한을 올바르게 설정한다.

파일의 I/O는 서버 프로세스에 의해서 행해지므로 그 프로세스의 오너(oracle)가 읽고 쓰기할 수 있어야 한다.

작성과 권한의 설정예 (root 에 의한 조작:권한만 있으면 root 일 필요는 없습니다)

# mkdir /u05/file_storage/recv_dir

# mkdir /u05/file_storage/send_dir

# chgrp dba /u05/file_storage/recv_dir

# chgrp dba /u05/file_storage/send_dir

# chown oracle /u05/file_storage/recv_dir

# chown oracle /u05/file_storage/send_dir

# chmod 700 /u05/file_storage/recv_dir

# chmod 700 /u05/file_storage/send_dir

디렉토리·오브젝트에 의한 파일 액세스

■ 디렉토리의 작성 CRAETE DIRECTORY

Oracle 9i 버젼부터 UTL_FILE 패키지가 CREATE DIRECTORY에 대응하게 되었다.
디렉토리의 추가에 따르는 재기동도 불필요.

디렉토리 오브젝트의 작성
DIRECTORY의 작성은 CREATE DIRECTORY 권한이 필요

CREATE DIRECTORY recv_area AS '/u05/file_storage/recv_dir';
CREATE DIRECTORY send_area AS '/u05/file_storage/send_dir';

디렉토리에의 액세스권의 설정
읽기 권한과 쓰기 권한은 개별적으로 처리한다.

        GRANT READ ON DIRECTORY recv_area TO user_name ;
        GRANT WRITE ON DIRECTORY send_area TO user_name ;
         --
         SELECT * FROM ALL_DIRECTORIES ;

■ 파일쓰기 (초기화 파라메터 사용시 )

CREATE OR REPLACE PROCEDURE RIVUS.CREATE_DIR_WRITE_SAMPLE

AS

vHandle   UTL_FILE.FILE_TYPE;

vDirname  VARCHAR2(250);

vFilename VARCHAR2(250);

vOutput   VARCHAR2(32767);

BEGIN

vDirname  := 'SEND_AREA'; -- (주)디렉토리 오브젝트명을 대문자로 지정한다

vFilename := 'test.txt';

vHandle   := UTL_FILE.FOPEN(vDirname ,vFilename,'w', 32767);

  vOutput   := 'CREATE DIRECTORY 경유로의 파일출력';

UTL_FILE.PUT_LINE(vHandle, vOutput);

UTL_FILE.FCLOSE(vHandle);

EXCEPTION WHEN OTHERS THEN

UTL_FILE.FCLOSE_ALL;

RAISE;

END;

/

■ 파일읽기 (초기화 파라메터 사용시 )

CREATE OR REPLACE PROCEDURE RIVUS.CREATE_DIR_READ_SAMPLE

AS

vHandle   UTL_FILE.FILE_TYPE;

vDirname VARCHAR2(250);

vFilename VARCHAR2(250);

vInput    VARCHAR2(32767);

BEGIN

vDirname := 'RECV_AREA';

vFilename := 'test.txt';

vHandle   := UTL_FILE.FOPEN(vDirname ,vFilename,'r', 32767);

BEGIN

    LOOP

      UTL_FILE.GET_LINE(vHandle, vInput,32767);

      DBMS_OUTPUT.PUT_LINE(vInput);

    END LOOP;

EXCEPTION WHEN NO_DATA_FOUND THEN

    DBMS_OUTPUT.PUT_LINE('파일의 마지막');

END;

UTL_FILE.FCLOSE(vHandle);

EXCEPTION WHEN OTHERS THEN

UTL_FILE.FCLOSE_ALL; RAISE;

END;

/

주의사항

UTL_FILE 패키지를 사용해 한 번에 입출력할 수 있는 길이는 32767(32K) 바이트 (※)
OPEN 한 파일은 반드시 CLOSE 하도록 예외 처리를 해둔다.UTL_FILE.FCLOSE_ALL 를 사용하면 편리
RAW형을 출력할 수 있지만 줄 끝에는 OS 고유의 개행 코드가 반드시 부여된다.

Oracle 10g 에서는 wb 에 의한 (RAW 모드) FOPEN 가 서포트되고 있으므로, 그 쪽을 사용하면 문제 없다.

이 제한에 있어서 Oracle 10g 이후가 아니면 순수한 바이너리필드를 사용할수 없다.

(※) 한 번의 기입으로 32KB 를 넘을 수 없지만, RAW 모드로의 기입에 대해서는 여러 차례에 기입을 분할하는 것으로 1행이 32KB 를 초과하는 것이 가능하다

◆DIRECTORY 편

UTL_FILE 패키지를 사용해 PL/SQL에서 파일의 입출력을 실시할 수 있다.Oracle 9i 이후부터는 CREATE DIRECTORY 를 사용하여 유저 단위, 읽어들이기, 쓰기의 제한이 가능하게 되었다. 또한 디렉토리를 추가하고 싶은 경우에도 Oracle 의 재기동이 필요하지 않으므로 다운 타임을 줄일 수 있다

준비작업

■ 디렉토리 작성

디렉토리 파일의 작성 (UTL_FILE_DIR 편과 같다)

디렉토리를 작성할 경우에 OS 의 DBA 인 유저(통상은 oracle)로 디렉토리를 작성한다.

내용은 생략 ⇒ UTL_FILE 의 사용법 (UTL_FILE_DIR 편)의 준비작업을 참조

디렉토리·오브젝트에 의한 파일 액세스

■ 디렉토리의 작성 CRAETE DIRECTORY

Oracle 9i 버젼부터 UTL_FILE 패키지가 CREATE DIRECTORY 에 대응하게 되었다.
디렉토리의 추가에 따르는 재기동도 불필요.

디렉토리 오브젝트의 작성
DIRECTORY의 작성은 CREATE DIRECTORY 권한이 필요

CREATE DIRECTORY recv_area AS '/u05/file_storage/recv_dir';
CREATE DIRECTORY send_area AS '/u05/file_storage/send_dir';

디렉토리에의 액세스권의 설정
읽기 권한과 쓰기 권한은 개별적으로 처리한다.

        GRANT READ ON DIRECTORY recv_area TO user_name ;
        GRANT WRITE ON DIRECTORY send_area TO user_name ;
         --
         SELECT * FROM ALL_DIRECTORIES ;

■ 파일쓰기 (초기화 파라메터 사용시 )

CREATE OR REPLACE PROCEDURE RIVUS.CREATE_DIR_WRITE_SAMPLE

AS

vHandle   UTL_FILE.FILE_TYPE;

vDirname VARCHAR2(250);

vFilename VARCHAR2(250);

vOutput   VARCHAR2(32767);

BEGIN

vDirname := 'SEND_AREA'; -- (주)디렉토리 오브젝트명을 대문자로 지정한다

vFilename := 'test.txt';

vHandle   := UTL_FILE.FOPEN(vDirname ,vFilename,'w', 32767);

  vOutput   := 'CREATE DIRECTORY 경유로의 파일출력';

UTL_FILE.PUT_LINE(vHandle, vOutput);

UTL_FILE.FCLOSE(vHandle);

EXCEPTION WHEN OTHERS THEN

UTL_FILE.FCLOSE_ALL;

RAISE;

END;

/

■ 파일읽기 (초기화 파라메터 사용시 )

CREATE OR REPLACE PROCEDURE RIVUS.CREATE_DIR_READ_SAMPLE

AS

vHandle   UTL_FILE.FILE_TYPE;

vDirname VARCHAR2(250);

vFilename VARCHAR2(250);

vInput    VARCHAR2(32767);

BEGIN

vDirname := 'RECV_AREA';

vFilename := 'test.txt';

vHandle   := UTL_FILE.FOPEN(vDirname ,vFilename,'r', 32767);

BEGIN

    LOOP

      UTL_FILE.GET_LINE(vHandle, vInput,32767);

      DBMS_OUTPUT.PUT_LINE(vInput);

    END LOOP;

EXCEPTION WHEN NO_DATA_FOUND THEN

    DBMS_OUTPUT.PUT_LINE('파일의 마지막');

END;

UTL_FILE.FCLOSE(vHandle);

EXCEPTION WHEN OTHERS THEN

UTL_FILE.FCLOSE_ALL; RAISE;

END;

/

주의사항

UTL_FILE에서 한번에 입출력할 수 있는 길이는 32767(32K) 바이트 (※)
OPEN 한 파일은 반드시 CLOSE 하도록 예외 처리를 해둔다.UTL_FILE.FCLOSE_ALL 를 사용하면 편리
RAW형을 출력할 수 있지만 줄 끝에는 OS 고유의 개행 코드가 반드시 부여된다.

Oracle 10g 에서는 wb 에 의한 (RAW 모드) FOPEN 가 서포트되고 있으므로, 그 쪽을 사용하면 문제 없다.

이 제한에 있어서 Oracle 10g 이후가 아니면 순수한 바이너리필드를 사용할수 없다.

(※) 한 번의 기입으로 32KB 를 넘을 수 없지만, RAW 모드로의 기입에 대해서는 여러 차례에 기입을 분할하는 것으로 1행이 32KB 를 초과하는 것이 가능하다

'오라클' 카테고리의 다른 글

OMF(ORACLE MANAGED FILES) 개념과 사용 예제 (0)	2009.09.25
External Table을 이용 물리적 파일을 처리하는 DML작업 성능개선. (0)	2008.10.01
[오라클 10g] 플래시백의 설정/관리 (0)	2008.09.25
Oracle10g에서의 Automatic Retention - ORA-1555에러방지 (0)	2008.09.25
Oracle의 메모리 구조 - SGA(DataBase Buffer Cache) (0)	2008.09.25

[오라클 10g] 플래시백의 설정/관리

오라클 | 2008. 9. 25. 14:10

Posted by 시반

1. Flashback기술

* Flashback 기술은 recovery 기술의 혁신적 발전을 가져왔습니다.
* 기존 recovery 기술은 느립니다.
- 9i 때부터 Flashback 쿼리가 존재 했었습니다.
- 잘못된 데이터만이 아니라 전체 데이터베이스나 파일을 복원해야 합니다.
- 데이터베이스 로그의 모든 변경 사항을 검토해야 합니다.

※ 기존 백업이 느린 이유는 백업받아 놓은걸 올려놓고 로그(온라인로그, 아카이브로그)를 적용해야되기 때문에 느립니다.
백업 받아놓은것이 용량이 클경우 속도가 상당히 느립니다.

* Flashback은 빠릅니다.
- 변경 사항은 행 및 트랜잭션으로 나타낼 수 있습니다.
- 변경된 데이터만 복원됩니다.

* Flashback 명령은 쉽습니다.
- 복잡한 다단계 절차가 포함되지 않습니다.

※ Flashback 기술은 데이터를 보면서 시점을 앞뒤로 되돌릴 수 있는 새로운 기능을 제공합니다.
※ Flashback 기술은 간단히 변경된 데이터에만 작용하므로 recovery 과정이 매우 향상됩니다.

1) Flashback 기술을 사용되는 경우

표에서 Tx는 트랜젝션의 약자입니다.
Recycle bin은 윈도우시스템의 휴지통과 같은 개념입니다.
언두 데이터는 AUM(Automatic Undo Management)를 사용해야지만 사용할수 있습니다.

※ Flashback 기술은 오라클 데이터베이스에서 논리적 손상이 발생했을 때 데이터를 빠르고
쉽게 recovery해야 하는 경우에 사용합니다.

2) 오류를 Flashback

* Flashback Database는 해당 시점 이후에 발생한 모든 변경 사항을 실행 취소하여 해당 시점 이전으로 데이터베이스를 전환합니다.

* Flashback Table은 백업을 복원하지 않고 과거의 특정 시점으로 테이블을 recovery합니다.
* Flashback Drop은 실수로 삭제된 테이블을 복원합니다.

3) Flashback Database

Flashback Database 작업은 다음 특성을 갖습니다.

* 데이터베이스에 대해 되감기 버튼처럼 작동합니다.
* 유저에 의해 논리적 데이터가 손상된 경우 사용할 수 있습니다.

※ 데이터베이스에 물리적 손상이 발생했거나 media가 손실된 경우에는 기존 recovery 방법을 사용해야 합니다.

* 복원 시간 단축

- Flashback Database는 Flashback Database 로그라는 일종의 로그 파일을 사용하여 구현됩니다.
- 오라클 데이터베이스는 Flashback Database 로그에 있는 데이터 블록의
"이전 이미지"를 주기적으로 기록합니다.
- 블록 이미지를 재사용하여 원하는 목표 시간 직전에 flashback 로그가 캡처된 시점으로
데이터 파일 변경 사항을 빠르게 취소할 수 있습니다.
- 그런 다음 데이터 간의 차이(gap)를 메우기 위해 리두 로그 파일의 변경 사항이 적용됩니다.
- Flashback Database 로그는 Flash Recovery Area에서 자동으로 생성되고 관리됩니다.

* 고려사항

- Flashback Database 작업이 완료되면 다음 방법 중 하나를 사용하여 데이터베이스를 열어야 합니다.
* 정확한 목표 시간 또는 SCN이 사용되었는지 확인하려면 읽기 전용 모드로 엽니다.
* 갱신을 허용하려면 RESETLOGS 파라미터를 사용합니다.

- flashback의 반대는 recovery입니다.
* 따라서 Flashback Database 작업을 언두하려면 데이터베이스를 앞으로 recovery해야 합니다.

* 제한사항

- 다음 상황에서는 Flashback Database를 사용할 수 없습니다.
* 콘트롤 파일이 복원되었거나 재생성된 경우
* 테이블스페이스가 삭제된 경우
* 데이터 파일이 축소(shrink)된 경우

* 활성화

- Flashback Database는 Enterprise Manager에서 Maintenance 탭 페이지의
Recovery Settings 링크를 사용하여 활성화할 수 있습니다.
- 이 페이지의 Flash Recovery 영역에서 체크 박스를 선택하고 retention 시간
(데이터베이스 flashback을 위해 과거로 돌아갈 수 있는 시간 분량)을 지정합니다.

4) Flashback Table

* Flashback Table은 특정 시점으로 테이블을 recovery합니다.
* Flashback Table은 in-place 작업입니다.
(해당 종속 객체에 수행한 변경 사항만 롤백하는 in-place 작업입니다.)
* 데이터베이스는 온라인 상태를 유지합니다.

* Flashback Table을 사용하여 백업을 복원하지 않고 테이블을 특정 시점으로 recovery할 수 있습니다.
* Flashback Table 작업을 수행하기 위해 언두 테이블스페이스에서 데이터를 검색합니다.
* 테이블을 flashback하려면 FLASHBACK TABLE 권한이 필요합니다.
* flashback 작업을 수행 중인 테이블에서 행 이동이 활성화되어야 합니다.

* 테이블에서 행이동 활성화

※ Enterprise Manager를 사용하여 테이블에서 행 이동을 활성화하려면
   다음 단계를 수행하십시오.

   1.Administration 속성 페이지의 Schema 영역에서 Tables를 선택합니다.
   2.행 이동을 활성화할 테이블 이름을 누릅니다. View Table 페이지가 나타납니다.
   3.Edit를 눌러 Edit Table 페이지로 이동합니다.
   4.Options 탭을 눌러 테이블의 Enable Row Movement 설정을 변경할 수 있습니다.
   5.Enable Row Movement를 Yes로 설정하고 Apply를 누릅니다.

※ 라인모드
     SQL> SHOW USER
   USER은 "SYS"입니다.                                  -- scott에게 flashback 권한을 부여
     SQL> grant flashback any table to scott; -- 권한이 부여되었습니다.

SQL> conn soctt/tiger -- USER은 "SCOTT"입니다.

SQL> alter table emp enable row movement; -- 테이블이 변경되었습니다.

   -- emp 테이블과 dept 테이블은 FK키로 연결되어 있는 테이블입니다.
   -- 이런경우 연과되는 테이블도 행 이동 활성화를 해줘야 됩니다.
   SQL> alter table dept enable row movement; -- 테이블이 변경되었습니다

* Flashback Table 수행

※ 다음 단계를 수행하여 Enterprise Manager에서 테이블을 flashback할 수 있습니다.

1.Maintenance 속성 페이지의 Backup/Recovery 영역에서Perform Recovery를 선택합니다.
Perform Recovery 페이지가 나타납니다.
2.Object Level Recovery 영역의 Object Type drop-down list에서 Tables를 선택합니다.
3.Operation Type으로 Flashback Existing Tables를 선택합니다. Perform Object Level Recovery를 누릅니다.

"Perform Object Level Recovery: Point-in-time" 페이지가 표시됩니다.
4."Flashback to a timestamp" 또는 "Flashback to a known SCN"을 선택한 다음
flashback할 시간 기록 또는 SCN을 지정하고 Next를 누릅니다.
5.Add Tables를 눌러 flashback 작업 대상 리스트에 테이블을 추가합니다. Next를 누릅니다.
6.종속 테이블이 있는 경우 Dependency Options 페이지가 나타납니다.종속 테이블을 처리할 원하는 옵션을 선택합니다.
일반적으로 일관된 flashback을 보장하도록 "Cascade"를 선택합니다.Next를 누릅니다.
7."Perform Object Level Recovery: Review" 페이지가 나타납니다.
정보를 검토하고 Submit를 누릅니다. Confirmation 페이지가 나타납니다.

※ Administration 페이지의 Schema 영역에 있는 Tables 링크에서도 테이블을 flashback할 수 있습니다

* Flashback Table 고려사항

- FLASHBACK TABLE 명령은 단일 트랜잭션으로 실행되며 배타적 DML lock을 획득합니다.
- 통계는 flashback되지 않습니다.
- 현재 인덱스와 종속 객체는 유지됩니다.

- Flashback Table 작업의 특징

* 시스템 테이블에서 수행될 수 없습니다.
* DDL 작업을 확장할 수 없습니다.
* Alert log file에 기록됩니다.
* 언두 및 리두 데이터를 생성합니다.

5) Flashback Drop

-- 사용자 오류 발생
SQL> DROP TABLE employees;

-- 복구
SQL> FLASHBACK TABLE employees TO BEFORE DROP;

* Enterprise Manager를 통해 삭제된 테이블 Flashback

- Database Control 콘솔을 사용하여 삭제된 테이블을 flashback하려면
Maintenance 페이지의 Backup/Recovery 영역에서 Perform Recovery를 선택합니다.

- Type 영역에서 Object Type에 대해 Tables를 선택하고 Operation Type 영역에
Flashback Dropped Tables를 선택합니다. 완료된 후에 Perform Object Level Recovery를 누릅니다.
- 이제 Recycle bin에서 삭제된 테이블을 선택할 수 있는
"Perform Object Level Recovery: Dropped Objects Selection" 페이지가 나타납니다.
View Content를 눌러 삭제된 테이블의 내용을 query할 수도 있습니다. recovery할 테이블을 선택하고 Next를 누릅니다.
- 동일한 스키마 내에 동일한 이름을 가진 테이블이 존재하는 경우 테이블의 이름을 바꿀 수 있는

"Perform Object Level Recovery: Rename" 페이지가 나타납니다. Next를 눌러 계속합니다.
"Perform Object Level Recovery: Review" 페이지에서 작업의 세부 내용을 검토하고
해당 SQL 문을 표시할 수 있습니다. 준비가 되면 Submit를 누릅니다.
이제Confirmation 페이지가 나타납니다. OK를 눌러 Maintenance 페이지로 돌아갑니다.

* Flashback Drop 고려사항

- Flashback Drop은 다음 테이블에서 작동하지 않습니다.
* SYSTEM 테이블스페이스에 있는 테이블
* Fine-Grained Auditing 또는 Virtual Private Database를 사용하는 테이블
* 딕셔너리 관리방식의 테이블스페이스에 있는 테이블
* 공간 부족으로 수동 또는 자동 삭제(purging)를 통해 삭제된 테이블

- 다음에 대해서는 종속성이 보호되지 않습니다.
* Bitmap-join 인덱스
* Materialized view 로그
* 참조 무결성 제약 조건
* 테이블보다 먼저 삭제된 인덱스

6) Flashback Time Navigation

* Flashback Query
- 지정된 시점의 모든 데이터를 query합니다.

* Flashback Versions Query
- 두 시간대 사이에 있는 행의 모든 버전을 봅니다.
- 행을 변경한 트랜잭션을 봅니다.

* Flashback Transaction Query
- 트랜잭션에 의해 수행된 모든 변경 사항을 봅니다.

* Flashback Query 개요

- SELECT 문의 AS OF 절을 사용하여 데이터를 확인할 시간 기록을 지정할 수 있습니다.
- 이 기능은 데이터 불일치 분석에 유용합니다.

※ AS OF 절 다음에 TIMESTAMP 또는 SCN이 올 수 있습니다.

* Flashback Versions Query 개요

- Flashback Versions Query 기능을 통해 VERSIONS 절을 사용하여 두 시점 또는
두 SCN 사이에 존재하는 행의 모든 버전을 검색할 수 있습니다.

출처 : Rich Tiger Story

'오라클' 카테고리의 다른 글

External Table을 이용 물리적 파일을 처리하는 DML작업 성능개선. (0)	2008.10.01
[Oracle 10g] UTL_FILE 패키지.. (0)	2008.09.25
Oracle10g에서의 Automatic Retention - ORA-1555에러방지 (0)	2008.09.25
Oracle의 메모리 구조 - SGA(DataBase Buffer Cache) (0)	2008.09.25
[Oracle DataBase 10g] 오라클 데이타베이스 구조 (0)	2008.09.25

Oracle10g에서의 Automatic Retention - ORA-1555에러방지

오라클 | 2008. 9. 25. 10:33

Posted by 시반

UNDO_RETENTION 파라미터는

자동 언두 관리(automatic undo management)"모드를 지원하는 Oracle9i Database에서 처음 소개된 파라미터이다. 이 파라미터는 언두 이미지를 일정 시간 동안 유지할 수 있게 하는 기능으로, 플래시백 질의(flashback query)를 지원한다. 언두 이미지를 찾아가는 과정에서 긴 질의의 SQL 문장에 대해서 ORA-1555 에러(snapshot too old)를 종종 볼수 있었다. Oracle9i Database까지는 ORA-1555 에러를 자동 처리할 수 없었지만, Oracle Database 10g부터는 긴 질의를 수행하는 경우 ORA-1555 에러의 빈도를 줄이기 위해서 UNDO_RETENTION 파라미터를 자동으로 튜닝하는 기능을 제공한다.
(ORA-1555 에러에 대한 자세한 설명은 metalink 문서 40689.1 “ORA-1555 snapshot too old-Detail Explanation” 참조)

'언두 리텐션’ 에 의한 만료시간 정보는 각 언두 세그먼트 헤더 안의 Extent 맵에 저장되기 때문에, 인스턴스가 비정상 종료 되더라도 Extent맵에 적용된 undo_retention 값은 계속 보장받는다.

먼저, undo_retention으로 인한 만료된 Extent와 만료되지 않은 Extent를 알아보자.

Expired Extent :

UNDO_RETENTION 시간을 초과한 Extent 트랜잭션이 커밋된 후
언두이미지가 언두 리텐션시간을 초과한 경우

Unexpired Extent :

UNDO_RETENTION 시간을 초과하지 않은 Extent 트랜잭션이 커밋된 후
언두 이미지가 언두 리텐션 시간을 초과하지 않은 경우

각각의 언두 Extent의 Expired, Unexpired 상태는 dba_undo_extents에서 확인할 수 있다.

Automatic Undo Retention의 기본 세팅
1.UNDO_RETENTION = 0
2.SMU(System Managed Undo)를 사용한다.

UNDO_RETENTION을 0으로 세팅하는 경우 UNDO_RETENTION은 기본적으로 900초를 설정하게 되며, MMON 프로세스는 30초 주기로 Max-querylen을 계산하게 된다. MMON은계산된 Maxquerylen 값을 근거로 tuned_undoretention 값을 결정하게 되며, 이후 UNDO_RETENTION이 계산된 tuned_undoretention으로 설정된다.

tuned_undoretention 값은 다음과 같이 결정된다.
tuned_undoretention = Maxquerylen + 300초

다음은 그 예이다.

select tuned_undoretention, maxquerylen, maxqueryid from v$undostat ;

tuned_undoretention             Maxquerylen         MaxqueryID
---------------------------- ----------------- --------------------
2300                                 2000                   gpxxh7pysj4fs <============= Here.
900                                   1                        25z699hs9r3wy
900                                   1                        2syxvjbg8d6s4
900                                   44                      5scq3kj3rm7tz

언두 세그먼트의 Extent 할당 순서

1. 자기 자신의 Extent의 프리 블록을 찾는다.
2. 다음 Extent가 만료된 Extent인지 확인한다.
3. 언두 테이블스페이스에서 새로운 Extent를 할당한다.
4. 오프라인 트랜잭션 테이블에서 만료된 Extent를 가져온다(steal).
5. 온라인 트랜잭션 테이블에서 만료된 Extent를 가져온다(steal).
6. autoextend가 가능하다면 파일을 확장하여 Extent를 할당한다.
7. 자신의 트랜잭션 테이블에서 만료되지 않은 Extent를 재사용한다.
8. 오프라인 트랜잭션 테이블에서 만료되지 않은 Extent를 가져온다(steal).
9. 에러(ORA-30036)를 발생한다.

Oracle9i Database 이상의 데이터베이스를 운영하면서 언두테이블스페이스에 대해서 dba_free_space 의 결과로 여유공간이 얼마 남아있지 않은 것을 종종 볼 수 있다.
이는 긴 질의를 보장하기 위해서, 다시 말해 긴 질의 수행시 ORA-1555의 발생을 최대한 피하기 위해서
언두테이블 스페이스에 남아 있는 공간에 위에서 소개한 순서로 Extent를 할당하기 때문이다.
언두 테이블스페이스의 공간이 충분히 남아 있지 않더라도 필요한 Extent를 할당할 때에는 세그먼트에서 Extent를 재사용하거나 가져올 (steal) 수 있다.
각 Extent가 만료된 것인지 아닌지에 대한 정보는 dba_undo_extents 데이터딕셔너리 뷰에서 확인할 수 있다.
이와 같이 활성상태가 아닌 만료 Extent 및 만료되지 않은 Extent가 많이 남아 있다면, 언두 테이블스페이스의 여유 공간이 얼마 남아 있지 않더라도 트랜잭션에 별 영향을 주지 않는다.
하지만, ORA-1555 에러 발생의 우려가 있기 때문에 v$undo_stat의 tuned_undoretention 값을 참조하여 적정한 언두 테이블스페이스 크기를 조정해 주는 것이 좋다.
그리고 Oracle Database 10g R2부터는 고정 언두 테이블스페이스에 대해서 ‘언두 개런티 모드’ 가 아니라면, UNDO_RETENTION 파라미터는 무시되며, 언두 테이블스페이스의 크기와 사용량에 의해서 언두 리텐션 값을 최대값으로 자동으로 설정하게 된다.

고정 언두 테이블스페이스:

자동확장이 꺼져 있는 언두 테이블스페이스,

Oracle9i Database 이상부터 언두 Extent를 할당하는 기능 중 하나로, 스페이스 관련 에러를 방지하기 위해서 다른 언두 세그먼트로부터 Extent를 가져오는(steal) 기능이 제공되지만, Oracle Database 10g에서는 만료되지 않은 Extent를 보장하기 위해서 언두 테이블스페이스에 대한 개런티 모드를 제공한다. (그러나, 디폴트는 노개런티 모드이다.)
언두 테이블스페이스에 대한 개런티 옵션은 데이터베이스를 생성하거나
언두 테이블스페이스를 생성하는 시점에서 지정이 가능하며, alter tablespace명령으로 지정할 수 있다.

예)

개런티 모드로의 전환
ALTER TABLESPACE undotbs RETENTION GUARANTEE;
데이터 딕셔너리 뷰에서 확인
SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS';
tablespace_name Retention
------------------------------ -----------
UNDOTBS GUARANTEE
노개런티 모드로의 전환
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
데이터 딕셔너리 뷰에서 확인
SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS';
tablespace_name Retention
------------------------------ -----------
UNDOTBS NOGUARANTEE

'오라클' 카테고리의 다른 글

[Oracle 10g] UTL_FILE 패키지.. (0)	2008.09.25
[오라클 10g] 플래시백의 설정/관리 (0)	2008.09.25
Oracle의 메모리 구조 - SGA(DataBase Buffer Cache) (0)	2008.09.25
[Oracle DataBase 10g] 오라클 데이타베이스 구조 (0)	2008.09.25
ERROR:ORA-12154:TNS:couldnot resolve service name (0)	2008.09.24

Oracle의 메모리 구조 - SGA(DataBase Buffer Cache)

오라클 | 2008. 9. 25. 09:28

Posted by 시반

오라클 10g에서는 ASMM(자동공유메모리관리)를 사용하여 메모리 관리작업을 간소화하고 있다.

ASMM이 활성화되어 있는 경우 처음부터 메모리를 관리할 특정구성요소에 대해 초기화파라미터를 설정하지 말것을 권장하고 있으며 ASMM 할당의 영향을 확인한 후에 특정구성요소할당을 조정할지를 결정한 다음 해당 구성요소에 대해 값을 지정할 수 있다.

이러한 값은 각 구성요소의 최소메모리 크기로 간주되어 자동조정에 사용할 수 있는 메모리양이 제한 되지만 작업환경에서 ASMM으로 제공되지 않은 특별한 크기조정이 필요한 경우 사용된다.

대상 초기화파라미터는 SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, DB_CACHE_SIZE, STREAMS_POOL_SIZE 가 있다.

Database Buffer Cache는 초기파일에서 정한 DB_Cache_SIZE 만큼의 메모리 영역으로 , 데이터 파일에서 읽어 들여 가장 최근에 사용된 데이터 블록을 저장하는 데이터베이스 버퍼들로 구성되어 있으며. 즉 사용자들에 의해 사용된 데이터의 캐시 정보라고 보면 된다.

예를 들어 소트나 병렬 읽기와 같은 Operation에서는 Database Buffer Cache를 사용하지 않으며 그 외의 대부분의 경우엔 Database Buffer Cache를 사용 합니다. Database Buffer Cache는 요구된 데이터를 메모리 블록에서 처리 함으로서 물리적인 디스크 I/O를 줄여 주게 된다.

오라클 서버의 사용자들이 어떤 데이터를 요구할 때 오라클 서버 프로세스는 Database Buffer Cache를 체크하여 여기에 있으면 즉시 반환하고 없으면 해당 데이터 파일에서 데이터를 읽어 들여 Database Buffer Cache에 로딩 후 요청한 데이터를 반환 한다.

데이터베이스버퍼 캐시는 크게 두 부분으로 구성되어 있는데 Data Block(after image), Undo Block(before image)로 구분이 가능하다… 찾고자 하는 데이터가 데이터베이스버퍼 캐시에 있지 않으면 데이터파일에서 읽어 오는데 읽은 블록을 USER가 수정하면 데이터베이스버퍼 캐시의 블록이 변경 되는 것이다. 이때 이 블록은 새로운 데이터를 가지고 있는 상태이기 때문에 AFTER 이미지라고 하며 오라클은 Undo Block에 데이터의 원본을 저장하는데 이를 before image라고 한다. 변경된 사항을 DB에 Commit하기 전에 변경을 취소하면 Undo Block의 정보를 이용하여 원래 상태로 되돌릴 수 있다.

Database Buffer Cache는 메모리에서 수정 되었지만 아직 디스크에 기록하지 않은 Dirty Buffer로 구성된 Write List와 LRU 리스트가 있으며 LRU List는 사용이 가능한 Free Buffer와 현재 사용중인 Pinned Buffer, 저장이 필요한 수정된 Dirty Buffer로 구성 된다.

사용자의 질의(SELECT, INSERT, UPDATE, DELETE)로 인해 Database Buffer Cache에 로딩된 데이터와 변경된 데이터들은 Database Buffer Cache에 보관 되며 시간이 지나면 Dirty Buffer가 되어 Writr List로 옮겨 지고 한정된 Database Buffer Cache내에서 서버 프로세스가 더 이상 Free Buffer를 찾을 수 없을 때 DBW0(database Writer) 프로세스는 Write List의 Dirty Buffer를 데이터 파일에 저장하게 된다.

이러한 Database Buffer Cache의 크기는 DB_BLOCK_SIZE(데이터베이스 버퍼캐시내의 고정된 블록의 크기)와 DB_CACHE_SIZE(Standard BLOCK의 CACHE 크기)로 결정되며 단위는 바이트 이다. 물론 9i이전 버전에서는 DB_BLOCK_BUFFERS (데이터베이스 버퍼 캐시 내의 블록의 수)로 그 크기(DB_BLOCK_SIZE * DB_BLOCK_BUFFERS)를 변경 했는데 9i에서부터 DB_BLOCK_BUFFERS는 DB_CACHE_SIZE로 대체되었고, 이는 4개까지의 DB_nK_CACHE_SIZE로 구성될 수 있다.

데이터베이스 버퍼 캐시에 올려지는 데이터의 성격에 따라 데이터베이스 버퍼 캐시의 영역을 다음과 같이 구분하고 있다.

KEEP : Access 빈도가 높아 버퍼 캐시안에서 지속적으로 보관해야 하는 데이터
RECYCLE : 재사용 가능성이 낮아서 데이터나 대용량의 데이터를 Access 한 직후에 메모리에서 삭제
DEFAULT : 일반적인 LRU 알고리즘을 적용하는 데이터베이스 버퍼 캐시

[예]
Create index emp_ename …. storage  (buffer_pool keep);

Alter table emp …. Storage (buffer_pool recycle);

오라클 8i에서 Database Buffer Cache는 오라클 데이터베이스 서버와 다르게 데이터베이스를 생성 할 때 DB_BLOCK_SIZE 만큼 고정된 블록의 크기로 Database Buffer Cache를 만들었지만 9i부터는 DB_nk_CACHE_SIZE로서 파라미터가 사용되어 데이터베이스 관리자가 직접 2KB~32KB까지 가변적인 데이터 베이스 버퍼 블록의 크기를 정하여 사용하는 것이 가능하다. 또한 Buffer Cache Advisory 가능을 제공하여 버퍼 캐시를 조절 했을 경우 성능을 예측하는 통계정보 등도 보여주며 V$DB_CACHE_ADVICE를 모니터링 하고 시스템의 전체적인 메모리 사용과 데이터베이스 고유의 메모리 영역을 고려하여 9i에서는 동적으로 SGA를 조절함으로서 인스턴스의 다운 없이 튜닝한 Database Buffer Cache를 적용하는 것이 가능해 졌다.

[예]
예) initSID.ora 파일에서
SGA_MAX_SIZE = 128M
DB_CACHE_SIZE = 96M
SHARED_POOL_SIZE = 32M 일때…

Note : DB_CACHE_SIZE는 Oracle 9i에 새롭게 도입된 파라미터

위와 같은 상태일 때 동적으로 SHARED_POOL_SIZE를 64M로 늘리면 에러가 발생 한다.

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=64M;
(insufficient memory error message)

이 에러는 SHARED_POOL_SIZE를 늘림으로써 전체 SGA의 크기가 SGA_MAX_SIZE
보다 커지기 때문에 발생한다. (96M + 64M > 128M)

이를 해결하기 위해서는 DB_CACHE_SIZE를 줄인 후, SHARED_POOL_SIZE를 늘린다.

SQL> ALTER SYSTEM SET DB_CACHE_SIZE=64M;
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=64M;
Note : DB_CACHE_SIZE가 shrink되는 동안에
ALTER SYSTEM SET SHARED_POOL_SIZE=64M;
를 하면 insufficient error가 발생할 수도 있다.

이 경우는 DB_CACHE_SIZE가 shrink된 후 다시 수행하면 정상적으로 수행이 된다.

Note : 위 예제의 경우 estimated SGA 크기가 128M 이상이므로, granule의 단위는 16M이다. 따라서 SGA 파라미터의 크기를 16M의 정수배로 했다. 16M의 정수배가 아닌 경우는 지정한 값보다 큰 값에 대해 16M의 정수배 중 가장 가까운 값을 택하게 된다.

즉, 아래 두 문장의 결과는 똑같다.
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=64M;
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=49M;

Note : LARGE_POOL_SIZE 와 JAVA_POOL_SIZE 파라미터는 동적으로 변경하는 것이 불가능하다.

또한 Oracle 9i부터 테이블스페이스를 서로 다른 block size로 만들 수 있는 기능 및, 각 block size별 cache의 크기를 지정 할 수 있게 되었으며.  SYSTEM 테이블스페이스는, 데이터베이스의 표준 BLOCK SIZE를 사용하며 다른 테이블스페이스는, 4종류의 서로 다른 BLOCK SIZE를 사용할 수 있게 되었다. 표준 BLOCK SIZE는 DB_BLOCK_SIZE 파라미터에 의해 지정되고 그 블럭에 대한 CACHE의 크기는 DB_CACHE_SIZE 파라미터에 의해 지정된다.

표준 이외의 BLOCK SIZE는 CREATE TABLESPACE 문장의 BLOCKSIZE 절에 의해 지정할 수 있다. 표준이외의 BLOCK SIZE별 CACHE 크기는, DB_nK_CACHE_SIZE 파라미터에 의해 지정되며 여기서 n은 2, 4, 8, 16, 또는 32 Kbytes 가 될 수 있다.

표준 BLOCK SIZE 또는 기본 BLOCK SIZE는 일반적으로 OS의 시스템 BLOCK SIZE와 동일한 크기 또는 그 배수로 지정한다.

DB_CACHE_SIZE 파라미터(DEFAULT CACHE SIZE) 는 표준 BLOCK SIZE에 대한 CACHE의 크기를 지정한다.(기본은 48MB로 지정)

SYSTEM 테이블스페이스는 표준 BLOCK SIZE로 생성이 되며 DEFAULT CACHE SIZE를 사용하게 된다. 표준 BLOCK SIZE 또는 비 표준 BLOCK SIZE 이거나 각 BLOCK SIZE별 cache는 동일한 크기의 BLOCK SIZE를 사용하는 어떤 테이블스페이스에 의해서도 사용될 수 있으며 만약, 데이터베이스를 디자인 할 때 다중 BLOCK SIZE를 고려한다면   DB_CACHE_SIZE 이외에도, 사용하고자 하는 BLOCK SIZE에 대한   DB_nK_CACHE_SIZE 값을 지정하여야 한다.
이는 사용하고자 하는 모든 BLOCK SIZE에 대해, 각각의 CACHE의 크기를 지정해 주어야 한다는 말로. 이와 같이 BLOCK SIZE 별 CACHE 크기 지정을 하는 방법은 4개 까지의 표준 이외의 BLOCK SIZE를 사용하는 테이블스페이스를 데이터베이스에서 액세스 할 수 있게 함으로써 BLOCK SIZE별 목적에 맞는 CACHE의 크기를 상세하게 지정할 수 있게 해 주는 것이다.

DB_BLOCK_SIZE 값은 데이터베이스를 생성할 때만 지정 가능하며 한번 생성된 데이터베이스에 대해서는 그 값을 바꿀 수 없으므로 값을 정할 때 신중하게 결정하여야 합니다.  SGA 크기를 산정하기 위해서는 다음과 같은 공식을 사용 합니다.

DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE+ SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFERS + 1MB

'오라클' 카테고리의 다른 글

[오라클 10g] 플래시백의 설정/관리 (0)	2008.09.25
Oracle10g에서의 Automatic Retention - ORA-1555에러방지 (0)	2008.09.25
[Oracle DataBase 10g] 오라클 데이타베이스 구조 (0)	2008.09.25
ERROR:ORA-12154:TNS:couldnot resolve service name (0)	2008.09.24
[본문스크랩] PL/SQL (16) - 대용량 오브젝트 타입 - BFILE (0)	2007.12.21

[Oracle DataBase 10g] 오라클 데이타베이스 구조

오라클 | 2008. 9. 25. 09:00

Posted by 시반

utility Computing : 사용한 만큼을 비용을 지불하겠다. (= grid Computing )

Oracle's Grid infrastructue;
- 저렴한 비용
- 서비스의 질이 향삼됨
- 관리가 쉽다.

Oracle's grid computing technology includes;

ASM(Automatic Storage Management)
데이터베이스가 실행되는 동안 여러 메모리 풀의 크기를 자동으로 조정하여 필요한 경우 메모리를 할당하고 할당 해제합니다. 자동 공유 메모리 관리를 사용하기 위해서는 Oracle 10g에서 새로 추가된 SGA_TARGET 파라미터만 셋팅 하면 됩니다.

Real Application Clusters (RAC) - 서버 구성연결형태

Oracle Stream

Enterprise Manager Grid Control

Oracle Server : 개방적이고, 통합적인 데이터 베이스 관리 시스템
Oracle Server = Instance + Database

User Process - 사용자가 접속요청을 할때 생성된다.

Server Process - 사용자가 연결되면 생성된다. 접속된 사용자의 요구사항을 처리한다.

Background Process - No mount에서 시작된다.( Instance 시작시)

Data buffer Cache

사용자가 요청한 데이터 블록의 복사본을 저장한다.
LRU 알고리즘을 통해 관리된다.
최근에 엑세스하지 않은 버퍼를 삭제하여 데이터 베이스 버퍼 캐시에 새로운 블록을 위한 공간을 만든다.

Redo log buffer

데이터 블록의 일어난 모든 변화를 저장하는 메모리 공간
기본적으로 복구에 사용됨

Shared pool

Library Cache + Dictionary Cache + Control Sturctures

Shared Pool은 하나의 데이터베이스에 행해지는 모든 SQL 문을 처리하기 위하여 사용 된다.
Shared Pool은 문장을 실행하기 위해 그 문장과 관련된 실행 계획과 구문분석 정보가 들어 있다.

Streams pool - 오라클 스트림에서 사용하는 메모리 영역

Large pool

SGA를 구성하는 Optional 성격의 메모리 대용량 메모리를 할당 할 때 사용한다. 백업 및 복원작업, I/O 서버프로세서

Java Pool - Oracle JVM에 접속해 있는 모든 세션에서 사용하는 자바코드가 사용하는 메모리 영역

ASM

자동 메모리 관리

버퍼캐쉬를 자동으로 늘려준다. 10분 마다 메모리 캡쳐를 뜬다.( Workload 작업량을 찍는다.)
작업량에 따른 버퍼캐쉬를 자동으로 늘려준다. 자동 공유 메모리 관리를 사용하기 위해서는 Oracle 10g에서 새로 추가된 SGA_TARGET 파라미터를 사용한다

System Monitor (SMON) : 오라클 인스턴스 Fail시 인스턴스를 복구한다. Mount와 Open 사이에 발생한다.
Process Monitor (PMON) : 비정상 종료된 데이터베이스 접속을 종료한다. 세션의 모든트랙잭션 롤백한다. 잠금을 해제한다. 기타 자원을 사용가능하게 한다. 사용불능 디스패쳐를 재시작한다.

DataBase Writer - Data Base Buffer cache 내용을 데이터 파일에 저장하는 작업을 수행한다. Dirty block을 파일에 기록한다. Dirty buffer 들을 디스크에 기록하여 Database Buffer Cache를 Clean하게 유지한다. 최대 10개의 DBWn을 가지나 인스턴스에서는 하나의 DBWn을 사용한다.

Check Point - (CKPT) : 현재 Redo log와 Checkpoint 번호로 data file과 Control file의 헤더를 동기화한다. DBWn에 신호를 보낸다. 물리적 정보를 기록한다. 데이터파일 헤더정보를 갱신한다.

CheckPoint는 언제발생하는가?
   - log switch 시
   - shutdown abort를 제외한 shutdown 시
   - ALTER SYSTEM CHECKPOINT command를 DBA가 날렸을 때
   - backup 이 실행될 때
   - LOG_CHECKPOINT_TIMEOUT parameter에서 설정한 값이 수행될 때.
   - LOG_CHECKPOINT_INTERVAL parameter에서 설정한 값이 수행될 때.

LogWriter(LGWR) - 서버 프로세스가 데이터 베이스 버퍼 캐시에 저장된 원본 데이터와 변경된 데이터의 복사본을 리루로그 엔트리(리두로그버퍼)에 저장하면 LGWR 프로세스는 리두로그 버퍼에 존재하는 리두로그 엔트리들을 디스크에 있는 온라인 리두로그파일에 기록합니다. 그리고 이프로세스는 현재 온라인 리두 로그의 로그 시퀸스 번호를 데이터 파일 헤더와 컨트록 파일에 기록하고 마지막으로 Dirty Buffer List를 비웁니다.데이터베이스 환경 설정에 따라 변경된 블록들이 다양한 시점에 DBWR에 의해 디스크에 쓰여져서 체크포인트가 발생하면 LGWR은 DBWR에게 변경 사항을 기록하도록 신호를 보냅니다.

Server Process and Database Buffer Cache

Buffers

Pianned :동일한 블록을 다른 세션이 사용하지 못하게 함. 메모리 락이 설정됨
Clean : 삭제대상 블럭
Free or unused : 빈블럭 , 한번도 사용안함 . Dirty 다음 Free
Dirty : 변경완료 DBWN -> Free

Physical Database Structure

Control Files - 최신의 물리적 저장 정보유지
Data Files - 데이터 저장소
Online redo log files
Parameter file - instance open에 필요한 정보 (SharedPool + LargePool)
Backup files
Archive log files - 백업시 사용되는 로그파일
Password File - Sysdba의 정보보관소
Alert and trace log files - 필수검사 로그 파일

Tablespaces and Data Files

Tablespace - 논리적인 저장공간 , 가장큰 저장영역

Data Files - 한시점에서 하나의 테이블스페이스에 생성된다

'오라클' 카테고리의 다른 글

Oracle10g에서의 Automatic Retention - ORA-1555에러방지 (0)	2008.09.25
Oracle의 메모리 구조 - SGA(DataBase Buffer Cache) (0)	2008.09.25
ERROR:ORA-12154:TNS:couldnot resolve service name (0)	2008.09.24
[본문스크랩] PL/SQL (16) - 대용량 오브젝트 타입 - BFILE (0)	2007.12.21
[본문스크랩] PL/SQL (15) - 대용량 오브젝트 타입 - CLOB, BLOB (0)	2007.12.21

ERROR:ORA-12154:TNS:couldnot resolve service name

오라클 | 2008. 9. 24. 20:31

Posted by 시반

oracle 10g에서 tnsping은 되지만 sqlplus에 접속이 되지 않을때..

10g 사용중 클라이언트를 깔고 서버 접속시 분명히 tnsping은 되는데 sqlplus를 비롯한 application들이 접속이 안되는 거였다.

에러 내용은
ORA-12154: TNS:could not resolve ..

ORA-12154 . 원래 이 에러는 client 에서 DB 접근시 tnsnames.ora 에 db_alias 나 tnsnames.ora 설정이 잘못되어 있을 경우 나타난다. 따라서 대부분 이때 살펴보아야 할 network 설정 파일은 sqlnet.ora 와 tnsnames.ora 두개의 파일이다.

일반적으로 tnsnames.ora의 설정부분을 잘못 설정한 경우 발생하기 때문에 확인하면 된다.(머 카피해서 이름이랑 아이피정도만 변경?ㅋ)

하지만 설정부분에 큰 이상이 없는데 접속이 되지 않을때 두번째로 의심해볼만한 것은 sqlnet.ora 파일이다

sqlnet.ora파일에서 해당IP의 접근을 허용해주면 된다.

# tcp.invited_nodes=(DB로컬 아이피와 접속을 허용할 ip)
tcp.invited_nodes=(192.168.3.211)
# tcp.excluded_nodes=(접속을 차단할 ip)

내 경우는 방화벽 때문이었기 때문에 alias를 읽지 못해 발생한 문제.. 방화벽에서 막혀 있는 부분을 허용해주면 된다.

http://forums.oracle.com/forums/thread.jspa?threadID=267600&start=45&tstart=0

에 의하면 환경설정변수를 읽지 못해 그럴수도 있다고 한다.
이 경우는 시스템 환경변수에 ORACLE_HOME을 등록하고 값으로 Home패스를 등록하면 정상적으로 동작이 가능하다.

(변경후 부팅이 필요함) 내컴퓨터 -> 속성 -> 시스템등록정보 -> 고급 -> 환경변수 -> 시스템 변수에 등록하면 된다.

변수이름 : ORACLE_HOME
변수 값 : E:\oracle\product\10.2.0\client_1
( 실제 설치된 경로는 E:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora )

경우에 따라서 재부팅이 필요할 수 있다.

'오라클' 카테고리의 다른 글

Oracle의 메모리 구조 - SGA(DataBase Buffer Cache) (0)	2008.09.25
[Oracle DataBase 10g] 오라클 데이타베이스 구조 (0)	2008.09.25
[본문스크랩] PL/SQL (16) - 대용량 오브젝트 타입 - BFILE (0)	2007.12.21
[본문스크랩] PL/SQL (15) - 대용량 오브젝트 타입 - CLOB, BLOB (0)	2007.12.21
[본문스크랩] PL/SQL (14) - 커서(cursor) FOR UPDATE 절 (0)	2007.12.21

[본문스크랩] PL/SQL (16) - 대용량 오브젝트 타입 - BFILE

오라클 | 2007. 12. 21. 15:31

Posted by 시반

###################################################################################################

# source site : blog.naver.com/tangamjaelt

# master : 강용운

# email : tangamjaelt@korea.com

# last release : 2007.08.30

###################################################################################################

1. BFILE

- 오라클 데이터베이스에 저장되는 것이 아니라 외부 운영체제에 저장된 오브젝트에 액세스한다.

- 디렉토리 오브젝트와 파일이름이 필요하고 관련 권한이 필요하다.

- Commit, Rollback 에 대한 트랜잭션을 지원하지 않는다.

- 파일은 읽기전용이다.

- too many files open 에러 발생시 init 파일에서 SESSION_MAX_OPEN_FILES 파라미터를 수정한다.

- 파일을 열었으면 반드시 닫는다.

2. 디렉토리 오브젝트

- 디렉토리 오브젝트 이름과 경로를 매핑시킨다.

- OS상 실제 경로를 지정하는 것이 아니라, 이 디렉토리 오브젝트를 호출하여 액세스한다.

3. DBMS_LOB 패키지

- DBMS_LOB 패키지를 통해 LOB을 조작할 수 있다.

※ BFILE 에 사용되는 함수와 프로시저

함수/프로시저	종류 / 사용가능 데이터 형	설 명
BFILENAME	Function / BFILE	PL/SQL블록이나 테이블에 파일의 위치에 대한 포인터(Locator) 생성한다.
FILEOPEN	Procedure / BFILE	Locator와 연결된 파일을 연다.
FILECLOSE	P / BFILE	Locator와 연결된 파일을 닫는다.
FILECOLSEALL	P / BFILE	BFILE을 모두 닫는다.
FILEEXISTS	F / BFILE	Locator가 가리키는 위치에 파일이 존재하는지 검사한다.
FILEISOPEN	F / BFILE	파일이 이미 열려있는지 검사한다.
FILEGETNAME	P / BFILE	디렉토리 오브젝트와 경로를 반환한다.
COMPARE	F / 모든 LOB	두개 LOB을 비교한다.
GETLENGTH	F / 모든 LOB	LOB의 길이를 반환한다.
INSTR	F / 모든 LOB	LOB에 지정된 문자스크링과 일치하는 패턴을 찾는다.
READ	P / 모든 LOB	지정한 size만큼 LOB을 버퍼로 읽어들인다.
SUBSTR	F / 모든 LOB	파라미터에 의해 지정된 전체 또는 일부 LOB을 반환한다.

(1) BFILENAME

- BFILENAME 함수를 통해 테이블에 BFILE에 대한 Locator를 삽입한다.

- 파일이 삭제되거나 이동될 경우 Locator는 예전 위치를 가리키고 있기 때문에 에러가 난다.

- 사용형식 : BFILENAME(디렉토리오브젝트, 파일이름)

(2) FILEOPEN

- BFILE을 사용하려면 우선 파일을 열어야한다.

- 사용형식 : FILEOPEN(Locator, DBMS_LOB.FILE_READONLY)

(3) FILECLOSE

- BFILE을 다 읽은 뒤 항상 파일을 닫는다.

- 사용형식 : FILECLOSE(Locator)

(4) FILECLOSEALL

- 모든 BFILE을 처리한 뒤 세션을 끝낼 경우, 모든 BFILE을 닫을 수 있다.

- 사용형식 : FILECLOSEALL

(5) FILEEXISTS

- 파일에 지정한 곳에 제대로 붙어 있는지 확인할 경우 사용한다.

- 있으면 1, 없으면 0 을 리턴한다.

- 사용형식 : FILEEXISTS(Locator)

(6) FILEISOPEN

- BFILE 사용 전에 파일이 이미 열려있는지 확인한다.

- 열려있으면 1, 닫혀있으면 다른 정수값을 리턴한다.

- 사용형식 : FILEOPEN(Locator)

(7) FILEGETNAME

- Locator와 연결된 디렉토리 오브젝트와 파일 이름을 반환한다.

- 사용형식 : FILEGETNAME(Locator, 디렉토리오브젝트, 파일이름)

(8) COMPARE

- 전체 또는 일부 LOB을 비교할 경우 사용

- 중복파일 제거를 위해 두 개 외부파일을 비교할 때 사용할 수 있다.

- 데이터가 똑같으면 0, 다르면 0이 아닌 값을 반환한다.

- 사용형식 : COMPARE(LOB1, LOB2, 비교할 byte수, LOB1 비교시작위치, LOB2 비교시작위치)

(9) GETLENGTH

- 오브젝트의 실제 길이를 byte 단위로 알 수 있다.

- 사용형식 : GETLENGTH(Locator)

(10) INSTR

- 지정한 위치부터 찾고자 하는 패턴과 일치하는 n번째 패턴을 검색한다.

- 패턴이 없으면 0, 패턴이 있으면 해당 오프셋을 반환한다.

- 사용형식 : INSTR(Locator, 패턴, 시작위치, n번째)

(11) READ

- 파일의 일부 또는 전체를 메모리로 읽어들인다.

- 사용형식 : READ(Locator, 읽을양, 시작위치, 버퍼위치)

(12) SUBSTR

- 파일에서 지정된 바이트수만큼 추출하는데 사용한다.

- 사용형식 : SUBSTR(Locator, 읽을양, 시작위치)

===================================================================================================

[ TEST ]

1. 아래와 같이 3개의 jpg 파일이 OS에 존재한다.

2. jpg 파일이 존재하는 경로를 가지는 디렉토리 오브젝트를 생성한다.

CREATE OR REPLACE DIRECTORY poster_dir AS '/export/home/oracle/plsql';

3. 다른 사용자가 사용하게 하려면 디렉토리 오브젝트를 읽을 권한을 준다.

GRANT READ ON DIRECTORY poster_dir TO user;

- 현재 디렉토리 생성자가 kangyw 이므로 본인에게 권한을 주면 에러 발생.

4. 프로시저 작성

bfile_test.sql

DECLARE
        v_Imagefile     BFILE;
        v_Dirname       VARCHAR2(30);
        v_Location      VARCHAR2(1000);
        v_Fileisopen    INTEGER;
        v_Fileexists    INTEGER;

BEGIN
v_Imagefile := BFILENAME('POSTER_DIR', 'bourne_1.jpg');
v_Fileisopen := DBMS_LOB.FILEISOPEN(v_Imagefile);

v_Fileexists := DBMS_LOB.FILEEXISTS(v_Imagefile);

        IF v_Fileexists = 1 THEN
                DBMS_OUTPUT.PUT_LINE('File Exists!!');
        ELSE
                DBMS_OUTPUT.PUT_LINE('File could not found!!');
        END IF;

        IF v_Fileisopen = 1 THEN
                DBMS_OUTPUT.PUT_LINE('File is already Open!!');
        ELSE
                DBMS_OUTPUT.PUT_LINE('File is not open!!');
                DBMS_LOB.FILEOPEN(v_Imagefile);
        END IF;

        DBMS_LOB.FILEGETNAME(v_Imagefile, v_Dirname, v_Location);
        DBMS_OUTPUT.PUT_LINE('The Directory Object is: ' || v_Dirname);
        DBMS_OUTPUT.PUT_LINE('The File Name is: ' || v_Location);

DBMS_LOB.FILECLOSE(v_Imagefile);
END;
/

- 결과

'오라클' 카테고리의 다른 글

[Oracle DataBase 10g] 오라클 데이타베이스 구조 (0)	2008.09.25
ERROR:ORA-12154:TNS:couldnot resolve service name (0)	2008.09.24
[본문스크랩] PL/SQL (15) - 대용량 오브젝트 타입 - CLOB, BLOB (0)	2007.12.21
[본문스크랩] PL/SQL (14) - 커서(cursor) FOR UPDATE 절 (0)	2007.12.21
[본문스크랩] PL/SQL (13) - 커서(cursor) (0)	2007.12.21

[본문스크랩] PL/SQL (15) - 대용량 오브젝트 타입 - CLOB, BLOB

오라클 | 2007. 12. 21. 15:31

Posted by 시반

###################################################################################################

# source site : blog.naver.com/tangamjaelt

# master : 강용운

# email : tangamjaelt@korea.com

# last release : 2007.08.30

###################################################################################################

1. LOB

- LOB은 TEXT, 그래픽, 이미지, 비디오, 사운드 등 구조화되지 않은 대형 데이터를 저장하는데 사용한다.

- 일반적으로 테이블에 저장되는 구조화된 데이터들은 크기가 작지만, 멀티미디어 데이터는 크기가 크다.

- 크기가 큰 데이터는 DB에 저장하기 힘들기 때문에 OS상 존재하는 파일을 데이터베이스가 접근하게 된다.

- LONG, LONG RAW 데이터 유형은 예전에 사용던 것이고, 현재는 대부분 LOB 데이터 유형을 사용한다.

- TO_LOB 함수를 이용하여 LONG 및 LONG RAW 를 LOB 으로 변경할 수 있다.

종류

- CLOB : 문자 대형 객체 (Character). Oracle Server는 CLOB과 VARCHAR2 사이에 암시적 변환을 수행한다.

- BLOB : 이진 대형 객체 (Binary). 이미지, 동영상, MP3 등...

- NCLOB : 내셔널 문자 대형 객체 (National). 오라클에서 정의되는 National Character Set을 따르는 문자.

- BFILE : OS에 저장되는 이진 파일의 이름과 위치를 저장. 읽기 전용 모드로만 액세스 가능.

데이터베이스 내부, 외부에 따라

- 내부 : BLOB, CLOB, NCLOB - Table에 LOB 형식의 컬럼을 생성하고 이곳에 데이터의 실제위치를 가리키는 Locator(위치자) 저장.

- 외부 : BFILE

특징

- 하나의 테이블에 여러 개의 LOB 열(column) 가능

- 최고 4GB까지 저장

- SELECT로 위치자 반환

- 순서대로 또는 순서없이 데이터 저장

- 임의적 데이터 액세스

LOB 구성

- LOB 값 : 저장될 실제 객체를 구성하는 데이터

- LOB 위치자 : 데이터베이스에 저장된 LOB값의 위치에 대한 포인터

- LOB열에는 데이터가 없고 LOB 위치자만 들어있다.

2. 내부 LOB

- LOB값은 데이터베이스에 저장된다.

- 사용자 정의 유형 속성, 테이블의 열, 바인드 변수, 호스트 변수, PL/SQL변수, 파라미터 또는 결과

- 동시성 방식, 리두 로그 및 복구 방식, 커밋 또는 롤백 트랜잭션 사용 가능

- BLOB 데이터 유형은 Oracle Server 내에서 비트 스트림으로 해석된다.

- CLOB 데이터 유형은 단일 바이트 문자 스트림으로 해석된다.

- NCLOB 데이터 유형은 데이터베이스 national character set 의 바이트 길이에 따라 멀티바이트 문자 스트림으로 해석된다.

내부 LOB 관리

- LOB 데이터 유형을 포함하는 테이블을 생성한 후 이를 채운다.

- 프로그램에서 LOB 위치자를 선언하고 초기화한다.

- SELECT FOR UPDATE를 사용하여 LOB이 포함된 행을 잠그고 해당 행 값을 LOB 위치자에 넣는다.

- LOB 위치자를 LOB값에 대한 참조로 사용하여 DBMS_LOB 패키지 프로시저, OCI호출, OLE용 오라클 객체, 오라클 선행 컴파일러 또는 JDBC를 통해 LOB을 조작한다.

- SQL을 통해서도 LOB 관리 가능(일부)

- COMMIT을 통해 변경사항 적용.

3. CLOB

lob_test1.sql

CREATE TABLE CAR (
        id      NUMBER(6) PRIMARY KEY NOT NULL,
        name    VARCHAR2(20),
        price   NUMBER(10),
        review CLOB)
/

INSERT INTO CAR VALUES (100, 'SM7', 25000000, '삼성에서 나온 SM7을 타보니 정말 편안했다.')
/
INSERT INTO CAR VALUES (200, 'PORTER', 13000000, '역시 1톤트럭의 대표자!!')
/
INSERT INTO CAR VALUES (300, 'VERNA', 9000000, '싸고 좋은 거 같아용~^^*')
/
INSERT INTO CAR VALUES (400, 'SANTAFE', 33000000, EMPTY_CLOB())
/

- 테이블 생성시 CLOB 타입 컬럼을 선언하고, 데이터 입력.

- 따로 SIZE는 지정하지 않는다.

- EMPTY_CLOB() 함수는 위치자(Locator)를 아무 것도 없는 데이터로 초기화시킨다.

- BLOB 의 경우엔 SQL*Plus 에서 볼 수 없다는 에러가 발생하였다.

SP-0678: Column or attribute type can not be displayed by SQL*Plus

'오라클' 카테고리의 다른 글

ERROR:ORA-12154:TNS:couldnot resolve service name (0)	2008.09.24
[본문스크랩] PL/SQL (16) - 대용량 오브젝트 타입 - BFILE (0)	2007.12.21
[본문스크랩] PL/SQL (14) - 커서(cursor) FOR UPDATE 절 (0)	2007.12.21
[본문스크랩] PL/SQL (13) - 커서(cursor) (0)	2007.12.21
[본문스크랩] PL/SQL (12) - 데이터타입 (%ROWTYPE 과 %TYPE) (0)	2007.12.21

« 1 2 3 »

'oracle'에 해당되는 글 29건

'오라클' 카테고리의 다른 글

SQL> show parameter utl

NAME TYPE VALUE

-------------------------- ----------- --------------------------------------------------------

create_stored_outlines string

utl_file_dir string /usr/tmp, /usr/tmp, /app/oracle/product/10. 2.0/db/appsutil/outbound/NDB2_cdb4, /usr/tmp

: /home1/var/lll/projs/if/work 폴더(물리적 파일위치) UTL_FILE_DIR 파라미터에 추가하여야 함.

- Directory 지정

CREATE OR REPLACE DIRECTORY dat_dir AS '/home1/var/lll/projs/if/work ';

CREATE OR REPLACE DIRECTORY log_dir AS '/home1/var/lll/projs/if/work ';

CREATE OR REPLACE DIRECTORY bad_dir AS '/home1/var/lll/projs/if/work ';

CREATE OR REPLACE DIRECTORY dat_dir AS '/usr/tmp';

CREATE OR REPLACE DIRECTORY log_dir AS '/usr/tmp';

CREATE OR REPLACE DIRECTORY bad_dir AS '/usr/tmp';

GRANT READ ON DIRECTORY dat_dir TO 배치유저; à 프로그램 수행 유저

GRANT WRITE ON DIRECTORY log_dir TO 배치유저;

GRANT WRITE ON DIRECTORY bad_dir TO 배치유저;

- External Table 생성하기

CREATE TABLE TB_PAYMENT_UPDATE (

payment_data varchar2(180)

) ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY dat_dir

ACCESS PARAMETERS

(

records delimited by newline

badfile bad_dir:'ext_test%ar_%p.bad

logfile log_dir:'ext_test%ar_%p.log'

fields terminated by ','

missing field values are null

(

payment_data

)

)

LOCATION ('payment_update.dat')

)

PARALLEL 4

REJECT LIMIT UNLIMITED ;

ALTER TABLE 배치유저. TB_PAYMENT_UPDATE NOPARALLEL

개선 전 :

- FILE READ : MAX 300만건 à /home1/var/lll/projs/if/work/payment_update.dat (물리 파일)

- UPDATE STATEMENT 수행 : MAX 300만번 수행됨.

UPDATE TB_PAYMENT SET

APPLICATION_ID = :sql_application_id,

WDRW_IMPSB_RSN_CD = :sql_wdrw_impsb_rsn_cd,

ATST_RETN_AMT = to_number(:atst_retn_amt2),

SYS_UPDATE_DATE = SYSDATE

WHERE WORK_YYMM BETWEEN TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')

AND TO_CHAR(SYSDATE,'YYYYMM')

AND BILL_ACNT_GRP_NO = MOD(:sql_bill_acnt_no,20) + 1

AND BILL_ACNT_NO = :sql_bill_acnt_no

AND ATST_BILL_SEQNO = to_number(:atst_bill_seqno2)

AND FILE_PRSS_NO = :sql_file_prss_no

개선 후 :

- FILE READ : 테이블 두번 FTS로 해석 à Merge Into ~

- UPDATE : Merge Into

~

MERGE /*+ USE_HASH(T B) */ INTO TB_PAYMENT T

USING ( SELECT /*+ LEADING(H P U) USE_HASH(H P U) FULL(H) FULL(P) FULL(U) */ à File 읽기

P.WORK_YYMM ,

P.BILL_ACNT_GRP_NO,

P.BILL_ACNT_NO ,

H.FILE_PRSS_NO ,

P.ATST_BILL_SEQNO ,

P.WORK_DV_CD ,

U.WDRW_IMPSB_RSN_CD,

TO_NUMBER(U.ATST_RETN_AMT) ATST_RETN_AMT

FROM TB_PAYMENT P,

(

SELECT SUBSTR(PAYMENT_DATA,36,9) BILL_ACNT_NO, à 파일에서 해당 데이터 위치

SUBSTR(PAYMENT _DATA,45,10) ATST_BILL_SEQNO,

SUBSTR(PAYMENT _DATA,9,11) ATST_RETN_AMT,

SUBSTR(PAYMENT _DATA,3,6) WDRW_IMPSB_RSN_CD

FROM TB_PAYMENT_UPDATE

WHERE PYM_DATA LIKE 'RD%' ) U,

( SELECT SUBSTR(PYM_DATA,24,5) FILE_PRSS_NO

FROM TB_PAYMENT_UPDATE

WHERE PYM_DATA LIKE 'RH%'

utl_file_dir string /usr/tmp, /usr/tmp, /app/oracle/product/10.
2.0/db/appsutil/outbound/NDB2_cdb4, /usr/tmp

MERGE /+ USE_HASH(T B) / INTO TB_PAYMENT T

USING ( SELECT /+ LEADING(H P U) USE_HASH(H P U) FULL(H) FULL(P) FULL(U) / à File 읽기