10장. 애플리케이션 튜닝

10장. 애플리케이션 튜닝

 

학습 목표

 

이번 장을 마치면 다음과 같은 작업을 수행 할 수 있다.

 

①   세션 통계를 수집하기 위한 SQL Trace 기능 설정

②   SQL 트레이스 파일들을 통합하기 위한 TRCSESS 유틸리티의 사용

③   tkprof 유틸리티를 이용하여 트레이스 파일들의 포맷팅

④   tkprof 명령에 의한 출력 해석

 

말단 애플리케이션에 대한 트레이스의 필요성

그림 10-1

 

사용자가 트레이스 메커니즘을 활성화하면, 오라클 데이터베이스는 각 서버 별 하나의

트레이스 파일을 생성하여 트레이스를 수행한다.

 

전용 서버 모델(dedicated server model)에서는 특정 클라이언트를 트레이스하는 것이 특별한 문제가 되지 않는데,

하나의 전용 서버 프로세스가 하나의 세션을 담당하기 때문이다. 해당 세션에 관한 모든 트레이스 정보는 세션을

제공하는 해당 전용 서버의 트레이스 파일에서 확인 할 수 있다. 그러나, 공유 서버(shared server) 구성에서는

클라이언트가 매번 다른 프로세스들에서 서비스를 제공받는다. 해당 사용자 세션과 관련된 트레이스 정보는 서로

다른 프로세스들의 서로 다른 트레이스  파일들로 흩어져 저장된다. 이러한 사실은 특정 세션의 시작에서부터

종료까지 완전한 정보를 얻기 어렵게 만든다. 뿐만 아니라, 성능 혹은 디버깅을 위해 특정 서비스에 대한 트레이스

정보를 통합하고자 한다면 어떻게 해야 하는가? 사용자는 동일한 서비스를 사용하는 여러 개의 클라이언트와

이 서비스를 제공하는 해당 서버 프로세스들의 트레이스 파일들을 가지기 때문에 이러한 작업 또한 상당히 어렵다.

 

말단 애플리케이션 트레이스

 

말단  애플리케이션  트레이스는  멀티  티어  환경에서  성능  문제의  진단  과정을  단순화한다. 멀티 티어 환경에서

말단 클라이언트로부터의 요청은 미들 티어에 의해 서로 다른 데이터베이스 세션으로 라우팅된다. 이것은 서로 다른

데이터베이스 세션 간 클라이언트의 트레이스를 어렵게 한다. 말단 애플리케이션 트레이스는 클라이언트 식별자를

이용하여 데이터베이스 서버에 대한 모든 티 어에서 특정 말단 클라이언트를 고유하게 추적한다.

 

과부하 SQL 문장과 같은 과도한 작업 부하의 원인을 식별하기 위해 말단 애플리케이션 트레이스 를 사용 할 수 있다.

또한, 사용자의 성능 문제들을 해결하기 위해 데이터베이스 수준에서 사용자 의 세션이 수행하는 작업들을 식별 할 수도 있다.

 

말단 애플리케이션 트레이스는 서비스 내의 특정 모듈과 액션을 트레이스하여 애플리케이션 작업 부하의 관리를 단순화

한다. 말단 애플리케이션 트레이스를 이용하여 다음과 같은 방식으로 작업 부하 문제를 식별 할 수 있다.

①   클라이언트 식별자 : HR과 같은 로그온 아이디를 기반으로 말단 사용자를 지정한다.

②   서비스 : 공통 속성, 서비스 수준 임계값, 우선 순위를 갖는 애플리케이션의 그룹 또는 단일 애플리케이션을 지정한다.

③   모듈 : 애플리케이션 내에 기능적 블록을 지정한다.

④   액션 : 모듈 내에서 INSERT 또는 UPDATE와 같은 액션을 지정한다.

⑤   세션 : 주어진 데이터베이스 세션 식별자(SID)를 기반으로 세션을 지정한다.

말단 애플리케이션 트레이스를 위한 주요 인터페이스는 EM이다.

 

진단 트레이스 파일의 위치

 

그림 10-2

 

ADR(Automatic Diagnostic Repository)은 트레이스, 장애 덤프, 패키지, 경보 로그, Health Monitor 보고서, 코어 덤프

등과 같은 데이터베이스 진단 데이터를 위한 파일 기반 저장소이다. Oracle Database 11g, Release 1부터 전통적인

_DUMP_DEST 초기화 파라메터는 무시되었다.

ADR 루트 디렉터리는 ADR 베이스라고 알려져 있다. 이 위치는 DIAGNOSTIC_DEST 초기화 파라메터에 의해 지정된다.

위 그림의 표는 Oracle Database 10g와 Oracle Database 11g 모두에 존재하는 트레이스 데이터와 덤프를 분류한 것이다.

Oracle Database 11g에서는 포그라운드와 백그라운드 트레이스 파일들간의 차이점이 존재하지 않는다. 두 가지 형식의

파일들은 $ADR_HOME/trace 디렉터리에 저장된다.

V$DIAG_INFO를 이용하여 일부 중요한 ADR의 위치를 확인 할 수 있다.

 

모든 비 장애 트레이스 파일들은 TRACE 디렉터리에 저장된다. 이러한 점이 이전 버전과의 큰 차 이점이며, 치명적인

오류 정보는 장애 덤프 대신 해당 프로세스 트레이스 파일에  덤프된다. Oracle Database 11g부터 장애 덤프는 정상

프로세스 트레이스 파일과는 별도로 저장된다.

 

