농부지기

[ java.대용량조회.oboe.js, ResultHandler ]

1. WAS단에서 대용량(약 10만)을 조회 시 WAS Memory Over Flow가 발생할 수 있다.

    그래서 oboe.js 나 ResultHandler를 이용해서 이 문제를 해결 할 수 있다.

2. 개발방법

    . 열공 ~~

3. 참고 소스

   . url : http://mycup.tistory.com/170

            http://blog.daum.net/mskweon82/23

            https://blog.naver.com/chm8410/220236592186

[ Oracle.튜닝-Index ]

1. index

    a. 많은 양의 페이지를 읽을 때

         - 본문을 처음부터 끝까지 쭉 읽는 것이 빠를 가능성이 높다.

    b. 적은 양의 페이지를 읽을 때

         1. Step

             색인을 뒤져서 페이지 번호를 찾는다.

         2. Step

             본문의 해당 페이지로 찾아 간다.

2. Index 분류

    a. B*Tree Index

    b. Bitmap Index

    c. Reverse Key Index

    d. Descending Index

    e. Function-based Index (FBI)

    f.  Domain indexes Index

3. B*Tree Index

    a. 구성요소

        . Root Block  -> Branch Blocks -> Leaf Blocks

    b. Root Block, Branch Blocks

        . 이 2개의 Block에는 data block에 접근하는 자료는 없다.

    c. Leaf Blocks

        . data block에 접근할 수 있는 rowid를 가지고 있다.

        . Leaf Node Block은 서로 양방향으로 연결되어 있다.

4. rowid

    a. 구성 : Object ID + File Number + Block Number + Row 위치 (Block내)

5. 결합인덱스

    a. 선행컬럼 선정순서

        1. 조건절에서 해당 컬럼이 항상 사용되는가

        2. 조건절에서 항상 '='로 사용되는가

        3. 컬럼의 분포도가 좋은가

        4. 정렬(sort)를 대신 할 수 있는가

        * 즉, index 컬럼 순서는 분포도 보다 '=' 검색이 더 우선 순위가 더 높다.

    b. 후행컬럼 선정기준

         1. 범위연산자로 조회되는 경우 (Between)

    c. 예제

         1. 만약, 급여테이블이 존재 할 때

             급여년월 + 사원번호 + 급여액   이라는 index가 존재 할 때 적절한지 파악해보면

             ; 급여년월은 주로 '=' 로 검색하고

             ; 사원번호, 급여액은 between 으로 검색하기에 적절하게 된다.

         2.

    c. 실행계획

        1. INDEX SKIP SCAN

            . A + B + C 에 대한 index가 존재 할 때

              where B = '12' AND C = '가가'; 로만 조건절에 적용시 11g 부터 적용될 실행계획임

            . 첫 번째 선행컬럼(A)의 데이터 분포도가 나쁘고 (고유한 값이 작고 중복된 값이 많은 경우)

            . 두 번재 선행컬럼(B)의 데이터 분포도가 좋은 경우 (고유값이 많고 중복이 적은 경우)

            . skip의 효과가 좋은 것이지, 모든 경우에 효율성이 좋은 것은 아님

6. 인덱스를 사용하지 못하는 경우

    a. 인덱스 사용이 비효율적인 경우

    b. 비교가 불가능한 경우

7. 인덱스와 힌트

    a. INDEX ~ 특정 인덱스를 사용하도록 유도하는 힌트

        . SELECT /*+ INDEX(emp_large EMP_IDX01) */ ..

    b. INDEX_ASC ~ 인덱스를 오름차순으로 접근하도록 하는 힌트

        . SELECT /*+ INDEX_ASC(emp_large EMP_PK) */ ..

    c. INDEX_DESC ~ 인덱스를 내림차순으로 접근하도록 하는 힌트

        . SELECT /*+ INDEX_DESC(emp_large EMP_PK) */ ..

8. Function-based Index (FBI)

    a. Index가 걸려 있는 컬럼에 계산식이나 함수가 사용되는 경우에는 Index를 사용할 수 없음

    b. FBI는 컬럼에 계산식이나 함수를 적용하여 자주 조회를 하는 경우에 사용가능

10. 용어

      a. 분포도가 좋다.

          . 고유한 값이 많다.

          . 데이터 처리 범위가 좁다.

          . 처리 일량이 적다

      b. 분포도가 나쁘다.

          . 중복된 값이 많다.

          . 데이터 처리 범위가 넓다

          . 처리 일량이 많다.

[ Oracle. 실행계획 - 자원통계정보 ]

1. Statistics

2. Recursive Call

    . 재귀호출이라고 해석 하며 Oracle DBMS가 사용자의 요청(sql)을 처리 하기 위하여

      내부적으로 사용한 DBMS Call(SQL)한 횟수

    . Trigger, 사용자정의 함수 호출(function), Hard Parsing등

3. DB Block Gets

    . 주로 DML 문장 실행에 필요해서 읽은 블록 수

4. Consistent Gets

    . 주로 Select문장 실행에 필요해서 읽은 블록 수

5. Physical Reads

    . 메모리에서가 아니라 DISK에서 읽은 블록 수

6. redo size

    . DML문장 실행 시 발생한 Redo Log Size

