Altibase SQL Tuning Guide for Developers

Presentation on theme: "Altibase SQL Tuning Guide for Developers"— Presentation transcript:

1 Altibase SQL Tuning Guide for Developers

2 1 Introduction of SQL Tuning

3 개요 SQL Tuning Definition 최소의 비용으로 사용자가 원하는 결과를 얻도록 SQL 을 변경 및 수정하는 것
비용 : I/O, CPU Usage, Elapsed Time … Index 구성 및 배치에 대한 설계 및 정책 수립 SQL Tuning 장점 성능 향상을 위해 드는 비용 및 투자 시간, 즉 COST 가 적게 소요 원리만 알면 누구나 수행할 수 있음. (Not only DBA but you can do it) 비용대비 효과가 가장 큼.

4 개요 SQL Tuning 한계 Modeling 단계에서 성능 및 튜닝의 대한 부분이 고려되지 않는다면 SQL Tuning 으로 성능을 향상시키는 부분은 한계가 있음. SQL 튜닝으로 매우 큰 데이터 집합이 리턴되거나, 매우 거대한 볼륨 전체를 핸들링 하는 업무의 성능향상에는 제한이 있음.

5 2 INDEX

6 개요 INDEX B-Tree(Balanced Tree) 자료구조를 가진 Altibase Object. (Sorted)
테이블과 동일하게 별도의 저장 공간을 사용함. Memory Table 에 사용되는 Index는 해당 데이터가 존재하는 Pointer 로만 구성됨. (Only 16 bytes per each row) Pointer가 정렬되어 있는 구조. Disk Table 에 사용되는 Index 는 데이터 값을 가지고 있으며, 해당 데이터 값은 해당 Row가 있는 위치 주소를 가지고 있음.

7 개요 Single Column Index 한 컬럼의 내용으로 정렬한 Index
Ordering 에 대한 고려사항만 필요함. (ASC / DESC) Composite Index 여러 개의 컬럼으로 구성된 Index 컬럼의 순서 및 각 컬럼의 Ordering 이 매우 중요함. 주로 사용되는 조건을 파악 후 생성 하여야 함.(Index가 무용지물이 되는 경우가 있음.)

8 개요 INDEX 의 한계 및 고려사항 Index를 사용해도 무조건 빠르다고는 볼 수 없음.
해당 인덱스 컬럼의 선택도(Selectivity)가 중요. 전체 데이터가 아닌 특정범위 데이터들에 대하여 찾을 때 성능 개선의 효과가 있으며, 해당 특정범위가 적으면 적을 수록 성능 개선의 효과가 커짐. 모든 질의 형태에 대해 Index를 사용할 수 없음. Index를 사용하기 위해서는 Index를 사용할 수 있는 형태로 SQL을 변경시켜주어야 함. Ex) 데이터형의 통일, 순서, 컬럼의 대한 변경 및 가공 금지등 Index 수가 증가할수록 DML Query의 수행시간이 증대됨. (INSERT/UPDATE/DELETE) => 최소한의 Index를 생성하여 최대의 성능을 구현하여야함.

9 개요 INDEX 의 한계 및 고려사항 한 테이블을 Access 할때 사용하는 Index는 오직 하나만 사용함. (어느쪽을 사용할지는 Altibase Optimizer가 결정함) SELECT Y.ENAME FROM EMPLOYEE X WHERE X.ENO = 8890 AND X.ENAME = ‘MSKIM’ ENO 컬럼으로 IDX_EMP1 , ENAME 컬럼으로 IDX_EMP2라는 인덱스가 있을때 EMPLOYEE 테이블을 Access 하는데 사용할 수 있는 INDEX는 IDX_EMP1, IDX_EMP2 중에 한 개만 사용할 수 있음.

10 SELECT * FROM EMPLOYEE WHERE ENO = 1;
개요 How Index Works (Using unique index) SELECT * FROM EMPLOYEE WHERE ENO = 1; ENO 는 PRIMARY KEY로 선언되어있고, PRIMARY KEY는 NULL 을 허용하지 않는 UNIQUE INDEX 가 생성됨.

11 개요 How Index Works (Using unique index)
Query Processor __SYS_IDX_ID_403 (INDEX) EMPLOYEE (TABLE) Sorted Not Sorted - Root node 부터 Index를 검사. TABLE 에서 데이터를 참조하여 비교함 (Pointer를 통한 Direct 참조이므로 비용이 거의 없음) - Index에서 Table의 데이터의 Pointer 를 찾으면, 해당 Pointer 를 통해 TABLE 데이터 획득.

