SQL 트레이스를 사용한 SQL 튜닝

SQL 트레이스를 사용한 SQL 튜닝

목차

■ SQL 트레이스란
■ 트레이스의 취득 방법
■ 트레이스의 출력처
■ 취득한 트레이스의 분석

◆ SQL 트레이스란

■ SQL문의 처리의 흐름     ●해석     ●실행     ●패치(문의의 경우만)해석 ■ 튜닝의 지표     ●실행 계획     ●시간     ●I/O량     ●대기 SQL문은 텍스트로서의 SQL문을 분석해 실행 계획을 작성하는 「해석」, 생성된 실행 계획에 근거하고 처리를 실시하는 「실행」, 그리고 문의 결과의 행을 꺼내는 「패치」라고 하는 흐름으로 처리됩니다. 한편, SQL문 튜닝의 지표가 되는 요소로서는 SQL문의 실행 계획 SQL문의 처리에 필요로 한 시간, 처리중에 발생한 I/O의 양이나 대기의 상황등을 들 수 있겠지요. SQL 트레이스는 SQL문을 처리하면서 이러한 정보를 트레이스·파일에 출력하는 기능입니다. 출력내용 ■ 해석·실행·패치의 각 국면의 회수, CPU 시간 및 경과시간, 물리 읽기와 논리 읽어들여, 처리된 행수 ■ 라이브러리·캐쉬·미스의 발생 상황 ■ SQL를 해석한 유저명 ■ 실행 계획 (커서의 클로우즈시에 출력)     ● 처리행수, I/O블록수, 경과시간 ■ 바인드 변수로 설정된 값 (취득 방법에 의존) ■ 실행중에 발생한 대기 (취득 방법에 의존) SQL 트레이스의 기능을 유효하게 함으로, 실행된 각 SQL문 마다 해석, 실행, 페치의 각각의 처리에 대해서 행해진 회수, 사용한 CPU 시간과 경과시간, 물리 읽기나 논리 읽기를 한 블록수, 처리된 행수라고 하는 정보가 트레이스·파일에 기록 됩니다. 또 SQL문의 해석이나 실행으로 발생한 라이브러리·캐쉬·미스의 회수나, SQL문을 해석한 유저명등도 기록됩니다. SQL의 커서가 클로우즈 되면 SQL문의 실행 계획과 그 각 스텝에 있어서의 처리행수나 I/O블록수, 경과시간의 정보도 트레이스에 출력됩니다. 게다가  트레이스의 취득 방법에 따라서는, SQL 문중의 바인드 변수에 실제로 설정된 값이나, SQL문의 처리중에 발생한 대기의 상황도 트레이스에 출력할 수 있게 되어 있습니다. 이러한 정보를 이용하고, 실행 시간의 오래 걸리는  SQL나, I/O량이 많은 SQL문을 특정하거나 SQL가 적절한 실행 계획으로 실행되고 있는지 확인하거나 어떠한 대기가 원인으로 SQL의 실행에 시간이 걸리고 있는지를 조사 할 수 있으므로, SQL 트레이스는 튜닝의 초기의 단계에서 효율이 나쁜 SQL를 특정할 때에도, 또 튜닝이 필요한 어플리케이션이나 SQL가 특정한 후 실행 계획이나 대기에 주목한 개별의 SQL 튜닝에도 이용할 수 있습니다.

 

◆ 트레이스의 취득방법

