[SQL Tuning]튜닝의 기초와 옵티마이져

튜닝의 기초와 옵티마이져

비효율적인 SQL 성능의 원인

• 오래되거나 누락된 optimizer 통계
• 누락된 access구조
• 최적 상태가 아닌 execution plan 선택
• 잘못 작성된 SQL

튜닝 방법론

• 하향식으로 진행(TOP DOWN)
• 분석
• 설계
• 구현

cursor를 할당 받는 다는 것은 PGA에서 UGA에 메모리 공간을 할당 받는다는 것이다.

SQL문 처리 단계

• 커서를 생성
• 구문 분석
• query 결과 기술
• query 출력 정의
• 변수를 바인드
• 명령문 병령화
• 명령문 실행
• query행 패치
• 커서를 닫는다.

SQL 실행 과정

• parser(구문분석)
• Transformation 전까지 진행
• syntax check
• semantics check(의미상 유효성 체크)
• 권한 확인
• private SQL area 할당
• LC(library cache)에서 shared SQL area 있는지 확인
• SQL명령문을 ASCII code값으로 변경 후, hash function을 이용해 hash value를 return 받기
• 있으면 hard parsing
• 공백, 대소문자, 주석, 객체, 힌트 조건절 비교값 - 상수, 바인드변수에 따라 실행계획이 공유되지 못한다.
• 없으면 soft parsing
• qery transformer(Optimizer: CBO-Cost Base Optimizer)
• Cost Estimator(=cost 계산기)
• Selectivity : 비율
• 0~1사이의 값을 가진다
• where dpetno = 20일 경우
• deptno의 num_distinct : 10이면 selectivity는 1/10 => 0.1
• num_distinct는 구분되는 값의 갯수이다.
• 위 조건의 전제는 균등분포이다.
• 불듄등 할 경우 histogram이 필요
• where sa > 100 and sal < 1000일 경우
• range인 경우 low value와 high value가 있으므로
• 1000-100 / range(5000(high value)-10(low value)) = 900/4990
• Cardinality : 건수
• selectivity * num_rows(user_tables)
• num_rows : 행의 갯수
• cost = (IO Time + CPU Time) / SBI(single block IO time) avg time
• IO Time = single block IO time(index scan) + multi block IO time(full table scan)
• CPU Time = filtering, 함수 수행...
• cost = 5이면 SBI를 5번 한 만큼의 일의 양을 뜻한다.
• plan generator
• row wource generator
• 실행할 수 있는 코드로 변환

==================SQL Developer==================

exec dbms_stats.gather_table_stats('SCOTT','EMP');

아래에서는 DEPTNO의 num_distinct는 3으로 출력된다.

옵티마이저 동작 제어

• 파라미터(이 값을 참고해서 동작)
• optimizer_mode = choose(~9i): 10이후로는 아래의 세개 적용
• all_rows(defalut)
• 전체 throughput(처리량)
• 전체 범위 처리
• first_rows
• first_rows_N
• response time을 중시(OLPT 환경에서 적당)
• N은 1, 20, 100, 1000
• N건의 행을 빨리 나오게 하는 실행계획이라는 의미
• optimizer_dynamic_sampling = 2(DEFAULT) : 통계 정보가 없을때, 파스단계해서 통계정보를 샘플링해서 보는 방식
• optimizer_features_enable = 11.2.0
• 버전을 설정
• optimizer_index_caching
• optimizer_index_cost_adj = 100(default)%
• 50%일경우 index cost의 비중을 반으로 줄였다는 것인데, Index scan cost가 50이고, full table scan cost가 40일때, full table scan을 선택할텐데, 50%이므로, index scan cost는 25가 되므로, index scan이 선택된다.
• db_file_multiblock_read_count
• db file의 block을 읽는 갯수

<<<<복습>>>>

HR 사용자로 접속해서 다음의 명령을 수행하여 copy_emp 테이블을 생성후 작업하세요.

SYS@orcl2> CREATE TABLE COPY_EMP

           AS

           SELECT * FROM HR.EMPLOYEES; 

Table created.

SYS@orcl2> update copy_emp

           set hire_date=to_date('2008-04-21 09:00:00', 'yyyy-mm-dd hh24:mi:ss')

           where employee_id=167;

1 row updated.

SYS@orcl2> update copy_emp

           set hire_date=to_date('2008_04_21 09:01:30','yyyy-mm-dd hh24:mi:ss')

           where employee_id=173;   

1 row updated.

SYS@orcl2> commit;

Commit complete.

1. 사번(EMPLOYEE_ID)이 200,201인 사원을 다음과 같이 표시하세요.

EMPLOYEE_ID LAST_NAME                 HIREDATE   SAL

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

        200 Whalen                    2003/09/17    $4,400.00

        201 Hartstein                 2004/02/17   $13,000.00

SYS@orcl2> select employee_id, last_name, to_char(hire_date, 'YYYY/MM/DD') hiredate, to_char(salary,'$999,999.99') sal

           from copy_emp

           where employee_id in (200,201);

