전용 Exadata 인프라의 자율운영 AI 데이터베이스에서 SQL 추적 사용

Autonomous AI Database on Dedicated Exadata Infrastructure와 함께 SQL Tracing을 사용하면 애플리케이션에서 로드가 많은 SQL 문과 같이 과도한 데이터베이스 워크로드의 소스를 식별할 수 있습니다.

SQL Trace 정보

응용 프로그램 작업이 예상보다 오래 걸리는 경우 구문 분석, 실행 및 패치(fetch) 단계에서 해당 SQL 문에 소요된 시간과 같은 세부 정보와 함께 이 작업의 일부로 실행된 모든 SQL 문의 추적을 얻으면 성능 문제의 원인을 식별하고 해결하는 데 도움이 됩니다. 자율운영 AI 데이터베이스에서 SQL 추적을 사용하여 이를 달성할 수 있습니다.

SQL 추적은 자율운영 AI 데이터베이스에서 기본적으로 사용 안함으로 설정됩니다. SQL 추적 데이터 수집을 시작하려면 이 매개변수를 활성화해야 합니다. SQL 문을 추적하려면 ADMIN 유저로 다음 작업을 구현하십시오.

자율운영 AI 데이터베이스에서 SQL 추적 구성

SQL 추적을 위해 자율운영 AI 데이터베이스를 구성하려면 다음을 수행합니다.
  1. 클라우드 오브젝트 스토리지에 추적 파일을 저장할 버킷을 생성합니다.
    SQL 추적 파일을 저장하기 위해 버킷은 자율운영 AI 데이터베이스가 지원하는 모든 클라우드 객체 저장소에 있을 수 있습니다. 예를 들어, Oracle Cloud Infrastructure Object Storage에서 버킷을 생성하려면 버킷 생성을 참조하십시오.

    참고:

    SQL 추적 파일은 표준 스토리지 계층에서 생성된 버킷에서만 지원되므로 Oracle Cloud Infrastructure Object Storage에서 버킷을 생성할 때 스토리지 계층으로 표준을 선택해야 합니다. 표준 오브젝트 스토리지 계층에 대한 자세한 내용은 스토리지 계층 이해를 참조하십시오.
  2. DBMS_CLOUD.CREATE_CREDENTIAL을 사용하여 클라우드 오브젝트 스토리지 계정에 대한 인증서를 생성합니다.
    예:
    BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'adb_user@example.com', 
    password => 'password'
);END;
/

    다양한 오브젝트 스토리지 서비스에 대한 username 및 password 매개변수의 인수에 대한 자세한 내용은 CREATE_CREDENTIAL Procedure를 참조하십시오.

  3. SQL 추적 파일용 버킷에 대한 클라우드 오브젝트 스토리지 URL을 지정하고 클라우드 오브젝트 스토리지에 액세스하기 위한 인증서를 지정하려면 초기화 매개변수를 설정합니다.
    1. Cloud Object Storage에서 로깅 버킷을 지정하려면 데이터베이스 속성 DEFAULT_LOGGING_BUCKET을 설정합니다.
      예를 들어 Oracle Cloud Infrastructure(OCI) Object Storage를 사용하여 버킷을 생성하는 경우,
      SET DEFINE OFF;
ALTER DATABASE PROPERTY SET 
   DEFAULT_LOGGING_BUCKET = 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/bucket_name/o/';

      여기서 namespace-string은 OCI Object Storage 네임스페이스이고 bucket_name은 이전에 생성한 버킷의 이름입니다. 자세한 내용은 Understanding Object Storage Namespaces를 참조하십시오.

      지역 목록은 Regions and Availability Domains을 참조하십시오.

      SQL 추적 파일에 사용하는 클라우드 객체 저장소는 자율운영 AI 데이터베이스가 지원하는 모든 클라우드 객체 저장소일 수 있습니다.

    2. 데이터베이스 속성 DEFAULT_CREDENTIAL을 2단계에서 생성한 인증서로 설정합니다.
      예:
      ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME';

      인증서에 스키마 이름을 포함해야 합니다. 이 예에서 스키마는 ADMIN입니다.

