오라클

[펌] trace 사용법

끄적끄적 2008. 12. 8. 11:08
SQL 튜닝의 기반이 되는 통계 정보

[출처] 【제5회】SQL 튜닝의 기반이 되는 통계 정보|작성자 hirokorea


본 자료는 일본 @IT(http://www.atmarkit.co.jp/fdb/index/index-db.html#tuneorasql) 에 株式会社アゲハ加藤 猛씨가 연재한 Oracle SQLチューニング講座를 번역 재구성한 것입니다.

 

전회「튜닝이 필요한 SQL 걸러내기」에서는 동적 성능뷰를 사용해 튜닝 대상이 될만한 SQL를 걸러내는 방법을 설명했습니다. 튜닝을 실시하기 위해서는 SQL의 실행 계획등 보다 상세한 정보가 필요합니다. 이번 회에서는 이러한 정보를 취득하는 방법, 수집한 정보의 분석 방법에 대해 설명합니다

 

■SQL 상세 정보의 취득

SQL 튜닝을 실시할 때에 중요한 정보로서는 SQL 실행 계획이나 실행시의 퍼포먼스에 관한 통계 정보가 있습니다.

 

지금까지 설명한 것처럼 SQL의 실행 속도는 그 실행 계획에 의해 큰폭으로 다릅니다. 그 때문에 SQL 실행시의 실행 계획이나 SQL이 사용하는 자원등을 조사해 그 실행 계획이 적절한 것인지를 평가할 필요가 있습니다. SQL 튜닝에 필요한 정보를 취득하려면 몇개의 방법이 있습니다만 대표적인 이하의 3개에 관해서 설명합니다.

 

 

실행계획의 취득

취득

난이도

어플리케이션 처리 전체의 정보 취득

각 SQL의 실행 시간에 관한 정보

취득에 의한 시스템 부하

정보 취득 범위

SQL 트레이스＋TKPROF 유틸리티

◎

△

◎

◎

△

인스턴스 또는 특정세션이 실행하는 전SQL

SQL*Plus의 AUTOTRACE 기능

○

◎

×

△(*)

◎

자기세션의 SQL

동적 성능뷰의 이용
(V$SQL,
V$SQL_TEXT,
V$SQL_PLAN)

○

○

△

○

○

인스턴스로 실행된 SQL

표 1　SQL 상세 정보를 취득하는 3개의 방법의 비교
(*) SQL*Plus로 set timing on를 설정하는 것으로 대체 가능

 

표1은 각방법의 특징을 집계한 것입니다. 사용 목적에 따라 적절한 취득 방법을 선택합니다. 예를 들면 튜닝의 효과를 보다 정확하게 측정하기 위해서는 해당 어플리케이션 혹은 SQL의 SQL 트레이스를 취득합니다. SQL의 실행 계획을 간단하게 확인하고 싶은 경우에는 SQL*Plus의 AUTOTRACE 기능을 사용하는 것이 효율적이지요.V$SQL등에서 조사한 SQL 등, 과거에 실행된 SQL의 실행 계획을 조사하기 위해서는 동적 성능뷰를 사용합니다.그러면, 각각의 방법에 대해 설명해 갈 것입니다.

 

■SQL 트레이스, TKPROF 유틸리티의 사용 방법

SQL 트레이스는 실행된 SQL의 실행 계획이나 퍼포먼스 통계 정보등을 텍스트 파일에 출력하는 기능으로 특정의 세션 혹은 인스턴스의 전세션의 정보를 취득할 수 있습니다. SQL 트레이스의 출력 결과를 그대로는 매우 이해하기 어렵기 때문에 TKPROF 유틸리티를 사용해 파일의 내용을 보기 쉬운 서식으로 정형합니다.

◆ SQL 트레이스, TKPROF 유틸리티의 실행 순서
그러 면 실제로 SQL 트레이스, TKPROF 유틸리티를 사용해 SQL의 상세 정보를 취득하는 순서를 설명합니다. 덧붙여 아래와 같은 순서는 SQL*Plus로부터의 실행예입니다만 같은 문장을 어플리케이션중에서 처리해서 어플리케이션으로 실행되는 SQL의 트레이스를 취득하는 것도 가능합니다.

1. SQL의 실행 유저(여기에서는 SCOTT로 접속)로 데이타베이스에 접속한다

$ sqlplus scott/passwd

 

Oracle Net 경유해 SQL 트레이스를 취득하는 경우에는 접속 방법(전용 서버 접속, 공유 서버 접속)에 따라 트레이스 파일의 작성 디렉토리가 다르기 때문에 주의가 필요합니다

  • 전용 서버 접속：초기화 파라미터 USER_DUMP_DEST에 지정한 디렉토리

  • 공유 서버 접속：초기화 파라미터 BACKGROUND_DUMP_DEST에 지정한 디렉토리

2. 시간에 관련하는 통계의 수집을 실시하도록 설정한다

SQL> ALTER SESSION SET TIMED_STATISTICS=TRUE;

 

Oracle9i 이후부터는 STATISTICS_LEVEL 파라미터의 설정치가 「ALL」또는 「TYPICAL(디폴트치)」의 경우에는 TIMED_STATISTICS 파라미터는 「TRUE」로 설정되므로 명시적인 설정은 필요 없습니다.

 

3. SQL 트레이스의 취득을 개시한다

SQL> ALTER SESSION SET SQL_TRACE=TRUE;

 

세션이 종료, 혹은 명시적으로 SQL 트레이스의 취득을 종료할 때까지, 해당 세션에서 실행되는 모든 SQL에 관해서 트레이스가 취득됩니다.

4. 튜닝 대상 SQL를 실행한다

SQL> SELECT COUNT(*) 

       FROM orders
      WHERE o_orderdate BETWEEN '96-04-01' AND '97-04-01' 

        AND O_ORDERSTATUS='O';

（注：⇒기호는 표시의 사정으로 되풀이하고 있는 것을 나타냅니다)

 

