About DATABASE

출처 : Oracle Technology Network

Oracle9i Dataguard 구성 방법
2006년 1월 22일
민연홍(Oracle OCM), DBA LG카드 중형서버운영파트


미 국의 911테러 이후 전세계 IT시스템의 화두는, 자연재해 또는 테러로 인해 서비스 손실을 입게 될 경우에도 이를 복구할 수 있는 Standby 시스템을 구축하는 것이 되었다. 서버뿐만 아니라 디스크 및 백업장비까지 손실되었을 경우에도 다른 지역에서 서비스를 기동할 수 있는 시스템 구성이 각광받게 된 것이다. 이러한 Standby시스템에서 오라클의 dataguard는 데이터 무손실을 구현할 수 있는 방법으로, 한국에서는 여러 금융기관에서도 사용하고 있다. 여기서는 dataguard에 대한 간단한 소개와 그 구성방법에 대해서 알아본다.

Part 1. dataguard 개요 및 아키텍처
  1) dataguard 란 무엇인가?
  2) switchover and failover
  3) standby DB의 종류
  4) dataguard의 서비스 종류
  5) protection mode
  6) dataguard의 시스템 구성(2가지 종류)

Part 2. standby DB 기동방법

PartⅠ. dataguard 개요 및 아키텍처

 1) dataguard 란 무엇인가?

   - primary DB와 standby DB를 동기화시켜, primary DB가 하드웨어 장애 등의 문제가 생겼을 경우 standby DB로 failover 또는
     switchover 시킬 수 있는 시스템 구성을 말한다.
   - Oracle Net을 통해서 primary DB의 변경정보를 standby DB로 적용시켜 운영된다.

      

 2) switchover and failover

   ① 자동실행이 아니라 DBA가 action을 취해야 한다.

   ② switchover
     - OS 작업 또는 서버 PM작업 시 사용(primary -> standby , standby -> primary)

   ③ failover
     - 디스크 fail 등 긴급상황에서 사용, dataguard 재구성 필요

 3) standby DB의 종류

   ① Physical standby database
     - block 대 block 기반으로 primary DB의 redo log를 적용시켜 standby DB를 동기화

   ② Logical standby database
     - 같은 schema 정의로 공유
     - primary DB의 sql 문장을 standby DB에 적용

 4) dataguard의 서비스 종류

   ① Log transport Services
     - primary DB에서 standby DB로 redo log 정보를 자동으로 전송

   ② Log Apply Services
     - redo log를 standby DB에 적용

   ③ Role Management Service
     - 데이터베이스는 primary/standby로 두 가지의 상대적으로 배타적인 role을 가진다.
       Role Management Service는 log transport service와 log apply service를 failover 또는 switchover의 상황에 동적으로
       변경할 수 있다.

 5) protection mode

   ① Maximum Protection - primary    DB와 standby DB의 redo log를 동기화 시킨다.
      standby DB가 네트워크 이상 등의 이유로 standby로의 전송이 안될 경우 primary DB를 halt시킨다.
      데이터는 서로 동기화되어 primary DB에서 commit을 하게 되면 standby DB에서 commit이 완료될 때까지 primary DB에서 commit 완료를 하지 않는다.
      성능에는 문제를 줄 소지가 있으나 failover 상황이 오더라도 데이터 손실은 없다. physical standby DB에만 가능하다.

   ② Maximum availability - Maximum Protection과 마찬가지로 primary DB와 standby DB를 동기화시킨다.
      단, standby DB가 네트워크 문제 등의 이유로 전송이 안될지라도 halt되지는 않는다.
      데이터는 maximum protection과 마찬가지로 primary DB에서 commit을 하게 되면 standby DB에서 commit이 완료될 때까지
      primary DB에서 commit 완료를 하지 않는다. 만약 standby DB가 unavailable상태일 경우 임시로 불일치 시킨다.
      또 다시 standby DB가 available하면 자동으로 동기화 시킨다. 성능에는 문제를 줄 소지가 있으나 failover 상황이 오더라도
      데이터 손실은 거의없다. physical standby, logical standby DB 모두 가능하다.

   ③ Maximum Performance - default protection mode이다. primary data에 대한 protection이 가장 낮다.
      primary database에 transaction이 수행되면 이것을 asynchronous 하게 standby DB에 적용한다.
      즉, maximum protection, maximum availability의 경우에는 standby DB에 적용(commit) 될 때까지 primary db의 transaction 이 적용(commit)되지 않았으나,
      Maximum Performance 모드에서는 비 동기화 시키므로 primary DB에서 standby DB가 transaction 적용이 끝날 때까지
      기다리지 않는다. 즉 standby db의 문제로 인해서 primary DB에 성능영향이 가지 않는다.
      단, failover시에는 약간의 데이터 손실을 가져올 수 있다.

 6) dataguard의 시스템 구성(2가지 종류)

   ① physical standby database 구성 (LGWR process를 사용한 Physical standby DB)
      Archiving to a Physical standby Destination Using the Logwriter Proces


      


     - primary db의 LGWR 프로세스가 standby DB로 redo log를 보내고, standby DB의 RFS 프로세스가 redo log를 standby redo
      log에 적용시킨다. archiving되면 archived redo logs가 되고 이것을 MRP process가 standby DB에 적용시킨다.

   ② logical standby DB 구성
      Archiving to a Logical standby Destination Using the Logwriter Process

      

     - logical standby DB는 primary DB에서 수행된 SQL문장을 LGWR프로세스가 standby DB로 보내고 RFS 프로세스가 받아서
      Archived redo logs에 쓴다. LSP (Logical standby process)가 standby DB에 적용시킨다.

     - primary DB에서 log switch가 일어나게 되면 standby DB의 RFS 프로세스에 이를 알려주어 log switch가 되도록 한다.

  - 주의 standby db의 startup 방식을 보면 아래와 같다. 아래 그림을 기억해두면 편하다.
    

   ① standby DB를 read only mode에서 managed recovery mode로 변경
     - 그대로 명령 또는 shutdown immediate 이후 재기동

     - 첫번째 방법

      SQL> alter database open read only;
      SQL> recover managed standby database disconnect; 

     - 두번째 방법

      SQL> shutdown immediate 
      SQL> startup nomount
      SQL> alter database mount standby database;
      SQL> recover managed standby database disconnect;

   ② shutdown 에서 managed recovery mode 로 변경

      SQL> startup nomount
      SQL> alter database mount standby database;
      SQL> recover managed standby database disconnect;

   ③ managed recovery mode 에서 read only mode 로 변경

      SQL> recover managed standby database cancel;
      SQL> alter database open read only;

   ④ read only standby DB 에서 managed recovery mode로 변경
      (먼저 standby DB에 연결된 모든 세션을 종료)

      SQL> recover managed standby database disconnect;

     - 만약 유저의 세션 때문에 실패할 경우

      SQL> shutdown immediate
      SQL> startup nomount
      SQL> alter database mount standby database;
      SQL> recover managed standby database disconnect;

  - 실습에서 사용 할 primary DB 이름은 MIN 이고, standby DB 이름은 STBY 이다.
  - 여기서는 하나의 서버에서 2개 DB를 구성하는 방법으로 수행한다.
  - 서로 다른 두 대의 서버에서도 같은 방법으로 구성 할 수 있다.
  - primary DB는 /data1/oradata/MIN에 구성되어 있다.
  - standby DB는 /data1/oradata/STBY에 구성되어 있다.

 1) 리스너 설정 및 기동

  아래와 같은 네트워크 설정을 해준다. 각 서버마다 설정해준다.
    - MIN DB에서 설정(primary DB)

