인덱스의 재구성
사용자의 테이블에 select문이 실행되면 최초 구성된 벨러스트리 구조의 인덱스는 좌와 우가 대칭적인 주조를 가짐으로서 좋은 성능을 보장해 준다. 하지만 테이블에 데이터가 UPDATE, INSERT, DELETE되면 처음에 만들어 졌던 벨러스 구조는 깨지고 좌와 우의 구조도 달라지게 된다. 이러한 현상이 발생하면 좋은 성능을 보장해 줄 수 없기 때문에 데이터베이스 관리자는 주기적으로 또는 비 주기적을 밸런싱이 깨진인덱스를 분석하고 인덱스를 제구성하는 작업을 해야만 성능을 기대할 수 있다.
ANALYZE TABLE [table명] VALIDATE STRUCTURE; --> 밸런싱이 깨진 인덱스를 분석하는 명령어
ANALYZE 명령어 실행 후 분석결과는 INDEX_STATS 자료사전에 저장된다.
ALTER INDEX [index명] COALESCE; --> 인덱스 블록의 조각난 FREE SPACE를 하나의 연속적인 공간으로 합쳐주는 명령어
여러개의 블록이 조각나면 공간낭비, 성능저하의 원인이 되므로 성능향상에 도움이 된다.
ex)
SQL> create index pk_emp on emp(empno);
인덱스가 생성되었습니다.
SQL> select index_name, index_type from user_indexes;
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
PK_EMP NORMAL
SQL> analyze index pk_emp validate structure; --> index_stats 자료사전에 결과가 저장된다.
인덱스가 분석되었습니다.
SQL> select (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 as index_stat -->
2 from index_stats;
[DEL_LF_ROWS_LEN] 컬럼은 인덱스 블록중 삭제되었던 행들의 전체길이 값이며,[LF_ROWS_LEN]컬럼은 현재 인덱스가 리퍼 블록에 저장되어 있는 인덱스 테이터의 전체길이를 의미한다.
INDEX_STAT
----------
0 ---> 밸런싱 정도가 20%를 초과하면 인덱스를 재구성해야 좋은 성능을 기대할 수 있다.
SQL> alter index pk_emp rebuild;
인덱스가 변경되었습니다.
인덱스 클러스터
테이블 전체 스캔방법 또는 인덱스 스캔방법으로 검색에 대한 성능을 기대할 수 없는 경우 사용하는 기법으로 2개이상의 테이블이 조인된 형태로 블록에 저장되는 기법을 의미한다.
SQL> select rowid, deptno, dname from dept;
ROWID DEPTNO DNAME
------------------ ---------- ---------------
AAAHanAAJAAAAAmAAA 10 인사부 --->
AAAHanAAJAAAAAmAAB 20 전산부
AAAHanAAJAAAAAmAAC 30 총무부
데이터를 저장한 블록이 AAAAAm 블록이다 아래의 emp 테이블에서 데이터가 저장된AAAAA+ 블록과 다르다는것을 확인할 수 있다.
SQL> select rowid, empno, ename, deptno from emp;
ROWID EMPNO ENAME DEPTNO
------------------ ---------- ---------- ----------
AAAHcjAAJAAAAA+AAA 111 일지매 10
AAAHcjAAJAAAAA+AAB 112 황진희 20
AAAHcjAAJAAAAA+AAC 113 홍길동 30
SQL> l
1 select emp.empno, emp.ename, dept.dname -->
2 from emp, dept
3* where emp.deptno = dept.deptno
SQL> /
EMPNO ENAME DNAME
---------- ---------- ---------------
111 일지매 인사부
112 황진희 전산부
113 홍길동 총무부
2개의 테이블을 조인한다면 하나의 결과를 보기 위새서 각 테이블을 모두 읽고 내부적으로 sort작업을 한 뒤 조건을 만족하는 데이터를 보여주게 된다. 그런데 이러한 작업을 할때 데이터의 행을 수가 아주 많다면 이러한 방법으로 의 검색은 매우 늦어 질 수 밖에 없을 것이다.
이러한 문제를 개선한 방법이 인덱스 클러스트 이다.
인덱스 클러스트를 통한 테이블 조인
인덱스 클러스트를 생성하는 기본문법
CREATE CLUSTER CLU1
(dept number(1))
SIZE n;
여기서 사용된 dept 컬럼은 2개이상의 테이블이 조인될때 조인에 참여되는 컬럼을 선언한 것이다. 이것을 클러스트 키라고 한다. size절은 하나의 블록에 저장되는 같은 크러스트 키를 가지는 행의 길이 를 의미한다.
CREATE INDEX i_CLU1 ON CLUSTER CLU1; --> [클러스트 인덱스]는 테이블의 데이터를 빠르게 검색할 때 인덱스를 사용하는 것처럼 인덱스 클러스트에 대한 인덱스를 의미한다.
CREATE TABLE emp(no char(3), name varchar2(14),deptno number(1))
CLUSTER CLU1(deptno);
CREATE TABLE dept(deptno number(1), dname varchar2(13))
CLUSTER CLU1(deptno);
인덱스 클러스트는 [인덱스 클러스트] --> [클러스트 인덱스] --> [테이블1] --> [테이블2] 순서로 생성된다.
인덱스 클러스트를 변경하고 삭제하는 명령어
ALTER CLUSTER [클러스트명] SIZE n; --> 클러스트 사이즈 조정
DROP TABLE [테이블명1] CASCADE CONSTRAINTS; --> 클러스트를 삭제하기전 관련테이블을 먼저 삭제한후 클러스트를 삭제한다.
DROP TABLE [테이블명2] CASCADE CONSTRAINTS;
DROP CLUSTER [클러스트명] CASCADE CONSTRAINTS;
DROP CLUSTER [클러스트명] INCLUDING TABLES CASCADE CONSTRAINTS; --> INCLUDING TABLES 절을 사용하여 클러스트와 테이블을 동시 삭제하는 방법
EX) 예제
SQL> create cluster clu_emp_dept
2 (dept number(3))
3 size 1000;
클러스터가 생성되었습니다.
SQL> create index i_clu_emp_edpt
2 on cluster clu_emp_dept;
인덱스가 생성되었습니다.
SQL> create table dept
2 (deptno number(3),
3 dname varchar2(14))
4 cluster clu_emp_dept(deptno);
테이블이 생성되었습니다. --> 각각의 테이블에 데이터를 입력한다.
SQL> create table emp
2 (empno number(4),
3 ename varchar2(10),
4 deptno number(3))
5 cluster clu_emp_dept(deptno);
테이블이 생성되었습니다.
SQL> select rowid, empno, ename, deptno from emp;
ROWID EMPNO ENAME DEPTNO
------------------ ---------- ---------- ----------
AAAHckAAJAAAABGAAA 111 일지매 10 -->
AAAHckAAJAAAABGAAB 112 황진희 20
AAAHckAAJAAAABGAAC 113 홍길동 30 EMP 테이블과 DEPT 테이블에서 데이터를 저장하기 위해 저장한 블럭이 AAAABG 로 같다는 것을 확인 할수 있다. 클러스터를 이용하기 전에는 위에서 처럼 블록이 서로 달랐다.
SQL> select rowid, deptno, dname from dept;
ROWID DEPTNO DNAME
------------------ ---------- --------------
AAAHckAAJAAAABGAAA 10 전산과
AAAHckAAJAAAABGAAB 20 인사과
AAAHckAAJAAAABGAAC 30 총무과
일반적인 경우에는 서로 다른 블록에 데이터가 저장되어 있지만 인덱스 크러스트는 조인도리 때 조인되는 컬럼 값을 기준으로 같은 블록 내에 행이 저장된다. 그리고 SELECT문이 실행되면 하나의 블록에 있는 데이터를 읽어서 별도의 분류작업 없이 사용자에게 결과를 보여주게 된다. 즉 2개 이상의 테이블이 조인된 형태로 블록에 저장되는 기법이다.
SQL> select emp.empno, emp.ename, dept.dname
2 from emp, dept
3 where emp.deptno = dept.deptno;
EMPNO ENAME DNAME
---------- ---------- --------------
111 일지매 전산과
112 황진희 인사과
113 홍길동 총무과
사용된 예제는 실제로 데이터를 얼마 가지고 있지 않기때문에 검색상의 속도 차이는 느낄수 없지만 행수가 많다면 인덱스 크러스트를 이용하는 방법 이 빠를 것이다.
해쉬 클러스트
하나의 대용량 테이블에서 오직 하나의 행을 검색할 때 해쉬 알고리즘을 이용해서 검색하기 때문에 매우 빠른 성능을 보장하는 효과적인 기법이다. 인덱스 클러스트가 2개 이상의 테이블로 만들어진다면 헤쉬 클러스트는 하나의 테이블로 생성한다.
해쉬 클러스트 생성 기본문법
CREATE CLUSTER clu2
(empno number(4)) --> 인덱스 크러스트와 같이 empno 컬럼은 해쉬함수에 의한 결고 값이며 클러스트 키를 의미한다.
HASHKEYS 40 --> 해쉬 클러스트가 저장하게 될 전체 행수를 의미한다.
HASH IS empno --> 해쉬 함수를 정의해 주는 부분 해쉬함수는 사용자가 직접 저의할수도 있고 오라클 서버가 제공하는 해쉬알고리즘을 사용할 수도 있으면 테이블 내의 특정 커럼을 정의할 수도 있다.
SIZE 40; --> 하나의 블록 내에 저장되는 하나의 행 길이를 의미한다.
CREATE TABLE emp
(empno number(4),
ename varchar2(10),
sal number,
comm number)
CLUSTER clu2(empno);
클러스트 삭제 방법
DROP TABLE [테이블명1] CASCADE CONSTRAINTS; --> 클러스트를 삭제하기전 관련테이블을 먼저 삭제한후 클러스트를 삭제한다.
DROP TABLE [테이블명2] CASCADE CONSTRAINTS;
DROP CLUSTER [클러스트명] CASCADE CONSTRAINTS;
DROP CLUSTER [클러스트명] INCLUDING TABLES CASCADE CONSTRAINTS; --> INCLUDING TABLES 절을 사용하여 클러스트와 테이블을 동시삭제하는 방법
ex) 예제
SQL> create cluster clu_emp
2 (job varchar2(10))
3 size 500
4 hashkeys 40;
클러스터가 생성되었습니다.
SQL> create table emp2
2 (empno number,
3 ename varchar2(10),
4 job varchar2(10))
5 cluster clu_emp(job);
테이블이 생성되었습니다. --> 테이블 생성후 데이터 입력
SQL> select * from emp2;
EMPNO ENAME JOB
---------- ---------- ----------
11 일지매 DB관리자
12 황진희 선생님
SQL> select * from emp2 where job = '선생님';
EMPNO ENAME JOB
---------- ---------- ----------
12 황진희 선생님
튜닝을 위한 표준화 작업
튜닝을 통해 좋은 성능을 기대하기 위새서는 SQL문을 처리하는 과정을 보다 잘 이해해야 한다.(오라클 데이터베이스 구조 에서SELECT 문의 처리과정 참조)
SELECT 문의 처리과정을 통해 사용자가 실행한 SQL문이 데에터베이스에 처음으로 사용된 문장이닞 아닌지를 SGA 영역의 공유 풀 영역을 검색하여 확인하고 이미 사용된 문장이라면 구분분석 단계를 처리할 필요가 없고 처음 사용되었다면 정상적으로 구문분석 단계
를 처리하게 된다. 이러한 방법으로 SQL문을 처리하는 이유는 되도록 구문분석단계를 처리하지 않고 실행단계를 바로 처리함으로써 구문분석의 불필요한 시간과 메모리 공간을 줄여 보다 빠르게 SQL문을 처리하기 위해서 이다.
그렇다면, 데이터베이스에서 실해되는 SQL문들이 어떻게 구문분석을 피해서 실행되 수 있도록 할 수 있을까?
개발자들이 최초 프로그램을 개발할 때 일정한 규칙 또는 약속에 의해 동일한 SQL문이 만들어 질수 있도록 표준화를 한다면 구무분석 단계를 피할 수 있어 보다 빠른 실행속도를 보장받을 수 있다. 또한, 향후 프로그램의 유지보수도 쉽게 할 수 있는 장점을 가지고 있다. 먼저 동일한 SQL문이란 어떤 것일까? 다음의 경우를 만족하는 SQL문을 동일한 SQL문이라고 한다.
1.대문자,소문자가 동일해야 한다.
2.띄어쓰기 규칙이 동일해야 한다.
3.공백(SPACE)의 개수가 동일해야 한다.
4.SQL문에 사용된 변수의 데이터 타입과 변수 명이 동일해야 한다.
2개의 문장이 동일하기 위해서는 한마디로 PERFECT한 조건을 가져야하는데 첫 번째 실행된 SQL문이 구분분석 단계를 처리했다면두 번찌 실행한 SQL문은 그 구문분석 정보를 참조만 하기 때문에 바로 실행단계를 처리할 수 있어 빠른 성능을 기대할 수 있다.
SQL문 표준화 하기
다음 예제는 시스템 개발초기에 프로젝트 관리자 또는 데이터베이스 분석/설계자에 의해 동일한 SQL문을 만들어 내기 위한 표준화
작업의 일부이다. 이러한 표준화 작업은 사용자의 개발 환경에 맞게 적절하게 변형해야 하며 새로운 내용의 추가 및 삭제가 추가적
으로 필요한다.
1.SQL문은 대문자 또는 소문자로 통일한다.
2.변수명은 SQL문, 객체명, 커럼명과 구분하기 위해 소문자로 작성한다.
예) SELECT * FROM emp; UPDATE emp SET sal * 1.1 WHERE deptno = 10;
예) SELECT resno, hname INTO :ls_resno, :ls_hname
FROM emptbl
WHERE hname LIKE '홍길동';
3.다른 스키마의 테이블을 호출할 때는 Schema명.table명 으로 작성한다. 만약 시노님을 작성하여 사용할 때에는 시노님 명을 결정하는 네이밍 룰(NAMING RULE)을 잘 작성하여 이름만 봐도 이해할 수 있도록 한다.
예)SELECT resno, hname
FROM scott.emp
WHERE resno = 1234;
4.SQL문의 각 단어의 여백은 한 칸으로 한다. 콤마는 앞 문자와는 간격을 두지 말고 뒷 문자와는 간격을 1칸으로 작성한다.
또한 커럼 리스트는 되도록 모두 한줄에 기술한다.(2줄 이상일 경우는 첫줄, 첫 컬럼에 맞추어 작성한다.)
예) SELECT resno, hname INTO :ls_resno, :ls_hname
FROM emptbl
WHERE hname LIKE '홍길동';
5.SQL문 내의 변수 명은 변수선언 기준안에 따르며 해당 커럼명을 접두어와 결합하여 사용한다.
예) string gi_resno //사원번호(G는 글로벌 변수, L은 로컬변수 I는 정수타입 S는 문자타입
6.SQL문의 SELECT , FROM, WHERE 절은 라인의 선두에 기술한다.
예) SELECT resno, hname INTO :ls_resno, :ls_hname
FROM emptbl
WHERE hname LIKE '홍길동';
인덱스를 사용하지 못하는 경우
테이블에 인덱스를 생성하는 이유는 보다 빠른 검색속도를 보장받기 위해서 이다. 하지만, 인덱스를 생성하고 SQL문을 실행했다 하더라도 인덱스를 사용하지 못하고 테이블 전체 스캔을 통해 데이터를 검색할 수도 잇다. 왜냐하면, 인덱스를 사용하지 못하는 예외의 경우가 있기 때문이다. 개발자들은 자신이 작성하는 SQL문이 인덱스를 사용하지 못하는 경우를 피해야 만 좋은 성능을 기대할 수 있다는 것을 염두에 두어야 한다.
1.!=(부정 연산자)를 사용하면 인덱스를 사용할 수 없다. 왜냐하면, 인덱스는 검색하고자 하는 컬럼의 데이터가 전체 데이터의 약10% 범위에 있을 때 가장 빠르게 검색할 수 있는데 부정(!=, <>)의 의미는 4~10%범위를 벗어난 범위를 의미하기 때문에 인덱스가 있다 하더라도 오라클 서버는 전체 테이블 스캔을 검색하며 인덱스를 사용하지 않는다.
SQL>SELECT * FROM emp WHERE id != 2434;
2.IS NULL을 사용하면 인덱스를 사용할 수 없다. 왜냐하면, 인덱스를 생성하면 인덱스에는 NULL값은 포함도지 않기 때문에 IS NULL의 의미는 인덱스가 없음을 의미하고 전체 테이블스캔으로 데이터를 검색한다.
SQL>SELECT * FROM emp WHERE id IS NULL;
3.NOT IN을 사용하면 인덱스를 사용할 수 없다. 이유는 != 연산자와 동일하다.
SQL>SELECT * FROM emp WHERE id NOT IN(1244,1342);
4.와일드카드로 시작하는 LIKE문은 인덱스를 사용할 수 없다. 왜냐하면 검색해야할 범위를 알 수 없기 때문에 오라클 서버는 전체 테이블스캔이 더 빠른 검색을 해줄 수 있다고 판단하여 인덱스를 사용하지 않는다. 단 와일드 카드로(%) 끝나는 검색조건에는 인덱스가 사용된다.
(예 SQL>SELECT ~ WHERE name LIKE'홍%'; )
SQL>SELECT * FROM emp WHERE name LIKE'%길동';
5.인덱스가 있는 컬럼을 표현식 또는 함수로 변형을 시키면 인덱스를 사용할 수 없다.
(Name 컬럼에 index가 있다고 가정하면) 인덱스 데이터를 다시 가공하여 검색하면 인덱스 덱스 데이터는 사용할 수 없다.
SQL>SELECT * FROM emp WHERE sunstr(name,1,2)='홍';
6.인덱스가 있는 컬럼의 데이터 타입을 그대로 사용하지 않으면 인덱스를 사용할 수 없다.
(sal 컬럼은 number 타입인데 문자로 검색할 경우)
SQL>SELECT * FROM emp WHERE sal='10000000';
7.같은 테이블에 있는 다른 컬럼과 비교하면 인덱스를 사용할 수 없다.
SQL>SELECT * FROM emp WHERE sal > commission;
옵티마이저
옵티마이저란 사용자가 실행한 SQL문을 분석하여 가장 빠르게 실행될 수 있는 실행계획을 결정해 주는 오라클 프로세스를 말한다.
오라클 데이터베이스에는 공식기반 옵티마이저(RULE-BASED-OPTIMIZER)와 비용기반 옵티마이저(COSTED-BASED-OPTIMIZER)가 있는데 오라클 데이터베이스를 설치하면 기본환경은 COSTED-BASED-OPTIMIZER 이다.
RULE-BASED-OPTIMIZER
RBO는 미리 정해져 있는 공식에 의해 가장 빠른 실행 방법이 결정된다. 즉 대부분의 사람이 상식적으로 생각하는 소요시간에 의해 가장 빠르게 실행되는 방법이 결정된다.
오라클 데이터베이스에서 제공하는 공식기반 옵티마이저의 실행 우선순위
1.ROWID에 의한 단일행 실행
2.Cluster-join에 의한 단일행 실행
3.Cluster-key, Primary-key를 사용한 Hash-Cluster key에 의한 단일행 실행
4.Unique-key, Primary-key 에 의한 단일행 실행
5.Cluster 조인
6.Hash-Cluster Key
7.인덱스화된 Cluster-key
8.복합인덱스
9.단일 컬럼 인덱스
10.인덱스가 구축된 컬럼에 대한 제한된 범위 검색
11.인덱스가 구축도니 커럼에 대한 무제한 범위 검색
12.정렬-병합 조인
13.인덱스가 구축된 칼럼에 대한 max, min
14.인덱스가 구축된 컬럼에 대한 order by
15.Full -Table Scan
ex) emp라는 사원 테이블이 있고 2000건의 행이 입력되어 있다고 가정한다. 개발자는 사원번호 7369의 행 정보를 검색하는 sql문을 다음과 같이 3가지 방법으로 실행 할 수 있다.
1.SELECT * FROM emp WHERE empno = 7369;
2.CREATE index i_emp_empno ON emp(empno);
SELECT * FROM emp WHERE empno = 7369;
3.SELECT * FROM emp WHERE rowid = 'AAAAfBAACAAAAEqAAA';
첫번째 방법은 15단계에 의한 공식에서 15번째 인 테이블 전체 스켄방법으로 테이블의 처음부터 마지막까지를 일일이 읽어서 데이터를 검색하는 방법이다.
두번째는 9번째 단일 컬럼 인덱스에 의한 단일 컬럼 검색방법으로 검색하는 방법이다.
세번째는 첫번째 로우아이딩에 의한 단일 컬럼 검색방법으로 검색하는 방법이다.
세가지 방법은 각각 같은 결과를 출력해주지만 실행속도를 비교해 보면 3 --> 2 --> 1 순으로 빠른 실행속도를 보여줄 것이다. 결론적 으로 사용자는 자신이 실행하는 SQL문이 15단계의 공식에서 몇 번째 단계로 실행되느냐에 따라 실행속도가 죄우될 수 있다.
OPTIMIZER_MODE = RULE --> 공식 기반 옵티마이저로 데이터베이스 환경 설정하는 파라미터(INIT.ORA 파일에 정의)
ALTER SESSION SET OPTIMIZER_GOAL = RULE --> 특정세션에만 공식기반 옵티마이저를 생성하는 방법
비용기반 옵티마이저(COST-BASED-POTIMIZER)
CBO는 RBO와는 달리 실제로 가장 빠르게 실행되는 방법의 원가(COST)를 계산하여 선택하는 방법이다. 어떤 테이블에 인덱스를 생성하고 SELECT 문을 실행하면 대부분의 사용자들은 자신이 만든 인덱스에 의해 SQL문이 싱행될 것이라고 생각한다. 하지만 오라클 비용기반 옵티마이저는 만약, 전체 테이블 스캔방법이 인덱스 스캔 방법보다 더 빠르게 실행될 수 있다면 인덱스를 사용하지 않는다.
기본적으로 오라클 9i 데이터베이스는 CBO를 제공하지만 다음과 같은 환경설정과 기타정보가 존재할 때에 만 사용가능하며 만족하지 못한다면 RBO가 적용될 수도 있다. (CBO는 비용산출을 위한 분석정보가 존재해야만 정확한 비용을 계산할 수 있다. 분석정보가 없다면 정확한 비용을 산출할 수 없기 때문에 RBO와 동일한 개념이 될 수 있다는 의미이다.)
OPTIMIZER_MODE = [CHOOSE | FIRST_ROWS] --> INIT.ORA 에 정의되어 있는 설정 파라미터
ALTER SESSION SET OPTIMIZER_GOAL = [CHOOSE | FIRST_ROWS] --> 특정세션에만 공식기반 옵티마이저를 설정하는 방법
ANALYZE TABLE [table 명] [COMPUTE STATISTICS]; [ESTIMATE STSTISTICS n [percent | rows]];
[DELETE STATISTICS];
통계정보를 만들어 주는 명령어 이 명령어에 의해 DBA_TABLES 또는 DBA_INDEXES 자료사전에 통계정보가 저장된다.
[COMPUTE STATISTICS] 절은 테이블에 저장되어 있는 모든 행을 대상으로 통계정보를 수집
[ESTIMATE STSTISTICS n [percent | rows]] 절은 테이블에 저장되어 있는 n-PERCENT의 데이터로 통계정보를 수집n-ROWS데이터로 통계정보 수집
[DELETE STATISTICS]절은 수집되어 있는 통계정보를 삭제한다.
ex)
SQL> analyze table emp compute statistics;
테이블이 분석되었습니다.
SQL> analyze index pk_emp estimate statistics;
인덱스가 분석되었습니다.
SQL> analyze table emp delete statistics;
테이블이 분석되었습니다.
SQL> analyze index pk_emp delete statistics;
인덱스가 분석되었습니다.
애플리케이션 튜닝도구
Explain Plan 명령어
Explain Plan명령어는 SQL문이 처리되는 과정의 구문분석 단계에서 서버 프로세스에 의해 해당 SQL문이 어떻게 실행되는 것이 가장 빠르게 실행될 수 있는지를 결정하게 되는데 이 결과를 실행계획(EXPLAIN PLAN)이라고 하며 이결과를 확인할때 사용하는 튜닝도구가 Explain Plan명령어 이다.
Explain Plan 명령어에서 참조할수 있는 내용들
1.데이터베이스에서 sql문이 실행될때의 상태정보를 제공해 준다.
2.sql문이 테이블 전체 스캔방법으로 검색하는지 익덱스 스캔방법으로 검색하는지에 대한 실행 경로 정보를 제공해 준다.
3.테이블에 인덱스를 추가하거나 삭제한 경우 어떻게 실행계획인 달라지는지 보여준다.
4.sql명령문의 where절과 from절의 미묘한 변화에 대한 정보를 제공한다.
Explain plan 명령어를 실행하는 단계
1.SQL>START d:\oracle\ora92\rdbms\admin\utlxplan.sql --> 먼저, EXPLAIN_PLAN명려어를 사용하기 위해서는 SQL문에 대한 분석
SQL>START d:\roacle\ora92\sqlplus\admin\plustrce.sql 결과를 저장할 PLAN_TLABE테이블을 생성해야 한다. 오라클 데이터 베이스를 설치하면 기본적으로 설치되지 않으므로 사용자 별로 생성해야 하며 데이타베이스 사용자별로 생성할수 있다. 또한 사용자는 이기능을 사용하기 위한 권한을 가져야 한다. PLUSTRACE.SQL스크립트를 실행한다.
2.SQL>EXPLAIN PLAN SET STATEMENT_ID = [id값] -->
INTO PLAN_TABLE FOR[분석대상 SQL문];
PLAN_TALBE테이블이 생성된후 EXPLAIN PLAN명령어를 실행하면 분석결과가 테이블에 저장된다. SET STATEMENT_ID 절은 여려명의 사용자가 동시에 분석작업을 하게될 때 분석된 결과를 서로 구분하기 위한 식별자를 의미하며 30문자까지의 스트링이 정의될 수 있다. 그리고 INTO PLAN_TABLE절은 분설결과를 해당 테이블에 저장한다.
3.SQL>SELECT LPAD('',2*LEVEL)||operation|| -->
DECODE(id,0,'Cost='||position)"OPERATION"
FROM PLAN_TABLE
WHERE[조건절];
또는
SQL>SET AUTOTRACE[ON|OFF|TRACEONLY]
실행계획에 대한 분석 작업이 끝나면 다음과 같은 SQL문에 의해 PLAN_TABLE을 참조하게 된다. 별도의 스크립트를 작성하는 이유는 분석결과를 쉽게 보기 위해서 이다.
PLAN_TABLE 테이블의 OPERATION 컬럼에 나타날 수 잇는 내용에 대한 설명
OPERTTION 설 명
FILTER SQL문의 WHERE절에 정의된 조건으로 테이블의 행을 검색해 준다.
INDEX/RANGE 인덱스를 이용하여 테이블에 있는 데이터를 검색한다.
INDEX/UNIQUE unique-index 또는 primary-key에 의한 인덱스의 테이블에 있는 데이터를 검색한다.
SORT/MERGE 두개의 테이블을 조인하여 분류한다.
SORT/GROUP BY SQL문장의 GROUP BY절에 정의된 컬럼으로 데이터를 분류한다.
SORT/JOIN 조인된 각 테이블의 데이터를 분류한다.
SORT/ORDER SQL문장의 ORDER BY절에 정의된 컬럼으로 데이터를 분류한다.
TABLE ACCESS/FULL 테이블 전체 스갠 방법으로 검색한다.
************************************************************************************
(PLUSTRACE 롤이 기본 생성상태인 데이터베이스에는 존재하지 않는것 같다. SYS 사용자로 접속한후 스크립트를 생성한후 생성되고
권한도 생겼다.
SQL> connect sys/as sysdba
암호 입력:
연결되었습니다.
SQL> start d:\oracle\ora92\sqlplus\admin\plustrce.sql --> 스크립트 실행
SQL>
SQL> drop role plustrace;
drop role plustrace
*
1행에 오류:
ORA-01919: 롤 'PLUSTRACE'(이)가 존재하지 않습니다
SQL> create role plustrace;
롤이 생성되었습니다.
SQL>
SQL> grant select on v_$sesstat to plustrace;
권한이 부여되었습니다.
SQL> grant select on v_$statname to plustrace;
권한이 부여되었습니다.
SQL> grant select on v_$session to plustrace;
권한이 부여되었습니다.
SQL> grant plustrace to dba with admin option;
권한이 부여되었습니다.
SQL> connect scott/test1# --> 일반 사용자는 PLUSTRACE 롤이 SYS 관리자에 의해 생성되었더라도 권한을 부여 받지 않아서 권한이 없다.
연결되었습니다.
SQL> set autotrace on --> PLUSTRACE 롤 권한 부족을 SET AUTOTRACE ON 명령어를 사용할 수 없다. 아래에 설명된다.
SP2-0618: 세션 식별자를 찾을 수 없습니다. PLUSTRACE 롤이 사용 가능한지 점검하십
시오
SP2-0611: STATISTICS 레포트를 사용 가능시 오류가 생겼습니다
SQL> grant plustrace to scott; --> PLUSTRACE 롤 권한을 일반 사용자에게 부여한다.
권한이 부여되었습니다.)************************************************************************************
EX)
SQL> start d:\oracle\ora92\rdbms\admin\utlxplan.sql --> utlxplan.sql스크립트를 실행해서 plan_table 테이블을 생성
테이블이 생성되었습니다.
SQL> explain plan set statement_id = 'JSM' --> explain plan 명령어를 상요하여 sql문의 실행 계획을 분석한다.
2 for select * from emp where empno = 7934;
해석되었습니다.
SQL> l --> plan_table을 참조하기 위해 미리 작성된 sql문을 실행하여 실행계획을 참조한다.
1 select lpad(' ',2*level)||operation||
2 decode(id,0,'cost= '||position) "operation",
3 options "options", object_name "objects"
4 from plan_table
5 where statement_id = 'JSM'
6 connect by prior id = parent_id
7* start with id = 0
SQL> /
operation options objects
------------------------------ --------------- ----------
SELECT STATEMENTcost=
TABLE ACCESS BY INDEX ROWID EMP
INDEX RANGE SCAN PK_EMP
이 sql문은 emp 테이블(objects 컬럼정보)에서 인덱스 스켄방법(options 컬럼)으로 select문이 실행되었음을(operation 컬럼) 알 수 있다.
*************2개 이상의 테이블이 조인되는 sql문의 실행 계획분석 예제******************
2개이상의 테이블에서 조인에 참여되는 컬럼에는 인덱스가 생성되어 있지 않다. 이런 경우에는 각각 테이블의 데이터를 읽은후 그 결과를 다시 결합하여 사용자에게 보여준다.
SQL> truncate table plan_table; --> 앞 예제에서 분석한 sql문의 결과가 저장되어 있으므로 plan_table 내용을 삭제해준다.
테이블이 잘렸습니다.
SQL> explain plan set statement_id = 'JSM'
2 for
3 select e.empno, e.ename, d.dname
4 from emp e, dept d
5 where e.deptno = d.deptno;
해석되었습니다.
SQL> select lpad(' ',2*level)||operation||
2 decode(id, 0, 'cost= '||position) "OPERATION",
3 options "OPTIONS", object_name "OBJECTS"
4 from plan_table
5 where statement_id = 'JSM'
6 connect by prior id = parent_id
7 start with id = 0;
OPERATION OPTIONS OBJECTS
------------------------------ --------------- ----------
SELECT STATEMENTcost=
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL DEPT
SORT JOIN
TABLE ACCESS FULL EMP
6 개의 행이 선택되었습니다.
결과를 보는 방법은 먼저 emp테이블을 테이블 전체 스캔 방법으로 읽어서 분류하고 다시 dept테이블을 테이블 전체 스캔방법으로 읽어 분류한다음 2개의 결과를 merge join 하고 있다.
SET AUTOTRACE ON 명령어
EXPLAIN PLAN 명령어는 PLAN_TABLE 테이블을 생성하고 EXPLAIN PLAN명령어에 의해 실행계획을 분석한 다음 SELECT문에 의해 결과를 참조하게 된다. SET AUTOTRACE ON 명령어는 PLAN_TABLE을 생성한 후 한번 만 설정해주면 SQL문이 실행될 때마다 실행계획을 화면에 출력해 준다. 사용자는 이기능을 사용하기 위해 PLUSTRACE라는 권한을 가져야만 한다. 물론 PLAN_TABLE 테이블도 생성되어 있어야 한다.
SQL>SET AUTOTRACE [ON | OFF | TRACEONLY] --> 기본 문법
[ON]은 SQL문의 실행결과와 실행계획 그리고 통계정보를 보여주는 옵션이다.
[OFF]는 어떤 결과도 보여주지 않는다.
[TRACEONLY]는 실행계획과 통계정보만을 보여준다.
SQL> set autotrace on --> PLUSTRACE롤 권한이 부여되었기 때문에 명령어 사용이 가능하다.
SQL> select * from dept;
DEPTNO DNAME LOC --> 질의 결과
---------- -------------- -------------
12 RESEARCH SARATOGA
10 ACCOUNTING CLEVELAND
11 ART SALEM
13 FINANCE BOSTON
21 SALES PHILA.
22 SALES ROCHESTER
42 INT'L SAN FRAN
7 개의 행이 선택되었습니다.
Execution Plan --> 실행 계획
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'
Statistics --> SQL문이 서버와 클라이언트에서 실행될때 상요한 시스템 의 자원 현상 SQL문이 얼마나 많은 블록을 통해 검색되 었는지 사용한 메모리의 크기가 얼마인지 등과 같은 정보를 제공해 준다. 이러한 결과를 통해 시스템 자원계획을 수립할 수 있다.
----------------------------------------------------------
29 recursive calls
0 db block gets
7 consistent gets
1 physical reads
0 redo size
662 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed
SQL> set autotrace off --> 기능을 끈다.
SQL*TRACE 와 TKPROF
EXPLAIN PLAN 명령어와 함께 애플리케이션 튜닝작업 시 자주 사용되는 기능 중에 SQL*TRACE라는 기능이 있다. 데이터베이스 사용자가실행한 SQL문에 대한 실행계획 만을 보여주는 EXPLAIN PLAN과는 달리 SQL*TRACE 기능은 SQL문의 실행계획과 더불어 실행 소요시간과 디스크 및 메모리로부터 읽은 블록수에 대한 정보도 포함하고 있다. SQL*TRACE기능에 의해 분석되는 결과는 운영체제 상의 디렉토리 구조에 생성된다. 하지만, 이 파일은 바이너리 형태이기 때문에 사용자가 직접 눈으로 확인할 수가 없다. 그래서, TKPROF 유틸리티를 사용하여 바이너리 형태에서 텍스트 파일형태로 변화하여 참조하게 된다.
명령어를 실행하는 단계
1. MAX_DUMP_FILE_SIZE = [크기]
TIMED_STATISTICS = TRUE
USER_DUMP_DEST = [디렉토리경로]
2.SQL_TRACE = TRUE
SQL> ALTER SESSION SET SQL_TRACE = TRUE;
3.분석하려는 SQL문을 실행한다.
4.D:\>TKPROF [분석된바이너리파일][변환된파일][옵션,,,,]
1.SQL*TRACE기능을 사용하기 위해서는 먼저, INIT.ORA파일에 파라미터를 추가로 설정해야 한다. MAX_DUMP_FILE_SIZE 은 SQL*TRACE명령어를 사용하면 분석결과가 운영체제 상의 바이너리 파일이 생성되는데 이파일에 분석결과가 저장될 때 최대 크기를 의미한다.
TIMED_STATISTICS 는 SQL문의 분석시 실행시간에 관련된 통계정보를 수집해준다.
USER_DUMP_DEST 는 SQL_TRACE 파라미터에 의해 생성되는 분석결과가 저장되는 디렉토리 경로를 의미한다.
2.SQL_TRACE는 데이터베이스슷 사용하는 모든 사용자가 실행한 SQL문에 대한 실행 결과를 분석해준다. INIT.ORA파일에 이 파라미터를 설정하면 된다. 이 방법 이외에 세션레벨에서 설정하는 방법도 있는데 세션레벨에서 설정되면 그 세션에서 실행된 SQL문에 한해서만 분석해 준다. 오라클 사에서는 되도록 INIT.ORA파일에 정의하는 방법을 권장하지 않는다. 그이유는 모든 사용자의 SQL문을 분석하려면 데이터베이스가 너무 많은 작업을 해야 하기 때문에 성능이 저하된느 현상이 발생하기 때문이다.
3.분석해야 할 SQL문을 실행한다. 그리고, 세션을 종료한수 1의 USER_DUMP_DEST파라미터에 설정된 운영체제 상의 데릭토리 경로로 이동한다. 이 경로에 분석결과가 바이너리 형태로 생성된다.
4.분석된 결과(트레이스 파일)는 바이너리 형태의 파일이기 때문에 직접 편집해서 확인해 볼 수가 없다. 그래서 오라클 데이터베이스에서 제공하는 TKPROF 유틸리티를 사용하여 사용자가 확인 할 수 있는 형태의 포맷으로 바꾸어야 한다. 분석된 CPU사용시가, 디스크 사용빈도, 검색된 행의 수 등을 확인해 볼수 있다. 또한 EXPLAIN PLAN을 통해 확인했던 실행계획도
참조해 볼수 있다.
TKPROF 유틸리티에서 사용할 수 있는 옵션들
OPTION 설명
EXPLAIN=[table명] 실행계획 정보를 함께 보여준다.(해당 사용자에게는 plan_table이 생성되어 있어야 한다.)
PRINT = n 트레이스파일내에 분석된 sql문의 수를 n만큼 만 제한하여 출력할때 사용한다.
RECORD = [파일명] 트래이스 파일내에 분석된 SQL문을 지정한 파일에 저장한다.
SORT=[옵션] 트래이스 파일내에 분선된 SQL문장을 지정한 옵션에의해 분류하여 출력한다.
SYS=NO 트래이스 파일내에 생성된 SQL문장 중에 오라클 서버가 내부적인 작업을 위해 실행한 SQL문을 출력시 포함할것인지를 결정할때 사용한다.
ex)
다음과 같이 init.ora파일에 sql*trace와 관련되 파라미터를 설정한후 데이터베이스를 다시 시작한다.
max_dump_file_size
timed_statistics=TRUE
user_dump_dest=D:\oracle\admin\ORA92\udump
SQL> connect scott/test1#
연결되었습니다.
SQL> alter session set sql_trace = true; --> 데이터베이스 재 시작후 세션 레벨에서 SQL*TRACE기능을 활성화 시킨다. 모든 사용자의 SQL문에 대해 분석해주는 데이터베이스 전체 모드보다는 세션레벨에서 세션이 변경되었습니다. 필요한 사용자의 필요한 SQL문만 적용하는것이 전체 성능에 도움을 준다.
SQL> select emp.empno, emp.ename, dept.deptno
2 from emp, dept
3 where emp.deptno = dept.deptno;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7782 CLARK 10
7839 KING 10
7934 MILLER 10
SQL> EXIT --> 분석할 SQL문 실행후 세션을 종료한다.
세션이 종료되는 순간 USER_DUMP_DEST파라미터가 지정하는 디렉토리에 *.TRC확장자를 가진 트레이스 파일이 만들어 질 것이다.
D:\oracle\admin\ORA92\udump>TKPROF ora92_ora_3528.trc ora.tkf explain=scott/test
1# sys=no
TKPROF: Release 9.2.0.1.0 - Production on 화 Jun 10 03:35:12 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. --> 실행후 파일에 만들어 졌다.
*************************파일 내용 분석하는 방법*************************************************************************
TKPROF: Release 9.2.0.1.0 - Production on ȭ Jun 10 03:35:12 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: ora92_ora_3528.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set sql_trace = true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.01 0 0 0 0
Misses in library cache during parse: 0 <0 은 데이터베이스에서 처음실행됬다는 의미>
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 59 (SCOTT) < SCOTT 사용자에 의해 실행되었다는 의미>
********************************************************************************
select emp.empno, emp.ename, dept.deptno < 사용자가 실행한 SQL문 >
from emp, dept
where emp.deptno = dept.deptno
< CALL 컬럼은 해당 SQL문이 실행되면서 구문분석(PARSE), 실행(EXECUTE), 인출(FETCH)단계가 어떻게 처리되었는지를 설명한다.>
< COUNT SQL문이 처리될 때 분석, 실행, 인출을 몇 번씩 실행했는지를 보여 준다. >
< CPU SQL문의 처리 단계별로 CPU를 몇초 사용했는지를 보여준다. >
< ELAPSED SQL문의 처리 단계별로 처리된 소요시간 을 보여준다. >
< DISK 사용자가 실행한 SQL문에 의해 디스크로부터 해당 테이블을 읽은 블록 수를 의미한다. >
< QUERY DISK에서 읽은 테이블 데이터를 데이터버퍼 캐시영역에 저장한 다음 서버 프로세스가 사용자에게 리턴할 때 읽은 데이터
캐시영역의 블록수를 의미한다. 다른 사용자가 같은 데이터를 사용하고 있다면 버퍼캐쉬의 해당 브록에서 데이터를 가져
온다. >
< CURRENT 만약 DML문이 실행되었다면 변경전 데이터를 읽은 블록수를 의미한다. >
< 이결과에서 사용자가 주의해야 할 내용은 SQL문이 실행될때 사용된 CPU시간과 (QUERY + CURRENT)블록수를 줄여나가야만 보다
빠른 검색속도를 보장받을 수 있다는 것이다. 이에 대한 자세한 설명은 서버튜닝에서 알 수 있다. >
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.06 2 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 2 6 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.06 4 8 0 3
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
3 MERGE JOIN
7 SORT JOIN
7 TABLE ACCESS FULL OBJ#(30487)
3 SORT JOIN
3 TABLE ACCESS FULL OBJ#(30488)
Rows Execution Plan < SQL문의 실행계획 >
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
3 MERGE JOIN
7 SORT (JOIN)
7 TABLE ACCESS (FULL) OF 'DEPT' < 테이블 전체 스캔으로 검색 >
3 SORT (JOIN)
3 TABLE ACCESS (FULL) OF 'EMP'
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.06 2 2 0 0
Execute 2 0.00 0.01 0 0 0 0
Fetch 2 0.00 0.00 2 6 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.04 0.07 4 8 0 3
Misses in library cache during parse: 1
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 12 0.01 0.01 0 0 0 0
Execute 18 0.00 0.06 0 0 0 0
Fetch 33 0.00 0.04 2 54 0 23
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 63 0.01 0.12 2 54 0 23
Misses in library cache during parse: 10
2 user SQL statements in session.
12 internal SQL statements in session.
14 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: ora92_ora_3528.trc
Trace file compatibility: 9.00.01
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
12 internal SQL statements in trace file.
14 SQL statements in trace file.
12 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
SCOTT.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
161 lines in trace file.
********************************************************************
SSISO Community