지속적 쿼리 알림 (Continuous Query Notification, CQN) 사용

지속적 쿼리 알림(CQN) 개요

Oracle 데이터베이스의 지속적 쿼리 알림(Continuous Query Notification, CQN) 기능은 애플리케이션이 데이터베이스 내 특정 테이블 또는 뷰의 변경 사항을 실시간으로 감지하고 대응할 수 있도록 지원하는 강력한 메커니즘입니다. CQN을 사용하면 더 이상 주기적으로 데이터베이스를 폴링하여 변경 여부를 확인할 필요 없이, 데이터 변경 시점에 즉시 알림을 받아 애플리케이션 로직을 실행할 수 있습니다.

CQN의 주요 이점

  • 실시간 대응: 데이터 변경 즉시 알림을 받으므로, 애플리케이션이 최신 데이터에 기반하여 즉각적으로 대응할 수 있습니다.
  • 자원 효율성: 주기적인 폴링 방식과 달리, 데이터가 변경될 때만 알림이 발생하므로 불필요한 데이터베이스 자원 소모를 줄일 수 있습니다.
  • 확장성: CQN은 수많은 클라이언트를 효과적으로 지원할 수 있도록 설계되어, 대규모 환경에서도 안정적인 서비스를 제공합니다.
  • 유연성: 애플리케이션은 다양한 조건과 이벤트를 기반으로 알림을 등록하고, 필요한 정보만 추출하여 사용할 수 있습니다.

CQN 작동 방식

  1. 애플리케이션 등록: 애플리케이션은 데이터베이스에 연결하여 특정 테이블이나 뷰에 대한 변경 사항을 감지하기 위한 CQN 등록을 생성합니다. 이때 알림을 받을 이벤트 유형 (예: INSERT, UPDATE, DELETE) 및 필터 조건을 지정할 수 있습니다.
  2. 알림 생성: 데이터베이스 내에서 지정된 테이블이나 뷰에 데이터 변경이 발생하면, 데이터베이스는 해당 CQN 등록에 대한 알림을 생성합니다.
  3. 알림 전송: 데이터베이스는 생성된 알림을 OCI (Oracle Call Interface) 클라이언트로 전송합니다. OCI는 알림을 비동기적으로 애플리케이션에 전달하는 역할을 합니다.
  4. 애플리케이션 처리: 애플리케이션은 OCI로부터 알림을 수신하고, 등록 시 지정한 콜백 함수를 실행하여 필요한 작업을 수행합니다.

CQN 설정 및 사용 예제

아래 예제는 PL/SQL을 사용하여 CQN을 설정하고 사용하는 방법을 보여줍니다.

PL/SQL Notification Handler 생성

먼저, 알림을 처리할 PL/SQL 스토어드 프로시저를 생성합니다.

  
  CREATE OR REPLACE PROCEDURE emp_change_handler (
  ntfnds IN SYS.AQ$_JMS_NOTE
  ) AS
  BEGIN
  -- 알림 정보 추출
  DBMS_OUTPUT.PUT_LINE('알림 발생!');
  DBMS_OUTPUT.PUT_LINE('이벤트 유형: ' || ntfnds.event_desc.dml_type);
  DBMS_OUTPUT.PUT_LINE('테이블 이름: ' || ntfnds.event_desc.table_name);
  -- 추가적인 알림 처리 로직 구현
  END;
  /

CQN 등록 생성

다음으로, DBMS_CQ_NOTIFICATION.REGISTER 프로시저를 사용하여 CQN 등록을 생성합니다. 이 프로시저는 등록 ID, 쿼리, 옵션 및 핸들러 프로시저를 인수로 받습니다.

  
  DECLARE
  regid NUMBER;
  qry VARCHAR2(200) := 'SELECT employee_id, last_name, salary FROM employees WHERE department_id = 10';
  BEGIN
  regid := DBMS_CQ_NOTIFICATION.REGISTER (
  qry  => qry,
  qosflags => DBMS_CQ_NOTIFICATION.QOS_ROWIDS + DBMS_CQ_NOTIFICATION.QOS_DML,
  handler => 'emp_change_handler',
  timeout => DBMS_CQ_NOTIFICATION.MAX_TIMEOUT,
  nvl(NULL,'hr')
  );
  
  DBMS_OUTPUT.PUT_LINE('등록 ID: ' || regid);
  COMMIT;
  END;
  /

