비달사쑨 블로그

최근 DIP에서 개최한 SQL 성능튜닝 수업을 듣고 왔습니다.

총 3일간 하루 8시간의 강의로 진행 되었는데, 제가 초짜라.. 굉장히 어려웠던 부분이 많았습니다.(그 중에 많이 졸기도 했지요..)

그래서 최대한 수업을 따라가며 핵심 키워드라도 필기해서 나중에 구글링 해서 찾아보자는 생각으로 열심히 받아적었습니다.

아마 긴 시간의 포스팅이 될 것 같지만, 포기하지 않고 써내려 가겠습니다. 부족한 내용이 많더라도 양해 부탁드립니다.

1. 문제가 되는 쿼리 찾아내는 방법

2. 실행계획 보는 방법

3. 다양한 사례로 알아보는 SQL 튜닝

1

보통 조인이 잘못되면 cartesian join이 일어납니다.(보통 교차곱을 사용 할 경우는 없음) 이를 확인하고 수정하면 됩니다.

2

Nonequi join이 있는 경우 Hash join이 불가능 합니다. 보통 이러한 방식은 피하는게 좋음

ex) WHERE e.sal between s.losal and s.hisal    <==  이러한 방식으로 사용하지 말자

3

Hint는 안쓰는걸 추천. Hint를 써야 할 상황이면 구조를 변경하거나 새로운 쿼리를 작성하는게 좋다.

4

/*+ ORDERED */

/*+ APPEND */

/*+ PARALLEL(table degree) */  =>  DBA와 상의가 필요한 부분

이 세가지 힌트를 자주 사용합니다.

5

CASE와 DECODE의 차이는 CASE구분의 성능이 훨씬 좋으니 그냥 CASE써라

6

ROLLUP을 자주 사용하자 !  => union으로 되어있는 쿼리를 ROLLUP으로 변경할 수 있으면 변경하자

7

WITH절로 VIEW를 만들면 성능이 좋아진다 ??

8

CUBE를 사용해보자 ... !

9

http://www.gurubee.net/lecture/2382

역시 DB 쿼리는 함수위주로 작성해야한다. 쿼리작성할때 함수위주로 생각하자. 만약 안된다면 그때 다른 방식을 찾아보도록하자

10

테이블 데이터 타입 제대로 확인하고 쿼리를 작성하자. 데이터타입이 맞지않으면 인덱스를 타지 않을 수 있다.

11

정규표현식이 함수보다 더 성능이 좋다.

12

인덱스 순서를 잘 보자. 인덱스 순서만 바꿔도 성능이 달라질 수 있다. (가장 적은 데이터를 인덱스를 앞으로 놓자 ! 범위

                                                                                          를 좁혀서 1차 가공해서 많이 걸러내자)

13

결합인덱스를 생성할때는 순서를 잘 생각해서 생성하자

14

상호참조 서브쿼리는 성능을 떨어트린다.

15

사례2 => 사진으로 설명

사례3 => 사진

        => 실행계획에서는 index를 사용한다고 하지만 실제로 데이터를 가져올때는 타지 않는다? 확인 필요

사례4 => 사진

        => Drived 테이블을 재설정 해야한다.(조인순서 재설정) 왜냐하면 Drived 테이블에서 조인 범위를 좁히면 다음 조

             인에 더욱 유리하다.

사례5  => CA와 DA의 값이 많은 줄 알고(데이터 분포 잘못 판단) 의도적으로 인덱스를 막았지만, 인덱스가 먹혀들어갔

              다.

         => 인덱스 순서 뒤집어야한다. (인덱스 순서 새로 조정해서 다시 생성)

         => 사진

사례6 => 사진

        => 인덱스를 새로 설정하고(JHCOD + JEPUM) 새로 설정한 컬럼이 인덱스를 탈 수 있도록 WHERE절 수정

