개발자가 해야 할 SQL문 튜닝 #2

개인적으로 SQL문에 대한 튜닝을 정리하고 있었다. MS지에 마침 내가 생각하고 있던 부분이 일목요연(?)하게 정리한 기사가 있어, 함께 정리하여 공개한다. 자신이 DB에 문외한이라면 다른 것들은 차치하고서라도 INDEX가 잘 타고 있는지 확인하면 충분할 것 같다. 대개의 문제는 index의 문제이고 크게 성능이 향상되는 부분도 이 부분이다. 다른 것들에 대한 튜닝은 눈에 띄는 성능향상이 발생하는 경우가 극히 드물 것이다. 이에 대해 반대의견이 물론 있을 테지만.. SQL튜닝 -> bind변수사용과 성능향상예제 -> 파싱정보(v$session->v$sql_area->v$sql_text) -> plan( SQL> set autotrace on explain) -> statspack 프로그램 성능을 악화시키는 주요원인 1. 네트웍을 통한 통신 오버헤드(DB로의 빈번한 호출 - DBMS Calls) 2. 비효율적인 SQL문 - 보통 SQL문이 인덱스를 사용하도록 변경하여 해결한다. 3. 불필요한 파싱/바인딩(Parsing/binding) - 특히 커서사용시 * parsing: SQL문법이 올바른지 유효한 DB객체를 참고하고 있는지 체크하는 것. * binding: 오라클이 호스트변수에 값을 읽고 쓸 수 있도록 그 주소를 결합(연결)하는 것. * parsing은 CPU intensive한 작업으로 실제 SQL문을 실행하는 것보다 더 큰 오버헤드를 유발할 수 있다.   파싱의 오버헤드가 차지하는 비중이 크다는 것을 명심해 두도록 하라. 프로그램 성능을 악화시키는 주요원인에 대한 해결책 1. 호스트배열(host array) 사용 2. embeded PL/SQL 사용 3. SQL문 최적화 4. 인덱스의 사용 5. 행단위 락(row-level locking)을 사용 6. 불필요한 파싱을 제거 해결책에 대해 좀 더 자세히 알아보자. 1. 호스트배열(host array) 사용 1번의 SQL문 실행으로 대량의 데이터를 다룰 수 있다. 예를 들어, 다음과 같이 하면 한번의 실행으로 100개의 데이터를 insert할 수 있다.         int emp_number[100];         char name[100][11];                  /* insert할 100개의 데이터를 배열에 넣어라. ... */                  EXEC SQL INSERT INTO emp(emp_number, name) VALUES (:emp_number, :emp_name); 2. embeded PL/SQL 사용 PL/SQL블럭내 여러개의 SQL문을 사용하여, 이를 DB서버에 한꺼번에 전달하여 처리한다. 이렇게 하면, DB서버와의 통신오버헤드를 상당히 줄일 수 있다. 잘 사용하면 굉장한 성능향상을 가져오는 반면, DBMS에 의존적인 프로그램이 되버리는데, DB작업만을 하는(database-intensive) 배치프로세스가 아니라면, 개인적으론 거의 쓰지 않는다. 3. SQL문 최적화 실행되는 모든 SQL문은 실행계획(execution plan)을 가진다. 실행계획이란 SQL문을 실행하기 위한 일련의 단계들를 의미한다. 옵티마이저 힌트의 사용: select /*+ cost */ ename, sal into ... Trace facility의 사용 : trace facility와 explain plan을 사용하면 느린 SQL문을 파악할 수 있다.         Trace facility : 실행되는 모든 SQL문에 대한 통계정보를 제공한다.                                          이 통계정보를 통하여 가장 시간이 오래 걸리는 SQL문을 파악할 수 있다.         explain plan : SQL문에 대한 실행계획(execution plan)을 파악할 수 있다.                        Trace facility를 이용하여 가장 오랜 실행시간을 갖는 SQL문을 파악하고, 이 SQL문에 대해                        explain plan을 이용하여 실행계획을 보면, 사용된 SQL문이 비효율적인지 파악할 수 있다. 4. 인덱스의 사용 설명할 필요가 없을 정도로 중요한 부분이다. where절에 인덱스컬럼을 사용하라는 것이다. 인덱스는 전체 테이블의 레코드중 15%내의 데이터만 검색시 사용하도록 한다. 그렇지 않다면 full table scan이 오히려 더 낫다. 가장 기본적인 것인데도 안지켜지는 경우가 의외로 많다. 인덱스를 사용하는 것이 좋다는 것은 아는데, 인덱스의 개념과 사용방법을 정확히 몰라서 자신이 작성한 SQL문이 인덱스를 사용하고 있는지 자신도 알지 못하는 경우도 많은 것 같다. 이에 대한 자세한 내용은 후반부에 설명하겠다. 5. 행단위 락(row-level locking)을 사용 테이블전체(table-level)에 락을 걸지 말고, 사용할 행(row-level)에만 락을 걸어라는 것이다. 사실 LOCK TABLE문을 명시적으로 사용하지 않는다면 디폴트로 행단위 락을 사용하므로 그다지 신경쓸 부분은 아닐 것 같다. 6. 불필요한 파싱을 제거 불필요한 파싱의 제거는 프로그램의 성능을 높일뿐 아니라, 오라클 자체의 귀중한 자원인 메모리(SGA내 shared pool의 library cache)를 낭비하지 않는다는 측면에서 매우 중요하다고 볼 수 있다. DB 응용프로그램 개발시 유의사항 SQL문이 재사용되려면 동일한 SQL문이어야 한다. (대소문자, 공백,..) bind variable를 사용하여 hard parsing을 줄인다. SQL구문 작성시 유의사항 인덱스를 사용하도록 한다. 최적화된 조인을 하도록 한다. 전체범위를 액세스하지 않고 일부분만을 처리하더라도 동일한 결과를 얻을 수 있는 방법을 모색한다. 클러스터링을 고려한다. 한번의 호출에 다량의 데이터를 동시에 액세스 하여 처리하도록 한다. 인덱스를 사용하도록 한다. 1. 인덱스 컬럼에 변형을 가하지 않는다. 2. 부정형(not, <>)으로 조건을 기술한 경우 인덱스를 사용할 수 없다. 3. NULL과 비교시 인덱스 사용안됨 4. 테이블 전체레코드수의 10% 이상의 데이터들을 가져올때 인덱스를 사용하는 것은 의미가 없다. 4. 복합인덱스(composite index)의 경우 컬럼순으로 사용해야 한다. 특히 첫번째 컬럼은 필히 사용해야 함. 아래에서 where조건에 지정된 컬럼에는 인덱스가 생성되어 있다고 가정한다. 즉, 아래의 예에서 사용된 ename, sal, hiredate, empno 컬럼등에는 인덱스가 있는 것이다. 인덱스가 없다면 질의를 수정하더라도 full table scan이 발생한다. select * from emp where substr(ename,1,4) = 'JAME'; -> select * from emp where ename like 'JAME%'; select * from emp where sal*12 = 3500000; -> select * from emp where sal = 3500000/12; select * from emp where to_char(hiredate, 'yyyymmdd') = '19811203' -> select * from emp where hiredate = to_date('19811203', 'yyyymmdd');         // 년월일 값만 가질 경우만 가능 select * from emp where hiredate between to_date('19811203', 'yyyymmdd') and to_date('19811203', 'yyyymmdd')+ 0.99999; (more proper) select * from emp where ename || empno = 'SMITH7369'; -> select * from emp where ename = 'SMITH' and empno = '7369'; select dname, deptno from dept where deptno in (select deptno from emp); -> select dname, deptno from dept where exists (select deptno from emp where dept.deptno = emp.deptno); 아래 질의는 좀 더 파악할 필요가 있다. select * from emp where empno <> 7782; -> select * from emp where empno < 7782 or empno > 7782; select * from emp minus select * from emp where empno = 7782; select * from emp where not exists (select * from emp where empno = 7782); select * from emp where comm is not null; -> select * from emp where comm > -1; or select * from emp where comm > ''; 문자컬럼일때 최적화된 조인을 하도록 한다. A,B,C라는 테이블이 존재하고 조건에 대한 결과집합이 각각 10000, 100, 10의 수를 갖는다고 하자. 조인순서는 C->B->A가 가장 적당한 순서일 것이다. 조인순서 select A.*, B.*, C.* from A,B,C where A.id = B.id and B.id=C.id -> select /*+ ordered */ A.*, B.*, C.* from C, B, A where A.id = B.id and B.id=C.id 부분처리 ( 좀 더 파악할 필요 있음) select sms_id, msg_text from sm_data where sms_id>0 -> select /*+ index( order sm_data_pk) */ sms_id, msg_text from sm_data where sms_id>0; select max(sms_id)+1 from sm_data; -> select /*+ index_desc(sm_data sm_data_pk) */ sms_id+1 from sm_data where rownum=1; SUM, MIN, MAX, COUNT, UNION, DISTINCT, MINUS, INTERSECT, ORDER BY를 되도록 피하라. * 중복을 허용한다면 union 보다는 union all을 사용하라. * order by는 게시판 페이지 사용시 select id, name from (select rownum rn, id, name from   (select /*+ index_desc (t t_pk) */ id, name from t ) ) where rn between 1 and 100; 검증되지 않은 것들. 배치성 작업시 여러 SQL문을 하나의 SQL문으로 수정.(아래는 고객, 우편번호 테이블을 조인하여 1개의 질의로 수정한 것.) select a.고객번호, a.고객명, a.우편번호 주소, b.나머지주소 from 고객 a, 우편번호 b where a.우편번호 = b.우편번호; 해시조인으로 배치성작업의 효율 증대. 동등조인(Equi Join)만 가능. select /*+ use_hash(b a) full(b) full(a) */ a.고객번호, a.고객명, a.우편번호 주소, b.나머지주소 from 고객 a, 우편번호 b where a.우편번호 = b.우편번호; 온라인 작업시 중첩루프 사용 select /*+ use_nl(a b) */ a.고객명, b.사용요금 from 고객 a, 요금 b where a.고객ID = b.고객ID; 배치성 작업일때 해시조인 사용 select /*+ use_hash(a b) */ a.고객명, b.사용요금 from 고객 a, 요금 b where a.고객ID = b.고객ID;

This article comes from dbakorea.pe.kr 강명규 (Leave this line as is) 

[출처] 개발자가 해야 할 SQL문 튜닝 #2|작성자 우당탕탕