한 번에 대량의 SQL를 실행해 SQL 트레이스를 취득하는 경우에는 그 만큼의 트레이스 파일이 출력 가능한 충분한 디스크 용량이 있는 것을 확인해야 합니다.또 MAX_DUMP_FILE_SIZE 파라미터에 명시적으로 값을 설정한 경우에는 트레이스 파일의 최대 사이즈가  설정치×OS블록 사이즈로 제한되므로 동일 세션내에서 대량의 SQL를 실행할 때  주의가 필요합니다(Oracle R8.1.6이후부터 MAX_DUMP_FILE_SIZE의 디폴트치는 UNLIMITED입니다).

 

5. SQL 트레이스의 취득을 정지해, SQL*Plus를 종료한다

SQL> ALTER SESSION SET SQL_TRACE=FALSE;
SQL> exit

6. TKPROF 유틸리티의 실행

$ tkprof ora_11111.trc 11111.prf explain=scott/パスワード
  aggregate=no sys=no sort=fchela

트레이스 파일의 출력  디렉토리로 이동해 작성된 트레이스 파일을 정형합니다(대상이 되는 트레이스 파일은 SQL 트레이스를 취득한 시각을 기본으로 특정한다 ).

 

TKPROF 유틸리티는 트레이스 파일을 보기 쉽게 정형할 때에 다양한 옵션을 지정할 수 있습니다.대량의 SQL가 트레이스 파일에 포함되어 있는 경우에는 옵션을 지정해 보다 효율 좋게 튜닝 작업을 진행시킬 수 있습니다.

 

상기의 예에서는 EXPLAIN 옵션으로 실행 계획을 출력해 AGGREGATE 옵션으로 중복 SQL를 개별적으로 출력 ,SYS 옵션으로 재귀 호출 주注1 을 배제, 그리고  SORT 옵션으로 페치시의 경과시간순서에 SQL를 소트하는 트레이스 파일을 정형하고 있습니다.

 