vi $ORACLE_HOME/network/admin/listener.ora smsvr1_MIN =    (ADDRESS_LIST =          (ADDRESS= (PROTOCOL= TCP)(Host= smsvr1)(Port=2001))    ) SID_LIST_smsvr1_MIN =  (SID_LIST =    (SID_DESC =      (ORACLE_HOME= /u/pkg/oracle/product/9.2.0)      (SID_NAME = MIN)    )  )

  -STBY DB 에서 설정(physical standby DB)

vi $ORACLE_HOME/network/admin/listener.ora smsvr1_STBY =    (ADDRESS_LIST =          (ADDRESS= (PROTOCOL= TCP)(Host= smsvr1)(Port=2002))    ) SID_LIST_smsvr1_STBY =    (SID_LIST =     (SID_DESC =      (ORACLE_HOME= /u/pkg/oracle/product/9.2.0)     (SID_NAME= STBY)    )   )

 2) tnsnames.ora 설정

  tnsnames.ora 파일을 설정한다. 서로 네트워크가 가능하도록 하는데 이름을 재대로 써야 한다.
  초기화 파라미터에서 log_archvie_dest_2='service=STBY LGWR SYNC AFFIRM' 일 경우
  STBY는 tnsnames.ora 에서의 접속이름을 말한다.

- MIN DB 설정 primary DB에서 설정 ( standby DB로 가는 네트워크 구성)

vi $ORACLE_HOME/network/admin/tnsnames.ora STBY =   (DESCRIPTION =      (ADDRESS = (PROTOCOL= TCP)(Host= smsvr1)(Port= 2002))      (CONNECT_DATA = (SID = STBY))   )

- STBY DB 설정, standby DB에서 설정 (primary DB로 가는 네트워크 구성)

vi $ORACLE_HOME/network/admin/tnsnames.ora MIN=   (DESCRIPTION =      (ADDRESS = (PROTOCOL= TCP)(Host= smsvr1)(Port= 2001))      (CONNECT_DATA = (SID = MIN))   )

 3) 오라클 초기화 파라미터 설정

- MIN DB (primary DB)

vi $ORACLE_HOME/dbs/initMIN.ora db_name=MIN compatible=9.2.0 control_files=('/data1/oradata/MIN/control01.ctl','/data1/oradata/MIN/control02.ctl') shared_pool_size=64M db_cache_size=16M undo_management=auto background_dump_dest=/data1/oradata/MIN/bdump user_dump_dest=/data1/oradata/MIN/udump core_dump_dest=/data1/oradata/MIN/cdump log_archive_start=true log_archive_dest_1="location=/data1/oradata/MIN/archive1" log_archive_format=%t_%s.arc #### 데이터가드를 위해서 변경하지 말 것 remote_archive_enable=true dg_broker_start=true log_archive_dest_state_1=enable log_archive_max_processes=3 standby_file_management=auto db_file_name_convert='/data1/oradata/STBY','/data1/oradata/MIN' log_file_name_convert='/data1/oradata/STBY','/data1/oradata/MIN' standby_archive_dest='/data1/oradata/MIN/archive2' ##### standby DB이면 열고, primary DB이면 닫는다. #fal_server=STBY #fal_client=MIN #lock_name_space=STBY ## primary DB이면 열고, standby DB이면 닫는다. log_archive_dest_2='SERVICE=STBY LGWR SYNC'

- STBY DB (standby DB) -주의할 것은 DB이름은 MIN 이며 instance 이름과 SID는 STBY 이다.
즉, 기동을 할 때에도 ORACLE_SID=STBY 로 설정하여 기동한다.

vi $ORACLE_HOME/dbs/initSTBY.ora db_name=MIN compatible=9.2.0 control_files=('/data1/oradata/STBY/control01.ctl','/data1/oradata/STBY/control02.ctl') shared_pool_size=64M db_cache_size=16M undo_management=auto background_dump_dest=/data1/oradata/STBY/bdump user_dump_dest=/data1/oradata/STBY/udump core_dump_dest=/data1/oradata/STBY/cdump log_archive_start=true log_archive_dest_1="location=/data1/oradata/STBY/archive1" log_archive_format=%t_%s.arc ### 데이터가드를 위해서 변경하지 말 것 remote_archive_enable=true dg_broker_start=true log_archive_dest_state_1=enable log_archive_max_processes=3 standby_file_management=auto db_file_name_convert='/data1/oradata/MIN','/data1/oradata/STBY' log_file_name_convert='/data1/oradata/MIN','/data1/oradata/STBY' standby_archive_dest='/data1/oradata/STBY/archive2' ## standby DB일 경우 아래를 열기, primary DB일 경우 닫기 fal_server=MIN fal_client=STBY lock_name_space=STBY ##primary DB이면 열고, standby DB이면 닫는다. #log_archive_dest_2='SERVICE=MIN LGWR SYNC'

-초기화 파라미터 설정에서 주의해야 할 것을 먼저 보면,
standby_file_management=auto 로 되어 있어야 primary DB에서 물리적인 테이블스페이스 및 데이터파일 추가할 경우 standby DB에 자동으로 생성된다.

standby_file_management=auto로 되어 있을 경우
db_file_name_convert='/data1/oradata/STBY','/data1/oradata/MIN'

log_file_name_convert='/data1/oradata/STBY','/data1/oradata/MIN'
의 파라미터에서 ' A 부분','B 부분'에서 B부분이 자신의 primary DB의 경로를 말하고 A부분이 변환할 standby DB의 경로를 말한다. 파라미터를 확인해보자.

fal_server, fal_client 는 standby DB에서만 사용한다. fal_server는 primary DB를 설정하고 fal_client는 standby DB를 설정해둔다. 이것을 설정할 경우 primary DB와 standby DB에 redo log의 gap이 발생했을 경우 자동으로 맞추어주는 역할을 한다.
fal_server=MIN
fal_client=STBY
lock_name_space는 한 대의 서버에서 primary, standby DB를 운영할 경우 사 용한다. 앞에서 말한 바와 같이 primary , standby DB
db_name은 같으며 instance_name(SID)만 다를 뿐이다. 같은 DB이름을 가진 instance를 한 대의 서버에서 기동하기 위해서는
lock_name_space를 지정해 주어야 한다. 만약 두 대의 서버에서 구성한다면 lock_name_space파라미터는 지정할 필요가 없다.
이것은 standby DB에서만 지정한다.
lock_name_space=STBY

 4) primary DB를 online backup으로 standby DB 위치로 restore

    <MIN DB primary DB>

