블로그 이미지
잡초 개발자의 좌충우돌 이야기 yysvip

카테고리

분류 전체보기 (211)
Java Programing (24)
Web Programing (20)
Database (36)
Tool (46)
까칠한 IT (27)
까칠한 정보 (42)
까칠한 Strory (16)
까칠한 Project (0)
Total1,164,494
Today4,461
Yesterday4,153

달력

« » 2018.12
            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          

공지사항

Oracle 튜닝 플랜보기

  까칠한 남자 Strory  petulantman.tistory.com       잡초 개발자 까찰한 쑤의 좌충우돌 개발 이야기

 

 

SQL 문장의 최적화와 최적화 방법.


  1) 규칙기반의 최적화 - RBO (Rule Based Optimization).


     ORACLE이 정의하고 있는 우선 순위를 기반으로 해서 문장 수행의 과정을 정의한다.

     기본적인 방법이라는 장점이 있으나 ORACLE이 정의하고 있는 우선 순위가 항상 최

     적화 되어있지 않을 수 있다는 변수를 갖고 있다는 단점이 존재한다.


     - ORACLE이 정의하는 RBO 기반 처리 우선순위표.

분류

액세스 경로

1

 Row ID(행 주소)에 의한 단일행 검색

2

 클러스터 조인에 의한 단일행

3

 유일키 또는 기본키가 있는 해시 클러스터키에 의한 단일행

4

 유일키 또는 기본키에 의한 단일 행

5

 클러스터 조인

6

 해시 클러스터 키

7

 인텍스된 클러스터 키

8

 복합 인덱스

9

 단일 열 인덱스

10

 인덱스된 열에서 제한된 범위 검색

11

 인덱스된 열에서 무제한 범위 검색

12

 정렬-병합 조인

13

 인덱스된 열에서 MAX 또는 MIN

14

 인덱스된 열에서 ORDER BY

15

 전체 테이블 스캔

   

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

     - Session 레벨의 최적화 관련 환경변수 설정을 이용하여 규칙기반의 최적화를 설정

       하기 위한 명령은 다음과 같다.

       . Alter Session Set Optimizer_Mode = 'Rule'

       SQL> alter session set optimizer_mode = 'rule' ;

       Session altered.

       SQL> select strcuid, strouid, dateproc, strtprocno from ecommloginfo ;

       STRCUID   STROUID   DATEPROC STRTPR

       ---------- ---------- --------    ------

       INTERPK    NATE1124   20020101

       INTERPK    WINKER71   20020101

       INTERPK    NATE1124   20000110

       INTERPK    GORA99     20000104  000002


       4 rows selected.


       Execution Plan

       ----------------------------------------------------------

       0      SELECT STATEMENT Optimizer=RULE

       1    0   TABLE ACCESS (FULL) OF 'ECOMMLOGINFO'

       Statistics

       -------------------------------------------------------

              0  recursive calls

              4  db block gets

              2  consistent gets

              0  physical reads

              0  redo size

           1721  bytes sent via SQL*Net to client

            700  bytes received via SQL*Net from client

              4  SQL*Net roundtrips to/from client

              1  sorts (memory)

              0  sorts (disk)

              9  rows processed

 

 

 

  2) 비용기반의 최적화 - CBO (Cost Based Optimization).


     통계자료를 기반으로 하여 문장 수행의 우선순위를 결정하므로 가장 정확한 최적화 기

     법으로 선택되어지고 있다. 그러나, 통계자료를 만들어 준다는 일이 항상 최신의 통계

     정보를 구축해 놓아야 한다는 부담으로 존재한다는 단점을 들 수 있다.


     - Session 레벨의 최적화 관련 환경변수 설정을 이용하여 비용기반의 최적화를 설정

       하기 위한 명령은 다음과 같다.

       . Alter Session Set Optimizer_Mode = Choose

         위의 명령은 SQL 문장을 수행함에 있어서 비용기반의 최적화를 지정한다.

       . Alter Session Set Optimizer_Mode = First_Row

         위의 명령은 SQL 문장을 수행함에 있어서 비용기반의 최적화를 수행하며 특히,

         명령수행의 첫 Response에 중점을 둔다.

       . Alter Session Set Optimizer_Mode = All_Row

         위의 명령은 SQL 문장을 수행함에 있어서 비용기반의 최적화를 수행하며 특히,

         명령수행의 전체 Response에 중점을 둔다.


     - 비용기반의 최적화를 위한 통계 정보의 구축.

       . Analyze Table table_name Compute Statistics

         지정한 테이블에 대한 전체의 통계를 구축한다.

       . Analyze Table table_name Estimate Statistics

         지정한 테이블에 대해 비율적 통계를 구축한다. Compute Statistic으로 생성한 통

         계에 비해서는 다소 정확도가 떨어질 수 있으나 생성속도가 빠르고 그 생성속도에


         비해서 상당히 정확한 통계를 구축한다.

       . Analyze Table table_name Delete Statistics

         구축되어 있는 통계자료를 삭제한다. 새로운 통계자료를 구축하기 전에 필요한 과

         정이다.


     - 통계 정보를 구축한 후 비용기반의 최적화 실습.

     SQL> analyze table ecommloginfo compute statistics for table for columns strcuid

           size 20 ;

     Table analyzed.

     SQL> alter session set optimizer_mode = 'choose' ;

     Session altered.

     SQL> select strcuid, strouid, dateproc from ecommloginfo

           2  where dateproc like '200001%' ;


     STRCUID    STROUID    DATEPROC

     ---------- ---------- --------

     INTERPK    WINKER71   20000110

     INTERPK    NATE1124   20000110

     INTERPK    GORA99     20000104


     Execution Plan

     ----------------------------------------------------------

     0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=26)

     1    0   TABLE ACCESS (FULL) OF 'ECOMMLOGINFO' (Cost=1 Card=1 Bytes

              =26)


     Statistics

     ----------------------------------------------------------

          0  recursive calls

          4  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

       1327  bytes sent via SQL*Net to client

        718  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          3  rows processed

 

 

 