자율운영 AI 데이터베이스에서 SQL 추적 사용

주:

SQL 추적을 활성화하면 추적 수집이 활성화되어 있는 동안 세션에 대한 응용 프로그램 성능이 저하될 수 있습니다. 이 성능 영향은 추적 데이터 수집 및 저장 오버헤드로 인해 발생할 수 있습니다.

데이터베이스 세션에 대해 SQL 추적을 활성화하려면 다음을 수행합니다.

  1. 선택적으로 응용 프로그램에 대한 클라이언트 식별자를 설정합니다. 이 단계는 선택 사항이지만 권장됩니다. SQL 추적은 추적 파일이 클라우드 객체 저장소에 기록될 때 클라이언트 식별자를 추적 파일 이름의 구성요소로 사용합니다.
    예:
    BEGIN
  DBMS_SESSION.SET_IDENTIFIER('sqlt_test');
END;
/
  2. 선택적으로 응용 프로그램의 모듈 이름을 설정합니다. 이 단계는 선택 사항이지만 권장됩니다. SQL 추적은 추적 파일이 클라우드 객체 저장소에 기록될 때 추적 파일 이름의 구성요소로 모듈 이름을 사용합니다.

    예:

    BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('modname', null);
END;
/
  3. SQL Trace 기능을 활성화합니다.
    ALTER SESSION SET SQL_TRACE = TRUE;
  4. 작업 로드를 실행합니다.
    이 단계에서는 전체 응용 프로그램 또는 응용 프로그램의 특정 부분을 실행합니다. 데이터베이스 세션에서 작업 로드를 실행하는 동안 SQL Trace 데이터가 수집됩니다.
  5. SQL 추적 사용 안함.
    SQL 추적을 비활성화하면 세션에 대해 수집된 데이터가 세션의 테이블과 SQL 추적을 설정할 때 구성한 버킷의 추적 파일에 기록됩니다.

SQL 추적을 사용 안함으로 설정

SQL 추적을 비활성화하려면 다음을 수행하십시오.
  1. SQL Trace 기능을 비활성화합니다.
    ALTER SESSION SET SQL_TRACE = FALSE;
  2. 필요에 따라 사용자 환경에 맞게 데이터베이스 속성 DEFAULT_LOGGING_BUCKET을 재설정하여 Cloud Object Storage에서 로깅 버킷의 값을 지울 수 있습니다.
    예:
    ALTER DATABASE PROPERTY SET DEFAULT_LOGGING_BUCKET = '';
SQL 추적을 사용 안함으로 설정하면 추적이 사용으로 설정된 상태로 세션이 실행되는 동안 수집된 추적 데이터가 테이블에 복사되어 클라우드 객체 저장소의 추적 파일로 전송됩니다.

자율운영 AI 데이터베이스에서 클라우드 객체 저장소에 저장된 추적 파일 보기

SQL 추적 파일 데이터를 사용하여 자율운영 AI 데이터베이스에서 애플리케이션 성능을 분석합니다. 데이터베이스 세션에서 SQL 추적을 사용 안함으로 설정하면 데이터가 DEFAULT_LOGGING_BUCKET로 구성된 클라우드 객체 저장소 버킷에 기록됩니다.

SQL Trace 기능은 세션에서 수집된 추적 데이터를 클라우드 객체 저장소에 다음 형식으로 기록합니다.

default_logging_bucket/sqltrace/clientID/moduleName/sqltrace_numID1_numID2.trc

파일 이름의 구성 요소는 다음과 같습니다.

  • default_logging_bucket: DEFAULT_LOGGING_BUCKET 데이터베이스 속성의 값입니다. 자세한 내용은 자율운영 AI 데이터베이스에서 SQL 추적 구성을 참조하십시오.

  • clientID: 클라이언트 식별자입니다. 자세한 내용은 자율운영 AI 데이터베이스에서 SQL 추적 사용을 참조하십시오.

  • moduleName: 모듈 이름입니다. 자세한 내용은 자율운영 AI 데이터베이스에서 SQL 추적 사용을 참조하십시오.

  • numID1_numID2: SQL Trace 기능에서 제공하는 두 개의 식별자입니다. numID1 및 numID2 숫자 값은 추적을 사용하여 각 추적 파일 이름을 다른 세션과 구분하고 클라우드 오브젝트 스토리지의 동일한 버킷에 추적 파일을 생성합니다.

    데이터베이스 서비스가 병렬화를 지원하고 세션이 Parallel Query를 실행하는 경우 SQL Trace 기능은 numID1 및 numID2 값이 다른 여러 Trace File을 생성할 수 있습니다.