- primary DB를 24시간 서비스 이므로 shutdown이 불가능한 것을 가정하에 구성한다.
- primary DB를 online backup으로 복사한다.
- online backup을 하는 것이므로 primary DB의 redo log는 standby DB로 전송하지 않는다. standby DB구성 시 자동으로
standby DB에서 redo log가 생성된다.

SQL> select tablespace_name, file_name, bytes/1024/1024 mega from dba_data_files; 

TABLESPACE_NAME	FILE_NAME	MEGA
------------------------------------------------------------------------------------------------------------
SYSTEM	/data1/oradata/MIN/system01.dbf	250
UNDOTBS	/data1/oradata/MIN/undotbs.dbf	100
USERS	/data1/oradata/MIN/users01.dbf	100

SQL> select name , bytes/1024/1024 mega from v$tempfile; 

NAME	MEGA
------------------------------------------------------------
/data1/oradata/MIN/temp01.dbf	100

- 여기서는 cp 명령이 standby DB로 전송하는 것을 뜻한다. ftp로 primary db의 백업을 전송한다.
   SQL> alter tablespace system begin backup;
   SQL> !cp /data1/oradata/MIN/system01.dbf /data1/oradata/STBY/system01.dbf
   SQL> alter tablespace system end backup;
   SQL> alter tablespace undotbs begin backup;
   SQL> !cp /data1/oradata/MIN/undotbs.dbf /data1/oradata/STBY/undotbs.dbf
   SQL> alter tablespace undotbs end backup;
   SQL> alter tablespace users begin backup;
   SQL> !cp /data1/oradata/MIN/users01.dbf /data1/oradata/STBY/users01.dbf
   SQL> alter tablespace users end backup;

- tempfile은 그대로 복사를 한다. begin backup, end backup 이 필요 없다. 단 tempfile 이어야 한다.
   SQL> !cp /data1/oradata/MIN/temp01.dbf /data1/oradata/STBY/temp01.dbf

 5) primary DB에서 standby control file을 생성해서 standby DB로 전송

    <MIN DB primary DB>

- primary DB에서 standby control file을 생성해서 standby DB로 전송한다
   SQL> alter database create standby controlfile as '/data1/oradata/STBY/stbyctl.ctl';

 6) standby DB에서 사용할 control file을 배치

    <STBY DB standby DB의 control file>

- standby DB에서 사용할 control file을, standby DB의 초기화 퍼라미터(initSTBY.ora)파일에 있는 control file 위치에 배치한다.
SQL> !cp /data1/oradata/STBY/stbyctl.ctl /data1/oradata/STBY/control01.ctl
SQL> !cp /data1/oradata/STBY/stbyctl.ctl /data1/oradata/STBY/control02.ctl

 7) standby DB 기동

    <STBY DB standby DB 에서 수행>

- standby DB를 기동시킨다. startup mount standby database 라는 명령은 없다. nomount까지 기동한 후 standby DB로 mount를 한다.

   SQL> startup nomount
   SQL> alter database mount standby database;

 8) standby DB에 standby redo log file 생성

    <STBY DB standby DB에서 수행>

우리는 처음으로 standby DB를 구성하였으므로 standby redo log를 추가해 주어야 한다.
여기서 중요한 것은 primary redo log와 standby redo log가 같은 크기를 가지고 있어야 한다.
만약 다를 경우에는 차후에 ORA-16139 media recovery required
에러가 발생하면서 takeover나 failover가 정상적으로 수행되지 않을 수 있다.

SQL> select * from v$logfile;

GROUP#	STATUS TYPE	MEMBER
---------------------------------------------------------------
1	ONLINE	/data1/oradata/STBY/log01a.log
2	ONLINE	/data1/oradata/STBY/log02a.log
3		/data1/oradata/STBY/log03a.log

SQL> alter database add standby logfile
           '/data1/oradata/STBY/stbylog01a.log' size 10M;
SQL> alter database add standby logfile
           '/data1/oradata/STBY/stbylog02a.log' size 10M;
SQL> alter database add standby logfile
           '/data1/oradata/STBY/stbylog03a.log' size 10M;
SQL> select * from v$logfile;

 

GROUP#STATUS	TYPE	MEMBER
------------------------------------------------------------------
1	ONLINE	/data1/oradata/STBY/log01a.log
2	ONLINE	/data1/oradata/STBY/log02a.log
3	ONLINE	/data1/oradata/STBY/log03a.log
4	STANDBY	/data1/oradata/STBY/stbylog01a.log
5	STANDBY	/data1/oradata/STBY/stbylog02a.log
6	STANDBY	/data1/oradata/STBY/stbylog03a.log

 9) primary DB에 standby redo log file 생성

    <MIN DB primary DB 에서 수행>

서버문제가 발생했을 경우 takeover를 해야 하므로 primary DB도 standby DB가 될 수 있기 때문에
미리 standby redo log를 만든다.

SQL> select * from v$logfile;

 

GROUP#>	STATUS TYPE	MEMBER
---------------------------------------------------------------
1	ONLINE	/data1/oradata/MIN/log01a.log
2	ONLINE	/data1/oradata/MIN/log02a.log
3	ONLINE	/data1/oradata/MIN/log03a.log

SQL> alter database add standby logfile
          '/data1/oradata/MIN/stbylog01a.log' size 10M;
SQL> alter database add standby logfile
          '/data1/oradata/MIN/stbylog02a.log' size 10M;
SQL> alter database add standby logfile
          '/data1/oradata/MIN/stbylog03a.log' size 10M;

SQL> select * from v$logfile;

GROUP#	STATUS TYPE	MEMBER
-------------------------------------------------------------
1	ONLINE	/data1/oradata/MIN/log01a.log
2	ONLINE	/data1/oradata/MIN/log02a.log
3	ONLINE	/data1/oradata/MIN/log03a.log
4	STANDBY	/data1/oradata/MIN/stbylog01a.log
5	STANDBY	/data1/oradata/MIN/stbylog02a.log
6	STANDBY	/data1/oradata/MIN/stbylog03a.log

 10) standby DB를 recovery managed mode로 기동

    <STBY DB standby DB 에서 수행>

standby DB를 recovery managed mode로 변경한다.
standby DB서버에서 MRP 프로세스가 생긴다.

   SQL> recover managed standby database disconnect;

 11) log switch 적용

    <MIN DB primary DB 에서 수행>

standby DB를 구성하는 동안 primary DB와 gap이 생겼을 것이다.
current redo log를 적용시킨다.

   SQL> alter system archive log current;

 12) primary DB 점검

            PROCESS STATUS
           ------------- ---------------
           ARCH       CLOSING
           ARCH       CLOSING
           LGWR       WRITING SQL> select dest_id "ID",destination,status,target, schedule,process,mountid mid from v$archive_dest order by dest_id;
