농부지기

[ Oracle. 실행계획 - SORT ]

1. sort 종류

    - 명시적 sort, 암시적 sort

2. Sort 수행위치에 따른 구분

    1. Memory Sort

        - Memory내에서 Sort작업이 전부 이루어 지는 경우

    2. Disk Sort

        - 메모리 부족시 Disk영역을 빌려 Sort가 수행되는 경우. 속도가 느림

2. 명시적 Sort 예제

    . sql>SELECT deptno, ename, sal
              FROM emp_large
             ORDER BY deptno, sal desc;

    . 실행계획

3. 암시적 Sort 예제

    . distinct에 의한 암시적 Sort

       - SELECT DISTINCT job FROM emp_large;

       - 9i 실행계획

          ; 대상 데이터를 job 컬럼을 기준으로 정렬 연산 수행한 후

            해당 데이터를 처음부터 마지막까지 읽어 내려 가면서 중복 걸랜 후

            결과를 리턴하기 때문

       - 10g 실행계획

          ; Distinct연산에 의한 Unique값을 찾기 위해 Hash연산을 사용

            정렬된 결과를 나타내지는 않지만 Sort보다 가벼운 연산

       - 9i 와 10g의 Distinct에 의한 Unique값을 찾는 비용은

          약 : 70% : 3% 이다.

4. 암시적 - Sort Merge Join

       -   sql>SELECT /*+ USER_MERGE(emp_large, depart) */
                               dname, ename, job_sal
                      FROM emp_large a, depart
                    WHERE a.dept_no = b.dept_no * 1;

          ; depart.dept_no 컬럼의 인텍스를 사용하지 못하게 하여 Sort연산을 실행하도록 함

       - 실행계획

          ;

5. SORT(AGGREGATE)  ~ 그룹행 연산 (실제적으로 SORT가 발생하는 것 아님)

     - sql > SELECT count(*)                 FROM emp_large WHERE ename like 'A%';

                SELECT max(sql), min(sal)  FROM emp_large;

                SELECT avg(sal)                  FROM emp_large;

     - 실행계획

        ; Count나 Sum, Avg와 같은 그룹행 연산에서 결과 집합을 집계(Aggreagte)연산을

          수행했다는 의미

        ; 실제적인 Sort를 하지는 않음

        ; 비용이 발생하지 않은 Operation은 실제수행된것은 아니고 개념적인 수행임

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

** 집합연산

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

1. 집합연산 종류

    1. UNION ALL

    2. UNION (합집합)

    3. INTERSECT (교집합)

    4. MINUS (차집합)

    -  UNION, INTERSECT, MINUS는 두 집한간에 공통된 요소를 찾아야 함

2. UNION ALL

    - sql > SELECT deptno FROM depart

               UNION ALL

               SELECT deptno FROM emp_large;

    - 실행계획

       ; depart 테이블에서 index가 존재하는 deptno컬럼 1개만 필요하므로

          INDEX FULL SCAN이 수행 됐음

         즉, 인덱스내에 필요한 모든 컬럼이 있으므로 인덱스에만 접근.

3. UNION

    - sql > SELECT deptno FROM depart

               UNION

               SELECT deptno FROM emp_large;

    - 실행계획

       ; UNION에서 중복된 값을 걸러내기 위해 SORT연산을 수행

       ; TempSpcace : 10g부터 추가

                                 SORT를 하기 위해 6280k의 공간이 필요하다는 의미

       ; 비용 (Cost) = CPU Cost + IO Cost

         : 633 의 비용 시 CPU Cost비중 2%

           (즉, Full Table Scan은 IO 집중(Intensive)적인 작업 이라는 의미)

       ; Memory Sort만 발생했음

          . CPU Cost가 100%라는 의미는 I/O Cost가 발생하지 않았다는 의미임

       ; 위 sql문장 수행전에  sql>SET AUTOTRACE TRACEONLY 하에서 다시 실행해보면

4. INTERSECT

    - sql > SELECT deptno FROM depart

               INTERSECT

               SELECT deptno FROM emp_large;

    - 실행계획

       ; depart는 Index Full scan이므로 NOSORT를 하고

         emp_large는 Table Access Full Scan이므로 Sort Unique Scan을 함

         그런 후 INTERSECTION을 의미상으로 수행

          (SORT UNIQUE : Unique값을 찾기 위해 Sort연산을 수행한다는 의미)

5. MINUS

    - sql > SELECT deptno FROM depart

               MINUS

               SELECT deptno FROM emp_large;

    - 실행계획

       ; 

[ Oracle. 실행계획 ]

1. 참고

     . AUTOTRACE나 Explain Plan을 통해서 나타나는 실행계획은 예상실행계획이고

     . SQL Trace를 통해서 나타나는 실행계획은 실제실행계획이다.

2. Group by

    . 9i까지는 Group by 시 Group by 에 정의 컬럼기준으로 정렬이되어 조회 됨

    . 10g 부터는 Group by 를 해도 정렬되어 나오지 않음.