12 SELECT * FROM EMPLOYEE WHERE DNO = 1003;
개요 How Index Works (Using non unique index) SELECT * FROM EMPLOYEE WHERE DNO = 1003; Department – Employee 관계는 1:n 관계. Employee 테이블의 DNO 컬럼은 중복된 값을 값을 가질 수 있음.

13 개요 How Index Works (Using non unique index) - DNO가 1003인 Row는 두개가 존재.
Query Processor EMP_IDX1 (INDEX) EMPLOYEE (TABLE) Sorted Not Sorted - DNO가 1003인 Row는 두개가 존재. - Index에서 Table의 데이터의 Pointer 를 찾으면, 해당 Pointer 를 통해 TABLE 데이터 획득.

14 SELECT * FROM EMPLOYEE WHERE UPPER(DNO) = ‘1’;
개요 How Index Works (Index scan fail) SELECT * FROM EMPLOYEE WHERE UPPER(DNO) = ‘1’; ENO 를 UPPER 함수를 통해 강제로 형태변환.

15 개요 How Index Works (Index scan fail)
Query Processor EMPLOYEE (TABLE) EMP_IDX1 (INDEX) Not Sorted - UPPER(DNO) 로 INDEX가 있는지 검사하지만, UPPER(DNO)로 생성된 INDEX가 없음. Altibase 는 FBI(Function Based Index)를 현재 지원하지 않음. 테이블 FULL SCAN 후 하나씩 비교하여 데이터 리턴

16 개요 INDEX SCAN Fail 이 일어나는 경우 컬럼 명에 함수 또는 연산자를 사용한 경우.
Ex) SELECT * FROM T1 WHERE C1 +1 > 0 SELECT * FROM T1 WHERE TO_CHAR(SOME_DATE) = ‘ ’ 비교 데이터형이 맞지 않을 경우 Ex) SELECT * FROM T1 WHERE CHAR_COLUMN = 1 Cf) CHAR나 VARCHAR 컬럼 인데 숫자를 비교할 경우 컬럼에 형변환이 일어남. Composite Index일 경우 순서가 맞지 않을 경우 Ex) C1 + C2 로 Composite Index가 선언되어있을때 SELECT * FROM T1 WHERE C2 = :value Cf ) 위 경우 Composite Index를 사용하게 하기 위해서는 C1 조건이 필요함.’ SELECT * FROM T1 WHERE C1 = :value1 and C2 = :value2 Altibase Optimizer 가 Index SCAN COST 가 더 소요된다고 판단하였을 때

17 개요 INDEX SCAN Fail 이 일어나는 경우 NOT IN Subquery 사용시
Ex) SELECT * FROM EMPLOYEE WHERE DNO NOT IN (SELECT DNO FROM DEPARTMENT WHERE DNO > 4);

18 개요 INDEX SCAN 수행하였지만 Acess Count가 높은 경우
Cardinality가 좋지 않은 Index를 사용할 경우 발생함. -> 어느 Index 를 사용하고 있는지 확인이 필요. -> A+B+C 형식의 Index생성시 A, C 조건을 사용할 경우 Access Count 가 높게 일어날 수 있음. (A조건만 사용할 수 있기 때문) 현재 조건으로 조회를 했을 때, 어느 정도의 데이터 건수가 발생되는지 파악.

19 3 PLAN

20 개요 Query Execution Phase 사용자가 던진 SQL은 다음의 순서로 수행됨.
Parse : SQL의 문법, 유효성 등을 판단 Optimize : SQL의 실행계획에 대한 생성 Execute : Optimize 단계에서 생성된 PLAN을 통해 SQL을 실행 SQL Query Tuning에 관계된 Phase 는 Optimize 단계 Parse Optimize Execute

21 개요 PLAN Optimize 단계에서 수행한 “어떻게 SQL을 수행할 것인지”에 대한 정보
PLAN 을 통해 어떤 식으로 Altibase 가 Query를 수행할 것인지 유추할 수 있음. SELECT / UPDATE/ DELETE 문에 대해서만 PLAN 을 생성함. (WHERE 절이 존재하는 문) INSERT에 대해서는 실행계획이 없음.

22 개요 How To View PLAN Information
Altibase 에서는 ALTER SESSION 문으로 수행한 SQL의 PLAN을 조회할 것인지에 대해 지정할 수 있음. -- PLAN 도 조회하고 데이터 결과값도 보고 싶을 때. -- 실제 Execution 하는 PLAN 을 나타내므로, Access Count 등에 대한 정보가 정확함. iSQL> ALTER SESSION SET EXPLAIN PLAN = ON; -- PLAN 만 조회하고, 실제 Execution 은 하지 않을때 -- 대용량 데이터를 조회하거나, 수행시간이 오래 걸리는 경우 사용함. iSQL> ALTER SESSION SET EXPLAIN PLAN = ONLY; -- PLAN 조회를 끝내고 데이터 결과값만 보고 싶을때. iSQL> ALTER SESSION SET EXPLAIN PLAN = OFF;