=> destination 2번에 우리는 service=STBY로 설정하였다. STBY는 tnsnames.ora에 나오는 접속이름이었다. STATUS=VALID 상태이고 STANDBY 이어야 한다.


=> 아무런 에러도 나와서는 안된다. 여기에서 에러가 났다면 primary DB를 먼저 기동하고 standby DB를 기동했을 경우 발생할 수도 있으나, standby로 전송이 안된 것일 수도 있으므로 다른 것도 확인을 해보아야 한다. 만약 standby DB를 먼저 기동하고 recovery managed mode에서 MRP 프로세스를 띄우고 그리고 나서야 primary DB를 기동시켰다면 아래에서는 아무런 메세지도 나와서는 안된다. 예제에서는 primary DB를 먼저 기동했으므로 메세지가 발생했을 것이다.

        MESSAGE         TIMESTAMP
        --------------------------
        --------------------------- SQL> select dest_id id,database_mode db_mode,recovery_mode, protection_mode,standby_logfile_count "SRLs", standby_logfile_active ACTIVE, archived_seq# from v$archive_dest_status;
==> db_mode가 MOUNTED_STANDBY 이어야 한다. recovery_mode 가 managed가 되어 있어야 primary DB에서 전송된 redo log정보를 standby DB에 적용시킬 수 있다.

        ID DB_MODE         RECOVER PROTECTION_MODE      SRLs ACTIVE ARCHIVED_SEQ#
        --- --------------- ------- -------------------- ---- ------ -------------
       1 OPEN            IDLE    MAXIMUM PERFORMANCE     0      0        45
       2 MOUNTED-STANDBY MANAGED MAXIMUM AVAILABILITY    2      0        45
       3 OPEN            IDLE    MAXIMUM PERFORMANCE     0      0         0

        PROCESS STATUS
        ---------------- ------------
        ARCH    CONNECTED
        ARCH    CONNECTED
       MRP0    WAIT_FOR_LOG
     RFS     WRITING
     RFS     ATTACHED

<STBY DB standby DB>

vi $ORACLE_HOME/dbs/initSTBY.ora <원복시킨다> ##standby 이면 열고, primary DB이면 닫는다. fal_server=MIN fal_client=STBY lock_name_space=STBY ##primary DB이면 열고, standby DB이면 닫는다. #log_archive_dest_2='SERVICE=MIN LGWR SYNC'

18) failover
- primary DB가 있는 건물이 무너지고 디스크 이상으로 인해서 DB 데이터파일이 손상되었다.
standby DB를 긴급하게 기동시켜야 한다. MIN DB를 shutdown abort로 Down시키고(디스크 fail 장애),
standby DB를 primary DB로 기동시킨다.

- failover을 한 후에 시스템을 복구해서 MIN DB를 primary DB, STBY DB를 standby DB로 원래대로 구성하려면 Dataguard를 재구성 해야 한다.

즉, failover을 했다면 failover이후에 new primary DB인 STBY DB를 통해서 MIN DB를 standby DB로 구성하고
takeover 시키면 된다.

<MIN DB primary DB>
- 디스크장애상황
SQL> shutdown abort

<STBY DB standby DB>
- recovery managed mode를 해제(cancel)가 아닌 끝내도록(finish) 한다. primary DB로 변경한다.
SQL> recover managed standby database finish;
SQL> alter database commit to switchover to primary;
$ORACLE_HOME/dbs/initSTBY.ora 파일에서 primary DB로 파라미터를 설정하고 기동한다.
SQL> startup

<주의> 만약 standby DB인 STBY DB를 primary DB로 failover하던 도중 ORA-16139 media recovery required
에러가 나면서 recovery 하라고 나온다면?

이럴 경우엔 아래와 같은 명령을 사용하도록 한다.
Log stream을 standby DB에 적용하지 못한 경우에 발생할 수 있다.
이러한 사항은 여러 문제로 인해서 발생할 수 있으므로 발생했다면 오라클에 공식적으로 문의 해야한다.
아래와 같이 skip하면 skip하는 만큼의 gap을 DB에 적용하지 못할 수 있으니 주의해야 한다.
SQL> alter database recover managed standby database finish skip wait;
SQL> alter database commit to switchover to primary;
$ORACLE_HOME/dbs/initSTBY.ora 파일에서 primary DB로 파라미터를설정하고 기동한다.
SQL> startup 

 

/*+ ALL_ROWS */
explicitly chooses the cost-based approach to optimize a statement
block with a goal of best throughput (that is, minimum
total resource consumption) 

전체 RESOURCE 소비를 최소화 시키기 위한 힌트.
             Cost-Based 접근방식.

/*+ CHOOSE */
causes the optimizer to choose between the rule-based
approach and the cost-based approach for a SQL statement
based on the presence of statistics for the tables accessed by
the statement 
             Acess되는 테이블에 통계치 존재여부에 따라
             Optimizer로 하여금 Rule-Based Approach와 Cost-Based Approach
             중 하나를 선택할수 있게 한다.
             Data Dictionary가 해당테이블에 대해 통계정보를 가지고 있다면
             Optimizer는 Cost-Based Approach를 선택하고,
             그렇지 않다면 Rule-Based Approach를 선택한다.

/*+ FIRST_ROWS */
explicitly chooses the cost-based approach to optimize a statement
block with a goal of best response time (minimum
resource usage to return first row)
가장 좋은 응답 시간의 목표로 문 블록을 최적화하기 위해 cost-based 접근
방법을 선택합니다. (첫번째 행을 되돌려 주는 최소의 자원 사용)

/*+ RULE */
explicitly chooses rule-based optimization for a statement
block 
  Rule-Based 최적화를 사용하기위해.

/*+ AND_EQUAL(table index) */
explicitly chooses an execution plan that uses an access path
that merges the scans on several single-column indexes 

 single-column index의 merge를 이용한 access path 선택.
             적어도 두개이상의 index가 지정되어야한다.

/*+ CLUSTER(table) */
explicitly chooses a cluster scan to access the specified table 
  지정된 테이블Access에 Cluster Scan 유도.
             Cluster된 Objects에만 적용가능.

/*+ FULL(table) */
explicitly chooses a full table scan for the specified table 
해당테이블의 Full Table Scan을 유도.

/*+ HASH(table) */
explicitly chooses a hash scan to access the specified table 
지정된 테이블Access에 HASH Scan 유도

/*+ HASH_AJ(table) */
transforms a NOT IN subquery into a hash antijoin to access
the specified table 

NOT IN SubQuery 를 HASH anti-join으로 변형

/*+ HASH_SJ (table) */
transforms a NOT IN subquery into a hash anti-join to access
the specified table 

 correlated Exists SubQuery 를 HASH semi-join으로 변형

/*+ INDEX(table index) */
explicitly chooses an index scan for the specified table
그 명시된 테이블을 위하여, 색인 scan을 고르는

/*+ INDEX_ASC(table index) */
explicitly chooses an ascending-range index scan for the specified
table 

