Computer >> 컴퓨터 >  >> 프로그래밍 >> 데이터베이스

Oracle 19c SQL 격리(Quarantine) 기능 완벽 가이드: 비정상 쿼리 자동 차단하기

이번 포스트에서는 SQL 격리(SQL Quarantine) 개념을 소개합니다. Oracle® Resource Manager를 활용하면 CPU, I/O 등 시스템 자원의 사용량을 규제하고 제한할 수 있습니다. 특히 주목할 만한 점은, 미리 정의한 임계값을 초과하는 장시간 실행 쿼리의 실행 자체를 사전에 차단할 수 있다는 것입니다.

SQL 격리란 무엇인가?

격리(Quarantine)는 말 그대로 '고립'을 의미합니다. SQL 격리는 Oracle Database 19c에 새롭게 도입된 기능으로, 비정상적으로 자원을 과다 소모하는 쿼리(runaway query)가 시스템에 주는 부담을 제거하는 데 사용됩니다.

여기서 말하는 비정상 쿼리란 Resource Manager에 의해 종료될 만큼 자원 또는 실행 시간 제한을 초과하면서, CPU와 I/O를 대량으로 소모하는 쿼리를 뜻합니다.

이 기능은 Exadata(Engineered Systems 상의 Oracle Database Enterprise Edition)와 DBCS/ExaCS(Oracle Database Exadata Cloud Service) 환경에서만 제공됩니다. 일반 환경에서 테스트하려면 숨김 파라미터를 설정한 뒤 데이터베이스를 재기동해야 합니다.

Alter system set "_exadata_feature_on"=true scope=spfile;

장시간 실행되는 쿼리는 어떻게 처리될까?

Database Resource Manager(DBRM)는 백그라운드 프로세스로, I/O나 CPU 같은 자원 사용 임계값을 초과하는 SQL 문장을 강제로 종료할 수 있습니다. 최대 실행 시간(maximum run-time) 임계값을 넘긴 쿼리 역시 종료 대상입니다.

제한을 초과한 SQL 문장과 해당 실행 계획(execution plan)은 '격리' 처리됩니다. 즉, 동일한 SQL이 동일한 실행 계획으로 다시 실행되려 하면 즉시 중단되며 다음과 같은 오류가 발생합니다.

error: ORA-56955: quarantined plan used.

이러한 오류가 발생하면 Object Quarantine이 오류를 유발한 객체를 고립시키고, 해당 객체가 데이터베이스 전체에 미치는 영향을 모니터링합니다.

여기서 말하는 객체는 테이블이나 인덱스가 아니라, Oracle이 격리할 수 있는 세션(session), 프로세스(process), SGA 트랜잭션, 라이브러리 캐시(library cache) 등을 의미합니다.

이제 아래 예시처럼 일정 시간 임계값을 초과해 실행되는 SQL 쿼리를 종료하거나 취소할 수 있습니다.

Oracle 19c SQL 격리(Quarantine) 기능 완벽 가이드: 비정상 쿼리 자동 차단하기

그림 1: 비정상 SQL 문장(Runaway SQL Statement)
이미지 출처: https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf

격리된 객체 정보 확인하기

dbaparadise.com의 자료에 따르면, 격리된 객체에 대한 정보는 V$QUARANTINE 및 V$QUARANTINE_SUMMARY 뷰를 조회하여 확인할 수 있습니다. 이 뷰를 통해 객체 유형, 객체의 메모리 주소, 실제 발생한 ORA- 오류 코드, 오류 발생 일시 등을 파악할 수 있습니다.

아래 예시처럼 서버의 CPU 사용률을 보면, 비정상 쿼리가 실행될 때 어떤 현상이 나타나는지 짐작할 수 있습니다. 세 개의 쿼리가 동시에 실행되면서 CPU를 거의 100%까지 소진하는 모습을 확인할 수 있습니다.

Oracle 19c SQL 격리(Quarantine) 기능 완벽 가이드: 비정상 쿼리 자동 차단하기

그림 2: 비정상 SQL이 소모하는 CPU
이미지 출처: https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf

SQL 격리의 효과

SQL 격리를 활용하면 비정상 쿼리로 인한 시스템 부담을 제거할 수 있습니다. Resource Manager가 자원 또는 실행 시간 제한을 초과하는 SQL 문장을 감지하면, 해당 문장이 사용한 SQL 실행 계획이 격리됩니다.

이후 동일한 SQL 문장이 동일한 실행 계획으로 실행되려 하면 즉시 종료됩니다. 이를 통해 시스템 자원 사용량을 크게 절감할 수 있습니다. 아래 그림에서 몇 개의 쿼리만 실행 중인데도 높은 사용률이 나타나는 것을 볼 수 있지만, 격리된 후에는 실행 전에 차단되므로 더 이상 시스템 자원을 소모하지 않습니다.

Oracle 19c SQL 격리(Quarantine) 기능 완벽 가이드: 비정상 쿼리 자동 차단하기

그림 3: SQL 격리로 절약된 CPU
이미지 출처: https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf

격리 기능 사용 절차

실제로 기능이 어떻게 동작하는지 단계별로 살펴보겠습니다.

먼저, Exadata 환경에서 작업하는 것처럼 데이터베이스를 설정해야 합니다.

alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;

그다음, Resource Manager를 구성하기 위해 다음 단계를 진행합니다.

  1. 대기 영역(pending area) 생성:

    begin
dbms_resource_manager.create_pending_area();
end;
/

  2. 리소스 컨슈머 그룹(consumer group) 생성:

    begin
dbms_resource_manager.create_consumer_group(CONSUMER_GROUP=>'SQL_LIMIT',COMMENT=>'consumer group');
end;
/

  3. 리소스 플랜(resource plan) 생성:

    begin
dbms_resource_manager.set_consumer_group_mapping(attribute => 'ORACLE_USER',value => 'DBA1',consumer_group =>'SQL_LIMIT' );
dbms_resource_manager.create_plan(PLAN=> 'NEW_PLAN',COMMENT=>'Kill statement after exceeding total execution time');
end;
/

  4. 리소스 플랜 디렉티브(plan directive) 생성. CANCEL_SQL 그룹은 기본적으로 이미 존재합니다:

    begin
dbms_resource_manager.create_plan_directive(
plan => 'NEW_PLAN',
group_or_subplan => 'SQL_LIMIT',
comment => 'Kill statement after exceeding total execution time',
switch_group=>'CANCEL_SQL',
switch_time => 10,
switch_estimate=>false);
end;
/
begin
dbms_resource_manager.create_plan_directive(PLAN=> 'NEW_PLAN', GROUP_OR_SUBPLAN=>'OTHER_GROUPS',COMMENT=>'leave others alone', CPU_P1=>100 );
end;
/

  5. 플랜, 컨슈머 그룹, 디렉티브에 대한 대기 영역 검증 및 제출:

    begin
dbms_resource_manager.validate_pending_area();
end;
/
begin
dbms_resource_manager.submit_pending_area();
end;
/

이제 권한을 부여하고 사용자에게 컨슈머 그룹을 할당해야 합니다.

  1. 권한, 롤, 사용자 할당을 위한 대기 영역 생성:

    begin
dbms_resource_manager.create_pending_area();
end;
/

  2. 사용자 또는 롤에 리소스 컨슈머 그룹 전환(switch) 권한 부여:

    begin
dbms_resource_manager_privs.grant_switch_consumer_group('DBA1','SQL_LIMIT',false);
end;
/

  3. 사용자를 리소스 컨슈머 그룹에 할당:

    begin
dbms_resource_manager.set_initial_consumer_group('DBA1','SQL_LIMIT');
end;
/

  4. 대기 영역 검증 및 제출:

    begin
dbms_resource_manager.validate_pending_area();
end;
/
begin
dbms_resource_manager.submit_pending_area();
end;
/

  5. 플랜 갱신 후 대기 영역 다시 제출:

    begin