참고 : 트레이스와 덤프의 주요 차이점은 트레이스가 SQL 트레이스 기능을 활성화 할 때처럼 연속적인 출력이 발생하는

반면, 덤프는 장애와 같은 이벤트에 의해 발생하는 단발성 출력이다. 또 한, 코어는 특별한 이진 메모리 덤프이다.

 

그림에서 DIAGNOSTIC_DEST 초기화 파라메터에 의해 정의되는 ADR 홈 디렉터리를 나타내기 위해 $ADR_HOME을

사용하였다. 그러나, ADR_HOME이라는 공식적인 환경 변수는 존재하지 않는다.

 

서비스란 무엇인가?

 

서비스의 개념은 노드들과 클러스터의 인스턴스들 사이에 커넥션 로드 밸런싱을 수행하는 리스너의 수단으로서 Oracle8i에서

처음 소개되었다. 그러나, 서비스의 개념, 정의, 구현은 획기적으로 확장되었다. 하나의 서비스는 해당 데이터베이스 내에서

실행되는 작업을 조직화하여, 해당 데이터 베이스의 관리, 측정, 튜닝, 복구를 수월하게 해준다. 하나의 서비스는 데이터베이스

내에서 공통 기능, 품질 기대치, 다른 서비스들과의 상대적 우선 순위를 갖는 연관된 작업들을 모아둔 것이다. 서비스는 단일

인스턴스 및 다중 인스턴스 데이터베이스 내에서 실행 되는 관리 및 경쟁 애플리케이션에 대한 단일 시스템 이미지를 제공한다.

 

표준 인터페이스, EM, SRVCTL을 사용하여 서비스들은 단일 개체로서 구성, 관리, 활성, 비활성, 측정 될 수 있다. 서비스는

가용성을 제공한다. 서비스가 중지되면, 살아남은 인스턴스에서 서비스가 빠르고 자동적으로 복구된다.

 

서비스는 튜닝을 수행하기 위한 디멘전을 제공한다. 서비스를 이용하면, 작업 부하들이 가시화 및 측정될 수 있다.

“service and SQL”에 의한 튜닝은 공유된 익명 세션을 갖는 대부분의 시스템에서 “session and SQL”에 의한 튜닝으로 교체되었다.

뷰의 트레이스 관점으로부터 서비스는 세션이 아닌 서비스 이름에 의해 트레이스 정보를 수집 할 수 있도록 허용하는 핸들을 제공한다.

 

클라이언트 애플리케이션과 서비스 사용

그림 10-3

 

애플리케이션 및 미들 티어 커넥션 풀은 TNS(Transparent Network Substrate) 커넥션 기술자를 사용하여 서비스를

선택한다. 선택된 서비스는 생성된 서비스와 반드시 일치되어야 한다.

위 그림의 첫 번째 예제는 ERP 서비스에 액세스하는데 사용될 수 있는 TNS 연결 기술자를 보여 준다.

두 번째 예제는 앞서 선언된 TNS 연결 기술자를 이용하는 두꺼운(thick) JDBC(Java Database Connectivity) 연결 기술자를 보여준다.

세 번째 예제는 동일한 TNS 연결 기술자를 이용하는 얇은(thin) JDBC 연결 기술자를 보여준다.

 

서비스 트레이스

 

애플리케이션은 서비스 내에서 중요 트랜잭션을 식별하기 위해 서비스를 MODULE 및 ACTION 이름으로 한정 시킬 수 있다.

이로 인하여 사용자는 비효율적으로 수행되는 트랜잭션들을 분류된 작업 부하에 위치 시킬 수 있다. 이것은 커넥션 풀  또는

트랜잭션 처리 모니터를 사용하는 시스템에서 성능을 모니터링하는 경우 중요하다. 이러한 시스템들의 경우, 해당 세션들은

공유되기 때문에 해당 세션들을 추적하는 것이 쉽지 않다. SERVICE_NAME, MODULE, ACTION, CLIENT_IDENTIFIER, SESSION_ID는

V$SESSION 내의 실제 컬럼들이다. SERVICE_NAME은 사용자가 로그인하는 시점에 자동으로 설정된다. MODULE및 ACTION

이름은 DBMS_APPLICATION_INFO PL/SQL 패키지 또는 특별한 OCI(Oracle Call Interface) 호출을 이용하여 애플리케이션에 의해 설정된다.

MODULE은 현재 실행 중인 프로그램에 대하여 사용자가 인식 할 수 있는 이름을 지정하여야 한다. 이와 유사하게 ACTION은

모듈 내에서 사용자가 수행하는 특별 한 액션(action) 또는 태스크(task)로 설정한다(예를 들어, 새로운 고객 입력). SESSION_ID는

세션이 생성 될 때, 데이터베이스에 의해 자동으로 설정되며, CLIENT_IDENTIFIER는

DBMS_SESSION.SET_IDENTIFIER 프로시저를 이용하여 설정 될 수 있다. 각 세션을 트레이스하는 전통적인 방식은 각 세션이

여러 작업 부하들에 걸친 SQL 명령에 대하여 트레이스 파일들을 생성하는 것이다. 이것을 이용하여 문제가 있는 SQL을 분석하기

위해 적중 또는 실패(hit or miss) 접근법을 사용하게 된다.

사용자가 제공한 기준(SERVICE_NAME, MODULE, ACTION)을 이용하여 특별한 트레이스 정보는 트레이스 파일 집합에 수집되며,

단일 출력 트레이스 파일로 결합 될 수 있다. 사용자는 특정 작업 부하와 관련된 SQL을 포함하는 트레이스 파일들 을 생성 할 수 있다.