■ TIMED_STATISTICS 파라미터     ● STATISTICS_LEVEL 파라미터에 의존         ◆TYPICAL(디폴트) or ALL            → TIMED_STATISTICS의 디폴트는 TRUE         ◆BASIC            → TIMED_STATISTICS의 디폴트는 FALSE     ● ALTER 커멘드로 동적으로 변경 가능 ■ MAX_DUMP_FILE_SIZE 그럼 실제로 SQL 트레이스를 취득하기 위해서 어떠한 설정을 실시하면 좋은가를 설명합니다. SQL 트레이스를 SQL 튜닝에 사용하기 위해서는, SQL의 실행중의 여러가지 처리에 필요로 했을 때 사이의 정보가 중요합니다.트레이스에 시간 정보를 출력시키기 위해서는, TIMED_STATISTICS 파라메타가 TRUE가 되어 있을 필요가 있습니다.  릴리스  9.2이후 버젼에서는   STATISTICS_LEVEL 이라고 하는 파라미터가 디폴트의 TYPICAL 또는 ALL라고 하는 값이 되어 있는 경우는 TIMED_STATISTICS는 디폴트로 TRUE로 설정됩니다만, STATISTICS_LEVEL가 BASIC으로 되어 있는 경우는 TIMED_STATISTICS의 디폴트치는 FALSE이므로, 명시적으로 TIMED_STATISTICS를 TRUE로 설정해 줘야 합니다. TIMED_STATISTICS 파라미터는 인스턴스의 재기동을 실시하지 않아도 ALTER 커멘드로 동적으로 변경 가능합니다. 또 SQL 트레이스는 트레이스·파일로 불리는 파일에 출력됩니다만, 그 파일의 사이즈가  MAX_DUMP_FILE_SIZE라고 하는 파라미터로 제한됩니다.파일의 사이즈가 이 값에 도달하면 그 이후의 정보가 출력되지 않게 되므로, 동일 세션으로부터 장시간에 걸쳐 SQL 트레이스를 취득하려고 하는 경우는 이 파라미터에도 주의가 필요합니다. ■ 인스턴스 전체로의 설정     ● 초기화 파라미터 SQL_TRACE=TRUE     영향이 크기 때문에 설정에는 주의가 필요 ■ 경향·세션에의 설정     ● ALTER SESSION SET SQL_TRACE = TRUE; ■ 타세션에의 설정     ● DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(9.2)         ◆KROWN: 2495 특정의 세션의 SQL_TRACE 를 취하고 싶다     ● DBMS_MONITOR (10.1)         ◆PL/SQL 패키지·프로시저 및 타입·레퍼런스 SQL 트레이스는, 인스턴스 전체로 설정하는것 또는 세션 단위로 설정하는 것이 가능합니다. 인스턴스 전체를 유효하게 하려면 초기화 파라미터의 SQL_TRACE를 TRUE로 해 인스턴스를 재기동합니다.그 러나 이 경우 모든 세션으로 SQL 트레이스가 유효하게 되기 때문에 모든 SQL 실행으로 트레이스가 생성되게 되어, 퍼포먼스에의 영향도 크고, 생성되는 트레이스 파일이 디스크 영역도 압박합니다.따라서 SQL 트레이스의 설정 대상이 되는 세션을 특정하기 힘든 경우나..등의 경우 이외는 이 방법을 사용해야 하지는 않습니다. ALTER SESSION SET SQL_TRACE=TRUE 를 실행하면 이 커멘드를 실행한 세션에서만 SQL 트레이스가 유효하게 되어, 그 이후에 세션내에서 실행된 SQL에 대해서 트레이스가 생성 됩니다.이 방법에 의해 다른 세션의 처리에는 영향을 주지 않고 트레이스를 취득할수 있습니다. 어플리케이션의 구조상의 이유로 ALTER SESSION 커멘드를 실행하고 나서 처리를 실시하는 것이 불가능한 경우 외부로부터 특정의 세션에 대해서 SQL 트레이스를 유효하게 할 필요가 있습니다. 릴리스 9.2까지의 버젼에서는 DBMS_SYSTEM 패키지의 SET_SQL_TRACE_IN_SESSION라는 프로시저로 SID와 SERIAL#에 의해서 세션을 지정해 SQL 트레이스를 유효하게 하는 것이 가능합니다. 릴리스 10.1에서는 DBMS_MONITOR라고 하는 패키지가 제공되어 SID와 SERIAL#로 특정되는 접속 끝난 세션에 대해서 SQL 트레이스를 유효하게 하는 것 외, 클라이언트·어플리케이션이 설정한 클라이언트 식별자나 서비스명등의 정보로, 트레이스를 유효하게 하는 세션을 지정하는 것도 가능하게 되었습니다. 어플리케이션에 클라이언트 식별자나 서비스명이라고 하는 정보를 설정하는 기능을 짜넣어 두는 것으로, 특정의 어플리케이션으로 SQL 트레이스를 유효하게 하는 것이 용이하게 됩니다. 또, 이 DBMS_MONITOR 패키지를 사용하면, 릴리스 9.2까지의 표준의 SQL 트레이스로는 출력되지 않고 이벤트라고 하는 Oracle 서포트로부터의 지시가 필요한 특수한 방법을 사용하지 않으면 얻을 수 없었던 바인드 변수의 정보나 대기의 정보를 출력시키는 것도 가능합니다. DBMS_MONITOR 패키지가 자세한 사용 방법에 대해서는 릴리스  10.1또는 10.2의 「PL/SQL 패키지·프로시저 및 타입·레퍼런스」의 메뉴얼을 참조해 주세요. ■ 트레이스의 종료     ● 세션 종료     ● 명시적인 트레이스 정지         ◆ALTER SESSION SET SQL_TRACE=FALSE         ◆DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION         ◆DBMS_MONITOR 어느 방법으로 트레이스 기능을 유효하게 했을 경우라도  명시적으로 트레이스 기능을 무효로 하지 않는 한 셋션을 절단하기 전까지는 SQL 트레이스가 취득됩니다.충분한 정보를 취득한 후 트레이스가 불필요하게 된경우 하지만 세션의 처리를 계속하고 싶은 경우는 트레이스 기능을 명시적으로 정지해 주세요.정지 방법은 각각의 설정 방법에 준거합니다.

