[oracle] sql 튜닝 테스트(AUTO TRACE)

AUTO TRACE를 활용한 DB 자가 진단 방법(SQL PLUS)

1. autotrace 환경(권한) 설정 : DBA 권한 (한번만 해주면됨)

A. PLUSTRACE 권한 생성

SQL> conn / as sysdba

SQL> @?/sqlplus/admin/plustrce.sql

B.해당 사용자에게 PLUSTRACE 권한 부여

SQL> conn / as sysdba

SQL> grant PLUSTRACE to 유저명

C. 해당 사용자에게 PLAN Table 생성

SQL> @?/rdbms/admin/utlxplan.sql

2. autotrace 환경(화면) 설정 (SQL PLUS- 실행할때마다 새로 설정)

A. Trace 시작

SQL> SET AUTOTRACE TRACEONLY

B. 화면폭 셋팅(150정도)

SQL> SET LINESIZE 150

C. PLAN 표현 형식

SQL> COLUMN PLAN_PLUS_EXP FORMAT A120

3.autotrace 결과 구조

A. Execution Plan : SQL 실행 PLAN 정보

B.Statistics : 실행 통계 정보

4. autotace 결과(Execution Plan)

A. Online 화면 처리 SQL에서는 FULL SCAN이 없는 것이 좋다(없어야 한다.).

B. Index를 통한 조회 라고 해서 모두 좋은 SQL은 아니다. 원하는 Index를 활용 하고 있는지 확인 해야 한다.

5. autotrace 결과(Statistics)

A. 항목

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

346 bytes sent via SQL*Net to client

496 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

B. 항목의 의미

db block gets : CURRENT block 이 요청된 횟수.

consistent gets : 한 block에 consistent read가 요청된 횟수.

physical reads : disk 로부터 읽어진 data block의 갯수.

redo size : 바이트단위로 만들어진 리두로그의 총용량.

bytes sent via SQL*Net to client : client 로 보내진 bytes 총갯수

bytes received via SQL*Net from client : sql*net 을 통해 client로 받아들인 bytes 총갯수

SQL*Net roundtrips to/from client : client와 오고간 sql*net message 의 총갯수

sorts (memory) : disk가 아닌 memory에서 행해진 sorting 작업의 횟수, sort_area_size에서 작업하는 수를 말함.

sorts (disk) : disk에서 한번이상 행해진 sorting 작업의 횟수, 임시테이블스페이스에 작업하는 횟수를 말함.

rows processed : 작업으로 발생된 총 결과 row수

C.튜닝 포인트

i.오라클의 튜닝은 값비싼 disk I/O(physical reads)를 줄임으로써 local I/O를 증가 시키는 것이다. 그러나 높은

consistent get은 튜닝을 해야 하는 포인트이기도 하다.

ii.Logical Reads : Consistent Gets + DB Block Gets

iii.Physical Reads : physical I/O가 발생한 Block의 요청 횟수 이다.

iv. Logical Reads 와 Physical Reads 가 높으면 튜닝의 대상이다. 온라인 프로그램에서는 작게는 500 혹은 1000 이상의

SQL은 확인이 필요하다.

v.Sorts(disk)는 DISK를 활용한 Sort 작업의 횟수 이다. 이 부분이 존재 하는 SQL은 확인이 필요하다.

- Range Scan 인덱스가 있지만 인덱스가 일치하지 않아 발생.(범위를 줄여줘야 좋다)

즉 해당인덱스에 여러건이 존재. Ex) 같은 설계번호에 여러가지 배서가 존재.

- Select * from (select * from …..) 에서 ()안의 sql은 최소한의 쿼리를 발생하는 것이 좋다. 한두건 정도.

- 조건절에 (+) 아우터 조인을 걸때는 모든조건에 (+)을 걸어줘야 정상적으로 아우터 조인이 걸릴 확률이 높다.

-(+) 아우터조인을 걸 때 인덱스가 정상 작동할 확률이 크다.

-(full scan)인경우 제데로 sql을 걸고있는지를 확인해야한다. Full scan이란 그테이블을 다 읽고 있다는 의미이므로.

- 조건절에 substract(a.piboja_jumin_birth(0,4)) = ‘1983’ 이런류의 조건을 걸때는 순서를 반대로 해야 인덱스를 탄다.

- 조인시 카테이션곱 이 생겼다면 해당 sql을 확인해야함. 조인이 10*100 이면 1000개의 퀴리발생.

[출처] 오라클 sql 튜닝 테스트(AUTO TRACE)|작성자 키아