옵티마이저 통계 2 + 바인드 변수

• DB 제작시 maintenance autotask 설정에 자동으로 통계 수집
• chain_cnt : migration의 횟수
• Noworkload 통계 : 인스턴스를 진행하며 1번 수집 
• workload 통계 : 자동으로 수집
• 시스템 통계는 제대로 된 workload에서 한번만 수집해 놓음
• 테이블 통계

1. 행 수, 블록 수, 평균 행 길이, 통계 상태
2. 상태 : 마지막 통계 이후에 얼마나 변했는가

• 인덱스 통계

1. B*-트리 레벨
2. 최하위 블록 수 -> 트리 레벨과 합치면 깊이
3. 인덱스 클러스터링 요소 : 데이터를 읽을 때, 한 블럭안에 모여있어서 그것만 읽어서 결과를 볼 수 있다면 좋음

• 컬럼이 조건절에 사용되는 비중까지 고려해서 통계 수집 가능
• 통계 수집시 설정한 기본값은 저장됨
• exec dbms_stats.gather_table_stats('HR','EMPLOYEE', method_opt=>'for all columns size auto') -> 위치지정 방식과 이름지정 방식 = hybrid

• 통계 Lock

• 자동 수집을 방지 -> 변화하는 것을 원치 않음
• 주로 휘발성 테이블에 사용됨
• 통계 복원 : 과거의 통계로 되돌리는 기능

• lock 실습

1. exec dbms_stats.gather_table_stats(user,'SALES'); -> 현재 유저의 SALES 테이블의 통계 수집
2.  select table_name, stattype_locked, last_analyzed
3. from dba_tab_statistics
4. where owner='USER01' and table_name='SALES'; -> last_analyzed가 방금 시간으로 되어있음
5. exec dbms_stats.delete_table_stats(user,'SALES'); -> 현재 저장되어 있는 통계를 제거
6. 다시 통계 생성 후 lock 걸기 -> exec dbms_stats.lock_table_stats(user,'SALES');
7. lock이 걸렸을 때는 통계를 수집 못함
8. unlock -> exec dbms_stats.unlock_table_stats(user,'SALES');

• 통계 복원 실습

1. dba_tab_stats_history : 31일간의 통계가 자동으로 저장되어 있음
2. delete시 현재 통계만 지워지고 기록은 남아있음
3. dba_tab_stats_history에서 통계가 생성된 시간을 복사
4. exec dbms_stats.restore_table_stats(user,'SALES',as_of_timestamp=>'시간');

• 통계 export 및 import

1. 데이터 뿐만 아니라 통계도 함께 export, import
2. DBMS_STATS 프로시저 사용
3. create_stat_table은 통계 테이블을 생성
4. export_*_stats는 통계를 통계 테이블로 집어 넣음
5. data pump로 통계 테이블을 이동
6. import_*_stats는 통계를 데이터 딕셔너리로 이동

---

바인드 변수 사용

---

• 커서 공유 및 다양한 리터럴 값

• 라이브러리 캐시에 서로 다른 sql문장들의 실행계획들이 저장됨
• 커서 공유하지 않아 실행계획이 동일하더라도 각각 parsing

• 커서가 공유되어 parsing을 새로하지 않고 1개의 실행계획으로 처리 가능
• 한 sql 문이 반복적으로 실행되는 경우
• 실행시간이 짧은 경우
• 더 좋은 실행계획이 존재하더라도 미리 만들어진 실행계획을 사용하는 경우가 생김
• 실습

1. alter system flush shared_pool; -> shared_pool을 비움
2. alter system flush buffer_cache; -> 버퍼를 비움
3. select sql_id, sql_text, parse_calls, loads, executions, invalidations
4. from v$sql
5. where sql_text like '%CURSOR_TEST%'
6. and sql_text not like '%v$sql%'; -> parsing된 것들 확인 가능
7. select /*+ CURSOR_TEST */ * from emp where empno=101; -> where절의 숫자를 바꾸며 여러번 실행하더라도 cursor 공유가 되지않아 parsing을 매번 실행함
8. 바인드 변수를 사용하면 cursor 공유 가능 -> variable b_no number
9. exec :b_no = 7788;
10. select /*+ CURSOR_TEST */ * from emp where empno = :b_no;
11. 7788, 7900 등 값들 입력
12. 다시 v$sql을 확인해보면 하나의 parse만 존재하는 것을 확인 가능
13. parse_calls : 몇 번 시도 했는지

• 바인드 변수 피킹

• 첫 번째 문장을 parsing할 때, 바인드 변수를 확인하여 실행계획을 공유
• 오라클이 알아서 판단하여 실행하기 때문에 현실 데이터와는 동떨어진 parsing을 할 수 있음
• 현실적인 값을 반영하기 위해 사용
• 처음에 오는 값이 현실성과 동떨어진 데이터라도 그 실행계획을 그대로 사용하기 때문에 문제가 생김

• Adaptive 커서 공유

• 적응적 커서 공유
• 첫 번째 실행 계획이 현실과 동떨어진 실행계획이라고 판단되면 다시 실행계획을 세움
• 데이터의 분포도에따라 여러 실행계획을 작성해 놓고 데이터에 맞게 사용
• 조건

1. bind variable 사용
2. bind variable peeking 사용
3. histogram 정보가 제공되거나 범위 연산일때

• 뷰

1. IS_BIND_SENSITIVE : 커서가 바인드에 따른 커서인지
2. IS_BIND_AWARE : 커서가 바인드 인식 커서인지

• 예제

1. 서로 다른 실행계획, 맞는 실행계획으로 실행
2. 바인드 변수를 사용했을 때의 이야기

• 바인드 변수를 사용하지 않는 경우

1. 변수가 아닌 상수들로 sql을 반복 실행 할 때
2. exact(기본 값) : 완전히 같을 때만 공유
3. force : 바인드 변수를 사용할 때처럼 
4. similar : 바인드 변수를 사용할 때처럼 하지만 안전한 리터럴에 대한 공유만 발생
5. 상수를 쓰지만 오라클 내부에서는 바인드 변수로 바꿔서 사용
6. cursor sharing을 exact가 아닌 다른 설정들로 사용한다면 adaptive cursor sharing으로 수행될 것임