3. CBO, RBO

    . 실행계획 CBO로 나오는지 RBO로 나오는지 구분방법

    . RBO는 Row, Bytes, Cost,Time이 나오지 않음

      - 즉, 차이즘은 비용 유무.

4. Optimzer가 실행계획 수립시 참조하는 통계정보 2가지

    1. System 통계정보

        . CPU의 처리능력(Mhz),Disk I/O 처리능력에 대한 정보

        . System 통계정보가 실행계획을 결정하는데 미치는 영향력을 볼 수 없음

    2. Object 통계정보

        . 각 Object(Table, Index)에 대한 통계정보

        . Object 통계정보가 실행계획을 결정하는데 미치는 영향력을 볼 수 있음

5. Cost (%CPU)

    1. 비용 (Cost) = CPU Cost + IO Cost

    2. 위예제에서 : 633 의 비용 시 CPU Cost비중 2%

        (즉, Full Table Scan은 IO 집중(Intensive)적인 작업 이라는 의미)

6. 실행계획

    SET TIMING ON;  --SQL처리 응답속도 조회
    SET AUTORACE TRACEONLY;           --sql문장수행 및 실행계획을 보여줌
    SET AUTORACE TRACEONLY EXPLAIN;   --SQL문장은 수행하지 않고 실행계획만 보여줌
      
    SELECT EXECUTIONS, FETCHES, SQL_TEXT
      FROM V$SQL
     WHERE SQL_TEXT like '%sql내용%';
     --EXECUTIONS : SQL수행 횟수
     --FETCHES    :

[ Oracle.PL/SQL(Anonymous Block, Stored Block) ]

1. Anonymous Block

    . 실행시점에 해당 Block이 네트웤을 타고 DBMS에 전단됨

    . SQL 처리과정과 동일하게 Parsing과 Execute과정을 거침

2. Stored Block

    . DBMS 서버 내에 저장 됨

    . 처음 실행 시에는 Parsing된 상태로 저장되기 때문에 빠르다고 생각함.

3. 참고

    . Anonymous,Stored Block 의 성능은 동일함

3. 각 프로그램별 처리 시간

    . 40만건을 처리시 수행 시간

4. Stored Block을 사용한 예제

5. Subquery를 사용한 예제

[ Oracle.PL/SQL(Bulk-binding, Bulk Collect) ]

1. 참고

2. Bulk-binding, Bulk Collect

    1. 특징

        - 빈번한 DBMS Call을 줄이기 위한 기법

3. PL_SQL 예문

[ Oracle SQL튜닝. Optimizer ]

1. Optimizer 개념

     - 최적화하기

     - 성능향상하기

     - Query Optimzer : 질의 최적화하기

2. Optimizer 종류

    a. RBO

        - 행동대장형 (조직폭력 단체)

        - Roles Base

        - 미리 정해진 고정된 15개의 규칙을 기반으로 판단

    b. CBO

        - CFO형 (기업)

        - 통계 기반

        - Data Dictionary들에 저장된 통계정보 기반 판단

3. RBO 15기지 규칙   

4. CBO - 통계정보 수집하기

    1. CBO 통계정보 > 지우기

        - ANALYZE  TABLE table_name  DELETE  STATISTIS;

    2. CBO 통계정보 > 전체 수집

        - Data가 많을 경우 시간이 오래 걸림

        - ANALYZE  TABLE table_name  COMPUTE  STATISTIS;

    3. CBO 통계정보 > 예측정보 수집

        - 대용량인 경우 estimate로만 수집해도 compute로 했던 정보와 거의 유사함

        - ANALYZE  TABLE table_name  ESTIMATE  STATISTIS;

5. CBO-통계정보 내역 보기

    1. 정의

        - 수집된 통계정보 내역 보기

    2. 테이블 정보

        SELECT * FROM USER_TABLES

        WHERE TABLE_NAME = 'table_name';

    3. 컬럼정보

        SELECT * FROM USER_TAB_COLUMNS

        WHERE TABLE_NAME = 'table_name';

    4. HISTOGRAM정보

        - 판매종류 컬럼에 히스토그램(Histogram)정보 수집

        - ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS 판매종류;

        SELECT * FROM DBA_HISTOGRAMS

        WHERE TABLE_NAME = 'table_name' AND COLUM_NAME = '판매종류';

[ Oracle SQL튜닝. 성능튜닝 Tool ]

1. DESC PLAN_TABLE

2. DBA가 PLAN_TABLE만들기

    - 방법 1

      > CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYSTEM.PLAN_TABLE;

    - 방법2

      > @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql

3. DBA가 모든사용자에게 권한부여

    > GRANT ALL ON PLAN_TABLE TO PUBLIC;

4. 성능Tool종류 4가지

     1. SQL Trace

     2. Explan Plan

     3. Autotrace

     4. DBMS_XPLAN