注1：재귀호출
SQL문을 처리하기 위해서 내부적으로 발행되는 SQL문을 가리킵니다.예를 들면 테이블의 존재나 권한의 체크등을 행하기 위한 SQL등이 있습니다.

 

표2에 편리한 옵션을 정리했습니다만 그 다른 옵션에 대해서는 메뉴얼 「데이터 베이스 성능튜닝 가이드 및 레퍼런스 」를 참조하세요

옵션명

설명

EXPLAIN

TKPROF 유틸리티 실행시의 실행 계획을 출력하기 위한 유저명/패스워드를 지정한다

AGGREGATE

DEFAULT：YES
YES를 지정했을 경우 동일한 SQL는 집계되고, 1회만 출력된다
NO를 지정했을 경우 SQL 단위의 집계는 행해지지 않고 실행된 회수분 출력된다.SQL를 개별적으로 조사하고 싶은 경우에는 NO를 설정한다

SORT

지정한 옵션에 의해서 내림차순으로 SQL가 출력된다
EXEELA：실행시의 경과시간순서
EXEDSK：실행시의 디스크 액세스 블록수순
EXEQRY：실행시의 액세스 블록수순
FCHELA：페치시의 경과시간순서
FCHDSK：페치시의 디스크 액세스 블록수순
FCHQRY：페치시의 액세스 블록수순
(그 외에도 다수의 옵션이 있다)

SYS

DEFAULT：YES
NO를 지정하면, 재귀 호출 콜을 정형한 파일에 포함하지 않는다

표 2　TKPROF 유틸리티의 주요 옵션

 

EVENT 10046으로의 SQL 트레이스의 취득

문서에는 기재되어 있지 않습니다만 EVENT로 불리는 디버그용의 기능으로 SQL 트레이스로 취득할 수 있는 정보에 추가해 보다 상세한 정보를 취득하는 것도 가능합니다.EVENT 10046에는 표 3과 같은 레벨이 있으며 통상의 SQL 트레이스로 작성되는 트레이스 파일에는 포함되지 않는 바인드 변수의 값이나 대기 이벤트를 확인할 수 있습니다.통상의 SQL 트레이스에 비해 매우 많은 정보가 파일에 출력되기 때문에 디스크의 빈 용량에 충분히 확보한다.

 

덧붙여 EVENT는 Oracle의 내부 동작 변경이나 디버그 등에 사용되는 것이어서 정식으로 서포트되는 것이 아닙니다.사용하는 경우는, 자기책임이 불가피 합니다.

레벨

내용

LEVEL 1

SQL_TRACE 기능과 동등

LEVEL 4

LEVEL 1의 정보로 추가하고, 바인드 변수 정보가 출력된다

LEVEL 8

LEVEL 1의 정보로 추가하고, 대기 이벤트 정보가 출력된다

LEVEL 12

LEVEL 1의 정보로 추가하고, 바인드 변수 정보, 대기 이벤트 정보가 출력된다

표 3　EVENT 10046의 레벨

SQL> ALTER SESSION SET EVENTS '10046 trace name context 
     forever, level 12';
SQL> +++ 튜닝 대상 SQL의 실행 +++
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

EVENT 10046의 사용예

 

디폴트의 SQL 트레이스에는 포함되지 않는 바인드 변수의 값을 확인하는 경우 LEVEL 4혹은 LEVEL 12로 취득한 SQL 트레이스를 TKPROF 유틸리티로 정형하지 않고 직접 참조합니다.이하의 예는 LEVEL 4로 바인드 변수를 확인한 결과입니다.

PARSING IN CURSOR #2 len=57 dep=1 uid=35 oct=3 lid=35 tim=1071814928612333 hv=1198499521 ad='58cdd7d0'
SELECT * FROM orders
WHERE o_orderkey=:b2 AND o_clerk=:b1
END OF STMT
PARSE #2:c=10000,e=8105,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=0,
tim=1071814928612320
BINDS #2:
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=13  
 oacfl2=1 size=24 offset=0
   bfp=405e2a4c bln=22 avl=02 flg=05
   value=1
 bind 1: dty=1 mxl=2000(1000) mal=00 scl=00 pre=00 acfl2=1 
 oacflg=13 size=2000 offset=0
   bfp=405e2224 bln=2000 avl=15 flg=05
   value="Clerk#000000951"