CLIENT_ID와 SESSION_ID에 대하여 동일한 작업을 수행 할 수도 있다.

 

참고 : DBA_ENABLED_TRACES는 활성화 된 트레이스들에 관련된 정보를 표시한다.

 

서비스 추적을 위해 EM 사용

그림 10-4

 

Performance 페이지에서 Top Consumers 링크를 클릭한다. Top Consumers 페이지가 표시된다.

 

Top Consumers 페이지는 여러 개의 탭을 가지고 있으며, 단일 시스템 이미지로서 사용자의 데이터베이스를 표시한다.

Overview 탭 페이지는 4개의 파이 차트 : Top Clients, Top Services, Top Modules, Top Actions를  포함한다. 각 차트는

사용자의 데이터베이스에서 최상의 자원 소비자에 대한 서로 다른 관점을 제공한다.

 

Top Services 탭 페이지는 사용자 데이터베이스에 정의된 서비스들에 대하여 성능 관련 정보를 표 시한다. 이 페이지에서

서비스 수준 별로 추적 기능을 활성 또는 비활성화 할 수 있다.

 

서비스 트레이스 : 예

그림 10-5

 

위 그림의 첫 번째 박스에서는 AP 서비스 하에서 기록되는 모든 세션들을 트레이스한다. 트레이스 파일은 모듈 및 액션과

상관 없이 해당 서비스를 사용하는 각 세션들 별로 생성된다. 좀 더 구체적으로 지정하려면 서비스 내의 특정 태스크만

트레이스 할 수 있다. 이것은 두 번째 예제로 서 PAYMENTS 모듈 내에서 QUERY_DELINQUENT 액션을 실행하는

AP 서비스의 모든 세션들이 트레이스된다. 서비스, 모듈, 액션에 의한 트레이스는 트레이스 파일들에서 서로 다른

프로그램들의 SQL을 조사하는 대신 특정 SQL만 집중해서 튜닝 할 수 있도록 해준다. 이 태스크를 정의하는 SQL 문장들만

트레이스 파일에 기록된다. 이것은 액션에 대한 관련 대기 이벤트가 식별 될 수 있기 때문에 서비스, 모듈, 액션에 의해 수집된 통계를 보완한다.

 

세 번째 예제에서 보는 것처럼 특정 클라이언트 식별자에 대하여 트레이스 기능을 시작 할 수도 있다. 이 예제에서  C4는 

SQL트레이스를 활성화 할 클라이언트 식별자이다. TRUE 인자는 대기 정보를 트레이스 파일에 저장되도록 지정한다. FALSE

인자는 트레이스 파일에 바인드 정보가 저장되지 않도록 지정한 것이다.

 

그림에서 보여지지는 않지만 해당 데이터베이스 전체에서 주어진 클라이언트 식별자에 대해 트레이스 기능을 비활성화하기

위해 CLIENT_ID_TRACE_DISABLE 프로시저를 사용 할 수도 있다. 트레 이스 기능을 비활성화하려면 다음 명령을 실행한다.

 

EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id =>"C4");

 

참고 : CLIENT_IDENTIFIER는 DBMS_SESSION.SET_IDENTIFIER 프로시저를 사용하여 설정 될 수 있 다.

 

세션 수준 트레이스 : 예

그림 10-6

 

성능 문제를 디버깅하기 위해 트레이스 기능을 사용 할 수 있다. 트레이스 기능을 활성화하는 프로시저들은

DBMS_MONITOR 패키지의 일부로 구현되어 있다. 이 프로시저들은 데이터베이스 전체에 대하여 트레이스 기능을 활성화한다.

 

인스턴스 전체에 대하여 세션 수준 SQL 트레이스를 활성화하기 위해 DATABASE_TRACE_ENABLE

프로시저를 사용 할 수 있다. 이 프로시저는 다음 파라메터들을 갖는다.

①   WAITS : 대기 정보를 트레이스 할 것인지 여부

②   BINDS : 바인드 정보를 트레이스 할 것인지 여부

③   INSTANCE_NAME : 트레이스를  활성화 할 인스턴스 지정. INSTANCE_NAME을 생략하면 전체 데이터베이스에 대하여 세션 수준의 트레이스가 활성화된다.

전체 데이터베이스 또는 특정 인스턴스에 대하여 SQL 트레이스 기능을 비 활성화하려면 DATABASE_TRACE_DISABLE을 사용한다.

이와 유사하게 SESSION_TRACE_ENABLE 프로시저를 사용하여 로컬 인스턴스에서 주어진 데이터베이스 세션 식별자에 대해서만

트레이스 기능을 활성화 할 수 있다. SID와 SERIAL# 정보는 V$SESSION에서 찾을 수 있다.

주어진 데이터베이스 세션 식별자 및 시리얼 번호에 대하여 트레이스 기능을 비 활성화하려면 SESSION_TRACE_DISABLE 프로시저를 사용한다.

 

참고 : SQL 트레이스는 일부 오버헤드가 발생되므로 보통 인스턴스 수준에서 SQL 트레이스를 활성화하지 않는다.

 

자신의 세션 트레이스

그림 10-7

 

DBMS_MONITOR 패키지는 DBA 롤을 가진 사용자만 실행 할 수 있지만, 모든 사용자들은 DBMS_SESSION 패키지를

사용하여 자신의 세션을 트레이스 할 수 있다. 자신의 세션에서 세션 수준의 SQL 트레이스를 활성화하려면

SESSION_TRACE_ENABLE 프로시저를 실행하면 된다. 그림의 예와 같다.

 

