Oracle-실행계획

   - 실행계획테이블에 저장된 실행계획을 조회

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

[ Oracle-실행계획 ]

 

1. Optimizer

    . RBO (Rule Based Optimizer)

       - 규칙기반

       - Oracle 7까지 기본 Optimzer방식이였음. 현재는 RBO방식을 사용하지 않음

    . CBO(Cost Based Optimzer)

       - 비용기반

       - CPU사용시간, Memory사용량등을 기반으로 수행

       - DB, Schema등 통계정보 관리

2. 실행계획 초기설정 방법

    . sql수행 후 실행계획을 보려면 아래 파일을 수행하면 됨

    . plan table생성 및 plustrace권한 부여

    . 파일위치 : C:\app\user\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql

    sys>@     --@를 key in 후 위 파일을 drag & drop 해서 sqlplus창에 이동

                   --@는 파일내부의 내용을 수행하는 예약어

                   --sys계정만 plustrce.sql 수행 가능 (즉, 실행계획 수행되도록 설정가능)

                   --이 설정을 해야 만 set autotrace on;등을 할 수 있음.

    sys>grant plustrace to scott;   --scott에 권한부여

                                              --이 권한이 부여 되어야 scott사용자는 set autotrace on;을 할 수 있음.

    scott>set autotrace on;   --sys에서 권한이 부여되었기에 정상수행

                                      --이후 수행되는 sql들은 실행계획도 같이 조회 됨

3. 실행계획 이해방법 1

    . 들여쓰기 존재

    . 맨 마지막 level 부터 수행

    . 같은 level에서는 위쪽라인부터 수행

3. 실행계획 이해방법 2

    . INDEX RANGE SCAN

      - index 범위 scan

    . TABLE ACCESS BY INDEX ROWID

      - index의 rowid를 이용해서 table access

      - 조건절에 rowid를 이용하게 되면 index scan없이 바로 table access가능

    . RBO 수행 성능 Rank

       1. Single row by rowid

       2. Single row by Cluster Join

       3. Single row by Hash Cluster key with Unique or Primary key

       4. Single row by Unique or Primary Key

       5. Clustered Join

       6. Hash Cluster Key

       7. Indexed Cluster Key

       8. Composite Index

       9. Single-Column Indexes

          - index range scan

       10. Bounded Range Search on Indexed Columns

       11. Unbounded Range Search on Indexed Columns

       12. Sort Merge Join

       13. MAX or MIN of Indexed column

       14. ORDER BY on Indexed Column

       15. Full Table Scan

1. INDEX RANGE SCAN : index는 존재 하지만 nonunique index인 경우 equal 검색할 때 index range scan으로 수행

2. INDEX UNIQUE SCAN : unique index인 경우 equal 검색할 때 index unique scan으로 수행

3. Full Table Scan : 상당히 느리지만 DB는 읽을때 한번에 128개 block(default size)만큼 읽어서 검색한다.

                       생각보다 큰 size를 읽어 메모리에서 검색하기 때문에 때론 빠를 수 있다.

                       (show parameter db_file_multi)

                       HWM(high water mark)까지 읽어 들인다.

                       

===========================================================

**  위 15개 이외의 실행계획

2. HASH GROUP BY 

   - 

===========================================================

1. 실행계획 - Statistics

    . recursive calls : - db입장에서 사용자가 수행한 sql을 call이라고 인식함)

                        - 요청된 sql에 대한 권한등을 db입장에서 내부적(table)으로 처리하는 sql수 횟수

    . db block gets

       - CURRENT mode에 있는 block의 데이터를 읽은 숫자

       - TKPROF 레포트에서 current에 해당하는 값이구요.

       - current mode에서는 곧 수정될 Segment header나 block을 얻을 때 일어납니다. 

       - INSERT, UPDATE, DELETE에서 데이터 쿼리부분이 아니라 

          수정될 값을 읽을 때 나타나는 숫자입니다.

       - select문일 경우에는 Full table scan일 경우 Segment header를 읽을 때 나타납니다. 

    . consistent gets

       - consistent mode에서 db block read를 수행한 숫자

       - TKPROF 레포트에서 query에 해당하는 값입니다. 

       - consistent라는 말은 read consistency와 관련이 있는데 즉 읽기 일관성이 보장되는 상황에서 읽는 숫자라는 것입니다. 

       - 데이터를 수정하지 않고 단지 읽기만 하기때문에 lock이 발생하지 않습니다. 

    . physical reads : disk에서 읽은 block수

    . redo size : 

    . bytes sent via SQL*Net to client : 

    . bytes received via SQL*Net from client : 

    . SQL*Net roundtrips to/from client : 

    . sorts (memory)  : memory에서 정렬 수

    . sorts (disk)       : disk에서 정렬 수

    . rows processed : 조회한 record수

=========================================================== 