#############################################################################

 

이거 어캐보는지 좀 갈켜주세요.

맨 안쪽 테이블부터 검색을 하면서 밖으로 나오는 건가요?

USSR01PK -> USSR010T -> USTP050T -> USSJ01PK -> USSJ010T -> COM_SYS_C0025372 -> COM_COMM010T 이런 순서로 검색하나요?

 

그리고 이걸 어떻게 이용하여 튜닝을 하게 되나요?

<FORM name=scfm_9507673 method=post>
  정해범 Cost 베이스로 본 것 같은데? 우선 Cost가 큰 것은 해당 연산을 수행하기 위해 자원을 낭비한 거지... 그런 걸 줄여야지. Cost 베이스 말고 다른 방법이 더 있을텐데 오래 되어서 생각이 잘... ㅋ~ USTP0501T는 Full table scan 한 것 같은데? 그런것두 없애 줘야 하구... got it?   댓글
</FORM>

 

 

#############################################################################

 

Plan에 대한 수행순서인데 정확한 판단은 힘들고 대략 아래 순서에 따라서 수행된다고 이해하면 될 거야...

(Optimizer가 어떻게 수행하는 지 정확한 판단을 내리기는 어렵거든... 암튼 참고가 되었으면 한다.)

 

① USTP050T 테이블을 Full Scan하고

 

② Full Scan한 각 각의 Row를 USSR010T 테이블과 조인하여
  USSR01PK 인덱스를 통한 1:1로 NESTED LOOP를 통해 매치된 결과가 나올 것이고

 

③ 2번항에서 나온 결과에 대한 각 각의 Row를 USSJ010T 테이블과 조인하여
  USSJ01PK 인덱스를 통한 1:1로 NESTED LOOP를 통해 매치된 결과가 나올 것이고

 

④ 3번항에서 나온 결과에 대한 각 각의 Row를 COMM010T 테이블과 조인하여
  SYS_C0025372 인덱스를 통한 1:1로 NESTED LOOP를 통해 매치된 결과가
  최종 조회된 Row로 나타나는 구조로 되어 있네^^*

 

2, 3, 4번 항에 대한 조인시 1:1 Unique한 NESTED LOOP가 반복되는 구조이므로 수행시간에 대한 영향은 그리 크지 않으므로 튜닝은 필요가 없을 테고

 

※ 문제는 1번항에 대한 Full Scan에 대한 영향도를 따져야 할 듯 함.

USTP050T테이블의 총 Row수가 1~2만건 정도라면 INDEX보다 Full Scan이 나을테고

 

그렇지 않다면
USTP050T테이블에 별도의 인덱스 구성이 필요할 것이라 생각됨.

 

조건에서 보면 A.PICK_YY = :pickYy와 A.DEPT_CD = :deptCD가 있는 것으로 보아
PICk_YY + DEPT_CD로 되어 있는 복합 인덱스 필요한 사항이라 생각 됨.

 

CREATE INDEX 인덱스명 ON USTP050T
(PICk_YY, DEPT_CD)
TABLESPACE 테이블스페이스명 
             .
             .
(테이블스페이명 이하는 기존 테이블에 대한 사항을 고려해서 적용하면 될 것 같음)


단, 인덱스 추가시에는 반드시 다를 애플리케이션에서 USTP050T테이블을 사용하는지 여부와 검색조건을 고려하여 생성하는 것이 바람직함.(타 애플리케이션 로직에 영향을 줄 수가 있음)

########################################################################

Posted by yysvip

최근에 달린 댓글

최근에 받은 트랙백

글 보관함