EXEC #2:c=0,e=1637,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,
tim=1071814928614269
FETCH #2:c=1720000,e=1933973,p=23248,cr=24063,cu=0,mis=0,
r=1,dep=1,og=4,tim=1071814930548284

리스트 1　EVENT 10046 LEVEL 4의 출력예

 

이번 SQL는 CURSOR #2 로 실행되고 있고 바인드 변수의 값은 그 직후에 있는 BINDS #2 가 됩니다. 여기에서는 value=1 value="Clerk#000000951"가 지정된 것을 확인할 수 있습니다.

 

별개 세션의 SQL 트레이스 EVENT 10046의 SQL 트레이스 취득 순서

 

방금전의 예는 자기세션의 SQL 트레이스를 취득하는 방법을 설명했습니다만 어플리케이션을 변경할 수 없는 경우나 벌써 실행중의 어플리케이션의 SQL 트레이스를 취득하고 싶은 경우라도 SQL 트레이스나 EVENT10046를 세트 하는 것도 가능합니다.

다만, 취득할 수 있는 것은 트레이스 취득 개시시점 이후의 정보가 됩니다.이미 실행중인 SQL에 관해서는 읽기 블록수등의 값이 부정확이 하기 때문에 주의. 이하에 별세션의 SQL 트레이스를 취득하는 예를 나타냅니다.

1. sysdba 권한을 가지는 유저로 로그인한다

$ sqlplus '/ as sysdba'
SQL>

2. 트레이스 취득 대상 세션의 SID, SERIAL#를 V$SESSION 동적 파포만스뷰로 확인한다

SQL> SELECT sid,serial#,username,program,machine,

            status,last_call_et 

       FROM v$session
      WHERE username='SCOTT';

SID SERIAL# USERNAME PROGRAM  MACHINE STATUS LAST_CALL_ET
--- ------- -------- -------- ------- ------ -------------
 10      12 SCOTT    AAA@AB6  lin006  ACTIVE            2

 

여기에서는 SCOTT 유저를 지정해 있습니다.

 

3. SQL 트레이스를 설정한다

SQL>EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(10,12,TRUE);

 

여기에서는, SID, SERIAL#, TRUE를 지정하고, 상기의 프로시저를 실행합니다.

 

4. SQL 트레이스의 설정을 해제한다

SQL>EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(10,12,FALSE);

또한 트레이스 파일은 자기세션의 SQL 트레이스를 취득했을 때와 같은 디렉토리하에 작성됩니다.

 

별도세션에 대해서 EVENT 10046의 SQL 트레이스를 설정하는 경우에는, 순서의 3 과 4.그리고 이하의 프로시저를 실행합니다.

3. EVENT 10046을 LEVEL 12로 설정한다

SQL> EXECUTE DBMS_SYSTEM.SET_EV(10,12,10046,12,'');

 

이쪽도 SQL 트레이스와 같이 잊지 않고 로 설정을 해제합니다.

4. EVENT 10046의 설정을 해제한다

SQL> EXECUTE DBMS_SYSTEM.SET_EV(10,12,10046,0,'');

 

 

■ 트레이스 파일의 주목 포인트

트레이스 파일에는 많은 정보가 출력됩니다. 그렇다면 튜닝을 실시할 때에 주목해야 할 포인트를 유틸리티에 의해 정형 후의 파일을 예로 설명하겠습니다. 

 

그림 1　TKPROF에 의해 정형 후의 트레이스 파일

（1）

SQL

실행한 SQL문

（2）

CPU 시간
(1/100초단위)

Parse, Execute, Fetch의 각 CPU 시간과 합계 CPU시간을 표시.1/100초이하의 경우, 0초로서 계산된다

