SQL TUNING - 기본

-- 튜닝 수업 환경 설정

(대소문자 구분을 끄기위해서 설정)

alter system set sec_case_sensitive_logon=FALSE;

 

ALTER USER SYSTEM IDENTIFIED BY MANAGER;

DROP USER SCOTT CASCADE;

 

CREATE USER SCOTT IDENTIFIED BY TIGER

 

GRANT CONNECT,RESOURCE,DBA TO SCOTT;

 ( pfile일 경우 spfile로 변경 )

 

1. 대용량 샘플 dmp 파일 imp 수행

 

[oracle@localhost ~]$ imp system/MANAGER full=y file=NEW_SCOTT_10.DMP;

 

-- imp 확인 (20개 table)

SELECT * FROM DBA_TABLES

WHERE OWNER='SCOTT';

 

20 rows selected.

 

-- public synonym 제공

select 'create public synonym '||TABLE_NAME||' for '||OWNER||'.'||TABLE_NAME||';'

from dba_tables

where owner='SCOTT';

 

EXPLAIN PLAN 명령어

 

■ SQL문의 실행계획과 상태 값을 제공하는 명령어

 

1) 옵티마이저의 유형 (공식기반 또는 비용기반)

2) 옵티마이저에 의해 결정된 실행 계획

3) 실행 계획의 선택 기준(비용계싼 결과 등)

4) SQL문을 실행하면서 사용한 DB구조의 상태 값

- 사용된 데이터버퍼 캐시 블록 수

- 데이터 파일로부터 읽은 블록 수

- 네트워크를 통해 전송된 데이터의 바이트 수

- Sorting 작업 발생 시 사용된 메모리 블록 수

- 처리된 데이터 량

 

SET AUTOTRACE 명령어

 

■ PLAN_TABLE을 생성 후 한번만 설정해주면 SQL문이 실행될 때 마다 실행계획을 한 화면에 출력

 

문법 SET AUTOTRACE [ ON | OFF | TRACE | TRACEONLY ] - ON : SQL문의 실행 결과와 실행계획 그리고 통계정보를 보여주는 옵션 - OFF : 어떤 결과도 보여 주지 않음 - TRACEONLY : 실행계획과 통계정보 만을 보여줌

 

* 켜놨다면 사용후 OFF로 반드시 바꿔줘야함 (조회할때마다 뜨기때문에)

 

SQL> SET AUTOTRACE TRACEONLY;

SQL> SELECT * FROM DEPT;

*

아래부터 올라감

sorts (momory) : PGA ( PGA 사용하다 모자라면 TEMP로 넘어감 )

sorts (disk) : TEMP

 

V$SQL_PLAN

∙이미 실행된 SQL에 대한 실행계획을 참조할 경우 사용

∙Shared pool 영역에서 직접 추출된 실행 정보

∙영원히 보관할 수는 없음

∙SQL문의 HASH_VALUE와 ADDRESS값을 알아야 함

   => V$SQL, V$SQLAREA에서 참조

-- hash_value, address 찾기

select sql_text

, hash_value

, address

from v$sqlarea

where upper(sql_text) like '%DEPT%'

 

 

 

select id , lpad ('', depth) || operation operation , options , object_name , optimizer , cost from v$sql_plan where hash_value = &1 and address = '&2' 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;

 

결과 분석 (Execution Plan)

SQL> set autotrace traceonly

SQL> select empno, ename, job, sal, dname

from emp, dept

where emp.deptno = dept.deptno;

 

 

SQL> CREATE INDEX DEPTNO_IDX ON EMP(DEPTNO);

= > 다시조회 해도 full scan 함

* 인덱스를 만들어도 데이터량이 적어서 풀스캔하게됨

 

-- INDEX를 타도록 Hint 설정

select /*+index(emp DEPTNO_IDX) */

empno, ename, job, sal, dname

from emp, dept

where emp.deptno=dept.deptno;

 

 

 

1) Parsing 단계에서 Data Dictionary로 부터 얻는 테이블의 상태정보 및 통계정보를 참조하기 위해 읽는 블록 수 * 다시 조회하면 줄어듬 2) DML 문이 실행될 때 발생하는 변경 전 데이터를 잠시 저장하기 위한 임시공간 3) SQL문이 실행될 때 디스크 상에 존재하는 테이블 및 인덱스를 저장하기 위한 메모리공간 * 가장 눈여겨 봐야할 튜닝 Point! 4) 데이터가 실제로 존재하는 디스크 상의 데이터 파일로부터 읽혀진 데이터 블록 수 * 다시 조회하면 줄어듬 5) DML문을 실행했을 때 변경 전 데이터와 변경 후 데이터를 로그버퍼 영역에 백업하기 위한 블록 수 6) 클라이언트와 서버 간의 전송된 데이터를 나타냄 * 튜닝 Point가 아님 7) sorting 작업을 위해 사용한 PGA 공간 * sorts (momory) : PGA ( PGA 사용하다 모자라면 TEMP로 넘어감 ) 8) sorting 작업을 위해 사용한 TEMP 사용 공간 * sorts (disk) : TEMP ==> 7,8 번이 둘다 많을시 시스템 튜닝을 고려 9) SQL문이 실행된 후 조건을 만족하는 행의 수 * 튜닝 Point가 아님

 

 

 

※ Update문 기본 튜닝

-- BIG_EMP에도 있는 사원은 COMM을 SAL에 더해서 LARGE_EMP의 SAL을 UPDATE 하려고 한다.

-- (SQL구문을 튜닝해서 더 효과적으로 만들기)

 

(튜닝 전)

update large_emp a

set a.sal = ( select b.comm + a.sal

from big_emp b

where b.empno=a.empno);

(튜닝 후)

update large_emp a

set a.sal = a.sal + (select b.comm

from big_emp b

where b.empno=a.empno);

 

 

* consistent gets 천만건 정도 차이나는걸 볼 수 있다

 더 좋은 효과를 위해선 index를 걸어주면 1/4로 주는 걸 볼 수 있다.