사용자의 트레이스 파일에 대한 덤프를 중지하려면 DBMS_SESSION.SESSION_TRACE_DISABLE 프로시저를 사용하면 된다.

 

TRACEFILE_IDENTIFIER 초기화 파라메터는 오라클 트레이스 파일 이름의 일부가 되는 사용자 정의 식별자를 지정한다.

이러한 사용자 정의 식별자를 사용하면 파일을 열고 해당 내용을 살펴보는 대신 파일 이름으로 트레이스 파일을 구분

할 수 있다. 이 파라메터는 세션 수준에서 매번 동적으로 변경되며, 다음 번 트레이스 덤프가 트레이스 파일에 기록된다.

이 파라메터는 해당 이름에 포함된 새로운 파라메터 값을 갖는다. 이 파라메터는 포그라운드 프로세스의 트레이스 파일의

이름을 변경 할 때만 사용되고, 백그라운드 프로세스들은 일반적은 형식으로 명명된 자신의 트레이스 파일들을 계속 사용한다.

포그라운드 프로세스의 경우, V$PROCESS 뷰의 TRACEID 컬럼이 이 파라메터의 값을 포함한다. 이 파라메터 값을 설정하면,

트레이스 파일 이름은 다음과 같은 형식 : sid_ora_pid_traceid.trc 을 갖는다.

 

참고 : SQL_TRACE 초기화 파라메터는 Oracle Database 10g부터 폐기되었다. 다음 문장을 이용하면 폐기된 파라메터들의 완전한 목록을 얻을 수 있다.

 

SELECT name FROM v$parameter WHERE isdeprecated="TRUE";

 

trcsess 유틸리티

그림 10-8

 

trcsess 유틸리티는 몇 가지 기준 : 세션 ID, 클라이언트 식별자, 서비스 이름, 액션 이름, 모듈 이 름을 기반으로 선택된

트레이스 파일들의 출력을 통합한다. trcsess가 트레이스 정보를 단일 출력 파일로 병합하면, 해당 출력 파일은 tkprof에

의해 처리 될 수 있다.

 

DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE 프로시저를 사용하는 경우, 추적 정보가 여러 개의 트레이스

파일들에 존재하게 되며, 반드시 trcsess 툴을 사용하여 해당 정보들을 단일 파일로 수집하여야 한다.

 

trcsess 유틸리티는 성능 또는 디버깅의 용도로 특정 세션 또는 서비스의 트레이스 결과를 통합하는데 유용하다.

 

전용 서버 모델에서는 단일 전용 프로세스가 하나의 세션을 서비스하기 때문에 특정 세션을 트레이스하는 것은 어려운

문제가 아니다. 해당 세션에 대한 모든 트레이스 정보는 해당 세션을 담당 하는 전용 서버의 트레이스 파일에서 살펴

볼 수 있다. 그러나, 서비스를 트레이스하는 것은 전용 서버 모델에서 조차 복잡한 태스크가 될 수도 있다.

 

뿐만 아니라, 공유 서버  구성에서 사용자 세션은 때때로 서로 다른  프로세스들에 의해 서비스된 다. 해당 사용자 세션에 해당하는 트레이스 정보는

서로 다른 프로세스들이 각각 가지고 있는 서로 다른 트레이스 파일들에 분산된다. 이것은 세션의 완전한 생명 주기를 얻기 어렵게 만든다.

 

trcsess 유틸리티 실행

그림 10-9

 

그림에서 보여지는 trcsess 유틸리티의 구문은 다음과 같다.

output은 출력이 기록될 파일을 지정한다. 만약, 이 옵션이 지정되지 않으면, 표준 출력이 사용된다.

①   session은 지정된 세션에 대한 트레이스 정보를 통합한다. 세션 식별자는 21.2371과 같이 세션 인덱스와

세션 시리얼 번호의 조합이다. 이 값은 V$SESSION 뷰에서 찾을 수 있다.

②   clientid는 주어진 클라이언트 식별자에 대한 트레이스 정보를 통합한다.

③   service는 주어진 서비스 이름에 대한 트레이스 정보를 통합한다.

④   action은 주어진 액션 이름에 대한 트레이스 정보를 통합한다.

⑤   module은 주어진 모듈 이름에 대한 트레이스 정보를 통합한다.

⑥   <trace file names>는 공백으로 분리된 모든 트레이스 파일 이름들의 목록이며, trcsess는 여기서 트레이스

정보를 검색하여야 한다. 와일드카드 문자 *를 트레이스 파일 이름을 지정하는데 사용 할 수 있다. 만약,

트레이스 파일들이 지정되지 않으면, 현재 디렉터리 내의 모든 파일들이 trcsess의 입력이 된다. ADR에서

트레이스 파일을 찾을 수 있다.

 

참고 : session, clientid, service, action, module 옵션 중 하나가 반드시 지정되어야 한다. 만약, 하나 이상의

옵션이 지정되었다면, 지정된 모든 기준을 만족하는 트레이스 파일들만 출력 파일들로 통합된다.

 

trcsess 유틸리티 : 예

그림 10-10

 

그림의 예제는 trcsess 유틸리티의 가능한 용도를 표현한 것이다. 예제는 사용자가 세 개의 서로 다른 세션을 가지고 있다고

가정한다. 두 개의 세션은 좌측과 우측에 위치하고 있으며, 중앙의 나머지 세션은 트레이스 기능을 활성 또는 비활성화하고

이전 두 세션의 트레이스 정보를 연결한다.

 

