SQL 격리

이 블로그 게시물은 SQL 검역소의 개념을 소개합니다. Oracle® Resource Manager를 사용하여 CPU 및 I/O와 같은 리소스의 사용을 규제하고 제한할 수 있습니다. 가장 흥미로운 사실은 정의된 임계값을 초과하는 장기 실행 쿼리의 실행을 방지할 수 있다는 것입니다.

SQL 격리란 무엇입니까?

격리는 격리를 의미합니다. SQL 격리는 런어웨이 쿼리의 오버헤드를 제거하는 데 사용할 수 있는 버전 19c 기능입니다. Runaway 쿼리는 리소스 또는 런타임 제한을 초과하고 리소스, CPU 및 IO를 많이 사용하기 때문에 Resource Manager에 의해 종료되는 쿼리입니다.

이 기능은 Exadata(Oracle Database Enterprise Edition onEngineered Systems) 및 DBCS/ExaCS(Oracle Database Exadata Cloud Service)에서만 사용할 수 있습니다. 이를 테스트하기 위해 밑줄 매개변수를 설정하고 다음 명령을 사용하여 데이터베이스를 바운스했습니다.

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

장기 실행 쿼리는 어떻습니까?

DBRM(데이터베이스 리소스 관리자)은 IO 및 CPU와 같은 특정 리소스 사용 임계값을 초과하는 SQL 문을 종료할 수 있는 백그라운드 프로세스입니다. 또한 최대 런타임 임계값을 초과하는 모든 쿼리를 종료할 수 있습니다.

표시된 SQL 실행 계획과 제한을 초과하는 SQL 문은 격리됩니다. 즉, 동일한 실행 계획을 사용하여 동일한 SQL이 다시 실행되면 SQL 문이 종료되고 다음 오류가 깜박입니다.

  error: ORA-56955: quarantined plan used.

이러한 오류가 발생하면 개체 검역소에서 오류를 일으킨 개체를 격리한 다음 모니터링하여 나머지 데이터베이스에 미치는 영향을 확인합니다.

테이블과 인덱스가 아니라 세션, 프로세스, SGA 트랜잭션 및 라이브러리 캐시를 포함하여 Oracle이 격리할 수 있는 개체를 말하는 것입니다.

이제 다음 예와 같이 주어진 임계값보다 오래 실행되는 SQL 쿼리를 종료하거나 취소할 수 있습니다.

그림 1:Runaway Sql 문 이미지 출처:https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf

검역된 개체에 대해 알아보기

https://dbaparadise.com/2020/01/everything-you-need-to-know-about-quarantined-objects/에 따르면 검역된 개체에 대한 정보를 얻으려면 V$QUARANTINE 및 V$QUARANTINE_SUMMARY 객체의 유형, 객체의 메모리 주소, 실제 ORA 오류, 오류 날짜 및 시간을 알려주는 뷰입니다.

다음 예제와 같이 서버의 CPU 사용률이 실행될 때 폭주 쿼리의 응답처럼 보인다고 가정할 수 있습니다. CPU를 용량의 거의 100%까지 활용하여 세 개의 쿼리가 동시에 실행되는 것을 볼 수 있습니다.

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

SQL 검역소

SQL Quarantine을 사용하여 런어웨이 쿼리의 오버헤드를 제거할 수 있습니다. ResourceManager가 리소스 또는 런타임 제한을 초과하는 SQL 문을 감지하면 해당 문에서 사용하는 SQL 실행 계획이 격리됩니다.

동일한 SQL 문이 동일한 SQL 계획을 사용하여 실행되면 즉시 종료됩니다. 이렇게 하면 시스템 리소스 사용량을 크게 줄이는 데 도움이 됩니다. 다음 그림에서 쿼리가 거의 실행되지 않을 때 높은 활용도를 볼 수 있습니다. 그러나 실행 전에 종료하면 격리된 후 더 이상 시스템 리소스를 소비하지 않습니다.

그림 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;

그런 다음 리소스 관리자를 설정하려면 다음 단계를 완료해야 합니다.

대기 영역 생성:

  begin
  dbms_resource_manager.create_pending_area();
  end;
  /

하나 이상의 리소스 소비자 그룹 생성:

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

리소스 계획 생성:

 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;
  /

리소스 계획 지시문을 만듭니다. 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;
 /

계획, 소비자 그룹 및 지침에 대한 보류 영역을 확인하고 제출합니다.

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

여기에서 권한을 할당하고 소비자 그룹을 사용자에게 할당해야 합니다.

<올 시작="6">

권한, 역할 및 할당된 사용자에 대한 보류 영역 만들기:

  begin
  dbms_resource_manager.create_pending_area();
  end;
  /

리소스 소비자 그룹에 대한 전환 권한을 사용자 또는 역할에 부여합니다.

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

리소스 소비자 그룹에 사용자를 할당합니다.

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

보류 중인 영역을 확인하고 제출하십시오.

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

이제 계획을 업데이트해야 하며 보류 중인 영역을 다시 제출해야 합니다.

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;
/

다음 단계

이전 단계는 리소스 관리자 설정을 완료합니다. 완료한 후에는 이 계획을 인스턴스에 할당하기만 하면 됩니다.

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

이 경우 리소스 관리자는 ORA-00040으로 실행을 종료했습니다. 오류.

문의 SQL_ID를 찾을 수 있습니까? 3hdkutq4krg4c입니다. .

SQL 검역소 생성

DBMS_SQLQ를 사용할 수 있습니다. 격리해야 하는 SQL 문의 실행 계획에 대한 격리 구성을 생성하는 패키지입니다.

다음 예와 같이 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 Quarantine이 설정된 상태에서 동일한 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이라는 두 개의 새 열이 표시됩니다. 및 voided_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 Quarantine 기능은 비용이 많이 들고 격리된 SQL 문의 향후 실행을 방지하기 때문에 성능 향상에 도움이 됩니다.

피드백 탭을 사용하여 의견을 작성하거나 질문하십시오. 영업 채팅을 클릭할 수도 있습니다. 지금 채팅하고 대화를 시작하세요.