（3）

경과시간
(1/100초단위)

Parse, Execute, Fetch의 각 경과시간과 합계 경과시간을 표시.1/100초이하의 경우, 0초로서 계산된다

（4）

Disk 액세스 블록수

디스크 I/O 발생으로 읽어들인 블록수

（5）

버퍼 액세스 블록수

메모리상에 액세스 한 블록수

（6）

행수

이 SQL를 실행한 결과 처리된 행수

（7）

Library cashe miss

값이 0의 경우 공유 풀상의 해석 결과가 존재했기 때문에 해석 처리가 배제된 것을 나타낸다

（8）

옵티마이져 모드

옵티마이져모드를 나타낸다

（9）

해석한 유저

SQL문을 해석한 유저 ID를 나타낸다

（10）

SQL가 실행되었을 때의 실행 계획

SQL가 실행되었을 때의 실행 계획을 나타낸다
(자세한 것은 후술)

（11）

TKPROF가 실행되었을 때의 실행 계획

TKPROF가 실행되었을 때의 실행 계획을 나타낸다
(자세한 것은 후술)

표 4　TKPROF 후의 트레이스 파일의 주된 출력 항목

◆ SQL 실행시의 실행 계획 보는 방법

그림 1의 (10)은 SQL가 실행되었을 때의 실행 계획을 나타내고 있습니다.

2행째에  TABLE ACCESS FULL 라고 출력되고 있으므로 풀 테이블 스캔을 했던 것이 확인할 수 있습니다. OBJ#의 괄호안의 숫자는 오브젝트 번호  OBJECT_ID를 나타내고 있어 DBA_OBJECTS 딕쇼내리·뷰를 참조해 대상의 오브젝트를 특정할 수 있습니다. 또 그 뒤로 표시되고 있는 「cr」 「r」 「w」 「time」의 파라미터는 각각 표 5와 같은 내용을 나타내고 있습니다.

 

cr

버퍼에서 읽은 합계 블록수

r

디스크에서 읽은 합계 블록수

w

디스크에 쓴 합계 블록수

time

합계 경과시간(1/1000000초, micor second)

표 5　실행 계획중의 파라미터의 의미

 

이러한 항목에 출력되는 값은 부모스텝(SORT 처리)의 값과 자식스텝(TABLE ACCESS 처리)의 값과의 합계치가 됩니다.그 때문에 각 스텝으로의 처리 시간이나 액세스 된 블록수등의 정보는 스텝마다의 차분으로부터 읽어낼 필요가 있습니다.

 

그림 1의(10), 「time」의 값을 예로 들어 설명하면, 최초의 스텝인 TABLE ACCESS FULL는, (60530996)이며, 이 값이 풀테이블 스캔에 걸린 시간이 됩니다. 다음의 스텝인 SORT GROUP BY는(62654805)가 되고 있습니다만 이 값에는 전 스텝의 TABLE ACCESS FULL의 값도 포함되어 있습니다. 그 때문에 순수한 SORT GROUP BY의 경과시간은(62654805 - 60530996 = 2123809)  즉 약 2.1초인 것을 알수 있겠네요.

 

◆ TKPROF 실행시의 실행 계획 보는 방법

그림 1의  TKPROF 유틸리티를 실행한 시점에서의 실행 계획 즉 현시점에서 이 SQL를 실행했을 때에 사용되는 실행 계획입니다. SQL 트레이스를 취득한 시점으로부터, 테이블 , 색인의 통계 정보가 갱신된 경우나 색인의 작성/삭제등이 일어나다면 SQL 트레이스 취득시점의 실행 계획과 달라 지는 경우가 있습니다.

 

◆SQL의 분석

그림 1의  실행 계획의 결과는 인덴트가 깊은(오른편으로 출력됨) 처리가 먼저 실행되어 결과가 위의 레벨에게 건네집니다. 동일한 레벨의 처리가 존재하는 경우는 보다 위에 있는 처리가 먼저 실행됩니다.

 