사례7 => 사진

         => SUGA_SERL_IX 의 인덱스 순서를 바꿔야한다. (EDSC로 먼저 걸러내고 SERL로 걸러내는게 훨씬 좋음)

사례8 => 사진

        => 데이터를 1차 가공할때 가장 적은 데이터를 가져오는게 핵심 !!!

사례9 => 사진

        => 테이블 A와 테이블B의 조인에서 인덱스를 탈 수 없습니다. 인덱스의 위치가 뒤에있어서

        => 조인 시 사용된 결합 인덱스의 컬럼순서를 변경

사례10 => 사진

          => 사례9와 일맥상통

사례11, 12, 13 => 사진

                   => 인덱스 순서 재설정

사례14 => IS NULL은 인덱스를 타지 못한다.

           => 사진

사례15 => OUTER조인 할 때는 IN, OR, SUBQUERY를 사용하지 못합니다.

          => 오류뜸

사례16 => OUTER조인으로 값을 잘 가져오다가 마지막에 B.CENTER으로 걸러내버려서 원하는 데이터가 모두 나오지 못

               했습니다. B.CENTER에도 OUTER조인을 걸어주면 해결 할 수 있습니다.

          => 사진

사례17, 18, 19 => 개인적으로 해보라는 말씀

사례20 => 조인 순서 잘못. 범위를 확 줄일 수 있는 테이블을 먼저 조인해야합니다.

          => ORDERED 힌트로 테이블 조인 순서를 적절하게 변경하면 좋습니다.

사례21 => NULL처리

사례22 => 어렵네..ㅎ 이건 쫌 봐야긋다잉?

사례23 => COUNT값에 이상이 생김

          => MAX에서 MIN을 뺄때, 전체 테이블 조회할 필요없이 MAX쿼리와 MIN쿼리를 따로 구해서 전체 테이블 조회

               를 막을 수 있음

1

TKPROF 알아보기

2

https://m.blog.naver.com/PostView.nhn?blogId=genie319&logNo=100147787477&proxyReferer=https%3A%2F%2Fwww.google.com%2F

실행계획 보는순서 3 => 5 => 4 => 2 => 6 => 1

3

ADP. SQLP 자격증 확인

4

materialized view => 확인해보기

5

index organized tables 란?

6

B tree index => 일반적인 테이블 정보에 대한 검색 시 사용

BitMap index => 대용량 데이터베이스 환경에서 나쁜 분포도에 대한 컬럼 검색 시 사용

Revers key index

Descending index => 최신 행 정보를 우선시 조회할 때 사용

Function Based index => 검색 조건에서 계산공식을 사용하는 경우 결과치를 인덱스로 생성

index Organization Table => Primary Key를 이용하여 Text컬럼의 정보를 검색할 때

7

기본적으로 인덱스가 걸린 컬럼은 형변환과 같은 값이 직접적으로 달라지면 안된다.

인덱스 컬럼 가공 사례 사진 추가

8

visuble invisible => 옵티마이저한테만 인덱스 보여주고 안보여주고 설정하는 방법

9

<문제되는 쿼리 찾아내기>

1

성능이란 : 성능향상 ? 사용자가 체감할 수 있는, 수치적 증명 수준

성능 저하 요인

 - SQL/APP/DB design (80%)

    - I/O(70%) + CPU(15%) + Memory(10%) + Else(5%)

 - H/W resource(2.5%)

 - DB Configuration(10%)

 -System Design(7.5%)

2

v$로 시작하는 명령어로 DB의 현상태, 성능정보를 확인할 수 있다. (가장 기본적인 성능체크)

but 과거의 데이터를 기준으로 나타내는 성능이기때문에, 그 상황을 고려해야한다.

3

AWR을 통한 DB분석 후 top_sql.sql을 사용하여 가장 리소스를 많이 잡아먹는 쿼리를 찾아낼 수 있다.

https://oracle-base.com/dba/scripts

4

DB의 전체적인 성능을 확인하고, CPU문제인지, 프로세스 또는 메모리 문제인지 정확히 파악 후, 조치가 필요하다.