23 개요 How To View PLAN Information PLAN 을 읽는 방법은 가장 안쪽부터, 위에서 아래의 순서로 읽음.
EMPLOYEE 테이블을 먼저 Access하여 ENAME 이 ‘KSKIM’ 인 ROW를 찾기 위해 EMPLOYEE 테이블 전체를 뒤짐(FULL SCAN) EMPLOYEE 테이블에서 ENAME=‘KSKIM’ 조건을 만족시키는 ROW가 1개. 이 결과를 통해 DEPARTMENT TABLE 을 검색함. (NEST LOOP) 검색조건은 EMPLOYEE 테이블에서 찾은 DNO 컬럼 조건(ON 절에 명시됨) 인덱스가 존재하므로 __SYS_IDX_ID_417 인덱스 를 사용함. (PK 생성시 자동으로 만들어진 Index) 데이터를 모두 찾았으므로, 이를 연산하여 사용자에게 결과 셋 1 Row를 리턴 함.

24 개요 How To View PLAN Information Access Count 를 주의 깊게 관찰 하여야 함.
FULL SCAN / INDEX SCAN 인지 파악하고 FULL SCAN 이면 INDEX SCAN 을 유도 ALTER SYSTEM SET TRCLOG_DETAIL_PREDICATE=1 옵션을 통해 더 자세한 실행계획을 확인 가능함. 각 조건에는 FILTER 와 KEY 조건이 있음. KEY 조건 : 해당 데이터를 찾기 위해 Indexing 을 수행함. FILTER 조건 : 해당 데이터를 찾기 위하여 전체 데이터를 뒤져 검색. Point : KEY 조건으로 최대 데이터를 많이 거르고, FILTER 조건으로 최대한 소량의 데이터를 걸러낼 때 수행속도가 증가됨.

25 개요 Tips $ALTIBASE_HOME/conf/glogin.sql 에 PLAN 및 Execution Time을 재는 SQL을 저장. isql 을 수행하면 사용자 입력을 받아들이기 전에 glogin.sql 파일을 수행함. 개발 중에 PLAN 을 미리 확인하고 개발하는 것이 추후 Tuning 에 대한 부담을 줄일 수 있음.

26 4 Join Optimization

27 개요 The Cases of Join optimization
JOIN CONDITION 에서 FULL SCAN, 또는 Index Access Count가 높은 경우 Join Condition 에 Index 생성 및 Index 순서 변경을 고려하여야 함. Index를 비효율적으로 사용하거나 사용하지 못하여 FULL SCAN 이 일어난 경우. JOIN Method 를 Altibase Optimizer가 잘못 판단한 경우. 너무 많은 테이블의 JOIN 으로 PLAN 을 생성하고 검증하는 시간 자체가 오래 걸리는 경우. GROUPING – JOIN 할 수 있는 경우 JOIN – GROUPING 순서로 Query가 된 경우.

28 개요 정상적인 경우 - DEPARTMENT의 DNO 조건이 있음. Primary Key 조건이라 DEPARTMENT
테이블을 먼저 SCAN - DEPARTMENT 에서 DNO 조건을 통해 EMPLOYEE 테이블 SCAN. EMPLOYEE 테이블에 DNO 컬럼에 EMP_IDX가 있으므로 INDEX를 경유하여 INDEX SCAN - 정상적인 경우이며, 대부분의 경우 이렇게 수행되어야 함.

29 개요 비정상적인 경우 (EMP_IDX INDEX 삭제 후)
- EMPLOYEE 테이블을 풀 스캔하여 나온 DNO 를 통해 DEPARTMENT 를 JOIN. - INDEX가 없는 상황에서는 최선의 선택. (먼저 읽는 순서가 바뀐다면 ?) - 질의는 동일하고, INDEX만 DROP 하였는데 ACCESS COUNT는 5.3 배 증가. - 만약 EMPLOYEE 테이블이 1억건이라면 ?

30 개요 Type mismatch in Join condition
Join Condition 에 사용되는 Data Type는 동일하게 유지하는 것이 권장됨. 내부적인 형 변환 발생시 INDEX를 사용하지 못하는 경우가 존재할 수 있음. 어쩔 수 없이 쓰는 상황이라면 꼭 PLAN 을 확인하고, 해당 조건이 INDEX 를 사용하는 지에 대한 사전 검증이 필요.