7. bytes snet via SQL*Net to client

    . Sql*Net을 통해서 보낸 Bytes수

8. bytes receieve via SQL*Net from client

    . Sql *Net을 통해서 받은 Bytes수

9. sorts(memory)

    . 메모리 내에서 발생한 sort 수

10. sorts(disk)

     . 디스크 내에서 발생한 sort 수

11. rows processed

      . 실행결과 출력된 row의 갯수

[ Oracle. 실행계획 - 기타연산자 ]

1. COUNT STOPKEY 연산자

    - sql > SELECT * FROM emp_large WHERE rownum < 10;

    - 실행계획

       ; 기본적으로 FULL TABLE SCAN을 하지만 조건절에 의해서 Stop하는 연산

2. FILTER 연산자

    - sql >SET AUTOTRACE TRACEONLY 

       sql >SELECT deptno, sum(sal) as total FROM emp_large

               WHERE job <> 'PRESIDENT'

               GROUP BY deptno

               HAVING sum(sal) > 6000;

    - 실행계획

       ; id 에서 HAVING절을 수행하기 위해서 FILTER연산자를 수행 했음.

[ Oracle. 실행계획 - JOIN ]

1. JOIN연산 종류

    1. Nested Loop Join

    2. Sort Merge Join

    3. Hash Join

2. Nested Loop Join

    - sql > SELECT /*+ USE_NL(emp_large, depart) */ dname, ename, job, sal

                  FROM emp_large a,  depart b

                WHERE a.deptno = b.deptno;

    - 실행계획

        ; NESTED LOOPS 수행방식

           . 2번 id를 먼저 수행 후 4,3 순서로 수행 됨

           . EMP_LARGE 에서 1건의 데이터를 읽어서 DEPART테이블에 접근하는 것을 반복하는 방식

             LOOP 라는 용어가 들어간 이유이다.

           . DEPART테이블을 하나씩 접근하므로 INDEX UNIQUE SCAN을 하게 된다.

    - 수행방법

        1. 조건절에 적용된 기준으로 emp_large테이블을 먼저 읽어서 SGA영역에 위치

        2. emp_large의 각 레코드 1개씩 depart 테이블과 join한다.

    - 특징

        1. 중첩된(Nested)Loop방식으로 처리되는 알고리즘

            (C언어나 기타 개발언어에서 종종 보던 중첩 Loop와 동일)

        2. 소량의 데이터 처리용

     - hine

         1. SELECT /*+ USE_NL(테이블1, 테이블2) */..

         2. SELECT /*+ ORDERED USE_NL(테이블1, 테이블2) */..

             . ORDERED란 FROM절에 기술된 테이블 순서대로 Nested Loop Join을 수행하라는 의미

3. Sort Merge Join

    - sql > SELECT /*+ USE_MERGE(emp_large, depart) */ dname, ename, job, sal

                  FROM emp_large a,  depart b

                WHERE a.deptno = b.deptno;

    - 실행계획

       ; id 2,4 각각 자료를 조회 후 정렬한 다음 join조건으로 JOIN을 하게 됨

         (depart는 index full scan이므로 정렬을 하지 않았음)

   - 특징

       . Sort merge Join을 할 때는 인덱스가 없어도 수행 될 수 있다.

       . Optimizer Mode가 ALL_ROWS인 경우에 자주 발생

       . Online 사용자에게는 부적합한 Join 알고리즘 임

       . Sort 연산에 많은 처리 비용이 발생

       . 대량의 데이터 처리용

4. Hash Join

    - sql > SELECT /*+ USE_HASH(emp_large, depart) */ dname, ename, job, sal

                  FROM emp_large a,  depart b

                WHERE a.deptno = b.deptno;

    - 실행계획

       참고) Sort merge Join에서는 full Table Scan을 수행한 후 Sort연산을 수행했었음.

       ; HASH JOIN

          . Sort Merge Join과 유사하게 접근하지만 Sort연산 대신 Hash 연산 사용

    - 장점

       . Sort Merge Join성능을 개선하려는 시도로 등장한 방법

    - 수행순서

       1. Sort 연산 후

       2. Merge 연산을 통해

       3. Join하는 방식

   - 특징

       . Sort Merge Join에 대한 성능 향상을 위해 7.3버전에 새로 등장한 알고리즘

       . '='연산자를 사용하는 경우에만 사용이 가능

       . Hash 함수와 Hash 테이블을 사용

          (Hash 함수 : 메모리상에 생성되는 데이터 집합)

       . Hash function : 데이터값 arg값으로 받아서 중복을 최소화한 고유한 주소 데이터를 생성함

       . Sort를 하지 않고도 대량의 데이터를 처리 할 수 있음.

9. 종합

    1. 전체 데이터 중 소량의 데이터를 Join으로 가져 올 때는 Sort Merge Join보다는

        Nested Loop Join이 효율적일 수 있다.

    2. OLTP성 트랙잭션 유형에는 Nested Loop Join이 어울린다.

    3. 배치성(DSS)성 트랜잭션 유형에는 Sort Merge Join/Hash Join이 어울린다.

    4. join처리 알고리즘

      5. Optimizer 실행계획 3단계

          . Query Transformation -> Query Optimization -> Generate Execution Plan

[ 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 = '판매종류';