SQL을 튜닝하기 위해서는 어떤 쿼리가 문제를 일으키는지 찾아내는것이 핵심 !!

( ※ DB Server Performance Tune Exam 1 ~ 3까지 사진 업로드) 9P ~ 10P

5

10프로 미만이면 ?

6

하드파싱 소프트 파싱 => 어차피 하드파싱하면 실행계획 나와서 소프트파싱 할 수 있는거 아닌가.

7

RAC란 뭘까 ..

https://12bme.tistory.com/322

8

IN / NOT IN => Subquery의 데이터가 작을 경우 유리

Exists / Not Exists = > Mainquery의 데이터가 작을 경우 유리

9

order by는 안하면 제일 좋다. 성능에 안좋음. 그리고 index가 걸린 컬럼을 사용하면 자동정렬이 되서 order by를 사용할필요가 없다.

10

목차

1. View란?

2. View의 사용방법

  2-1. 단일 테이블을 이용한 단순 View

  2-2. 복합 테입블을 이용한 복합 View

  2-3. WITH CHECK OPTION / WITH READ ONLY 옵션 알아보기

3. View의 장점 및 단점

4. View를 사용하는 이유

5. View에 관한 다른 정보

1. View란?

   뷰의 의미는 하나의 select문과 같다고 생각하면 된다.

   물론 뷰를 통해 insert, update, delete가 가능하지만 대개의 경우는 select를 위해 사용한다. 뷰란 한개 이상의 기본 테이블이나 다른 뷰를 이용하여 생성

   되는 가상 테이블(virtual table)이다.

   뷰는 기존에 생성된 테이블 또는 다른 뷰에서 접근할 수 있는 전체 데이터 중에서 일부만 접근할 수 있도록 제한하기 위한 기법이다.

   뷰를 가상 테이블이라하는 이유는 테이블은 디스크 공간이 할당되어 데이터를 저장할 수 있지만, 뷰는 데이터 딕셔너리 테이블에 뷰에 대한 정의만 저장

   저장하고 디스크에 저장 공간이 할당되지 않는다. 하지만 일반 사용자들은 SQL문을 사용하여 테이블에 저장된 데이터를 검색하고 조작하는 것과 유사하게 뷰를 이용할 수 있다.

- 뷰 자체는 데이터를 갖지 않지만, 기초 테이블의 데이터를 조회하고 수정할 수 있는 창과 같다.

- 뷰는 데이터 값이 아니라 실제적으로는 질의 문장만을 가진다.

- 물리적인 테이블을 근거한 논리적인 테이블

- 뷰는 기본 테이블에서 파생된 객체로서 기본 테이블에 대한 하나의 쿼리문(뷰테이블 X, 뷰쿼리 O)

- 사용자에게 주어진 뷰를 통해서 기본 테이블을 제한적으로 사용하게 된다.

2. View의 사용방법 

   2-1. 단일 테이블을 이용한 단순 View

- 하나의 테이블에서 특정한 조건에 맞는 레코드 들만 질의(QUERY)가능

- 하나의 테이블에서 특정한 컬럼들만 질의(QUERY)가능

    2-2. 복합 테입블을 이용한 복합 View

       - 여러 테이블의 칼럼을 모아서(JOIN) 하나의 테이블처럼 질의(QUERY)할 수 있도록 한 데이터베이스 오브젝트      

2-3. WITH CHECK OPTION / WITH READ ONLY 옵션 알아보기

  WITH CHECK OPTION

• 조건 컬럼값을 변경하지 못하게 하는 옵션이다.

          1. 뷰를 정의하는 서브 쿼리문에 WHERE절을 추가하여 기본 테이블 중 특정 조건에 만족하는 로우(행)만으로 구성된 뷰를 생성할 수 있다.

          2. 이때 WHERE절에 WITH CHECK OPTION을 기술하면 그 조건에 의해 기본 테이블에서 정보가 추출하는 것이므로 조건에 사용 되어진 컬럼 값은

             뷰를 통해서는 변경이 불가능하다.