첫 번째와 두 번째 세션은 클라이언트 식별자로 “HR session“을 지정하였다. 이 작업은 DBMS_SESSION 패키지를 이용하여 수행 할 수 있다.

그 다음, 세 번째 세션이 DBMS_MONITOR 패키지를 사용하여 해당 두 세션들에 대하여 트레이스 기능을 활성화하였다.

이 때, 두 개의 새로운 트래이스 파일들이 ARD에 생성된다. 각 세션 별로 만들어지는 트레이스 파일은 “HR session“이라는

클라이언트 식별자로 구분된다.

 

각각의 트레이스되고 있는 세션은 자신의 SQL 문장을 실행한다. 각 문장은 ADR 내 자신의 트레이스 파일에 트레이스 정보를 생성한다.

 

이 후, 세 번째 세션은 DBMS_MONITOR 패키지를 사용하여 트레이스 정보 생성을 중지하고, “HR session“ 클라이언트

식별자를 가진 세션에 대한 트레이스 정보를 mytrace.trc 파일로 통합한다. 예제는 모든 트레이스 파일들이

$ORACLE_BASE/diag/rdbms/orcl/orcl/trace 디렉터리에 생성된다고 가정하였으며, 이 경로는 대부분의 경우에 디폴트

위치이다.

 

SQL 트레이스 파일 내용

 

이미 살펴본 것처럼 SQL 트레이스 파일은 개별 SQL 문장들에 대한 성능 정보를 제공한다. 이 파 일에는 각 문장에 대한 다음과 같은 통계값이 포함된다.

①   파싱, 실행, 인출 횟수

②   CPU 및 전체 수행 시간

③   물리적 인기 및 논리적 인기 횟수

④   처리된 행의 개수

⑤   라이브러리 캐시 미스(Miss) 횟수

⑥   각 파싱이 발생 했을 때의 사용자명

⑦   각 커밋 및 롤백

⑧   각 SQL 문장에 대한 대기 이벤트 데이터 및 각 트레이스 파일에 대한 요약

 

만약, SQL 문장에 대한 커서가 닫혔다면, SQL Trace는 다음과 같은 정보를 포함하는 행 원본(Row Source) 정보를 제공한다.

각 SQL 문장의 실제 실행 계획을 보여주는 행 연산행의 개수, 일관성 인기 횟수, 물리적 인기 횟수, 물리적 쓰기 횟수,

각 연산에 소요된 시간. 이 정보는 STATISTICS_LEVEL 초기화 파라메터를 ALL로 설정한 경우에만 가능하다.

 

참고 : SQL Trace 기능의 사용은 성능에 심각한 영향을 미칠 수 있으며 시스템의 오버헤드, 과도 한 CPU 사용, 부적절한 디스크 공간을 발생시킬 수도 있다.

 

SQL 트레이스 파일 내용 : 예

 

 

그림 10-11

 

오라클 데이터베이스에서 생성 될 수 있는 Trace 파일의 종류는 다양하다. 이 과정과 관련된 파일을 일반적으로 SQL Trace

파일이라고 부른다.

위 그림은 이전 예제에 의해서 작성된 mytrace.trc SQL Trace 파일의 샘플 출력이다.

 

이러한 유형의 Trace 파일들에서 문장과 해당 문장에 해당하는 구체적인 커서를 찾을 수 있다.

문장 실행의 각 단계 : PARSE, EXEC, FETCH에 대한 구체적인 통계를 볼 수 있다. 보는 바와 같이 사용자의 쿼리가

리턴하는 행의 개수에 따라 한번의 EXEC에 대하여 여러 번의 FETCH가 발생 할 수도 있다.

 

해당 Trace 내용의 마지막 부분은 각 행 원본에 대한 누적 통계를 포함하는 실행 계획이다.

사용자가 Trace 기능을 활성화하는 방법에 따라 생성된 Trace 파일에서 대기 이벤트와 바인드 변수에 대한 정보를 얻을 수도 있다.

Trace 파일에서는 자신의 세션이 수행한 전체 내용을 얻을 수 없기 때문에 해당 Trace 파 일을 번역 할 필요가 없다.

예를 들어, 하나의 세션이 서로 다른 시점에서 동일한 문장을 여러 번 수행 할 수 있다. 해당 Trace 정보는 전체 Trace 파일에

분산되어 이러한 정보를 찾기는 매우 힘들다. 대신, tkprof와 같은 다른 툴을 사용하여 Row Trace 정보의 내용을 해석하도록 한다.

 

SQL Trace 파일 포맷팅 : 개요

그림 10-12

 

tkprof는 SQL Trace 파일을 파싱하여 좀더 인기 쉬운 출력을 생성하는 실행 파일이다. tkprof 내의 모든 정보는 Row Trace

파일에서도 사용 가능함을 기억해야 한다. tkprof를 사용하여 다양한 정렬 기능을 수행 할 수 있다. 명령 프롬프트에서

tkprof를 실행 할 때, 사용 가능한 많은 정렬 옵션이 있다. 유용한 옵션인 정렬 옵션은 인출에 소요되는 전체 시간으로

출력을 정렬 한다. 결과 .prf 파일은 해당 파일의 선두부터 가장 많은 시간을 소비하는 SQL 문장을 포함한다. 또 다른

유용한 파라메터는 sys이다. 이 파라메터는 SYS 사용자로 실행된 SQL 문장이 표시되지 않도록 방지한다. 그 결과, 출력

파일을 짧고 관리하기 수월하게 만들어 준다.

SQL Trace 파일들의 개수가 증가하면, 다음과 같은 작업을 수행 할 수 있다.