INDEX HINT와 동일 단,ASCENDING 으로 SCAN함을 확실히 하기위함.

/*+ INDEX_COMBINE(table index) */
If no indexes are given as arguments for the INDEX_COMBINE
hint, the optimizer uses whatever Boolean combination
of bitmap indexes has the best cost estimate. If particular
indexes are given as arguments, the optimizer tries to use
some Boolean combination of those particular bitmap indexes. 

 INDEX명이 주어지지 않으면 OPTIMIZER는 해당 테이블의
             best cost 로 선택된 Boolean combination index 를 사용한다.
             index 명이 주어지면 주어진 특정 bitmap index 의
             boolean combination 의 사용을 시도한다.

/*+ INDEX_DESC(table index) */
explicitly chooses a descending-range index scan for the specified
table 
 지정된 테이블의 지정된 index를 이용 descending으로 scan
             하고자할때 사용.


/*+ INDEX_FFS(table index) */
causes a fast full index scan to be performed rather than a full
table scan 

 full table scan보다 빠른 full index scan을 유도.

/*+ MERGE_AJ (table) */
transforms a NOT IN subquery into a merge anti-join to access
the specified table 

 not in subquery를 merge anti-join으로 변형

/*+ MERGE_SJ (table) */
transforms a correlated EXISTS subquery into a merge semi-join
to access the specified table 

correalted EXISTS subquery를 merge semi-join으로 변형

/*+ ROWID(table) */
explicitly chooses a table scan by ROWID for the specified
table 

지정된 테이블의 ROWID를 이용한 Scan 유도

/*+ USE_CONCAT */
forces combined OR conditions in the WHERE clause of a
query to be transformed into a compound query using the
UNION ALL set operator 

 조건절의 OR 를 Union ALL 형식으로 변형한다.
             일반적으로 변형은 비용측면에서 효율적일때만 일어난다.

/*+ ORDERED */
causes Oracle to join tables in the order in which they appear
in the FROM clause 

from절에 기술된 테이블 순서대로 join이 일어나도록 유도.

/*+ STAR */
forces the large table to be joined last using a nested-loops join
on the index 

 STAR QUERY PLAN이 사용가능하다면 이를 이용하기위한 HINT.
             STAR PLAN은 규모가 가장큰 테이블이 QUERY에서 JOIN ORDER상
             마지막으로 위치하게 하고 NESTED LOOP 으로 JOIN이 일어나도록
             유도한다.
             적어도 3개 테이블 이상이 조인에 참여해야하며 LARGE TABLE의
             CONCATENATED INDEX는 최소 3컬럼 이상을 INDEX에 포함해야한다.
             테이블이 ANALYZE 되어 있다면 OPTIMIZER가 가장효율적인 STAR PLAN을
             선택한다.    

/*+ DRIVING_SITE (table) */
forces query execution to be done at a different site from that
selected by Oracle 

QUERY의 실행이 ORACLE에 의해 선택된 SITE가 아닌 다른 SITE에서
             일어나도록 유도.

/*+ USE_HASH (table) */
causes Oracle to join each specified table with another row
source with a hash join 

각 테이블간 HASH JOIN이 일어나도록 유도.

/*+ USE_MERGE (table) */
causes Oracle to join each specified table with another row
source with a sort-merge join 

지정된 테이블들의 조인이 SORT-MERGE형식으로 일어나도록 유도.

/*+ USE_NL (table) */
causes Oracle to join each specified table to another row
source with a nested-loops join using the specified table as the
inner table 

테이블의 JOIN 시 테이블의 각 ROW가 INNER 테이블을 NESTED LOOP
             형식으로 JOIN 한다.

.



/*+ APPEND */ , /*+ NOAPPEND */
specifies that data is simply appended (or not) to a table; existing
free space is not used. Use these hints only following the
INSERT keyword.
데이타가 테이블로 단순히 덧붙여진다는 (or not)것 명시합니다; 무료인
현존하는 영역은 사용되지 않습니다.
단지 그 삽입 키 핵심어를 따르는 이 암시를 사용하시오.

/*+ NOPARALLEL(table) */
disables parallel scanning of a table, even if the table was created
with a PARALLEL clause
그 테이블이 PARALLEL 문절로 새로 만들어졌다면 테이블의 평행  순차 검색을
사용하지 않게 함

/*+ PARALLEL(table, instances) */
allows you to specify the desired number of concurrent slave
processes that can be used for the operation.
DELETE, INSERT, and UPDATE operations are considered for
parallelization only if the session is in a PARALLEL DML
enabled mode. (Use ALTER SESSION PARALLEL DML to
enter this mode.)
PARALLEL hint를 사용하면 query에 포함된 table의 degree를 설정할 수 있다.
예를 들어, 다음과 같이 hint를 적어 degree 4로 parallel query option을
실행하도록 할 수 있다.
이 때 parallel이란 글자와 괄호( '(' )사이에 blank를 넣지 않도록 한다.
select /*+ PARALLEL(emp, 4) */ * from emp; 
 
 

/*+ PARALLEL_INDEX
allows you to parallelize fast full index scan for partitioned
and nonpartitioned indexes that have the PARALLEL attribute
parallelize에 당신에게 빠른 가득한 색인 scan을 허락합니다. 그런데,
그것은 PARALLEL 속성을 가지고 있는 색인을 분할했고 nonpartitioned했습니다.

/*+ NOPARALLEL_INDEX */
overrides a PARALLEL attribute setting on an index
병렬이 색인을 나아가는 것을 속하게 하는 대체


/*+ CACHE */
specifies that the blocks retrieved for the table in the hint are
placed at the most recently used end of the LRU list in the
buffer cache when a full table scan is performed
그 블록이 찾아서 가져왔다는 것을 명시합니다. 그리고 그 테이블을 위해
그 암시에 놓여집니다. 그런데, 그것은 가장 요즈음 사용된 언제 그 버퍼 캐쉬,
가득한 테이블 scan에 있는 LRU 리스트의 끝입니다. 수행됩니다.

/*+ NOCACHE */
specifies that the blocks retrieved for this table are placed at
the least recently used end of the LRU list in the buffer cache
when a full table scan is performed
그 명시합니다. 그리고, 그 블록은 이 테이블을 위해 검색되면서 요즈음 사용된
언제 그 버퍼 캐쉬, 가득한 테이블 scan에 있는 LRU 리스트의 가장 작은 끝에
놓여집니다. 수행됩니다.

/*+ MERGE (table) */
causes Oracle to evaluate complex views or subqueries before
the surrounding query
오라클이 그 둘러싸는 질의 전에 복잡한 뷰나 부속 조회를 평가하게 합니다.

/*+ NO_MERGE (table) */
causes Oracle not to merge mergeable views
오라클이 mergeable 뷰를 합병하지 않게 하지 않습니다

/*+ PUSH_JOIN_PRED (table) */
causes the optimizer to evaluate, on a cost basis, whether or
not to push individual join predicates into the view
개개 접합을 미는 것이 그 뷰 안으로 단정 하든 간에 비용 방식으로 최적자가
평가하게 합니다.