• 예 1) 뷰를 생성한다.

  SQL> CREATE OR REPLACE VIEW VIEW_CHK30 AS SELECT EMPNO, ENAME, SAL, COMM, DEPTNO FROM EMP_COPY WHERE DEPTNO=30 WITH CHECK OPTION; > SELECT * FROM VIEW_CHK30; 

• 예 2) 급여가 1200이상인 사원은 20번 부서로 변경한다.

  SQL> UPDATE VIEW_CHK30 SET DEPTNO=20 WHERE SAL>=1200;  뷰를 통해서 일관된 정보를 볼 수 있게 된다.

WITH READ ONLY

• 기본 테이블의 어떤 컬럼에 대해서도 뷰를 통한 내용 수정을 불가능하게 만드는 옵션이다.

• 예 1) WITH READ ONLY 옵션을 사용한 뷰를 생성한다.

  SQL> CREATE OR REPLACE VIEW VIEW_READ30 AS SELECT EMPNO, ENAME, SAL, COMM, DEPTNO FROM EMP_COPY WHERE DEPTNO=30 WITH READ ONLY;

  SQL> SELECT * FROM VIEW_READ30;  

• 예 2) WITH READ ONLY 옵션을 기술한 VIEW_READ30뷰의 커미션을 모두 2000으로 변경한다.

  SQL> UPDATE VIEW_READ30 SET COMM=2000; 

3. View의 장점 및 단점

  <장점>

     - DB의 선택적인 부분만 보여주므로 접근을 제한

     - 다양한 접근 경로 설정

     - 복잡한 질의를 단순화

     - 데이터의 독립성 제공

     - 동일한 데이터를 또 다른 뷰로 표현

     - 한 개의 뷰에 여러 테이블의 데이터를 검색 가능

     - 한 개의 테이블로부터 여러 뷰를 생성 가능

 <단점>

     - 뷰의 정의를 변경할 수 없고 insert, delete, update에 많은 제한이 있음

4. View를 사용하는 이유

  - 자주 쓰는 쿼리문을 안쓰고 테이블만 조회하면 된다.

  - 보안에 유리하다.

  - 뷰 테이블에 자료가 추가되는 것은 실체 테이블에 반영되지 않기 때문에 주의를 요한다.

5. View에 관한 다른 정보

복합 뷰(Nested View)를 최소화하라
뷰는 엄청난 쿼리를 사용자들로부터 가리는데 훌륭하지만, 하나의 뷰 안에 또 다른 뷰와 내부에 있는 다른 뷰를 (계속해서) 중첩시키다 보면 심각한 성능 저하를 유발할 수 있다. 너무 많은 수의 복합 뷰는 모든 쿼리에 대해 엄청난 양의 데이터가 반환(Return) 되는 결과를 초래해서, 데이터베이스 성능을 말 그대로 기어 다니게 만들 수 있다. 혹은, 더 나가서, 쿼리 최적화기(Optimizer)가 포기해서 아무것도 반환되지 않을 수도 있다.

복합 뷰를 풀어내는 것으로 쿼리 응답 시간을 몇 분에서 몇 초로 줄일 수 있다.

출처 : https://wikidocs.net/4178

        http://sjs0270.tistory.com/54

        http://www.itworld.co.kr/tags/2665/SQL/105792

https://wikidocs.net/4177

DB기초 책

SQL 기본 및 활용.pdf

면접전이나 모르는 문법이 있을경우 참고하기 좋은 사이트

https://wikidocs.net/3914

데이터베이스의 트랜잭션에 대해 알아보기 전에 자바코드를 예로 들어 "트랜잭션이란 무엇인가??"에 대해 이해하고 넘어가자

트랜잭션 (Transaction)