◆ 트레이스의 출력처

■전용 서버 접속의 유저·세션     ● USER_DUMP_DEST 파라미터의 디렉토리 ■ 백그라운드·프로세스의 세션     ● BACKGROUND_DUMP_DEST 파라미터의 디렉토리 ■ 공유 서버 접속의 유저·세션     ● BACKGROUND_DUMP_DEST 파라미터의 디렉토리     ● 복수의 트레이스·파일에 출력될 가능성 있어 SQL 트레이스를 유효하게 하면 SQL의 실행에 따라 여러가지 정보가 트레이스·파일에 출력됩니다.트레이스·파일의 출력처는 SQL 트레이스가 유효하게 된 세션의 종류에 의해서 다릅니다. SQL 트레이스는 세션 단위로 유효/무효의 제어를 합니다만, 트레이스· 파일은 Oracle의 프로세스와 1대  1으로 할당할 수 있습니다. 따라서, 세션과 프로세스의 관계에 의해서 출력처의 트레이스·파일은 바뀝니다. SQL 트레이스가 유효하게 된 세션이 전용 서버 접속의 유저·세션의 경우, 어느 섹션은 1개의 서버·프로세스와 1대  1으로 대응 붙어 있을수 있기 때문에, 그 서버·프로세스의 프로세스 ID를 파일명에 포함한 트레이스· 파일이 USER_DUMP_DEST 파라미터로 지정된 디렉토리에 작성되어 세션의 SQL 트레이스는 그 파일에 출력됩니다. Oracle의 백그라운드·프로세스에는, SMON나 작업·큐 관련의 프로세스와 같이 내부적으로 SQL를 발행하는 프로세스가 있습니다만, 이러한 백그라운드·프로세스의 세션에 대해 SQL 트레이스를 유효하게 할 수도 있습니다. 이러한 백그라운드·프로세스의 세션은 프로세스와 1대  1인 점은 전용 서버 접속의 유저·세션과 같기 때문에, 세션의 트레이스가 1개의 파일에 출력되는 점은 같습니다만, 트레이스·파일의 출력처는 BACKGROUND_DUMP_DEST 파라미터로 지정된 디렉토리가 됩니다. 공유 서버 접속의 유저·세션의 경우, 1개의 세션의 처리가 끝까지 1개의 프로세스에서 실행된다고는 할 수 없습니다.공유 서버 접속의 세션으로 SQL 트레이스를 유효하게 했을 경우, 정보는 각각의 처리를 실행한 공유 서버· 프로세스의 트레이스·파일에 출력되기 때문에 세션이 복수의 공유 서버· 프로세스로 처리되었을 경우는 1개의 세션의 정보가 복수의 트레이스· 파일에 걸쳐 출력되게 됩니다.공유 서버·프로세스는 백그라운드· 프로세스 이므로, 출력처는 BACKGROUND_DUMP_DEST 파라미터로 지정된 디렉토리입니다. 릴리스 10.1에서는, 복수의 파일에 걸쳐 출력된 공유 서버 접속의 세션의 SQL트레이스를 1개의 파일에 정리하는 유틸리티가 제공되게 되었습니다. 이것에 대해서는 나중에 설명함다.

◆ 취득한 트레이스의 분석

