AWR을 이용한 고성능 데이터베이스 튜닝
공유풀은 힙(heap)으로 불리는 OS메모리 공간으로 구성됨.
힙(heap)은 헤더와 하나이상의 메모리익스텐트로으로 구성됨.
메모리익스텐트는 지속적으로 할당과 반납이 반복되며 여러개 작은조각으로 나누어짐.
메모리 영역이 작은단위로 나누어 지는 것을 공유풀단편화(FRAGMENTATION)이라 함.
단편화로 인해 쪼개진 영역은 청크라고 불리며 프리리스트에 의해 관리됨.
한번 사용된 청크는 다시 프리리스트에 등록되기 전까지 공유풀 LRU리스트에서 관리됨
LRU알고리즘을 이용하여 청크의 재사용률을 높이기 위해서임.
공유풀에 메모리 할당과정
1. 새로운 SQL에 대해 파스 수행하려면 힙영역 새로운 빈공간 할당받아야함.
2. 프리리스트에서 필요한 크기의 프리청크 서칭
3. 프리리스트 서칭후 있으면 힙영역 할당. 없으면 LRU리스트 사용가능 청크를 서칭
4. LRU리스트에도 없으면 4031에러 발생후 SQL파스 실패
예> SQL파스를 위해 256바이트가 필요할떄
1. 쉐어드풀 래치획득후 프리리스트로 부터 256바이트의 청크를 검색.
프리리스트에서 프리청크를 검색후 할당받기까지 모든단계에서 shared pool 래치를 할당 받아야 한다.
청크의 할당 해제가 빈번히 발생 한다면 청크는 더욱 작게 쪼개져 관리되어야 할 청크의 수가 증가 한다.
프리리스트를 검색하는 시간을 증가시키고 쉐어드풀 레치에 대한 경합을 증가시켜 성능저하를 유발 하며
4031에러를 발생시킨다.
바인드 변수를 사용하면 최초 수행할 때 최적화를 거친 실행계획을 캐시에 적재하고，
실행시점에는 그것을 그대로 가져와 값을 다르게 바인딩하면서 반복 재사용하게 된다.
여기서， 변수를 바인딩하는 시점이 (최적화 시점보다 나중인) 실행시점이라는 사실을 아
는 것이 중요하다. 즉， SQL을 최적화하는 시점에 조건절 컬럼의 데이터 분포도를 활용하
지 못하는 문제점을 갖는다.
TEST
이처럼 바인드 변수를 사용할 때는 정확한 컬럼 히스토그램에 근거하지 않고 카디널리
티를 구하는 정해진 계산식에 기초해 비용을 계산하므로 최적이 아닌 실행계획을 수립할
가능성이높다.
(1) 바인드 변수 Peeking
오라클은 9i부터 바인드 변수 Peeking 기능을 도입
Peekin양 이라는 단어가 의미하듯이 이 기능은 SQL이 첫 번째 수행되면서
하드파싱될 때 함께 딸려 온 바인드 변수 값을 살짝 훔쳐 보고 그 값에 대한 컬럼 분포를
이용해 실행계획을 결정
TEST
(2) 적응적 커서 공유
이 기능이 작동하려면 기본적으로 조건절 컬럼에 히스토그램이 생성돼 있어야 한다.
그 상태에서， 옵티마이저가 바인드 변수 값에 따라 실행계획을 달리 가져갈 필요가 있다
고 판단되는 SQL 커서에 대해서 이 기능이 활성화된다. 그런 커서를 13ind Sensitive 커
서 40)' 라고 부르며， v$sql을 조회해 보면 is bind sensitive 컬럼이 γ 로 표시돼 있다.
TEST
(3) 입력 값에 따라 SQL 분리
인텍스 액세스 경로(access Path)로서 중요하고 조건절 컬럼의 데이터 분포가 균일하지 않
은 상황에서 바인드 변수 시용에 따른 부작용을 피하려면 바인딩 되는 값에 따라 실행계
획을 아래와 같이 분리하는 방안을 고려해야 한다.
TEST
이처럼 union all을 이용해 SQL을 길게 작성하면， 라이브러리 캐시 효율을 떨어뜨리
고 Parse 단계의 CPU 사용률을 높일 뿐 아니라 Execute 단계에서도 CPU 사용률을 높
이는 결과를 초래한다.
SQL어플 분기 쿼리
(4) 예외적으로， Literal 상수값 사용
게다가 배치 프로그램이나 정보계 시스템에서 수행되는 SQL은 대부분 Long
Running 쿼리이므로 파싱 소요시간이 쿼리 총 소요시간에서 차지하는 비중에 매우 낮
고， 사용빈도도낮아하드파싱에 의한라이브러리 캐시 부하를크게 염려할펼요가없다.