/*+ NO_PUSH_JOIN_PRED (table) */
Prevents pushing of a join predicate into the view
접합 술부 중에서 그 뷰로 밀면서, 막는

/*+ PUSH_SUBQ */
causes nonmerged subqueries to be evaluated at the earliest
possible place in the execution plan
원인은 그 실행 계획에서의 가장 이른 가능한 장소에 평가되는 부속 조회를
nonmerged했습니다.

/*+ STAR_TRANSFORMATION */
makes the optimizer use the best plan in which the transformation
has been used.
최적자가 그 변형이 사용된 가장 좋은 계획을 사용하는 제작

생각의 전환이 DB를 최적화 시킨다
모든 DML을 INSERT로 변경하자

아직도 수많은 기업에서 성능 최적화를 수행하면 서도 주어진 SQL만을 그대로 최적화하려고 하는 경우가 많다. 물론 주어진 SQL의 튜닝을 통해 성능을 최적화할 수 있는 것은 분명하다. 하지만, 우리가 생각을 전환하여 기존이 방식과 다른 방식으로 수행하여 엄청난 성능 향상을 기대할 수 있는 경우도 많다. 대용량 데이터베이스로 변하고 있는 시점에서 성능 최적화를 위해 사용자 생각의 전환은 반드시 필요한 요소다.

권순용 | kwontra@hanmail.net

우리 주위에는 프로젝트의 성능을 향상시키기 위해 SQL 최적화에 전념하는 사이트들이 많이 있을 것이다. SQL 최적화를 통해 크게 성능 향상을 기대할 수 있는 것은 사실이다. 예전에는 성능 저하가 발생하는 경우 SQL 튜닝 보다도 해당 시스템의 CPU 또는 디스크 등의 자원을 증설하는 부분에 초점을 맞추었었다. 이와 달리 SQL을 튜닝하여 성능을 최적화하고자 하는 것은 매우 고무적인 현상임에는 틀림 없다.

그 만큼 관리자들의 생각이 IT 선진화로 가는 것은 아닐까? 하지만 아직도 SQL 튜닝을 고정 관념에 맞춰 그리고 그 자체로 튜닝하고자 하는 경우가 많은 것 같다. 우리가 조금만 다르게 생각한다면 SQL 튜닝의 효과를 배가 시킬 수 있다는 것을 아는가? 주어진 SQL을 그대로 보지않고 다르게 보는 순간 우리에게는 새로운 세상이 펼쳐질 것이다.

데이터 삭제를 DELETE로 수행하지 않고 데이터의 갱신을 UPDATE로 수행하지 않는다면 우리에게는 새로운 세상이 펼쳐질 것이다. 이제부터 이와 같은 현상에 대해 하나하나 자세히 확인해 보자.

DML은 왜 성능을 저하시키는가?

우리가 데이터를 저장하기 위해서는 INSERT를 수행하게 되고 데이터를 제거하기 위해서는 DELETE를 수행하게 된다. 또한, 기존의 데이터를 변경하기 위해서는 UPDATE를 수행하게 된다. 이와 같은 사실은 개발을 한번이라도 한 사람이라면 아니 SQL에 관련된 책을 한번이라도 본 사람이라면 누구나 알 수 있을 것이다.

DML 작업을 수행한다면 그리고 DML 작업을 수행해야 하는 데이터가 매우 많다면 많은 시간이 소요될 거라고 누구나 생각할 것이다. 그렇다면 이와 같이 대용량의 데이터에 대해 DML 작업을 수행하는 경우 어떤 이유에서 많은 시간이 소요되는 것일까? 이에 대해서는 많은 사람들이 정확한 개념을 갖고 있지 못하는 것 같다. ‘나를 알고 적을 안다면 100전 100승이 되듯이’ DML 작업의 성능을 최적화하기 위해서는 DML 작업이 왜 성능을 저하시키는지를 알아야 할 것이다. DML 작업이 왜 성능을 저하시키는지 정확히 이해하지 못한다면 우리는 어떤 방법을 사용해도 성능을 향상시킬 수 없을 것이다.

그럼 첫 번째로 INSERT의 성능 저하를 확인해 보자. INSERT는 데이터를 저장하는 SQL 중 하나로 아래와 같은 이유에서 많은 데이터의 저장 시 성능을 저하시키게 된다.

·로그 기록
·HWM BUMP UP
·인덱스의 개수
·롤백을 위한 로그 기록
·디스크 I/O

INSERT 작업은 위와 같이 4가지 현상에 의해 성능이 저하된다. 이는 어떤 데이터베이스를 이용해도 동일하게 발생하는 현상이다. 데이터베이스는 작업의 수행도 중요하지만 작업이 실패하거나 또는 다른 장애에 의해 시스템이 재기동 되는 등의 데이터베이스 장애에 대해 데이터를 보호해야 하는 중요한 책임을 가지고 있다.

이와 같은 이유에서 실제 데이터베이스에서 INSERT 작업을 수행하기 전에 어떤 작업을 수행하는지에 대한 로그를 기록해야 한다. 이와 같은 기법을 선 로그(LOG AHEAD) 기법이라고 한다. 실제 INSERT를 수행하기 전에 로그를 기록하기 때문에 우리는 언제든지 INSERT 작업 중 데이터베이스에 문제가 발생해도 복구가 가능하게 되는 것이다. 실제 INSERT 작업과 관계없는 로그를 기록해야 하기 때문에 INSERT의 성능은 저하된다.

그렇다면 HWM BUMP UP에 의한 성능 저하는 무엇을 의미하는 것인가? HWM BUMP UP은 오라클 데이터베이스의 내부적인 요소이다. 실제 INSERT를 수행하게 되면 해당 테이블에 할당되어 있는 공간에 데이터를 저장하게 되며 해당 공간을 익스텐트라고 부르게 된다. 익스텐트에는 HWM가 설정되어 있어 데이터는 HWM 앞의 블록에만 저장된다. 이 뜻은 무엇을 의미하는 것인가?

HWM 앞까지 데이터를 저장한 후에는 HWM가 뒤로 후진해야만 데이터를 INSERT할 수 있다는 의미가 된다. 이를 HWM BUMP UP이라 하며 많은 데이터를 INSERT하게 되면 HWM BUMP UP은 많은 횟수가 발생하게 될 것이다. 하지만 HWM BUMP UP은 고비용의 내부적인 작업이다. 따라서 대용량의 데이터를 저장한다면 HWM BUMP UP의 횟수 증가로 INSERT의 성능은 저하된다.

INSERT의 속도와 인덱스의 개수는 INSERT의 성능 향상을 위해 매우 중요한 요소이다. 데이터를 테이블에 저장하는 것은 여유 공간을 가지고 있는 데이터 블록에 해당 데이터를 저장하면 된다. 하지만 인덱스에는 정해진 위치가 존재하게 되므로 정해진 위치를 찾는 프로세스가 수행된다. 따라서 해당 테이블에 인덱스가 10개라면 이와 같이 저장되는 데이터에 대해 인덱스에서의 위치를 찾기 위해 정해진 위치를 찾는 프로세스가 10번 수행되어야 할 것이다. 이와 같기 때문에 인덱스의 개수가 많다면 INSERT의 성능이 저하되는 것은 당연한 사실일 것이다.