TKPROF을 사용하기 전의 SQL 트레이스의 파일의 해석 이러한 샘플 코드는 Oracle Database 10.1.0.4에서 동작 확인을 하였습니다. PARSING IN CURSOR #1 len=144 dep=0 uid=48 oct=3 lid=48 tim=1114958440761553 hv=1599640102 ad='2a382f0' select translated_name, list_price from product_information i, product_descriptions d where i.product_id = d.product_id and d.language_id = 'JA' END OF STMT PARSE #1:c=3907,e=4181,p=1,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=1114958440761548 BINDS #1: EXEC #1:c=976,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1114958440761727 WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0 WAIT #1: nam='db file sequential read' ela= 17 p1=4 p2=267 p3=1 WAIT #1: nam='db file scattered read' ela= 75 p1=4 p2=268 p3=5 ... 그러면 실제로 출력된 트레이스·파일의 정보를 분석하는 방법을 보고 갑시다. SQL 트레이스를 유효하게 하는 것에 의해서 생성된 트레이스·파일에는, SQL의 실행에 수반해 순서적으로 다음 정보가 덧붙여져 써져 갑니다. 그 내용은 표시되고 있는 형식입니다. 통상은 TKPROF라고 하는 유틸리티를 사용해 트레이스·파일을 가공해, 튜닝에 필요한 정보 를 보기 쉬운 형태로 정리해 확인합니다. select translated_name, list_price from product_information i, product_descriptions d where i.product_id = d.product_id and d.language_id = 'JA' call     count     cpu    elapsed      disk     query     current   rows ------- ------ --------- ---------- ---------- ---------- ------- ------ Parse        1      0.00       0.00          1          2 0           0 Execute      1      0.00       0.00          0          0 0           0 Fetch       21      0.00       0.00        385        410 0         288 ------- ------ --------- ---------- ---------- ---------- ------- ------ total       23      0.01       0.01        386        412 0         288 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 48 SQL 트레이스로 취득한 트레이스를 TKPROF 유틸리티로 가공하면, 각 SQL문 마다 정보가 정리됩니다. SQL문의 다음에 표시되는 테이블 형식의 부분은, SQL문의 해석을 실시하는 Parse, 실행하는 Execute, 그리고 문의의 경우에 행을 꺼내는 Fetch의 각 처리와 total 즉 모든 처리의 합계에 관해서, 정보를 집계한 것입니다. count는 Parse/Execute/Fetch를 한 회수를 나타내고 있습니다.Fetch는 SELECT문의 경우에 행해지는 처리입니다만, 그 회수는 문의에 의해서 꺼내지는 전행 수와 1회의 Fetch로 꺼내지는 행수에 의해서 바뀝니다. 예를 들면 SQL*Plus로 실행된 문의의 경우는, ARRAYSIZE로 불리는 SQL*Plus의 시스템 변수의 값이 영향을 줍니다.디폴트에서는 15이기 때문에, 1회의 페치로 15행씩 꺼내집니다.또, 같은 SQL 트레이스 안에서 동일 SQL문이 여러 차례 실행되었을 경우는 Parse나 Execute가 여러 차례 카운트 되기도 합니다. cpu는 각각의 처리로 사용된 CPU 시간, elapsed는 경과시간을 합계한 것입니다. TIMED_STATISTICS 파라미터가 FALSE의 경우는 이것이 모두 0이 됩니다. 또, 표시 단위는 초에 0.01초가 TKPROF로 표시 가능한 최소치가 되기 때문에, 그것보다 짧은 시간에 종료한 경우는 TIMED_STATISTICS 파라미터가 TRUE가 되어 있어도 0으로 표시됩니다. 효율의 나쁜 SQL이 특정 되어 있지 않은 단계에서 SQL 트레이스를 취득했을 경우는 경과시간 즉 elapsed의 값이 큰 SQL에 우선 주목하면 좋을 것입니다.CPU 시간이 짧음에도 불구하고 경과시 사이가 긴 경우는, SQL문의 처리중에 대기가 많이 발생하고 있을 가능성이 있습니다. 그러한 경우는 나중에 나오게될 대기 이벤트의 정보에 주목해야 합니다. 또 Execute나 Fetch에 비해 Parse의 시간이 차지하는 비율이 큰 경우는 SQL문이 복잡하고 해석에 시간이 오래 걸리는 경우도 있습니다만, 공유 풀의 사이즈가 적절하지 않기 때문에 딕셔너리 케쉬나 라이브러리·캐쉬의 캐쉬·미스가 발생하고 있는 것이 원인일 가능성도 있으므로 공유 풀의 튜닝이 필요할지도 모릅니다. disk는 디스크로부터 읽어들인 블록수를 나타내고 있습니다. query와 current는 디스크 읽기를 실시한 것과 버퍼·캐쉬로 히트 한 것을 포함한, 액세스 한 버퍼의 수를 나타내고 있습니다.query와 current의 차이는, 블록을 참조 목적으로 엑세스 했는지 갱신 목적으로 액세스 했는지입니다만, 실행한 SQL이  문의여도 일부의 블럭은 갱신 즉 current 모드로 액세스 되기도 합니다.query와 current는 말해 액세스수로 이므로 같은 SQL문내에서 동일 블록이 여러 차례 액세스 되었을 경우, 액세스마다 카운트되는 것에 주의해야 합니다. 논리 읽기인 query와 current의 합계에 대해서 물리 읽기인 disk의 비율이 높은 경우는, 버퍼·캐쉬가 유효하게 사용 되어 있지 않을 가능성이 있습니다.또, 처리 대상의 행수와 비교해 query나 current의 값이 큰 경우는, SQL의 실행 계획이 적절하지 않을지도 모릅니다.동일 오브젝트에 대한 참조와 갱신이 동시에 행해지고 있고, 읽기 일관성을 유지하기 위한 버퍼·이미지 작성이 다발하는 경우도 query열의 값이 증대하는 경향에 있습니다. rows는 처리된 행수를 나타내고 있습니다.문의의 경우는 Fetch,INSERT/UPDATE/DELETE의 경우는 Execute의 스텝에서 행수가 카운트 됩니다. 좌측 하단의 부분은 주로 SQL문의 해석에 관한 정보입니다. Misses in library cache during parse가  0이 아닌 경우, Hard Parse이라는 SQL문을 실제로 해석하는 처리를 한 것을 의미하고 있습니다. Oracle 인스턴스내에서 이미 같은 SQL문이 실행되어 해석 끝난 공유 커서가 라이브러리·캐슈에 캐쉬되고 있는 경우는 Hard Parse는 필요 없습니다만, 인스턴스 기동 후에 처음 실행되는 SQL문이나, 이전에 작성된 공유 커서가 라이브러리·캐쉬로부터 없어진 경우는 Hard Parse가 필요합니다. 동일 SQL문이 여러 차례 실행되는 환경에서 Hard Parse가 다발 하고 있는 경우는, 공유 풀의 사이즈가 적절하지 않을 가능성이 있습니다. 또, 이 부분에 Misses in library cache during execute라고 하는 항목이 표시되는 일이 있습니다.이것은 해석에 의해서 일단 라이브러리·캐쉬에 작성된 공유 커서가, 실행시에는 캐쉬로부터 없어졌던 것을 의미하고 있습니다. Optimizer mode는 OPTIMIZER_MODE 파라미터의 값을 나타내고 있습니다.다만 여기에 RULE이라 출력되고 있어도, 파티션표에의 액세스나 패러렐 문의 등 코스트 베이스·옵티마이져가 동작하고 있는 케이스가 있습니다. Parsing user id는 SQL문의 해석을 실시한 유저의 유저 번호를 나타내고 있습니다. Rows     Row Source Operation ------- --------------------------------------------------- 288  HASH JOIN  (cr=410 pr=385 pw=0 time=6176 us) 288   TABLE ACCESS FULL PRODUCT_INFORMATION (cr=16 pr=14 pw=0 time=862 us) 288   TABLE ACCESS FULL PRODUCT_DESCRIPTIONS (cr=394 pr=371 pw=0 time=3938 us) 계속해서 그 SQL문의 실행 계획이 Row Source Operation의 부분에 출력됩니다. 다만 이것은 SQL의 커서가 클로우즈 된 타이밍에 트레이스에 출력되는 정보이므로, 커서가 크로즈 되어 있지 않은 상태로 트레이스 출력이 종료했을 경우는 SQL 트레이스에도 TKPROF의 가공 결과에도 실행 계획은 출력되지 않습니다. 또 TKPROF 실행시에 explain 옵션을 지정했을 경우에는, TKPROF 실행시에 SQL문이 해석 되어 실행 계획이 출력됩니다만, 그 실행 계획은 Execution Plan라고 하는 표제로 표시됩니다. SQL 실행시에 SQL 트레이스에 출력되고 있던 실행 계획인 Row Source Operation와의 차이에 주의를 해야 합니다. SQL 트레이스를 취득했을 때와 TKPROF를 실행했을 때에 파라미터나 색인등의 조건이 다르면, Row Source Operation와 Execution Plan의 실행 계획은 반드시 같지 않을 수 있습니다. 9.2이후의 버젼에서는, SQL 트레이스로 출력되는 실행 계획의 각 스텝에, 몇개의 추가 항목이 표시되게 되었습니다. Elapsed times include waiting on following events: Event waited on                   Times   Max. Wait Total Waited -------------------------------- Waited ---------- ------------ SQL*Net message to client            21        0.00 0.00 db file sequential read               2        0.00 0.00 db file scattered read               34        0.00 0.00 SQL*Net message from client          21        2.87 2.90 이 부분은, SQL의 처리중에 발생한 대기 이벤트에 대해서  종류별로 대기 회수나 대기 시간이 집계된 것입니다. 이것이 출력되는 것은 SQL 트레이스에 대기 이벤트에 관한 정보가 출력되고 있는 경우입니다. 대기 이벤트의 정보는,  10.1이후의 릴리스로 DBMS_MONITOR를 사용하는지, 그것보다 전의 릴리스에서는 이벤트 10046이라는 것을 사용해 트레이스를 취득하지 않으면 안됩니다.이벤트의 설정에 대해서는, Oracle 서포트로부터의 지시에 따라 주세요. SQL문의 처리에 대해 CPU 시간에 비해 경과시간이 긴 경우는 여기서 특히 대기가 많은 이벤트에 주목해 그 대기를 감소시킬 방향으로의 튜닝을 실시해서 SQL의 실행 시간을 단축시킬수 있습니다