설명:

  • qry: 알림을 등록할 SQL 쿼리입니다. 이 쿼리의 결과 집합에 영향을 주는 변경 사항이 발생하면 알림이 전송됩니다.
  • qosflags: 알림의 품질 설정을 지정하는 플래그입니다. DBMS_CQ_NOTIFICATION.QOS_ROWIDS는 변경된 행의 ROWID를 포함하고, DBMS_CQ_NOTIFICATION.QOS_DML는 DML 이벤트 유형(INSERT, UPDATE, DELETE)을 포함하도록 지정합니다.
  • handler: 알림을 처리할 PL/SQL 프로시저의 이름입니다.
  • timeout: 등록의 유효 기간을 초 단위로 지정합니다. DBMS_CQ_NOTIFICATION.MAX_TIMEOUT은 등록이 만료되지 않도록 지정합니다.

테이블 변경 및 알림 확인

이제 employees 테이블의 department_id = 10인 행에 데이터 변경을 수행해 봅니다. 이렇게 하면 알림이 생성되고 emp_change_handler 프로시저가 실행됩니다.

  
  UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 200;
  COMMIT;

이 코드를 실행하면 emp_change_handler 프로시저에서 알림 정보가 출력되는 것을 확인할 수 있습니다.

OCI를 이용한 CQN 등록 (C 코드 예제)

CQN을 OCI 애플리케이션에서 사용하는 방법을 설명합니다. 다음은 OCI를 사용하여 CQN을 등록하고 알림을 처리하는 간단한 예제입니다.

  
  #include <stdio.h>
  #include <stdlib.h>
  #include <string.h>
  #include <oci.h>

  // ... (OCI 환경 설정 및 연결 코드 생략) ...

  int main()
  {
  // OCI 변수 선언
  OCIEnv          *envhp;
  OCISvcCtx       *svchp;
  OCIStmt         *stmtp;
  OCIDefine       *defnp = (OCIDefine *) 0;
  OCIBind         *bndp = (OCIBind *) 0;
  OCIError        *errhp;
  OCISession      *authp = (OCISession *) 0;

  // 등록 ID를 저장할 변수
  ub4             regid = 0;
  // ... (OCI 핸들 초기화 코드 생략) ...

  // CQN 등록을 위한 SQL 쿼리
  const char *query = "SELECT employee_id, last_name FROM employees WHERE department_id = :1";
  
  // 콜백 컨텍스트 - 콜백 함수에 전달될 데이터
  typedef struct {
  char *message;
  } callback_context_t;
  
  callback_context_t *ctxt = (callback_context_t*)malloc(sizeof(callback_context_t));
  ctxt->message = "알림 처리됨!";
  
  OCICQNotificationDescriptor *notif_descr = NULL;
  
  // 알림 콜백 함수
  static void notificationCallback(
  dvoid   *ctxp,
  OCICQNotificationDescriptor *notifdp
  )
  {
  callback_context_t *ctxt = (callback_context_t*)ctxp;
  printf("%s\n", ctxt->message);
  printf("알림 발생! 테이블: %s, 이벤트: %s\n", notifdp->table_name, notifdp->event_desc);
  }

  //  CQN 등록
  sword regStatus = OCICQRegister(svchp,
  errhp,
  ®id,
  (const text *) query,
  strlen(query),
  (void *)ctxt,
  notificationCallback,
  OCI_CQ_QOS_RELIABLE | OCI_CQ_QOS_ROWIDS,
  OCI_DEFAULT);
  
  if (regStatus != OCI_SUCCESS)
  {
  printf("CQN 등록 실패!\n");
  return 1;
  }

  printf("CQN 등록 성공, 등록 ID: %u\n", regid);

  // ... (OCI cleanup 코드 생략) ...
  return 0;
  }

고려 사항 및 제한 사항

  • 보안: CQN 등록을 생성하는 사용자에게는 알림을 받는 테이블에 대한 적절한 권한이 있어야 합니다.
  • 자원 관리: CQN은 데이터베이스 자원을 사용하므로, 불필요한 등록을 최소화하고 타임아웃 설정을 통해 장기간 유휴 상태인 등록을 관리해야 합니다.
  • 알림 손실: 네트워크 문제 또는 데이터베이스 장애로 인해 알림이 손실될 수 있습니다. 애플리케이션은 이러한 가능성에 대비하여, 필요한 경우 데이터 일관성을 검증하는 로직을 구현해야 합니다.
  • 복잡성: 복잡한 쿼리 또는 필터 조건을 사용하는 CQN 등록은 데이터베이스 성능에 영향을 미칠 수 있습니다.

결론

CQN은 Oracle 데이터베이스 애플리케이션의 응답성을 향상시키고 자원 사용량을 줄이는 데 유용한 기능입니다. 이 가이드에서 설명한 개념과 예제를 통해 CQN을 효과적으로 활용하여 실시간 데이터 변경에 대응하는 애플리케이션을 개발할 수 있습니다. 각 환경에 맞춰 튜닝하는 것이 중요합니다.

궁금한 점이나 추가적인 정보가 필요하시면 언제든지 질문해주세요.

위로 스크롤