①   각 개별 Trace 파일에 대하여 tkprof를 실행하면, 세션당 하나씩 포맷된 출력 파일 들이 생성된다.

②   Trace 파일들을 연결하고, 전체 인스턴스에 대하여 포맷된 출력 파일을 생성하기 위해 해당 결과에 tkprof를 실행한다.

③   여러 Trace 파일들의 Trace 정보를 통합하기 위해 trcsess 명령줄 유틸리티를 실행하고, 그 결과에 tkprof를 실행한다.

tkprof는 Trace 파일에 기록된 COMMIT과 ROLLBACK을 기록하지 않는다.

 

참고 : 세션을 추적 할 때, TIMED_STATISTICS 파라메터를 TRUE로 설정한다. 그 이유는 이 파라메터를 설정하지 않으면

시간 기반 비교가 수행되지 않기 때문이다. Oracle Database 11g에서 이 파라메터의 디폴트 값은 TRUE이다.

 

tkprof 유틸리티 실행

그림 10-13

 

tkprof 명령을 실행 할 때, 아무런 인자를 지정하지 않으면, 모든 tkprof 옵션에 대한 설명과 함께 사용법에 대한 설명이 표시된다.

그림에서는 다양한 인자를 보여주고 있다.

①   inputfile : SQL 트레이스 입력 파일을 지정한다.

②   outputfile : tkprof가 포맷된 출력을 기록할 파일을 지정한다.

③   waits : Trace 파일 내에 발견된 모든 대기 이벤트의 요약을 기록 할 것인지를 지정한다.

YES 또는 NO를 지정하며 디폴트는 YES이다.

④   sorts : SQL 문장들을 출력 파일 내에 출력하기 전에 Trace 된 SQL 문장들을 지정된 정렬 옵션으로 내림차순 정렬한다.

만약, 하나 이상의 옵션이 지정되면, 정렬 옵션에 지정된 값의 합으로 출력을 내림차순 정렬한다. 만약, 이 파라메터를

생략하였다면, tkprof는 문장을 최초로 사용된 순서로 정렬하여 출력 파일에 저장한다.

⑤   print : 정수값으로 지정된 개수 만큼 정렬된 SQL 문장들을 출력 파일에 저장한다. 만약, 이 파라메터를 생략하면,

tkprof는 모든 Trace 된 SQL 문장들을 출력한다. 이 파라메터는 옵션 SQL 스크립트에 영향을 미치지 않는다.

SQL 스크립트는 모든 Trace 된 SQL 문장에 대하여 항상 입력 데이터를 생성한다.

⑥   aggregate : NO로 지정하면 tkprof는 여러 사용자의 동일한 SQL 문장을 집계하지 않는다.

⑦   insert : Trace 파일 통계를 데이터베이스 내에 저장하는 SQL 스크립트를 생성한다. tkprof는 sqlscritfile에 저장한

이름으로 이 스크립트를 생성한다. 이 스크립트는 테이블을 생성하고 각 Trace 된 SQL 문장에 대한 통계 값을 테이블에 행으로 삽입한다.

⑧   sys : SYS 사용자에 의해 실행된 SQL 문장 또는 재귀 SQL 문장을 출력 파일에 저장 할 것인지를 활성 및 비활성화한다.

디폴트 값은 YES이며 tkprof는 이러한 정보를 출력 파일 에 저장한다. NO는 tkprof가 이러한 정보를 생략한다. 이 파라메터는

옵션 SQL 스크립트에 영향을 주지 않는다. SQL 스크립트는 재귀 SQL 문장을 포함하여 모든 Trace 된 SQL 문장에 대한 통계를 입력한다.

⑨   table : tkprof가 출력 파일에 실행 계획을 기록하기 전에 임시로 저장할 스키마 및 테이 블 이름을 지정한다. 만약, 지정된

테이블이 이미 존재한다면, tkprof는 해당 테이블 내의 모든 행들을 삭제하고 EXPLAIN PLAN 명령(해당 테이블에 다수의 행을 기록)을

위해 사용한 다음, 이러한 행들을 삭제한다. 만약, 이 테이블이 존재하지 않는다면, tkprof는 해당 테이블을 생성하고, 사용한 다음,

삭제한다. 지정된 사용자는 반드시 이 테이블에 INSERT, SELECT, DELETE를 수행 할 수 있어야 한다. 먄약, 해당 테이블이 존재하지

않았다면, 지정된 사용자는 반드시 CREATE TABLE과 DROP TABLE 명령을 수행 할 수 있어야 한다. 이 옵션은 다수의 개인이 EXPLAIN 값에

같은 사용자를 지정하여 tkprof를 동시에 실행 할 수 있도록 해준다. 이러한 개인들은 서로 다른 TABLE 값을 지정하여 임시 실행 계획 테이블을

처리하는 각각의 다른 사용자들 간의 파괴적인  간섭을  회피  할  수  있다. 만약, TABLE 파라메터 없이 EXPLAIN 파라메터를 사용하면 tkprof는

EXPLAIN 파라메터에 지정한 사용자 스키마의 PROF$PLAN_TABLE 테이블을 사용한다. 만약, EXPLAIN 파라메터를 사용하지 않고 TABLE 파라메터를

사용하면 tkprof는 TABLE 파라메터를 무시한다. 만약, 실행 계획 테이블이 존재하지 않으면 PROF$PLAN_TABLE 테이블을 생성하고, 최후에는 삭제한다.

⑩   explain : Trace 파일 내에 각 SQL 문장에 대한 실행 계획을 결정하고, 이 실행 계획들을 출력 파일에 기록한다. tkprof는 파라메터에 지정된

