홈 SQL_튜닝_01
포스트
취소

SQL_튜닝_01

게시 2023/09/01
By
9 분읽는 시간

튜닝을 공부하게 된 계기

Back-end 개발자로서는 SQL 개발은 필수이다 JAVA 진영에서는 그것을 대체하는 JPA 영역이 들어오긴 했지만 그래도 성능 면에서는 여전히 Native_SQL 을 따라갈 수 없다 그렇기에 이번 시간부터 개발자를 위한 튜닝 공부에 힘을 쏟기로 했습니다

옵티마이저

옵티마이저는 DBMS의 심장입니다 입력된 SQL 을 바탕으로 가장 적은 cost로 결과를 도출하게 하는 일종의 알고리즘 프로그램입니다 우리는 어떤 방식으로 옵티마이저가 최적의 Cost를 찾아내는지에 대해서 알아볼 것입니다

쿼리 처리 - SQL 최적화 과정

먼저 아래와 같은 쿼리가 있다고 해봅시다

1
2
3
4
5
6
7
8

SELECT 	emp.EMPNO 		
		,emp.ename
		,emp.job
		,dept.DNAME 
		,dept.LOC 
FROM EMP emp INNER JOIN DEPT dept ON emp.DEPTNO = dept.DEPTNO
ORDER BY ename

개발자는 이렇게 SQL 문을 작성하게 되는데 SQL 은 구조적 질의 언어(Structured Query Language)의 앞 글자를 따서 이름이 지어졌습니다 우리는 이렇게 구조적으로 프로그래밍을 했지만 이 언어가 옵티마이저를 통과해서 결과를 만들어내는 과정은 절차적일 수밖에 없습니다 우리가 해당 SQL 을 던져서 보내주면 옵티마이저 내부에서는 프로시저를 만들어서 결과를 생성하게 되는데 이를 SQL 최적화 과정이라고 합니다

SQL 처리 과정

  1. SQL 파싱 - SQL 문구를 분석해서 분해하는 파싱 트리 생성 문법적 오류가 있는지 체크 의미상 오류가 없는지 체크 이때 의미는 권한이 없는 테이블 호출 또는 없는 컬럼 호출

  2. SQL 최적화 - 옵티마이저는 미리 수집한 시스템 및 통계정보를 바탕으로 다양한 실행 경로를 비교 후 그중 제일 비용이 낮은 방식을 채택

  3. 로우 소스 생성 - 최적의 비용인 방식으로 채택된 실행 경로를 통해서 코드 또는 프로시저를 생성 이때 로우 소스 생성기가 그 역할을 맡게 됩니다

실행계획 맛보기

스크립트

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;
 
CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));
 
INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        sysdate,  800, NULL, 20);
         
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        sysdate, 1600,  300, 30);
         
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        sysdate, 1250,  500, 30);
         
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        sysdate,  2975, NULL, 20);
         
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        sysdate, 1250, 1400, 30);
         
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        sysdate,  2850, NULL, 30);
         
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        sysdate,  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        sysdate, 3000, NULL, 20);
         
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        sysdate, 5000, NULL, 10);
         
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        sysdate,  1500,    0, 30);
         
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        sysdate, 1100, NULL, 20);
         
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        sysdate,   950, NULL, 30);
         
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        sysdate,  3000, NULL, 20);
         
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        sysdate, 1300, NULL, 10);
 
CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );
 
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
 
CREATE TABLE BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM  NUMBER);
 
CREATE TABLE SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);
 
INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);
 
CREATE TABLE DUMMY
        (DUMMY NUMBER);
 
INSERT INTO DUMMY VALUES (0);
 
COMMIT;

해당 스크립트는 SCOTT 계정의 테스트 데이터입니다 이를 바탕으로 쓸 수도 있고 아니면 그때마다 만들 수 있습니다 이번에는 해당 스크립트를 사용하겠습니다

1
2
3
4
5
6
7
8
9

CREATE TABLE t AS 
	SELECT d.NO , e.* 
	FROM  emp e , (SELECT rownum NO FROM dual CONNECT BY LEVEL <= 1000) d ;

CREATE INDEX t_x01 ON t(deptno , no);
CREATE INDEX t_x02 ON t(deptno , job , no);

새로운 테이블 및 인덱스 생성

dbeaver 실행계획

1
2
3
4
5

SELECT * FROM t 
WHERE deptno = 10 
AND NO = 1

1

이렇게 쿼리 드래그 드롭한 뒤 우클릭 뒤 실행 -> 실행계획을 찾으면 된다 그리고 클릭하면 아마 자신의 컴퓨터 성능 및 오라클 버전과 성능에 대해서 모두 다른 결과가 나올 것입니다

1
2
3
4
5
6

Operation	Object	Optimizer	Cost	Cardinality	Bytes
SELECT STATEMENT		ALL_ROWS	2	3	126
TABLE ACCESS (BY INDEX ROWID BATCHED)	T	ANALYZED	2	3	126
INDEX (RANGE SCAN)	T_X01	ANALYZED	1	3	0

결과는 이렇게 나옵니다 우리는 위에서 인덱스 두 개를 만들었지만 옵티마이저 판단으로는 T_X01 인덱스는 Range Sacn 을 하는 게 가장 빠르다고 판단을 했고 그 결과 코스트가 2가 나오는 최적의 SQL 결과가 나오게 됩니다

힌트

힌트는 옵티마이저 판단보다는 개발자의 판단을 먼저 넣는 개념이라고 생각하면 됩니다 기본적으로 옵티마이저가 모든 SQL에 대해서 최적의 비용 실행계획을 찾을 수 없습니다 그렇기에 이때는 개발자가 직접적으로 힌트로 개입해서 옵티마이저가 갈려고 하는 방향을 잡아주는 길잡이 역할을 하는 것입니다 다음과 같이 T_X02 인덱스를 사용하게 해보겠습니다

1
2
3
4
5

SELECT /*+ index(t t_x02) */ *
FROM t 
WHERE deptno = 10 
AND NO = 1

힌트는 이와 같이 사용하고 사용법은 뒤에서 볼 것입니다 지금은 실행계획 보는 것이 중요하기에 그것을 먼저 보겠습니다

1
2
3
4
5

Operation	Object	Optimizer	Cost	Cardinality	Bytes
SELECT STATEMENT		ALL_ROWS	4	3	126
TABLE ACCESS (BY INDEX ROWID BATCHED)	T	ANALYZED	4	3	126
INDEX (SKIP SCAN)	T_X02	ANALYZED	3	3	0

결과는 비용 상승을 초래했습니다 T_X02 인덱스를 사용한 결과 SCAN 이 SKIP 이 되었고 이는 결국 비용이 증가하는 역할을 보였습니다 이처럼 힌트의 사용이 반드시 올바르게 옵티마이저를 인도하지는 않습니다 우리는 앞으로 어떻게 하면 쿼리의 비용을 줄이는지에 대해서 공부하게 될 것입니다

Back-end, SQL_Tuning
DataBase - Oracle SQL_Tuning
최근 업데이트
바로가기