31 개요 Specify your table only once if you possible.
Inline View , SubQuery 는 SQL 작성을 쉽게 하고 쉽게 원하는 결과를 만들 수 있도록 하지만, 동일한 테이블을 계속 기술 할 수 있는 문제가 생김. 되도록 테이블 명 기술을 한번만해서 중복하여 동일한 테이블에 Access 하는 SQL이 나오지 않도록 수정하는 것이 필요. 한번만 읽고 필요한 데이터를 모두 만들어낼 수 있어야함. 만약 불가능 하다면 최소화 시키는 방안을 강구하여야 함.

32 개요 Reduce results before joining, if you possible. 부서별로 급여 합계를 계산하라.
1:N 의 관계 형태에서 단순히 M쪽의 값을 GROUPING 한 결과를 1 쪽에 Join 하는 쿼리일때는 GROUPING 을 먼저 하는 것이 훨씬 유리. 부서별로 급여 합계를 계산하라. SELECT Y.DNO, Y.DNAME, SUM(SALARY) TOTAL_SALARY FROM EMPLOYEE X INNER JOIN DEPARTMENT Y ON X.DNO = Y.DNO GROUP BY Y.DNO, Y.DNAME SELECT Y.DNO, Y.DNAME, X.TOTAL_SALARY FROM ( SELECT DNO, SUM(SALARY) TOTAL_SALARY EMPLOYEE GROUP BY DNO ) X INNER JOIN DEPARTMENT Y ON X.DNO = Y.DNO

33 개요 Joining between Memory & Disk Tables
Memory 와 DISK Tablespace 에 존재하는 테이블들에 대해 JOIN 을 할 때, DISK 쪽 Temporary Tablespace 를 사용하게 되면서 생기는 성능 하락 이슈가 존재. 특히 GROUP BY 등을 사용하기 위한 SORT 작업이 DISK Tablespace에서 수행되면서 성능 저하되는 CASE가 있음. 위 경우 /*+ TEMP_TBS_MEMORY */ 힌트를 통해 Temporary Tablespace 를 메모리로 지정하여 속도를 높일 수 있음. 주의 : 매우 큰 집합이 Sort 되는 경우, Altibase VSZ가 커질 수 있음.

34 개요 Outer Join Optimization Outer Join 시 기준 테이블 조건이 Key Access Path가 됨.
SELECT X.DNAME, Y.ENAME FROM DEPARTMENT X LEFT OUTER JOIN EMPLOYEE Y ON X.DNO = Y.DNO WHERE X.DNO = 1000 AND Y.ENO = 8890 Y 테이블 조건인 Y.ENO=8890 조건은 인덱스가 존재하여도 무용지물! OUTER JOIN 의미상 EMPLOYEE 테이블 먼저 Access 하는 방법은 논리적인 문제가 존재함.. X 테이블 조건인 X.DNO = 1000 조건만 인덱스를 사용 가능.

35 개요 Outer Join Optimization 만약 기준테이블 조건이 존재하지 않는다면 ?
SELECT X.DNAME, Y.ENAME FROM DEPARTMENT X LEFT OUTER JOIN EMPLOYEE Y ON X.DNO = Y.DNO WHERE Y.ENO = 8890 SELECT X.DNAME, Y.ENAME FROM DEPARTMENT X INNER JOIN EMPLOYEE Y ON X.DNO = Y.DNO WHERE Y.ENO = 8890 위 두 형태의 Query 결과는 정확하게 “동치” 임. 왼쪽 Query는 OUTER JOIN 때문에 Y.ENO 조건이 PK 조건이더라도 INDEX를 사용할 수 없지만, 오른쪽 Query는 INNER JOIN 이므로 Y.ENO 조건에 대하여 INDEX 사용이 가능함. 위와 같이 불필요한 OUTER JOIN 의 제거는 FULL SCAN 을 피할 수 있고, 성능 향상에 도움.

36 5 LIMIT optimization

37 개요 Question EMPLOYEE에서 SALARY가 가장 높은 두명이 누군지 SALARY 순으로 정렬하여 보이시오.
SELECT ENAME, SALARY FROM EMPLOYEE ORDER BY SALARY DESC LIMIT 2 답은 정확하지만.. 위 질의가 1초에 100번씩 수행된다면….?

38 개요 Question Result - EMPLOYEE 테이블을 FULL SCAN 하여 SORT 작업을 수행하고, 최종 결과인
2 Rows 만 사용자에게 Return 함. - EMPLOYEE 테이블이 1억건 이라면… ?

39 개요 Solution - Same result, but extremely fast