1.EXPLANIN PLAN

  - PLAN_TABLE

    . oracle 10g 이전 버전

    . 관리자가 생성해야 됨

    . 생성파일 : C:\app\user\product\11.2.0\dbhome_1\RDBMS\ADMIN\??.sql

  - SYS.PLAN_TABLE$ : oracle 10g이후 버전 부터는 자동생성되어 있음.

  - DBMS_XPLAN.DISPLAY('테이블명', '문장ID', '형식')

    . package를 통해서 실행계획을 볼 수 있음

    . 실행계획테이블에 저장된 실행계획을 조회

    . DISPLAY : 예측 실행계획을 보여줌.

    . DISPLAY_CUSOR : 실측한 실행계획을 보여줌.

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

      구분            형식               설명

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

    . DISPLAY :     BASIC     Operation id, operateion name, option등 가장  기본적인 최소한의 정보를 출력

                      TYPCIAL   기본설정 값, 실행계획에서 얻고자 하는 대부분의 정보를 제공

                                   BSIC + ROWS, BYTES, OPTIMIZER COST, PARALLEL, PREDICATE등

                      SERIAL     TYPICAL과 동일(PARALLEL 제외)

                      ALL         사용자레벨에서 최대의 정보를 제공

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

     . DISPLAY_CUSOR  ALLSTATS   실제 액세스한 행 수와 수행시간과 Read, Write정보제공

                                           (누적된 값 제공)

                            ALLSTATS LAST   마지막 정보만 제공

                            ADVANCED ALLSTATS LAST  DISPLAY_CURSOR에서 지원하는 모든정보

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

2. EXPLANIN PLAN 수행

  - SCOTT>explain plan

             set statement_id = 'FULL_EMP'

             for

             SELECT * FROM EMP;

  - PLAN 테이블에 'FULL_EMP' id 로 실행계획을 저장

3. EXPLAN 내역 보기

   - 아래 sql문장을 파일로 저장 후 수행하면 됨

     수행시 statement_id를 요구함 'FULL_EMP' 를 입력하면 됨

   - 

   COL PLAN FORMAT A30

   --SET VERIFY OFF

   ACCEPT statement_id PROMPT "문장 id 입력:"

   SELECT cardinality "ROWS",

             lpad('', level-1) || operation || '' || options || '' || object_name "plan"

      FROM PLAN_TABLE

   CONNECT BY prior id = parent_id

       AND prior statement_id = statement_id

    START WITH id=0

       AND statement_id = '&statement_id'

   ORDER BY id;

  - COL PLAN FORMAT A30 : PLAN 컬럼의 자리수를 30자리까지 조회 시켜줌.

  - SET VERIFY OFF  : 수행 후 '구', '신'이라는 목록이 안 보임

4. DBMS_XPLAN.DISPLAY('테이블명', '문장ID', '형식')

   - 실행계획테이블에 저장된 실행계획을 조회

   - SELECT *

       FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', 'FULL_DEPT', 'TYPICAL'));

5. SQL*TRACE

   - 사용자의 특정한 작업을 볼 수 있음

   > show parameter trace

   - 결과 : sql_trace : FALSE  로 되어 있음.

            TRUE : 현 db에서 수행되는 모든 sql문장 수행에 대한 실행계획을 모두 생성함

  - 현 session만 sql trace 수행

  - 권한 필요 ( SYS>grant alter session to scott; )

  SCOTT>ALTER SESSION SET SQL_TRACE=TRUE;     --현 Session을 기준으로 지금부터 수행된 sql에 대한 Trace 정보를 생성 시작

  SCOTT>SELECT EMPNO, ENAME, SAL FROM EMP;   --sql수행 하면 실행계획테이블에 실행계획이 저장됨

  SCOTT>SELECT EMPNO, ENAME, SAL FROM EMP WHERE EMPNO > 50; 

  - 수행 후 SQL TRACE 정보는

    : C:\app\user\diag\rdbms\orcl\orcl\trace\ 폴더 하위에 생성 됨

  - trace 파일 내용보기

   . 단순 notepad등으로 보면 너무 지저 분함

   . cmd 창에서 아래 처럼 수행하면 trace파일을 일정한 format(이쁘게 ^^)으로 test.txt 파일로 만들어줌.

   . sys=no : 실행계획 결과에서 Statistics 목록에서 recursive calls에 대한 정보는 생성안함.

   C:\tkprof orcl_ora_4336.trc test.txt  sys=no  

   C:\notepad test.txt

6. V$ dynamic performace view

   - 배우지 않았음.

(참고 : Berryz WebShare  (파일공유))

--sql trace

SELECT d.value || '/' || lower(c.instance_name) || '_ora_' || b.spid || '.trc'  "User Trace File"

FROM v$session a, v$process b, v$instance c, v$parameter d,

          ( select * from v$mystat where rownum = 1 ) e

WHERE a.paddr = b.addr

AND a.sid = e.sid

AND d.name = 'user_dump_dest' 

/