어떤 사이트에서 어떤 테이블에 10개의 인덱스가 존재했으며 이를 최적화하여 5개의 인덱스로 변경한 적이 있다. 단지 10개의 인덱스를 5개로 감소시키는 순간 SQL의 변경 없이 INSERT 작업은 4배정도의 성능이 향상되었다. 이는 4배의 성능 향상이 중요한 것이 아니라 인덱스가 INSERT 작업에 많은 부하를 발생시킨다는 중요한 사실을 우리에게 전해주는 것일 것이다.

롤백을 위한 로그 기록은 해당 작업을 수행한 후 작업을 취소하는 경우 이전 데이터로 복구하기 위해 이전 데이터의 값을 저장하는 것을 의미한다. 이와 같은 작업 또한 실제 데이터를 저장하는 작업과는 별개로 수행되므로 INSERT의 성능 저하를 발생시키게 된다.

HWM BUMP UP을 제외한 로그 기록, 인덱스 및 롤백을 위한 로그 기록은 모두 디스크 I/O를 발생시킨다. 또한, 실제 데이터를 저장하는 작업에서도 디스크 I/O가 발생하게 된다. 이와 같이 모든 단계에서 디스크 I/O가 발생하기 때문에 INSERT의 성능은 저하될 것이다.

두 번째로 UPDATE의 성능 저하를 확인해 보자. UPDATE는 이미 저장되어 있는 데이터에 대해 변경 작업을 수행하는 것이다. 이와 같은 UPDATE는 아래와 같은 요소에 의해 성능 저하가 발생하게 된다.

·로그 기록
·UPDATE 컬럼이 사용된 인덱스의 개수
·롤백을 위한 로그 기록
·디스크 I/O

UPDATE의 경우에는 HWM BUMP UP은 발생하지 않게 된다. 또한, 인덱스의 개수도 해당 테이블에 존재하는 모든 인덱스는 아니며 UPDATE가 수행되는 컬럼이 사용된 인덱스의 개수를 의미하게 된다. UPDATE가 수행되면 해당 컬럼을 인덱스의 컬럼으로 구성하고 있는 인덱스만을 갱신하게 된다. 그렇기 때문에 UPDATE 컬럼이 사용된 인덱스의 개수에 의해 UPDATE 성능은 저하된다.

INSERT에 비해 UPDATE는 성능을 저하시키는 항목이 더 적다. 하지만, 동일한 양에 대해 INSERT와 UPDATE를 수행한다면 UPDATE가 성능 저하를 더 많이 발생시키게 된다. 이와 같은 이유는 왜일까? 분명히 성능 저하의 요소는 INSERT가 더 많기 때문에 INSERT가 더 많은 부하를 발생시킨다고 생각하기 쉽다. 이는 로그 기록과 롤백을 위한 로그 기록의 방식 차이 때문이다. INSERT 작업은 로그에 이전 데이터라는 것은 존재하지 않는다.

INSERT 작업이 수행된 데이터의 위치 정보만을 가지게 된다면 우리는 언제든지 롤백을 수행할 수 있으며 장애 시 복구도 어렵지 않게 된다. 하지만, UPDATE의 경우에는 이전 데이터의 값이 존재하기 때문에 이전 데이터를 로그에 기록하게 된다. 따라서 로그 기록 및 롤백을 위한 로그 기록에서 UPDATE가 INSERT에 비해 더 많은 데이터를 기록해야 하므로 디스크 I/O의 증가로 UPDATE의 성능은 INSERT의 성능보다 더욱 저하되게 된다.

세 번째로 DELETE 작업은 어떠한가? DELETE는 저장되어 있는 데이터를 삭제하는 기능을 수행하게 된다. 위와 같은 DELETE는 아래와 같은 항목들에 의해 성능이 저하된다.

·로그 기록
·인덱스의 개수
·롤백을 위한 로그 기록
·디스크 I/O

다른 항목은 INSERT 또는 UPDATE와 동일하다. 차이라면 로그 기록 및 롤백을 위한 로그 기록시 삭제되는 데이터의 이전 데이터와 이후 데이터를 모두 기록해야 한다는 것이다. 물론, 롤백을 위한 로그 기록 시에는 이전 데이터의 값과 위치 정보만을 가지게 된다. 결국, 이와 같은 이유로 동일한 양의 데이터를 DELETE하는 경우 INSERT에 비해 더 많은 디스크 I/O가 발생하게 되므로 성능은 저하되게 된다.

다양한 성능 저하의 요소를 가지는 DML 작업에서 대용량의 데이터에 대해서 어떤 방식으로 작업을 수행해야만 성능을 보장 받을 수 있겠는가?

INSERT의 성능 저하 요소를 감소시키자

일반적으로 DELETE 또는 UPDATE의 경우에는 INDEX의 개수만을 최적화하여 성능을 향상시킬 수 있다. 하지만, INSERT의 경우에는 위와 같은 성능 저하의 요소 중 아래와 같은 요소에 대해 획기적으로 감소시킬 수 있게 된다.

·로그 기록
·HWM BUMP UP
·롤백을 위한 로그 기록

위와 성능 저하 요소는 우리가 조금만 깊이 있게 고려한다면 최소화 시킬 수 있으며 위의 항목을 최소화 시킨다면 디스크 I/O는 자동으로 감소하게 된다. 그렇다면 어떻게 위의 성능 저하의 요소를 감소시킬 수 있겠는가?

첫 번째로 HWM BUMP UP과 롤백을 위한 로그 기록을 감소시키는 방법을 확인해보자. HWM BUMP UP을 제거하기 위해서는 HWM를 이동시키지 않고 데이터를 HWM 뒤에 존재하는 블록에 저장하면 될 것이다. 이와 같다면 롤백 또한 이전 데이터의 정보를 하나 하나 기록하는 것이 아니라 HWM의 위치 정보 하나만을 기록한다면 롤백 수행 시 HWM 위치 뒤에 존재하는 모든 블록을 제거한다면 롤백을 수행한 것과 동일한 현상이 발생할 것이다.

결국, HWM를 고정시키고 데이터를 HWM 뒤에 존재하는 블록에 저장시킨다면 HWM BUMP UP과 롤백을 위한 로그 기록에 의해 발생하는 성능 저하는 해결될 수 있을 것이다. 이와 같이 INSERT를 수행하는 방법이 바로 직접 로딩(DIRECT LOADING) 방식이다. 직접 로딩 방식을 사용한다면 HWM 뒤의 블록에 데이터를 저장하게 되므로 두 가지 문제는 모두 해결될 수 있을 것이다. 그렇다면 직접 로딩은 어떻게 사용하는 것인가?