40 개요 Why ? Query Processor Idx_emp_salary EMPLOYEE (INDEX) (TABLE)
STOP - INDEX 만 두건을 읽어 데이터 Access. - INDEX가 SALARY DESC 로 생성되어있으므로, 가장 큰 SALARY 값부터 2건을 리턴할 것임. - EMPLOYEE TABLE 이 수천, 수억건이 되어도, 항상 Constant Execution Cost 를 보장함 : 복잡도 O(n) = 1

41 개요 Limit Stop key 사용의 제한 GROUP BY 가 사용될 경우
ORDER BY 가 INDEX 를 사용하지 못하는 컬럼으로 사용될 경우 테이블의 데이터를 중간 변화 없이 Stream 형태로 나오게 하는 경우에 최적화. LIMIT 을 사용하는 경우 최대한 WHERE 조건절이 없을 수록 유리

42 6 SUB-QUERY

43 개요 The Cases of Subquery Optimization
과다한 Subquery의 사용으로 인하여 동일한 테이블을 계속하여 Access 하는 PLAN 이 나오는 경우 NOT IN 에 대한 이해 부족으로 큰 집합의 대한 NOT IN 사용으로 인한 성능 감소 Subquery 조건에 INDEX Columns 이 없거나 부적절하여 불필요한 Access Count 증가

44 개요 If you read your table twice or more …
Subquery 는 Query 안에 각각의 Query가 동시에 수행되므로 동일한 테이블을 계속 Read 하는 SubQuery가 있다면 성능의 저하가 일어남. 리턴되는 각 Row 만큼 SubQuery 가 수행됨. 즉 결과집합의 크기가 커질수록 성능은 더욱 감소함. (주의!) 만약 한 건의 데이터만 SubQuery에서 필요하다면 SubQuery 안에 LIMIT 1 을 기술하는 것도 좋은 튜닝 방안. 만약 동일 테이블을 SELECT list에서 두번 이상 조회한다면 SubQuery를 JOIN 으로 바꾸어서 수행하도록 노력할 것 SubQuery로 표현 가능한 모든 형태는 Join 으로도 가능함.

45 개요 SubQuery -> Joining form SELECT X.ENAME,
(SELECT DNAME FROM DEPARTMENT WHERE DNO = X.DNO) DNAME, (SELECT DTEL FROM DEPARTMENT WHERE DNO = X.DNO) DTEL , (SELECT DLOCATION FROM DEPARTMENT WHERE DNO = X.DNO) DLOCATION FROM EMPLOYEE X; SELECT X.ENAME, Y.DNAME, Y.DTEL , Y.DLOCATION FROM EMPLOYEE X INNER JOIN DEPARTMENT Y ON X.DNO = Y.DNO;

46 개요 Avoiding NOT IN Sub Query …
NOT IN 은 Index를 사용하지 못함. 이 경우 NOT IN 을 JOIN 절로 변경 가능. SELECT * FROM EMPLOYEE WHERE DNO NOT IN ( SELECT DNO FROM DEPARTMENT_DELETED); SELECT X.* FROM EMPLOYEE X LEFT OUTER JOIN DEPARTMENT_DELETED Y ON X.DNO = Y.DNO WHERE Y.DNO IS NULL

47 7 Collecting Queries

48 개요 V$STATEMENT SQL STATEMENT 정보를 조회하는 Performance View
수행 시간, 해당 SQL문에 대하여 정보를 가지고 있으므로 V$STATEMENT 를 조회하여 문제가 되는 쿼리를 찾을 수 있음. Long Query의 경우 잘려나올수 있음. 조회 시점 실행중인 SQL에 대한 정보에 대해 나옴. SELECT SESSION_ID, EXECUTE_SUCCESS, EXECUTE_TIME/1000 || ‘ms’ EXEC_MSEC, FETCH_TIME/1000 || ‘ms’ FETCH_MSEC, RPAD(SUBSTR(QUERY,1,80), 80, ‘ ‘) QRY FROM V$STATEMENT ORDER BY EXECUTE_TIME DESC, EXECUTE_SUCCESS DESC ;

49 8 DML Optimization

50 개요 INSERT / UPDATE /DELETE INSERT 속도 저하 -> Too Many Indices ?
-> DISK Table 일 경우 BUFFER_POOL_SIZE 가 너무 작게 잡힌 것이 아닌지 확인 필요. -> Table Lock 이 잡혔는지도 확인필요. (V$LOCK) -> I/O Contention ? UPDATE / DELETE 속도 저하 -> SELECT 쿼리 튜닝 내용 포함. -> INSERT 속도저하의 내용 포함. -> Key 를 불필요하게 UPDATE 하는 경우