3. SQL 실행계획 확인 ★★★ (미완)

 

▶SQL실행계획 확인방법

* SQL Plus의 Autotrace기능 (가장 일반적★) (ex.병원의 엑스레이) : 실행결과, 실행계획, 통계정보

* SQL trace 파일 생성 및 TKPROF 사용 (일반적★) (ex. SQL trace = 병원의 MRI처럼 훨씬 자세하다)

 - 모든 SQL 문장에 대한 추적파일 생성

 - TKPROF로 추적 파일의 내용 분석

 - 실제 튜닝에서 가장 많이 활용

* SQL 튜닝을 위한 기본적인 툴

* Oraccle Enterpise Manager : SQL 스크래치 패드 (GUI환경)

* EXPLAIN PLAN명령어 사용 : PLAN_TABLE에 실행정보저장

 

▶SQL*Plus Autotrace 기능 활성화

* plustrace 권한 생성

> conn / as sysdba  > @?/sqlplus/admin/plustrace.sql

* 해당 사용자에게 plustrace 권한 부여

> conn / as sysdba  > grant plustrace to scott;

* 해당 사용자로 로그인 후 plan table 생성

>conn scott/tiger  > @?/rdbms/admin/utlxplan.sql

 

▶Autotrace 모드 설정

> set autotrace on  --모든 메뉴를 다 보여달라  > set autot off   --autot 까지만 써도됨.  > set autot traceonly  --  > set autotrace traceonly explain  --  > set autotrace traceonly statistics   --

 

▶SQL*Plus autotrace 예제

SQL> set autotrace on SQL> select * from dept where deptno = 10;     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK Execution Plan ------------------------------------------------------    0      SELECT STATEMENT Optimizer=CHOOSE    1    0   TABLE ACCESS (FULL) OF 'DEPT' Statistics -------------------------------------------------------         178  recursive calls           0  db block gets          27  consistent gets           7  physical reads           0  redo size         629  bytes sent via SQL*Net to client         655  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           2  sorts (memory)           0  sorts (disk)           1  rows processed

 

 

▶SQL Trace기능

* Auto trace는 자신이 수행하는 쿼리만 추적할 수 있지만, SQLTrace는 다른사람이 수행하는 쿼리를 몰래 추적할 수 있는 장점이 있다.

* 모든 SQL수행에 대한 Trace파일 생성

 - 인스턴스 레벨 추적 : 전부 다 추적하므로 많은 부하 발생 - 각 프로세스별 추적 파일 생성

 - 세션 레벨 추적 : 이거로 써야된다!

* 구문분석, 실행 및 인출 단계에 대한 크기 및 시간 통계 정보

* 시스템의 전체적인 분석에 필요

* 관련 초기화 파일

 - USER_DUMP_DEST에 추적파일 생성됨.

 - TIMED_STATISTICS=true : 정확한 시간 통계치

 

▶SQL trace 기능 활성화

인스턴스 레벨 - 초기화 파라미터 수정, 재시동

SQL_TRACE = true

현재 접속중인 세션(내 것만 읽는다.)

sys> grant alter session to scott; --10g부터는 권한 줘야된다.  scott> alter session set tracefile_identifier='AAA'; -- 설정줌.  scott> alter session set sql_trace=true;  scott> alter session set timed_statistics=true;  -- 바로 위 두 문장을 쓰거나 또는 scott> EXECUTE dbms_session.set_sql_trae(true) 이 문장을 써주면 된다.  scott> show parameter user_dump_dest; --alertlog경로확인  $ cd /app/oracle/diag/rdbms/testdb/testdb/trace --select해서는 볼 수 없고, 파일에 직접 가봐야 알 수 있다.  $ ls *AAA*  --조회하면 trace 파일이 생성되어 있다. 이제 vi로 열어 분석하면 된다.

임의의 세션 추적 (다른사람 것도 읽는다.)

sql> execute dbms_system.set sql_trace_in_session (sid, serial#, true); -- v$session을 통해 SID와 SERIAL# 구함.

 

▶SQL trace 리포트 생성 - TKPROF

- USER_DUMP_DEST에서 해당 trace 파일 확인

>tkprof 파일명.trc 임의로생성.txt explain=scott/tiger sys=no --sys계정으로 만든 것은 빼고 보기 편하게 정렬해서 임의생성.txt에 저장.

 

-43page