갑자기 "트랜잭션"이라는것이 나와서 뜬금없다고 생각할 수도 있겠지만 트랜잭션과 예외처리는 매우 밀접한 관련이 있다. 트랜잭션과 예외처리가 서로 어떤 관련이 있는지 알아보도록 하자.

트랜잭션은 하나의 작업 단위를 뜻한다.

쇼핑몰의 "상품발송"이라는 트랜잭션을 가정 해 보자.

"상품발송"이라는 트랜잭션에는 다음과 같은 작업들이 있을 수 있다.

• 포장
• 영수증발행
• 발송

이 3가지 일들 중 하나라도 실패하면 3가지 모두 취소하고 "상품발송"전 상태로 되돌리고 싶을 것이다. (모두 취소하지 않으면 데이터의 정합성이 크게 흔들리게 된다. 이렇게 모두 취소하는 행위를 보통 전문용어로 롤백(Rollback)이라고 말한다.)

프로그램이 다음과 같이 작성되어 있다고 가정 해 보자. (※ 아래는 실제 코드가 아니라 어떻게 동작하는지를 간략하게 표현한 pseudo 코드1이다.)

상품발송() {
    포장();
    영수증발행();
    발송();
}

포장() {
   ...
}

영수증발행() {
   ...
}

발송() {
   ...
}

쇼핑몰 운영자는 포장, 영수증발행, 발송이라는 세가지 중 1가지라도 실패하면 모두 취소하고 싶어한다. 이런경우 어떻게 예외처리를 하는 것이 좋겠는가? ^^

다음과 같이 포장, 영수증발행, 발송 메서드에서는 예외를 throw하고 상품발송 메서드에서 throw된 예외를 처리하여 모두 취소하는 것이 완벽한 트랜잭션 처리 방법이다.

상품발송() {
    try {
        포장();
        영수증발행();
        발송();
    }catch(예외) {
       모두취소();
    }
}

포장() throws 예외 {
   ...
}

영수증발행() throws 예외 {
   ...
}

발송() throws 예외 {
   ...
}

위와 같이 코드를 작성하면 포장, 영수증발행, 발송이라는 세개의 단위작업 중 하나라도 실패할 경우 "예외"가 발생되어 상품발송이 모두 취소 될 것이다.

만약 위 처럼 "상품발송" 메서드가 아닌 포장, 영수증발행, 발송메소드에 각각 예외처리가 되어 있다고 가정 해 보자.

상품발송() {
    포장();
    영수증발행();
    발송();
}

포장(){
    try {
       ...
    }catch(예외) {
       포장취소();
    }
}

영수증발행() {
    try {
       ...
    }catch(예외) {
       영수증발행취소();
    }
}

발송() {
    try {
       ...
    }catch(예외) {
       발송취소();
    }
}

이렇게 각각의 메소드에 예외가 처리되어 있다면 포장은 되었는데 발송은 안되고 포장도 안되었는데 발송이 되고 이런 뒤죽박죽의 상황이 연출될 것이다.

실제 프로젝트에서도 두번째 경우처럼 트랜잭션관리를 잘못하여 고생하는 경우를 많이 보았는데 이것은 일종의 재앙에 가깝다.

이번 챕터에서는 자바의 예외처리에 대해서 알아보았다. 사실 예외처리는 자바에서 좀 난이도가 있는 부분에 속한다.

보통 프로그래머의 실력을 평가 할 때 이 예외처리를 어떻게 하고 있는지를 보면 그 사람의 실력을 어느정도 가늠해 볼 수 있다고들 말한다. 예외처리는 부분만 알아서는 안되고 전체를 관통하여 모두 알아야만 정확히 할 수 있기 때문이다.

정말 데이터베이스의 트랜잭션과는 달라보이지만, 트랜잭션이라는 단어에 아주 적합하게 설명하고있다.

아래 링크로 데이터베이스의 트랜잭션에대해 한번 더 이해하면 완벽하다.

http://mommoo.tistory.com/62