◆ TKPROK의 옵션

■ sys=no     ● 재귀 SQL의 정보를 집계하지 않는다 ■ aggregate=no     ● 동일 SQL의 정보를 집계하지 않는다 TKPROF에는 지정 가능한 몇개의 옵션이 있습니다.자세한 것은 각 릴리스의 「퍼포먼스· 튜닝·가이드」로 해설되고 있습니다만, 디폴트 이외의 값을 지정하는 것에 의해서 분석에 도움이 되는 몇개인가를 여기에서는 소개합니다. SQL 트레이스를 취득하고 TKPROF로 분석하려고 하면 유저나 어플리케이션이 실행한 것이 아닌 SQL가 나타나는 일이 있습니다. 이것은 유저가 요구한 처리를 실행하기 위해서 Oracle가 내부적으로 실행을 필요로 한 재귀 SQL로 불리는 것으로, SQL문의 해석시에 딕셔너리 정보를 참조하거나 INSERT 실행시에 extent를 추가하거나 하는 처리로 발생한 것입니다. TKPROF로 분석할 때 디폴트인 sys=yes의 설정에서는 이러한 재귀 SQL의 정보도 집계됩니다.재귀 SQL는 TKPROF의 집계 결과에서는 Parsing user id가 SYS가 되어 있습니다.이러한 재귀SQL에 대한 정보를 제외하고 유저가 실행한 SQL문만의 집계를 실시하고 싶은 경우는, TKPROF 실행시에 sys=no를 명시적으로 지정하면, 유저가 실행한 SQL문에게만 주목해 정보를 보는 것이 용이하게 됩니다. 다만, 퍼포먼스상의 보틀 넥은 재귀 SQL로 발생하기도 하므로, 리스폰스가 나쁘더라도 sys=no의 결과로부터 유저가 실행한 SQL 문장에 문제가 발견되지 않는 경우는, 재귀 SQL의 정보도 확인하는것이 좋습니다. 같은 세션으로부터 동일 SQL를 여러 차례 실행해 SQL 트레이스를 취득했을 경우 TKPROF로 분석할때에 디폴트의 aggregate=yes의 설정은 동일 SQL의 정보는 하나로 집계합니다.  Parse/Execute/Fetch의 정보나 대기의 발생 상황은 모든 실행의 합계가 되어, 실행 계획은 최초로 실행되었을 때의 것으로 TKPROF의 가공 결과에 출력됩니다. 그러나 동일 SQL의 여러 차례의 실행할때 1회째와 2번째로 퍼포먼스에 차이가 있는 경우는, 집계되어 버리므로 그 차이를 알수 없습니다. 그러한 경우는 TKPROF 실행시에 aggregate=no의 지정을 합니다. 동일 SQL에서도 각 실행 마다 나누어 집계나 실행 계획의 표시를 하게 되므로, 1회째의 실행과 2번째의 실행의 액세스·블록수나 실행 계획, 대기의 발생 상황의 차이를 확인 하는 것이 가능하게 됩니다. 다만, 동일 SQL의 여러 차례 실행이 다용되고 있는 세션에서는, aggregate=yes의 경우에 비해 TKPROF의 출력 파일의 사이즈는 커집니다. ■ explain=username/password     ● SQL문의 실행 계획을 생성(트레이스 취득시와 다를 가능성에 주의) ■ sort=<sort option>     ● 여러가지 요소로 SQL문을 소트 해 출력순서를 제어 SQL의 실행 계획은 커서가 클로우즈된 단계에서 SQL 트레이스에 출력되기 때문에 TKPROF로 특별히 옵션을 지정하지 않아도 Row Source Operation로서 표시됩니다. TKPROF의 explain 옵션은, TKPROF로 SQL 트레이스를 가공하는 시점에서, 재차 SQL문의 해석을 가서 실행 계획을 Execution Plan로서 표시시키는 것입니다. SQL 트레이스의 파일에 실행 계획이 출력되어 있지 않은 경우는, 이 옵션으로 TKPROF 가공시에 확인할 수 있습니다만, SQL 실행시 즉 SQL 트레이스의 취득시와 TKPROF 실행시로, 오프티마이자의 동작에 영향을 주는 파라미터의 값이나, 색인의 존재, 오프티마이자 통계 수집등의 조건이 다른 경우는, explain 옵션에서는 실제로 사용된 실행 계획과 같은 것을 취득할 수 없을 가능성도 있습니다. SQL 트레이스에 많은 SQL문의 정보가 출력되고 있는 경우 그 중에서 특히나 실행 시간의 긴 것이나 액세스·블록수가 많은 것을 특정하는데 시간이 걸리는 일이 있습니다. sort 옵션을 사용해서 TKPROF의 가공 결과를 실행에 필요로 한 시간이나 블록 읽을 회수등에서 소트 할 수 있으므로, 문제가 되고 있는 SQL의 특정이 용이하게 됩니다. TKPROF로 SQL 트레이스를 가공하는 경우는, 이러한 옵션을 잘 활용하시길...