그 때문에 이 실행 계획의 결과에서는 LINEITEM테이블의 풀테이블 스캔이 발생하고 나서 GROUP BY 처리를 하고 있는 것을 확인할 수 있습니다.또 (3) SQL의 경과시간이 「62.81초」이며, (10)의 상세하게 확인한 풀테이블 스캔만의 시간은 「 약 60.5초(time=60530996)」인 것을 알수 있습니다.

 

처리 시간의 대부분을 풀테이블 스캔이 차지하고 있으므로 이 점을 개선하는 것이 실행 시간의 단축으로 연결될 것이라고 예상할수 있습니다. 또, (4)라고(5)를 보면 거의 전블록의 액세스가 디스크 I/O를 수반하고 있는 일도 확인할 수 있습니다.(1)의 SQL를 보면 WHERE 조건이 포함되어 있으므로 조건열의 Cardinallity 조사해 색인 작성이 유효한가 조사를 진행시키게 됩니다.

 

■ SQL*Plus의 AUTOTRACE 기능

 AUTOTRACE는 SQL*Plus의 기능으로 SQL의 실행 계획 및 실행시에 필요로 한 system resource(이것을 실행 통계라고 부릅니다)등을 간단하게 확인할 수 있습니다. 또 트레이스에는 포함되지 않는 메모리소트, 디스크 소트의 발생 회수등도 확인할 수 있습니다.

 

◆ AUTOTRACE 기능의 설정 방법

AUTOTRACE 기능을 사용하려면 일단 사전 준비작업이 필요합니다. 

 

1. SYS 유저로 plustrce.sql를 실행해, PLUSTRACE 롤을 작성한다

$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql

이 스크립트는, 데이타베이스에 대해서 1번만 실행합니다

 

2. AUTOTRACE 기능을 사용하는 유저에게 PLUSTRACE 롤을 부여한다

SQL> GRANT plustrace TO scott;

3. 실행 계획의 정보를 격납하기 위한 PLAN_TABLE 테이블을 작성한다

$ sqlplus scott/passwd
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

AUTOTRACE 기능을 실행하는 유저로 utlxplan.sql를 실행해 실행 계획을 격납하기 위한 PLAN_TABLE 테이블을 작성합니다. 상기에서는 SCOTT 유저로 실행합니다.

 

이것으로 SCOTT 유저가 AUTOTRACE 기능을 사용할 준비는 완료입니다.

 

◆AUTOTRACE 기능의 사용 방법

그러면 실제로 AUTOTRACE 기능을 이용해 보겠습니다.AUTOTRACE 기능에는, 표 6에 있는 옵션이 있습니다. 여기에서는, TRACEONLY 옵션을 지정해 SQL의 실행 결과를 출력하지 않고 실행 계획, 실행 통계만을 출력합니다.

 

オプション

説明

SET AUTOTRACE ON

실행 계획과 실행 통계를 리포트 출력한다

SET AUTOTRACE OFF

리포트 출력을 하지 않는다

SET AUTOTRACE ON EXPLAIN

실행 계획만 리포트 출력한다

SET AUTOTRACE ON STATISTICS

실행 통계만 리포트 출력한다

SET AUTOTRACE TRACEONLY

데이터를 페치 하지만, 결과를 출력하지 않고 실행 계획, 실행 통계를 리포트 출력한다.뒤에 EXPLAIN, STATISTICS 옵션을 붙이는 것도 가능

표 6　AUTOTRACE의 주된 옵션

그림 2　AUTOTRACE의 출력 결과예

（1）

실행 계획

SQL의 실행 계획을 표시

（2）

recursive calls

SQL 실행시 내부에서 발행된 재귀 호출 콜수

（3）

db block gets

DML나 SELECT FOR UPDATE를 발행등에 발생하는 current 모드로 읽힌 블록수

（4）

consistent gets

SELECT를 발행했을 때 발생하는 읽기 일관성 모드로 읽힌 블록수

（5）

physical reads