5. 성능 Tool 특징

    1. Explan Plan

         - 사용하기 번거로운 단점

    2. Autotrace

         - 편리한 Tool

          - SQL Trace보다 자유로움.

    3. SQL Trace

       - 필수적인 Tool

     4. DBMS_XPLAN

         -Oracle 9i이후 버전부터 지원 - 사용편리, 유용

6. Explan Plan

7. Autotrace

    a. Autotrace설치방법 (dba권한 필요)

        dba>@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\plustrce.sql;

    b. Autotrace방법으로 실행계획보기

         sql>SET AUTOTRACE ON;

         sql>SELECT * FROM DUAL;

    c. Autotrace 수행결과 3가지 그룹

        1. SQL 실행결과

        2. SQL 실행계획과 Predicate Information

        3. SQL 실행 시 사용한 자원통계정보

8. SQL Trace

    a. Trace파일 생성

        ...

    b. 수행결과  3가지 그룹

        1. SQL문장

        2. SQL처리 단계별 자원사용량과 시간정보

        3. SQL 실행계획과 각 단계별 처리된 Rows수와 Logical ? Physical I/O 및 시간

    c. SQL처리 단계별 자원사용량과 시간정보

        1. count

            - DBMS Call을 의미하며 1개의 sql실행시 4번의 DBMS Call이 발생

        2. cpu

            - DBMS Call을 처리하기 위해 사용한 CPU시간

        3. elapsed

            -

        4. disk

            - sql을 처리하기 위해수행된 Physical Read한 Block의 개수

        5. query

            - Logical Read이며 주로 데이터 조회인 Select연산을 위해 읽어 들인 Block개수

        6. current

            - Logical Read이며 주로 데이터 수정인 DML연산을 위해 읽어 들인Block의 개수

        7. rows

            -

        8. Fetch . SELECT시 만들어진 결과 데이터를 Client에게 보내는 행위

                     . 1회 실행 시 1개의 Row를 가져오지만 Fetch회수가 2인 이유는 마지막 데이터를

                       읽은 후 다음 데이터의 존재 유무를 알기 위해 한번 더 Fetch를 하기 때문.

                     . 먄약, Fetch 10번이고, rows가 10,00이면

                       db는 was에 약(10,000/10) = 1,000건씩 전송했다는 의미

        9. Parse . Sort Parsing하지 않고, hard parsing할 경우 count가 1이 나옴

                      . Misses in library cache during parse과 동일한 숫자임

         a. Misses in library cache during parse

             . sql을 aprse하는 동안에 library cache안에서 Miss했다는 뜻.

               즉, Soft Parsing이 아닌 Hard Parsing했을 때 1 이라는 숫자가 조회됨

             . Parse결과절과 동일한 숫자임        

9. SPCPKG

    a. 설치

         sql>CONN SYS AS SYSDBA

         sql>@%ORACLE_HOME%\RDBMS\ADMIN\SPCREATE.SQL

    b. 설치확인

        탐색기 > @%ORACLE_HOME%\RDBMS\ADMIN\spcpkg.lis  파일 확인

    c. 보고서

        sql>conn perfstat    --perfstat : 설치시 id

         ....생략...

    d. 성능모니터링

        sql> conn perfstat/perfstat

        sql>execute statpack.snap;    --구간모니터링 시작

        .. 업무수행...

        sql>execute statpack.snap;    --구간모니터링 종료

10. 추가

     . AUTOTRACE나 Explain Plan을 통해서 나타나는 실행계획은 예상실행계획이고

     . SQL Trace를 통해서 나타나는 실행계획은 실제실행계획이다.

[ Oracle SQL튜닝. 접근경로(Access Path) ]

1. Access Path 종류

    a. Direct Access

    b. Indes Scan

    c. Full Table Sacn

1. Direct Access

    a. ROW_ID를 이용한 DATA 접근

2. Index Scan 세분화

    a. UNIQUE SCAN

         - 데이터를 1건 찾은 후 동일한 중복값이 있을 수 없기 때문에 검색을 바로 중지함

         - 예) unique index key가 존재하는 컬럼에 = (equal)조건일 경우

                 SELECT * FROM EMP_TABLE WHERE emp_no = 1234;

    b. RANGE  SCAN

        - Equal조건에 의한 Index Range Scan

          . 동일한값이 존재 할 수 있기 때문에 해당 조건에 1개 이상의 값을 리턴 할 수 있음.

          . 예) non unique index key가 존재하는 컬럼에 = (equal)조건일 경우

                SELECT * FROM EMP_TABLE WHERE emp_name = '강감찬';

    c. FULL SCAN
        - Index Fast Full Scan과 Index Full Scan이 존재

        - 인텍스 Block을 처음부터 끝까지 읽기

        - 예) SELECT /*+ USER_MERGE(emp_large, depart) */  dname, ename, job_sal

                   FROM emp_large a, depart b

                 WHERE a.deptno = b.deptno ;

    d. SKIP SCAN

    e. FAST FULL SCAN