쿼리 변경 알림 (Query Change Notification) 사용

개요

쿼리 변경 알림(Continuous Query Notification, CQN)은 오라클 데이터베이스에서 데이터 변경 사항을 실시간으로 감지하고, 애플리케이션에 비동기적으로 알리는 강력한 기능입니다. 이를 통해 애플리케이션은 데이터베이스의 변경 사항을 주기적으로 폴링(polling)하는 대신, 필요한 시점에만 데이터를 갱신하여 성능을 최적화할 수 있습니다. CQN은 웹 애플리케이션, 캐싱 시스템, 실시간 분석 등 다양한 분야에서 활용될 수 있습니다.

CQN 주요 개념 및 용어

  • 객체 변경 알림 (Object Change Notification, OCN): 테이블, 뷰 등 데이터베이스 객체의 변경 사항을 감지하는 알림 유형입니다. 객체 구조(스키마) 변경 또는 데이터 변경 시 알림이 발생합니다.
  • 쿼리 결과 변경 알림 (Query Result Change Notification, QRCN): 특정 쿼리의 결과 집합에 변경이 발생했을 때 알림을 제공합니다. 필터 조건에 의해 결과가 추가, 삭제, 수정될 때 알림이 발생합니다.
  • 알림 핸들러 (Notification Handler): 알림 발생 시 실행되는 클라이언트 측 콜백 프로시저 또는 서버 측 PL/SQL 저장 프로시저입니다. 알림 정보를 처리하고 필요한 작업을 수행합니다.
  • 등록 (Registration): 애플리케이션이 특정 객체 또는 쿼리에 대한 알림을 받기 위해 데이터베이스에 요청하는 과정입니다.
  • 큐 (Queue): 알림 메시지가 저장되는 오라클 고급 큐(Advanced Queueing, AQ)입니다.

CQN 구성 단계

CQN을 사용하려면 다음과 같은 단계를 거쳐야 합니다.

  1. 알림 핸들러 생성: 알림 발생 시 실행할 PL/SQL 저장 프로시저를 작성합니다.
  2. 등록 객체 생성: DBMS_CQ_NOTIFICATION.CREATE_REG_INFO 함수를 사용하여 등록 객체를 생성합니다. 알림 유형, 쿼리, 콜백 함수 등을 설정합니다.
  3. 알림 등록: DBMS_CQ_NOTIFICATION.REGISTER 프로시저를 사용하여 알림을 등록합니다.
  4. 알림 수신 및 처리: 데이터베이스 변경이 발생하면 알림 핸들러가 실행되어 알림 정보를 처리합니다.

예제 코드: 테이블 변경 알림 설정

다음 예제는 employees 테이블에 DML 작업이 발생할 때 알림을 받는 PL/SQL 프로시저를 보여줍니다.

 
 CREATE OR REPLACE PROCEDURE employee_change_handler (p_ntfnds IN SYS.AQ$_JMS_NOTIFICATION)
 AS
  v_msg SYS.AQ$_JMS_TEXT_MESSAGE;
  v_body VARCHAR2(32767);
 BEGIN
  v_msg := SYS.AQ$_JMS_TEXT_MESSAGE.CONSTRUCT(p_ntfnds.payload);
  v_body := v_msg.TEXT_VC;
  DBMS_OUTPUT.PUT_LINE('알림 발생: ' || v_body);
 EXCEPTION
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
 END;
 /
 
 DECLARE
  reginfo DBMS_CQ_NOTIFICATION.REG_INFO;
  regid NUMBER;
 BEGIN
  reginfo := DBMS_CQ_NOTIFICATION.NEW_REG_INFO();
  reginfo.ntf_grouping_class := DBMS_CQ_NOTIFICATION.NTF_GROUPING_CLASS_TRANSACTION;
  reginfo.ntf_types := DBMS_CQ_NOTIFICATION.NTF_TYPE_DML;
  reginfo.timeout := 60; -- 초 단위
  regid := DBMS_CQ_NOTIFICATION.REGISTER(
   registration_info => reginfo,
   query => 'SELECT employee_id, last_name FROM employees',
   callback => 'employee_change_handler',
   namespace => DBMS_CQ_NOTIFICATION.NAMESPACE_AQ,
   qos => DBMS_CQ_NOTIFICATION.QOS_RELIABLE
  );
  DBMS_OUTPUT.PUT_LINE('알림 등록 ID: ' || regid);
 END;
 /