디스크 액세스로 읽힌 블록수

（6）

redo size

REDO 로그에 쓰여진 싸이즈(byte)

（7）

bytes sent via SQL*Net to client

클라이언트에 보내진 합계 byte수

（8）

bytes received via SQL*Net from client

클라이언트부터 수신한 합계 byte수

（9）

SQL*Net roundtrips to/from client

클라이언트에 송수신 된 Net 메세지의 합계수

（10）

sorts (memory)

메모리소트 회수

（11）

sorts (disk)

디스크 소트 회수

（12）

rows processed

SQL가 처리한 건수

표 7　AUTOTRACE의 주된 출력 항목

 

■ V$SQL_PLAN로의 실행 계획 확인

 Oracle9i에서는 실행 계획이 공유 풀내에 캐쉬되고 있으므로 캐쉬에 보관 유지되고 있는 동안은, 과거에 실행된 SQL의 실행 계획을 확인할 수 있게 되었습니다. 실제의 확인 방법을 설명합니다.

 

V$SQL_PLAN에서 실행 계획 확인 방법

SQL의 실행 계획을 조사하기 위해서는 SQL가 특정되어있을 것, ADDRESS치, HAHS_VALUE치가 필요하게 됩니다(확인 방법은, 제4회 「튜닝이 필요한 SQL 걸러내기」를 참조해 주세요).

DEFINE W_ADDRESS=54B4DF20      -- 여기에 대상 SQL의 ADDRESS

DEFINE W_HASH_VALUE=1273901568 -- 여기에 대상 SQL의 HASH_VALUE


SELECT id,
lpad (' ', depth) || operation operation,
options,
object_name,
optimizer,
cost
FROM v$sql_plan
WHERE hash_value=&W_HASH_VALUE
AND address='&W_ADDRESS'
START WITH id = 0
CONNECT BY
(PRIOR id=parent_id
AND PRIOR hash_value=hash_value
AND PRIOR child_number=child_number)
ORDER SIBLINGS BY id, position;

  ID OPERATION  OPTIONS OBJECT_NAME  
    OPTIMIZE       COST
---- -------------------- --------------- -----------------
------------- -------- ----------
   0 SELECT STATEMENT                                        
              CHOOSE
   1  SORT                GROUP BY
   2   TABLE ACCESS       FULL            LINEITEM

리스트 2　V$SQL_PLAN로부터 출력한 실행 계획예

 

마지막으로 각 취득 방법의 메리트 디메리트를 표 8에 정리합니다.

 

메리트

디메리트

SQL 트레이스＋TKPROF 유틸리티

・상세한 정보가 취득 가능
・시간 통계의 취득이 가능
・어플리케이션으로 실행되는 모든 SQL를 취득 가능

・트레이스 파일을 격납하기 위한 디스크 영역이 필요
・·취득시에 다소의 부하가 발생

SQL*Plus의 AUTOTRACE 기능

・간단하게 실행 계획을 확인하는 것이 가능

・환경 설정이 필요

동적 퍼포먼스
뷰의 이용

・과거에 실행된 SQL의 실행 계획을 확인하는 것이 가능

・공유 SQL 영역이  환경이나 시스템 자체의 부하가 매우 높은 환경에서는 오버헤드가 되는 경우가 있다

표 8　각방법의 메리트/디메리트

 

◇

 

이상으로 SQL의 정보 수집에 관한 설명은 끝입니다. 금회에서 설명한 것 같이 실행 계획등 보다 상세한 정보를 수집해 튜닝을 진행합니다. 다음 회에서는 실제의 튜닝 방법이나  테크닉에 대해 설명합니다.

 

참고

SQL문의 튜닝 - SQL 트레이스의 취득 방법 및 TKPROF에 의한 통계 정보의 해석

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

STATSPACK 튜닝을 하기 위해 자원 소비가 많은 SQL를 특정하는 방법

 

반응형

'오라클'의 다른글

  • 현재글[펌] trace 사용법

관련글

댓글

티스토리툴바