2. 이름(LAST_NAME)이 KING인 사원을 다음과 같이 표시하세요.

EMPLOYEE_ID JOB_ID     MANAGER

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

        100 AD_PRES    No Manager

        156 SA_REP     146

SYS@orcl2> select employee_id, job_id, NVL(to_char(manager_id), 'No Manager') manager

           from copy_emp

           where last_name='King';

3. 입사일이 2008년 4월 21일인 사원을 다음과 같이 표시하세요.

EMPLOYEE_ID LAST_NAME                 HIRE_DATE

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

        167 Banda                     21-APR-08

        173 Kumar                     21-APR-08

SYS@orcl2> select employee_id, last_name, hire_date

           from copy_emp

           where to_char(hire_date,'DD-MON-YY') = '21-APR-08';

4. 부서번호(DEPARTMENT_ID)가 10, 20 인 사원들 중 급여가 5000보다 적게 받는 사원을 다음과 같이 표시하세요.

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID     SALARY

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

        200 Whalen                               10       4400

SYS@orcl2> select employee_id, last_name, department_id, salary

           from copy_emp

           where department_id in (10,20) and salary < 5000;

5. 매니저가 Russell(LAST_NAME)인 사원들을 다음과 같이 표시하세요.

   PHONE_NUMBER 는 앞의 통신사, 지역번호를 모두 없애고 표시합니다.

EMPLOYEE_ID LAST_NAME                 PHONE_NUMBER

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

        150 Tucker                    129268

        151 Bernstein                 345268

        152 Hall                      478968

        153 Olsen                     498718

        154 Cambrault                 987668

        155 Tuvault                   486508

6 rows selected.

SYS@orcl2> select employee_id, last_name, substr(phone_number, instr(phone_number, '.', 1, 3)+1) phone_number

           from copy_emp

           where manager_id = (select employee_id from copy_emp where last_name = 'Russell')

                 and last_name <> 'Russell';

6. Zlotkey(LAST_NAME)와 같은 급여를 받는 사원을 다음과 같이 출력하세요.

   (Zlotkey 본인은 표시하지 않습니다.)

EMPLOYEE_ID LAST_NAME                     SALARY

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

        162 Vishney                        10500

SYS@orcl2> select employee_id, last_name, salary

           from copy_emp

           where salary = (select salary from copy_emp where last_name='Zlotkey')

                 and last_name<>'Zlotkey';

7. 매니저가 아닌 사원을 다음과 같이 출력하세요.

EMPLOYEE_ID LAST_NAME

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

        162 Vishney

        133 Mallin

        136 Philtanker

        154 Cambrault

  ...

        132 Olson

        192 Bell

        113 Popp

        171 Smith

        127 Landry

        167 Banda

        130 Atkinson

        158 McEwen

        199 Grant

89 rows selected.

SYS@orcl2> select employee_id, last_name

           from copy_emp

           where employee_id not in(select distinct(manager_id) from copy_emp where manager_id is not null);

SYS@orcl2> select employee_id, last_name

           from copy_emp M

           where not exists (select 1 from copy_emp where manager_id = M.employee_id);

SH/SH 사용자로 다음을 수행하세요.

CUSTOMERS, SALES, TIMES, PRODUCTS를 사용합니다.

8. 1998년 1월 1일에 주문한 고객의 번호(CUST_ID), 이름(CUST_LAST_NAME), 주문 제품 번호(PROD_ID), 주문 제품 이름(PROD_NAME)을 다음과 같이 표시하세요.

  

  CUST_ID CUST_LAST_NAME                              PROD_ID PROD_NAME

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

      1604 Callihan                                         47 Deluxe Mouse

      1833 Salvadore                                        47 Deluxe Mouse

      3233 Peebles                                          47 Deluxe Mouse

      3929 Markerman                                        47 Deluxe Mouse

      6344 Conard                                           47 Deluxe Mouse

      7979 Newkirk                                          47 Deluxe Mouse

       513 Zoldos                                           48 Keyboard Wrist Rest

      1322 Barber                                           48 Keyboard Wrist Rest

      1832 Glidden                                          48 Keyboard Wrist Rest

      2583 Lucas                                            48 Keyboard Wrist Rest

...

   

327 rows selected.

SYS@orcl2> select c.cust_id, cust_last_name, s.prod_id, p.prod_name

           from customers c, sales s, products p

           where s.time_id = to_date('01-JAN-98', 'DD-MON-RR')

                 and s.prod_id = p.prod_id

                 and c.cust_id = s.cust_id;

SYS@orcl2> select c.cust_id, c.cust_last_name, s.prod_id, p.prod_name

           from customers c,

                (select cust_id, prod_id from sales where time_id = to_date('01-JAN-98', 'DD-MON-RR') ) s,

                products p

           where s.prod_id = p.prod_id

                 and c.cust_id = s.cust_id;