·INSERT /*+ APPEND */ …… SELECT ……

위와 같이 SELECT를 수행하여 해당 데이터를 테이블에 INSERT하는 경우에 APPEND 힌트를 사용하여 직접 로딩을 수행하게 된다.

두 번째로 로그 기록을 확인해 보자. 우리가 테이블에 데이터를 저장하는 경우 일반적으로는 LOGGING 상태이므로 앞서 언급한 모든 로그를 기록하게 된다. 하지만, 직접 로딩(DIRECT LOADING) 기법을 사용하게 된다면 HWM 뒤에 존재하는 블록에 데이터를 저장하게 되므로 별도의 로그를 기록하지 않아도 복구 시 HWM 뒤의 블록에 존재하는 데이터를 제거하면 될 것이다. 이와 같은 이유에서 직접 로딩 방식의 경우에는 로그를 기록하지 않는 NOLOGGING 방식으로 데이터를 저장할 수 있게 된다.

결국, 위와 같이 NOLOGGING 상태에서 직접 로딩을 수행한다면 앞서 언급한 3가지의 성능 저하 요소를 대부분 제거할 수 있으며 이로 인해 디스크 I/O는 감소하게 된다. 상황에 따라 다르지만 대용량의 데이터에 대해 NOLOGGING 상태의 직접 로딩은 일반 INSERT에 비해 10배 이상 성능을 향상시킬 수도 있으며 그 이상의 성능 향상을 기대할 수도 있다.

DELETE와 UPDATE의 성능을 최적화하자

대용량의 데이터에 대해 DELETE 또는 UPDATE를 수행한다면 엄청난 성능 저하가 발생할 수 있다. DELETE 또는 UPDATE는 INSERT에 비해 더 많은 자원을 사용하게 되며 직접 로딩 또는 NOLOGGING 상태와 같은 방법이 존재하지 않게 된다. 많은 데이터에 대해서는 과거에나 지금이나 성능 저하를 감수할 수 밖에는 없을 것이다. 하지만, 이와 같은 대용량의 DELETE 또는 UPDATE에 대해서도 성능을 최적화 시키는 방법은 존재한다.

우리는 데이터를 변경하기 위해서는 항상 UPDATE를 사용해야 한다고 생각하고 데이터를 삭제하기 위해서는 항상 DELETE를 수행해야 한다고 생각한다. 일반적으로 생각한다면 당연한 사실일 것이다. 하지만, 이와 같은 고정 관념으로는 대용량의 데이터에서 더 이상의 성능 향상을 기대할 수 없을 것이다.

대용량의 데이터에 대해 UPDATE 또는 DELETE를 수행하는 경우 최적의 성능을 보장 받기 위해서는 기존의 사고 방식에서 벗어나야 할 것이다. 결국, 생각하는 방식의 전환만이 대용량의 데이터에 대해 UPDATE와 DELETE의 성능을 최적화하는 유일한 방법이 될 것이다.

대용량의 데이터에 대해 데이터의 변경에 대해 UPDATE로 작업을 수행하지 말고 데이터의 삭제에 대해 DELETE로 작업을 수행하지 말아야 한다. UPDATE는 INSERT로 변경하고 DELETE 또한 INSERT로 변경하는 순간 최적의 성능을 기대할 수 있을 것이다. UPDATE를 INSERT로 DELETE를 INSERT로 수행하는 것이야말로 우리의 기존 고정 관념을 파괴하는 행위일 것이다. 이제는 이와 같은 기존의 고정 관념을 파괴하여 성능을 최적화해야 할 것이다.

그렇다면 어떤 이유에서 UPDATE 또는 DELETE 대신 INSERT를 사용해야 하는가? 이유는 간단하다. INSERT는 UPDATE와 DELETE와는 달리 직접 로딩과 NOLOGGING이 가능하기 때문이다. 이와 같은 성능 향상의 요소는 우리에게 엄청난 혜택을 제공하기 때문이다.

그렇다면 어떻게 UPDATE를 INSERT로 변경하고 DELETE를 INSERT로 변경할 수 있겠는가? 물론, 데이터 삭제에 INSERT를 사용해야 하기 때문에 작업 절차는 복잡해 질 수 있다. 예를 들어, TEST 테이블의 크기가 100GB이며 그 중 50GB에 해당하는 데이터를 삭제해야 한다고 가정하자. 그렇다면 DELETE를 수행하는 순간 우리는 엄청난 시간을 기다려야 해당 작업을 종료할 수 있을 것이다.

DELETE를 INSERT로 변경한다면 어떻게 되겠는가? 우선, TEST 테이블과 동일 구조의 TEST_IMSI 테이블을 생성한 후 TEST 테이블로부터 데이터가 삭제된 후 남게 되는 데이터만을 조회하여 TEST_IMSI 테이블에 INSERT를 수행한다. 해당 작업에는 TEST_IMSI 테이블을 NOLOGGING 상태로 변경한 후 해당 테이블에 직접 로딩을 수행해야 할 것이다. 고성능의 디스크를 사용하는 시스템이라면 1GB에 1분 정동의 INSERT 시간이 소요된다.

50GB의 데이터를 INSERT하면 되므로 최적화된다면 50분 정도의 시간이 소요될 것이다. 이 얼마나 빠른 속도인가? 물론, 경우에 따라 병렬 프로세싱을 이용해야 할 수 도 있다. 이와 같이 작업을 수행했다고 모든 것이 종료되는 것은 아니다. TEST 테이블을 TEST_BACKUP으로 이름을 변경하고 TEST_IMSI 테이블을 TEST 테이블로 이름을 변경해야 할 것이다. 물론, 인덱스가 필요하다면 인덱스도 생성해야 할 것이다.

이와 같이 작업 절차는 복잡해 지지만 DELETE를 수행하는 것보다는 10배 아니 그 이상의 성능 향상을 기대할 수 있을 것이다. UPDATE의 경우도 이와 다르지 않다. UPDATE 후의 데이터를 임시 테이블에 INSERT를 수행하고 해당 테이블의 이름을 변경한다면 기존 테이블에는 UPDATE 후의 데이터가 저장되므로 INSERT를 이용하여 UPDATE를 대신할 수 있게 된다.

이와 같은 방식으로 기존의 방식에서 벗어날 수 있다면 우리는 최적의 성능을 기대할 수 있을 것이다.

우리가 가지고 있는 기존의 방식을 버린다는 것은 쉬운 일이 아닐 것이다. 하지만, 더 좋은 방법이 있다면 과감히 새로운 방법을 선택하는 것도 필요할 것이다. 데이터의 갱신과 제거를 UPDATE와 DELETE로 구현하는 것이 아니라 INSERT로 구현한다면 많은 사람들이 의아해 할지도 모른다. 하지만, 분명히 가능한 일이며 이를 통해 우리는 INSERT의 최고의 아키텍쳐인 NOLOGGING과 직접 로딩을 이용할 수 있다는 것을 명심하길 바란다.

제공 : DB포탈사이트 DBguide.net