주:

SQL 추적이 동일한 세션 내에서 여러 번 사용 및 사용 안함으로 설정된 경우 각 추적 반복은 클라우드 객체 저장소에 별도의 추적 파일을 생성합니다. 세션에서 생성된 이전 Trace를 겹쳐쓰지 않도록 하기 위해 이후에 생성된 파일은 동일한 이름 지정 규칙을 따르고 Trace File 이름에 숫자 접미어를 추가합니다. 이 숫자 접미어는 숫자 1로 시작하고 이후의 각 추적 반복에 대해 1씩 증가합니다.

예를 들어, 다음은 클라이언트 식별자를 "sql_test"로 설정하고 모듈 이름을 "modname"로 설정할 때 생성되는 샘플 Trace File 이름입니다.

sqltrace/sqlt_test/modname/sqltrace_5415_56432.trc

TKPROF를 실행하여 추적 파일을 읽을 수 있는 출력 파일로 변환할 수 있습니다.

  1. 객체 저장소의 추적 파일을 로컬 시스템으로 복사합니다.
  2. 추적 파일이 저장된 디렉토리로 이동합니다.
  3. 다음 구문을 사용하여 운영 체제 프롬프트에서 TKPROF 유틸리티를 실행합니다.
    tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
 [aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
 [explain=user/password] [record=filename4] [width=n]

    입력 및 출력 파일만 필수 인수입니다.

  4. 온라인 도움말을 보려면 인수 없이 TKPROF를 호출하십시오.
    TKPROF 유틸리티 사용에 대한 자세한 내용은 Oracle Database 19c SQL Tuning Guide의 Tools for End-to-End Application Tracing 또는 Oracle Database 26ai SQL Tuning Guide를 참조하십시오.

자율운영 AI 데이터베이스에서 SESSION_CLOUD_TRACE 뷰의 추적 데이터 보기

SQL 추적을 사용으로 설정하면 추적이 사용으로 설정된 세션의 SESSION_CLOUD_TRACE 뷰에서 클라우드 객체 저장소의 추적 파일에 저장된 것과 동일한 추적 정보를 사용할 수 있습니다.
데이터베이스 세션에 있는 동안 SESSION_CLOUD_TRACE 뷰에서 SQL 추적 데이터를 볼 수 있습니다. SESSION_CLOUD_TRACE 뷰에는 ROW_NUMBER 및 TRACE의 두 열이 포함됩니다.
DESC SESSION_CLOUD_TRACE

Name       Null? Type
---------- ----- ------------------------------
ROW_NUMBER       NUMBER
TRACE            VARCHAR2(32767)

ROW_NUMBER는 TRACE 열에 있는 추적 데이터의 순서를 지정합니다. 추적 파일에 기록된 각 추적 출력 행은 테이블의 행이 되며 TRACE 열에서 사용할 수 있습니다.

세션에 대해 SQL 추적을 사용 안함으로 설정한 후 SESSION_CLOUD_TRACE 뷰에서 질의를 실행할 수 있습니다.

예:
SELECT trace FROM SESSION_CLOUD_TRACE ORDERBY row_number;

SESSION_CLOUD_TRACE의 데이터는 세션 기간 동안 지속됩니다. 로그아웃하거나 세션을 닫으면 데이터를 더 이상 사용할 수 없습니다.

SQL Trace가 동일한 세션 내에서 여러 번 활성화 및 비활성화되면 SESSION_CLOUD_TRACE는 모든 반복에 대한 추적 데이터를 누적하여 표시합니다. 따라서 이전에 추적을 비활성화한 후 세션에서 추적을 다시 활성화해도 이전 반복으로 생성된 추적 데이터는 제거되지 않습니다.