사용자명과 암호를 이용하여 시스템에 접속한 다음, EXPLAIN PLAN 명령을 실행하여 실행 계획을 결정한다. 지정된 사용 자는 반드시

CREATE SESSION 시스템 권한을 반드시 가져야 한다. EXPLAIN 옵션이 사용 되면 tkprof가 대용량 Trace 파일을 처리하는데 소요되는 시간이 길어진다.

⑪   record : statementfile에 지정된 파일 이름으로 Trace 파일 내의 모든 비 재귀 SQL 문 장에 대하여 SQL 스크립트를 생성한다.

이것은 Trace 파일로부터 해당 사용자의 이벤트를 재 구현하는데 사용 될 수 있다.

 

tkprof Sorting Options

 

Sort Option   Description

 

execu Number of buffers for current read during execute

exeqry Number of buffers for consistent read during execute

exedsk Number of disk reads during execute

exeela Elapsed time executing

execpu CPU time spent executing

execnt Number of executes that were called

prsmis Number of misses in the library cache during parse

prscu Number of buffers for current read during parse

prsqry Number of buffers for consistent read during parse

prsdsk Number of disk reads during parse

prsela Elapsed time parsing

prscpu CPU time parsing

prscnt Number of times parse was called

exerow Number of rows processed during execute

exemis Number of library cache misses during execute

fchcnt Number of times fetch was called

fchcpu CPU time spent fetching

fchela Elapsed time fetching

fchdsk Number of disk reads during fetch

fchqry Number of buffers for consistent read during fetch

fchcu Number of buffers for current read during fetch

fchrow Number of rows fetched

userid User ID of user that parsed the cursor

 

그림 10-14

 

tkprof 명령의 출력

 

tkprof 명령 출력은 SQL 처리 단계 별로 SQL 문장에 대한 통계 자료를 출력한다. 통계를 포함하는 각 행 별 단계는

call컬럼의 값으로 구분된다.

PARSE	이 단계는 SQL 문장을 실행 계획으로 번역하고, 적절한 권한을 가지고 있는지 확인한다. 또한, 테이블, 컬럼 및 다른 참조 객체들이 존재하는지 확인한다.
EXECUTE	이 단계는 오라클 서버에 의한 해당 문장의 실제 실행 단계이다. INSERT, UPDATE,DELETE 문장의 경우, 이 단계는 해당 데이터를 수정한다(필요에 따라 정렬 작업을 포함한다). SELECT 문장의 경우, 이 단계는 선택된 행들을 식별한다.
FETCH	이 단계는 쿼리에 의해 리턴된 핸들을 읽어오며, 필요에 따라 정렬을 수행한다. 인출은 SELECT 문장에서만 수행된다.

 

참고 : PARSE 값은 hard 및 soft 파스를 포함한다. 하드 파스는 실행 계획의 전개(최적화 포함)를 나타내며, 실행 계획은

라이브러리 캐시에 저장된다. 소프트 파스는 SQL 문장이 파싱을 위해 데이터베이스에 전송되는 것을 의미한다. 그러나,

데이터베이스는 라이브러리 캐시에서 실행 계획을 검색하고, 액세스 권한과 같은 것들만 검증하면 된다.

하드 파스는 특별히 최적화로 인하여 매우 고가이며, 소프트 파스는 라이브러리 캐시의 활동성 관점에서 고가이다.

 

 

그림 10-15

 

샘플 출력은 다음과 같다.

call     count      cpu      elapsed       disk      query    current   rows

------- ------ -------- ------------ ---------- ---------- ---------- ------

Parse        1     0.03         0.06          0          0          0      0

Execute      1     0.06         0.30          1          3          0      0

Fetch        2     0.00         0.46          0          0          0      1

------- ------ -------- ------------ ---------- ---------- ---------- ------

total        4     0.09         0.83          1          3          0      1

 

CALL 컬럼 다음에 tkprof는 각 문장에 대하여 다음 통계를 표시한다.

①   Count : 문장이 파싱, 실행, 인출된 횟수(다른 컬럼의 통계를 해석하기 전에 이 컬럼의 값이 1 이상인지 확인한다.

AGGREGATE=NO 옵션을 사용하지 않는 한, tkprof는 동일한 문 장의 실행 횟수를 하나의 요약 테이블에 집계한다).

②   CPU : 모든 파싱, 실행, 인출 횟수에 대하여 총 CPU 시간을 초로 표시한다.

③   Elapsed : 모든 파싱, 실행, 인출 횟수에 대하여 총 수행 시간을 초로 표시한다.

④   Disk : 모든 파싱, 실행, 인출 횟수에 대하여 디스크 상의 데이터 파일들로부터 물리적으로 읽어온 블록의 전체 갯수이다.

⑤   Query : 모든 파싱, 실행, 인출 횟수에 대하여 일관성 모드(consistent mode)로 읽어온 버퍼의 전체 수이다.

(쿼리의 경우, 보통 일관성 모드로 버퍼를 읽어온다).

⑥   Current : 현재  모드(current mode)에서 읽어온 버퍼의 전체  개수이다(데이터 조작 언어 문장의 경우 일반적으로

현재 모드로 버퍼를 읽어온다. 그러나, 세그먼트 헤더 블록은 언 제나 현재 모드로 읽어온다).