dbms_resource_manager.clear_pending_area;
dbms_resource_manager_create_pending_area;
end;
/
begin
dbms_resource_manager.update_plan_directive(plan=>'NEW_PLAN',group_or_subplan=>'SQL_LIMIT',new_switch_elapsed_time=>10, new_switch_for_call=>TRUE,new_switch_group=>'CANCEL_SQL');
end;
/

begin
dbms_resource_manager.validate_pending_area();
dbms_resource_manager.submit_pending_area;
end;
/

다음 단계

위 단계를 모두 마치면 Resource Manager 설정이 완료됩니다. 이후에는 이 플랜을 인스턴스에 할당하기만 하면 됩니다.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=NEW_PLAN;

DBA1 사용자로 로그인한 뒤, 리소스 플랜에 정의된 10초 경과 시간 임계값을 초과하는 쿼리를 실행해 봅니다.

이때 반드시 DBA1 사용자로 문장을 실행해야 하며, DBA1은 DBA 뷰에 대한 접근 권한이 있어야 합니다.

select a.owner_name,b.product_name,c.location,d.country_code
from import_pr_table a, item_table b, locate_dealer_table c,country_table d;

ERROR at line 1:
ORA-00040: active time limit exceeded - call aborted

이 경우 Resource Manager가 실행을 종료하며 ORA-00040 오류가 발생한 것을 확인할 수 있습니다.

해당 문장의 SQL_ID를 찾을 수 있을까요? 바로 3hdkutq4krg4c입니다.

SQL 격리 생성하기

DBMS_SQLQ 패키지를 사용하면 격리가 필요한 SQL 문장의 실행 계획에 대한 격리 구성(quarantine configuration)을 생성할 수 있습니다.

아래 예시처럼 SQL 텍스트 또는 SQL_ID를 기준으로 격리를 지정할 수 있습니다.

CREATE_QUARANTINE_BY_SQL_ID
or
CREATE_QUARANTINE_BY_SQL_TEXT

DECLARE
quarantine_sql VARCHAR2(30);
BEGIN
quarantine_sql := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(SQL_ID => '3hdkutq4krg4c');
END;
/

격리 구성을 생성한 후에는 DBMS_SQLQ.ALTER_QUARANTINE 프로시저를 사용해 격리 임계값을 지정할 수 있습니다.

BEGIN
DBMS_SQLQ.ALTER_QUARANTINE(
QUARANTINE_NAME => 'SQL_QUARANTINE_3hdkutq4krg4c',
PARAMETER_NAME => 'ELAPSED_TIME',
PARAMETER_VALUE => '10');
END;
/

이제 DBA_SQL_QUARANTINE 뷰를 조회하면 어떤 SQL 문장이 격리되었는지 확인할 수 있습니다.

SQL 격리가 적용된 상태에서 동일한 SQL 문장을 다시 실행하려 하면, 더 이상 실행되지 않습니다.

select a.owner_name,b.product_name,c.location,d.country_code from import_pr_table a, item_table b, locate_dealer_table c,country_table d;
ERROR at line 1:
ORA-56955: quarantined plan used

위 오류 메시지는 해당 문장에 사용된 실행 계획이 격리된 계획 목록에 포함되어 있다는 의미입니다. 임계값 한도를 초과했기 때문에 쿼리가 취소된 것입니다.

V$SQL 뷰를 확인해 보면 sql_quarantine과 avoided_executions라는 두 개의 새 컬럼을 볼 수 있습니다.

select sql_quarantine,avoided_executions from v$sql where sql_id='3hdkutq4krg4c';
SQL> select sql_quarantine,avoided_executions
2 from v$sql where sql_id='3hdkutq4krg4c';

SQL_QUARANTINE       AVOIDED_EXECUTIONS
---------------      ---------------
SQL_QUARANTINE_3hdkutq4krg4c
1

결론

SQL 격리 기능은 비용이 많이 드는 격리된 SQL 문장의 재실행을 원천적으로 차단함으로써, 데이터베이스 성능 향상에 크게 기여합니다.

궁금한 점이나 의견이 있다면 피드백 탭을 이용해 남겨 주세요. 또한 Sales Chat 버튼을 클릭해 지금 바로 상담을 시작할 수도 있습니다.