설명:

  • employee_change_handler 프로시저는 알림 정보를 받아 출력하는 핸들러입니다.
  • DBMS_CQ_NOTIFICATION.NEW_REG_INFO() 를 사용하여 등록 정보를 초기화합니다.
  • ntf_grouping_class 는 트랜잭션 기반 알림을 지정합니다.
  • ntf_types 는 DML 작업에 대한 알림을 지정합니다.
  • timeout 은 알림 유효 시간(초)을 설정합니다.
  • DBMS_CQ_NOTIFICATION.REGISTER 는 알림을 등록하고 등록 ID를 반환합니다.
  • NAMESPACE_AQ 는 AQ를 사용하도록 지정합니다.
  • QOS_RELIABLE 은 신뢰성 있는 알림 전송을 요청합니다.

예제 코드: 쿼리 결과 변경 알림 설정

다음 예제는 특정 급여 이상을 받는 직원의 수가 변경될 때 알림을 받는 PL/SQL 프로시저를 보여줍니다.

 
 CREATE OR REPLACE PROCEDURE salary_change_handler (p_ntfnds IN SYS.AQ$_JMS_NOTIFICATION)
 AS
  v_msg SYS.AQ$_JMS_TEXT_MESSAGE;
  v_body VARCHAR2(32767);
 BEGIN
  v_msg := SYS.AQ$_JMS_TEXT_MESSAGE.CONSTRUCT(p_ntfnds.payload);
  v_body := v_msg.TEXT_VC;
  DBMS_OUTPUT.PUT_LINE('알림 발생: ' || v_body);
 EXCEPTION
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
 END;
 /

 DECLARE
  reginfo DBMS_CQ_NOTIFICATION.REG_INFO;
  regid NUMBER;
 BEGIN
  reginfo := DBMS_CQ_NOTIFICATION.NEW_REG_INFO();
  reginfo.ntf_grouping_class := DBMS_CQ_NOTIFICATION.NTF_GROUPING_CLASS_TRANSACTION;
  reginfo.ntf_types := DBMS_CQ_NOTIFICATION.NTF_TYPE_QUERY;
  reginfo.timeout := 60;
  regid := DBMS_CQ_NOTIFICATION.REGISTER(
   registration_info => reginfo,
   query => 'SELECT COUNT(*) FROM employees WHERE salary > 10000',
   callback => 'salary_change_handler',
   namespace => DBMS_CQ_NOTIFICATION.NAMESPACE_AQ,
   qos => DBMS_CQ_NOTIFICATION.QOS_RELIABLE
  );
  DBMS_OUTPUT.PUT_LINE('알림 등록 ID: ' || regid);
 END;
 /

주의사항:

  • 쿼리 결과 변경 알림은 객체 변경 알림보다 더 많은 리소스를 소비할 수 있습니다. 쿼리가 복잡할수록 성능에 미치는 영향이 커질 수 있으므로 신중하게 사용해야 합니다.
  • 오라클 데이터베이스 12c 이상에서만 NTF_TYPE_QUERY 를 지원합니다.

CQN 문제 해결

CQN 설정 및 사용 중 발생할 수 있는 일반적인 문제와 해결 방법은 다음과 같습니다.

  • ORA-29975: 알림 큐가 존재하지 않음: 지정된 큐가 존재하지 않거나 액세스 권한이 없는 경우 발생합니다. 큐를 생성하고 필요한 권한을 부여해야 합니다.
  • ORA-30673: 알림 프로시저가 존재하지 않음: 지정된 PL/SQL 프로시저가 존재하지 않거나 실행 권한이 없는 경우 발생합니다. 프로시저를 생성하고 필요한 권한을 부여해야 합니다.
  • 알림이 발생하지 않음: 알림 유형, 쿼리, 콜백 함수 등이 올바르게 설정되었는지 확인합니다. 또한 테이블 변경이 실제로 발생했는지 확인합니다.

결론

쿼리 변경 알림은 애플리케이션 성능과 응답성을 향상시키는 데 유용한 기능입니다. 올바르게 구성하고 사용하면 데이터베이스 변경 사항을 실시간으로 감지하고 애플리케이션을 적절하게 갱신하여 효율적인 데이터 관리를 할 수 있습니다. 다만, 과도한 사용은 성능 저하를 야기할 수 있으므로 사용 목적에 맞게 적절히 설정해야 합니다.

위로 스크롤