◆ TTRCSESS

■ 릴리스 10.1으로부터 제공 ■ 복수 프로세스의 트레이스·파일에 출력된 SQL 트레이스의 정보를, 이하의 정보별로 통합하는 유틸리티     ● 세션(SID,SERIAL#)     ● 클라이언트 ID(DBMS_SESSION.SET_IDENTIFIER)     ● 서비스명     ● 액션명 (DBMS_APPLICATION_INFO.SET_MODULE)     ● 모듈명 ■ 이 유틸리티로 가공한 파일을 TKPROF로 집계 가능 공유 서버 접속의 세션의 경우, 트레이스·파일은 각 서버·프로세스가 생성하기 때문에 1개의 세션이 복수의 공유 서버·프로세스로 처리되었을 경우, SQL 트레이스도 복수의 파일에 걸쳐 출력되게 됩니다. 또, 동시에 복수의 공유 서버 접속 세션이 SQL트레이스를 취득하고 있는 경우, 1개의 공유 서버·프로세스의 트레이스·파일에 복수의 세션의 SQL 트레이스가 혼재해 출력될 가능성도 있습니다.릴리스 10.1으로부터 제공되고 있는 trcsess 유틸리티를 사용하면, 그러한 케이스로 세션 단위에 트레이스를 통합하는 것이 가능합니다. 통합한 트레이스는 그대로 TKPROF로 집계할 수 있으므로, 공유 서버 접속의 세션이 생성한 SQL 트레이스의 분석이 용이하게 됩니다. 또 trcsess에서는 세션 단위이외 클라이언트 ID나 서비스명 등 어플리케이션측에서 설정한 정보를 키에 SQL 트레이스를 통합할 수도 있습니다. 10.1이후의 릴리스에서는, 클라이언트 ID나 서비스명/액션명/모듈명은 DBMS_MONITOR 패키지를 사용한 SQL 트레이스의 유효/무효의 제어에도 사용할 수 있으므로, 어플리케이션에 이러한 요소를 설정하는 처리를 조합해도 좋을 것입니다. 클라이언트 ID는 DBMS_SESSION 패키지의 SET_IDENTIFIER 그리고, 서비스명/액션명/모듈명은 DBMS_APPLICATION_INFO 패키지의 SET_MODULE로 각각 설정이 가능합니다