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

전용 Exadata 인프라의 자율운영 AI 데이터베이스와 함께 SQL 추적을 사용하여 애플리케이션의 로드가 많은 SQL 문과 같은 과도한 데이터베이스 워크로드의 소스를 식별할 수 있습니다.

SQL 추적 정보

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

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

SQL Trace file 저장을 위한 데이터베이스 구성부터 시작합니다. 자세한 내용은 자율운영 AI 데이터베이스에서 SQL 추적 구성을 참조하십시오.
그런 다음 SQL Trace를 활성화합니다. 자율운영 AI 데이터베이스에서 SQL 추적 사용을 참조하십시오.

주: SQL 추적을 사용으로 설정하면 추적 수집이 사용으로 설정된 동안 세션에 대한 애플리케이션 성능이 저하될 수 있습니다. 이러한 성능 영향은 추적 데이터 수집 및 저장 오버헤드로 인해 발생할 것으로 예상됩니다.
SQL 추적 데이터 수집을 정지하려면 SQL 추적을 비활성화해야 합니다. SQL 추적 사용 안함을 참조하십시오.
SQL 추적을 사용 안함으로 설정하면 추적이 사용으로 설정된 상태로 세션이 실행되는 동안 수집된 추적 데이터가 세션의 SESSION_CLOUD_TRACE 뷰 및 버킷의 추적 파일에 기록됩니다. 이 뷰는 SQL 추적을 설정하는 동안 구성하는 추적 파일입니다. 추적 데이터를 볼 수 있는 두 가지 옵션이 있습니다.
- 클라우드 객체 저장소에 저장된 추적 파일의 SQL 추적 데이터를 보고 분석합니다. 자세한 내용은 자율운영 AI 데이터베이스의 클라우드 객체 저장소에 저장된 추적 파일 보기를 참조하십시오.
- SESSION_CLOUD_TRACE 뷰에 저장된 SQL 추적 데이터를 보고 분석합니다. 자세한 내용은 자율운영 AI 데이터베이스의 SESSION_CLOUD_TRACE 뷰에서 데이터 추적 보기를 참조하십시오.

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

SQL 추적을 위해 자율운영 AI 데이터베이스를 구성하려면 다음을 수행합니다.

클라우드 오브젝트 스토리지에 추적 파일을 저장할 버킷을 생성합니다.

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

참고: SQL 추적 파일은 표준 스토리지 계층에서 생성된 버킷에서만 지원되므로 Oracle Cloud Infrastructure Object Storage에서 버킷을 생성할 때 스토리지 계층으로 스토리지 계층을 선택해야 합니다. 표준 오브젝트 스토리지 계층에 대한 자세한 내용은 스토리지 계층 이해를 참조하십시오.
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를 참조하십시오.
초기화 매개변수를 설정하여 SQL 추적 파일 버킷에 대한 클라우드 오브젝트 스토리지 URL을 지정하고 클라우드 오브젝트 스토리지에 액세스하기 위한 인증서를 지정합니다.
1. 클라우드 오브젝트 스토리지에 로깅 버킷을 지정하려면 데이터베이스 속성 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은 이전에 생성한 버킷의 이름입니다. 자세한 내용은 객체 스토리지 네임스페이스 이해를 참조하십시오.
  
  지역 목록은 지역 및 가용성 도메인을 참조하십시오.
  
  SQL 추적 파일에 사용하는 클라우드 객체 저장소는 자율운영 AI 데이터베이스가 지원하는 모든 클라우드 객체 저장소일 수 있습니다.
2. 데이터베이스 속성 DEFAULT_CREDENTIAL을 2단계에서 생성한 인증서로 설정합니다.
  
  예:
```
 ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME';
```
  인증서에 스키마 이름을 포함해야 합니다. 이 예에서 스키마는 ADMIN입니다.

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

주: SQL 추적을 사용으로 설정하면 추적 수집이 사용으로 설정된 동안 세션에 대한 애플리케이션 성능이 저하될 수 있습니다. 이러한 성능 영향은 추적 데이터 수집 및 저장 오버헤드로 인해 발생할 것으로 예상됩니다.

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

선택적으로 응용 프로그램에 대한 클라이언트 식별자를 설정합니다. 이 단계는 선택 사항이지만 권장됩니다. SQL 추적은 추적 파일이 클라우드 객체 저장소에 기록될 때 클라이언트 식별자를 추적 파일 이름의 구성요소로 사용합니다.

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

예:
```
 BEGIN
   DBMS_APPLICATION_INFO.SET_MODULE('modname', null);
 END;
 /
```
SQL Trace 기능을 활성화합니다.
```
 ALTER SESSION SET SQL_TRACE = TRUE;
```
작업 로드를 실행합니다.

이 단계에서는 전체 응용 프로그램 또는 응용 프로그램의 특정 부분을 실행합니다. 데이터베이스 세션에서 작업 로드를 실행하는 동안 SQL 추적 데이터가 수집됩니다.
SQL Trace를 사용 안함으로 설정합니다.

SQL 추적을 사용 안함으로 설정하면 세션에 대해 수집된 데이터가 세션의 테이블과 SQL 추적을 설정할 때 구성하는 버킷의 추적 파일에 기록됩니다.

SQL Trace 사용 안함

SQL 추적을 비활성화하려면 다음을 수행하십시오.

SQL Trace 기능을 비활성화합니다.
```
 ALTER SESSION SET SQL_TRACE = FALSE;
```
필요에 따라 사용자 환경에 맞게 데이터베이스 속성 DEFAULT_LOGGING_BUCKET를 재설정하여 클라우드 오브젝트 스토리지의 로깅 버킷 값을 지울 수 있습니다.

예:
```
 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 숫자 값은 클라우드 오브젝트 스토리지의 동일한 버킷에 추적 및 추적 파일을 생성하여 각 추적 파일 이름을 다른 세션과 고유하게 구분합니다.

데이터베이스 서비스가 병렬화를 지원하고 세션이 병렬 질의를 실행하면 SQL Trace 기능에서 서로 다른 numID1 및 numID2 값을 가진 여러 추적 파일을 생성할 수 있습니다.

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

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

sqltrace/sqlt_test/modname/sqltrace_5415_56432.trc

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

Object Store에서 로컬 시스템으로 Trace File을 복사합니다.
추적 파일이 저장된 디렉토리로 이동합니다.

다음 구문을 사용하여 운영 체제 프롬프트에서 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]

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

온라인 도움말을 보려면 인수 없이 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는 모든 반복에 대한 추적 데이터를 누적해서 표시합니다. 따라서 이전에 추적을 비활성화한 후 세션에서 추적을 다시 활성화해도 앞의 반복에서 생성된 추적 데이터가 제거되지 않습니다.