DB 버퍼 캐시 내에서 데이터 블록을 해싱하기 위해 사용되는 키 값은 DBA(데이터 블록 주소)이다. 조회문을 던지면 원하는 데이터를 찾기 위해서 필요한 DBA를 해싱하여 해시 버킷을 찾는다. 해시 체인에 연결된 원하는 데이터의 버퍼 헤더를 찾아 버퍼 블록으로 가서 값을 읽는다. 만약 해시 체인에 원하는 버퍼 헤더가 없으면 캐싱된 데이터가 없다는 뜻이기 때문에 버퍼 헤더를 생성하고 디스크에서 데이터를 읽으면서 버퍼 헤더와 연결된 버퍼 캐시에 데이터를 저장하고 완료하면 다시 버퍼 캐시에서 데이터를 읽어 결과를 사용자에게 반환한다.
버퍼 해더가 LRU 체인에 연결되어 있다. 빈 공간이 필요해지면 LRU 체인에서 가장 오래되고 사용되지 않은 버퍼 헤더를 찾아 연결된 버퍼 캐시를 삭제하고 버퍼 체인에서 버퍼 헤더를 해제한다.
데이터를 찾기 위해 DBA를 해싱하고 해시 버킷에서 cache buffers chains 래치로 해당 버킷에 연결된 해시 체인을 다른 사람이 읽거나 수정하지 못하도록 막는다. 해시 체인과 버퍼 헤더를 통해 버퍼 캐시에 접근할 때 버퍼 해더에서 버퍼 Lock을 획득해서 shared lock이나 exclusive lock을 설정한다. 만약 exclusive lock 상태면 버퍼 lock 대기자 목록에 등록하고 cache buffers chains 래치를 해제한다. 따라서 cache buffers chains 래치를 해제하는 시점은 버퍼 해더에서 버퍼 lock을 획득하거나 대기자 목록에 등록한 뒤 시점이다.
share/exclusive 모드 버퍼 lock을 획득하고 작업을 마친뒤 lock을 해제하기 위해서는 버퍼 헤더를 액세스 하려는 다른 프로세스와 충돌을 방지하기 위하여 cache buffers chains 래치를 다시 획득하고 버퍼 lock을 해제한다. 버퍼 lock을 해제하고 나서 cache buffers chains 래치를 푼다.
버퍼 lock은 버퍼 Pin이라고도 표현한다.
버퍼 lock을 획득하기 위해서는 버퍼 핸들이 필요하고 버퍼 핸들은 한정된 공유 자원이기 때문에 cache buffer handles 래치를 얻어야 버퍼 핸들을 통해 버퍼 lock을 획득할 수 있다.
버퍼 Pinning은 같은 Call 내에서만 가능하다. Pinning 효과는 table access by index rowid / nl 조인(inner테이블) / index skip scan / dml(undo 블록)에서 사용된다.
Undo 세그먼트는 빠른 읽기/쓰기를 위해 블록들을 버퍼 캐시에 캐싱한다.
스칼라 서브쿼리는 기본적으로 Consistent 모드로 읽기를 수행한다. 따라서 update문에 스칼라 서브쿼리를 사용할때 일관성이 필요한 갱신일 때 스칼라 서브쿼리를 Current 모드 읽기가 되도록 해야 한다.
대기 이벤트란 DBMS 프로세스가 wait queue로 옮겨져 수면상태에 두는 것이다.
v$latch 뷰를 조회하면 get은 래치 요청 횟수며 횟수가 많아도 문제 되지 않는다. 하지만 misses는 래치를 요청했는 데 사용 중이라 곧바로 래치를 얻지 못한 횟수이며 너무 높으면 문제가 있다. spin_gets는 첫 번째 시도에서 래치를 얻지 못했지만 정해진 횟수 안에 래치를 얻은 횟수이다. sleeps는 spin_gets를 시도했지만 래치를 얻지 못하고 다시 시도하는 것이며 이때 latch free 대기 이벤트가 발생한다.
테이블에서는 PCTFREE 공간이 나중에 발생할 UPDATE를 위해 남겨두는 공간이지만 인덱스에서는 정렬이 필요하기 때문에 PCTFREE는 INSERT를 위한 공간으로 밖에 사용되지 않는다. 참고로 PCTFREE 공간은 인덱스 분할을 최소화하기 위함이지만 대개 효과가 없거나 일시적이다.
인덱스가 우측 맨 끝으로만 값이 입력되는 Right Growing 인덱스라면 PCTFREE를 0으로 설정하여 인덱스 크기를 줄일 수 있다.
TX Lock은 트랜잭션마다 하나씩 획득, TM Lock은 트랜잭션에 의해 변경이 가해진 오브젝트 수만큼 획득한다.
바인드 변수 Peeking은 첫 하드파싱할 때 바인딩값을 칼럼 분포도를 확인하고 실행계획을 세워서 이후 만들어둔 커서를 계속 사용하는 것이기에 평균적으로 인덱스 스켄이 빠른 쿼리였을때 하드 파싱단계에서 Full scan이 사용되면 성능이 오히려 떨어진다.
적응형 커서 공유는 유사한 컬럼 분포도끼리 커서를 만들어 사용하는 것이다. 단 기존 유사한 히스토그램값이 아닌 바인딩 변수가 들어오면 기존에 만들어둔 커서를 사용하고 다음번에 새로운 커서를 만들기 때문에 새로운 커서를 만들기 전 한 번씩은 성능에 영향이 있다.
커서를 공유한다는 말은 shared pool의 library cache를 공유한다는 뜻이다.
세션 커서 캐싱은 SQL문을 파싱 후 구문 분석, 라이브러키 캐시에서 커서를 찾아 포인터를 설정하는 과정을 생략시킨다. 그 이유는 Parse Call 횟수가 3보다 크면 세션 커서를 세션 커서 캐시로 옮겨서 SQL 텍스트와 공유 커서를 가리키는 포인터를 저장해 두어 사용하기 때문이다. 따라서 세션 커서 캐싱은 Parse Call 부하를 줄여주는데 많은 역할을 한다.
애플리케이션 커서 캐싱은 공유 커서 힙을 Pin 하고 메모리 공간을 PGA에 할당하는 작업을 생략시킨다. Pin 하여 반복 수행하므로 Parse Call이 한 번만 발생하는 것이다. 자바에서는 묵시적 캐싱이나 statement를 닫지 않고 재사용하면 된다.
오라클에서 Like 연산자보다 Instr 함수가 더 성능이 좋다.
사용자 정의 함수를 조건절에 사용하면 full scan일 때는 모든 row에 수행되고 index로 선두칼럼 또는 선두칼럼이 = 조건이고 그다음칼럼에서 사용되면 한번 사용되고 나머지는 필터링 순서에 따라 수행된다.
PL/SQL은 읽기 일관성이 보장되지 않기 때문에 DML문을 작성할 때는 낙관적 동시성 제어처럼 더블체크 해야 된다.
PL/SQL에서 Deterministic 키워드의 캐싱효과는 Call 내에서만 유요 하다.
Call 통계에서 disk가 100, query가 300, current가 100이면 disk에서 100개의 블록, consistent 모드로 200개의 블록, current 모드로 100개의 블록을 읽은 것이다. 따라서 총 읽은 블록 수는 400이다. (400-200-100-100=0). disk에서 읽은 블록은 다시 버퍼캐시에 쓰고 읽기 때문에 혼돈하지 말자.
테이블/인덱스 Prefetch는 익스텐트에 속한 인접한 블록들을 Prefetch 한다.
Result Cache는 shared Pool에 저장되며 /*+ RESULT_CACHE */ 힌트가 있으면 Result Cache에서 결과집합을 먼저 찾아보고 없으면 직접 쿼리를 실행해 값을 리턴하고 Result Cache에도 저장한다. 만약에 결과집합이 있으면 블록 I/O가 발생하지 않는다. 공간적 한계 때문에 SYSDATE나 바인드변수의 중복값이 많거나 DML이 자주 발생하는 테이블은 지양해야 된다. 하지만 대용량 데이터에서 작은 결과집합을 읽을 경우나 작은 결과집합을 반복적으로 읽을 때 좋다.
SQL 트레이스에서 Misses in library cache during parse와 excute가 발생했다면 SQL을 라이브러리 캐시에서 찾지 못해 하드파싱을 수행하고 실행계획을 라이브러리 캐시에 저장한 뒤 포인터를 LOC(라이브러리 오브젝트 캐시)에 담서 파싱단계를 마친다. 실행단계에서 커서를 오픈하고 LOC힙에 저장된 포인터로 찾아가 보니 핸들을 캐싱되어 있는데 힙 영역이 사라져서 실행단계에서 다시 하드파싱부터 수행하는 것이다. 해당 현상이 자주 일어나면 메모리 크기가 작거나 하드파싱이 자주 수행되어 커서 LOC힙 영역이 LRU방식으로 자주 밀려나기 때문이다.
해시 조인 시 PGA 메모리에 해시 맵이 만들어지는데 그 영역을 Hash Area라고 한다.
칼럼 히스토그램은 수집하고 관리하는 비용이 상당하기 때문에 컬럼 데이터가 분포가 편중되어 있고 바인드 변수가 아닌 상수값으로 검색되며 = 조건이 아닌 칼럼에만 사용하자.
오라클은 10g부터 22시~6시까지 통계를 수집하도록 gather_stats_job이 등록되어 있지만 통계정보가 없거나 통계정보 수집 후 DML이 많이 발생한 모든 오브젝트를 대상으로 한다.
집계 서브쿼리를 메인쿼리에 분석함수를 사용하는 형태로 변환할 때 WINDOW BUFFER 오퍼레이션이 실행된다.
뷰안에 rownum을 사용하면 merge가 안되는 것뿐만 아니라 옵티마이저가 알아서 수행하는 조건절 pushdown과 조건절 pullup이 동작하지 않는다.
조인제거 기능은 1:M 관계의 조인 조건식에서 PK/FK 제약이 설정된 있어야만 동작한다. 그게 아니면 발동하지 않기 때문에 애초에 쿼리를 잘 짜자.
PGA는 프로세스 정보 관리, UGA는 세션 정보 관리
CGA는 Call이 진행되는 동안 필요한 정보 저장, UGA는 세션 정보를 관리하기 때문에 Call을 넘어서 다음 Call까지 계속 참조되는 정보 저장
테이블이나 인덱스를 파티셔닝하면 같은 테이블이라도 물리적으로 별도의 세그먼트에 데이터가 저장된다.
파티션 키 컬럼의 Distinct Value 개수가 적으면 해시 파티셔닝으로 해도 고르게 분산되지 않을 가능성이 높기때문에 이때는 리스트 파티션으로 하는것이 좋다.
기본적으로 Range 파티션을 사용하고 대용량 트랜잭션이 발생하는 곳에 해시파티셔닝을 사용하는 것이 좋다.
보통 병렬 쿼리 작성 할때 FULL SCAN하기 때문에 스칼라 서브쿼리가 있으면 QC가 하는 일이 너무 많아져 성능이 느려진다. 따라서 가급적이면 일반 조인문장으로 변환하여 FULL+PARALLEL 방식으로 처리하면 좋다. 단 부분범위 처리가 가능하다면 스칼라 서브쿼리가 더 좋다.
MOD(15, -4) 3 / MOD(-15, 0) 0 / MOD(-15, -4) -3
LEFT나 RIGHT 조인 시도시 조인조건에 들지 않더라도 기준이 되는 테이블 로우는 다 나온다.
Unique 제약조건은 중복값 방지이지 null은 허용한다.
Restrict는 자식이 있으면 부모 제거 불가, Dependent는 부모가 없으면 자식 생성 불가하다.
윈도우 함수에서 집계 함수를 사용할때 order by가 없으면 통짜, order by가 있으면 unbounded preceding 기본값이 적용된다.