⑦   Rows : SQL 문장에 의해 처리된 행의 전체 갯수이다(이 값은 SQL 문장의 서브쿼리에 의해 처리된 행들은 포함하지

않는다. SELECT 문장의 경우, 리턴된 행의 개수가 인출 단계에서 나타난다. UPDATE, DELETE, INSERT 문장의 경우, 처리된

행들의 개수는 실행 단계에서 표시된다.)

 

참고

①   DISK는 v$sysstat 또는 AUTOTRACE의 physical reads와 동일하다.

②   QUERY는 v$sysstat 또는 AUTOTRACE의 consistent gets와 동일하다.

③   CURRENT는 v$sysstat 또는 AUTOTRAE의 db block gets와 동일하다.

 

 

그림 10-16

 

재귀 호출

사용자에 의해 제출된 SQL 문장을 실행하려면, 오라클 서버는 때때로 추가 문장을 실행하여야만 한다. 그러한 문장을

recursive SQL statements라고 부른다. 예를 들어, 만약, 테이블에 한 개의 행을 입력하는데, 해당 테이블이 해당 행을

저장할 만한 충분한 공간을 확보하고 있지 못하다면 오라클 서버는 동적으로 공간을 할당하기 위해 재귀 호출을 수행한다.

또한, 재귀 호출은 데이터 딕셔너리 캐시에서 데이터 딕셔너리 정보를 사용 할 수 없는 경우에 디스크에서 해당 정보를

얻어와야 하기 때문에 발생하기도 한다.

 

만약, SQL Trace 기능이 활성화 되어 있는 동안, 재귀 호출이 발생하면, tkprof는 출력 파일에서 해당 문장을 재귀 SQL

문장으로 표시한다. 출력 파일에서 재귀 호출의 목록을 제한하려면 명령줄 파라메터에 SYS=NO를 지정한다. 재귀 호출을

유발시키는 SQL 문장에 대한 출력에는 언제나 재귀 SQL 문장에 대한 통계가 포함된다.

 

라이브러리 캐시 미스

tkprof는 각 SQL 문장에 대하여 파싱 및 실행 단계에서 발생한 라이브러리 캐시 미스 횟수를 출력한다. 이 통계는 테이블

형식의 통계 다음에 별도의 라인에서 표시된다.

 

행 원본 연산(Row Source Operations)

여기에는 실행된 각 연산에 대하여 처리된 행의 개수와 물리적 인기 및 쓰기 : cr=일관성 인기, w=물리적 쓰기, r=물리적

읽기, time=시간(마이크로 초)와 같은 행 원본 정보를 제공한다.

 

사용자 ID 파싱

해당 문장을 파싱한 마지막 사용자의 ID이다.

 

행 원본 연산(Row Source Operation)

행 원본 연산은 SQL 문장의 실행에 대한 데이터 원본을 표시한다. 이것은 추적이 활성화되는 동안 커서가 닫힌 경우에만

포함된다. 만약, Trace 파일에 행 원본 연산이 나타나지 않으면, 그 다음은 EXPLAIN PLAN을 확인 할 수도 있다.

 

실행 계획

tkprof 명령 줄에서 EXPLAIN 파라메터를 지정했다면, Trace 된 SQL 문장의 실행 계획을 생성하기 위해 EXPLAIN PLAN

명령을 사용한다. tkprof는 실행 계획의 각 단계 별로 처리된 행들의 개수를 표시하기도 한다.

 

참고 : 실행 계획은 tkprof 명령이 실행 될 때, 만들어지며, Trace 파일이 생성 될 때 만들어진 것이 아니다. 이 결과는

다를 수 있는데, 예를 들어, 문장을 Trace하기 전에 인덱스를 생성하거나 삭제한 경우에 그럴 수 있다.

 

옵티마이저 모드 또는 힌트

문장이 실행되는 동안 사용된 옵티마이저 힌트를 나타낸다. 만약, 힌트가 없다면 사용된 옵티마이저 모드를 표시한다.

 

인덱스가 없을 때 tkprof 출력 : 예

그림 10-18

 

위 그림의 예는 여러 번의 실행(rows)을 통해 결과의 집계가 CUSTOMERS 테이블로부터 인출되었음을 보여준다. 이 작업은

0.12초의 CPU 시간을 필요로 한다. 출력의 행 원본 연산에서 볼 수 있는 것처럼 해당 문장은 CUSTOMERS 테이블을 전체

테이블 스캔한다. 해당 문장은 반드시 최적화되어야 한다.

 

참고 : 만약, CPU 또는 elapsed 값이 0이라면, timed_statistics가 설정되지 않은 것이다.

 

인덱스가 있을 때 tkprof 출력 : 예

그림 10-19

 

그림에서 보는 바와 같이 CUST_CITY 컬럼에 인덱스를 생성하여, CPU 시간은 0.01초로 감소되었다. 해당 문장이 데이터를

얻어오기 위해 인덱스를 사용하기 때문에 이러한 결과를 얻을 수 있다. 또한, 이 예제는 동일한 문장을 재실행하기 때문에

대부분의 데이터 블록들이 메모리에 존재한다. 인덱스는 성능을 급격히 향상시킨다. SQL Trace 기능을 사용하여 성능의

잠재적 향상 영역을 찾아야 한다.

 

참고 : 인덱스는 필요하지 않으면 만들지 말아야 한다. 인덱스는 INSERT, UPDATE, DELETE 명령의 처리 속도를 저하시키는데,

행에 대한 참조를 추가, 변경, 제거해야 하기 때문이다. 사용되지 않는 인덱스를 식별하고 제거하기 위해서 EXPLAIN PLAN을

통해 모든 애플리케이션 SQL을 처리하는 대신, 인덱스 모니터링을 사용 할 수 있다.