비효율적인 엑세스 패스 식별

식별

DB의 경우 논리적 읽기 횟수로 측정가능

• 5가지 이유

1. 논리적 읽기는 CPU사용을 반영한다.
2. 논리적 읽기는 물리적 읽기를 반영한다.
3. 논리적 읽기는 직렬로 수행되어야 하는 작업이다.
4. 논리적 읽기는 SQL트레이스,동적성능뷰 에서 쉽게 얻을 수 있다.
5. 논리적 읽기 횟수는 CPU와 디스크 I/O 서브시스템에 가해지는 부하에 독립적이다.

논리적 읽기 횟수 측정방법 - dbms_xplan 패키지

• A-rows 대비 Buffers 값에 대한 비교
• buffers 은 SQL트레이스 TKPROF 유틸리티 결과의 cr 과 동일
• 단,집계함수나 다른 테이블과의 결합이 있는지를 확인하고 정확한 엑세스 패스에 대한 고려가 필요하다.

함정

읽기 일관성

• 데이터의 읽기일관성을 보장하기 위해서 current블록에 대한 언두 블록을 사용하여 데이터 블록의 consistent 사본이 런타임에 생성된다.
• 이와 같은 실행을 위해서 몇번의 논리적 읽기가 추가적으로로 더 발생한다.
• 따라서 consistent 블록의 재구성 개수에 따라서 크게 논리적 블록의 읽기 수가 크게 달라진다.

로우 프리페칭

• 논리적 읽기는 데이터베이스 엔진이 블록을 한번읽어드릴때마다 1개씩 증가한다.
• 로우 프로패칭값이 해당 데이터의 로우에 가까울 수록 논리적 읽기는 증가하고 높으면 줄어든다.
• 다만, 실제 운영시스템에 반영하기전에 해다 테스트는 운영 어플리케이션의 값과 일치시켜야 왜곡을 피할 수 있다.

원인

1. 적절한 액세스 패스가 없는 경우
2. 적절한 액세스 패스는 존재하지만 쿼리 옵티마이저가 이를 사용하지 못하는 경우
3. 테이블이나 인덱스가 파티션되어 있으나 파티션 프루닝이 불가능한 경우 , 이 경우 전체 파티션을 액세스한다.
4. 테이블,인덱스에 적절한 파티션이 안되어진 경우
5. 통계정보 부족하거나 최신이 아닐 경우, 쿼리 옵티마이져가 잘못된 예측을 할 경우
6. 쿼리 옵티마이져 자체 버그|

해결책

• SQL구문을 효과적으로 실행하기 위해서는 노리적 읽기 횟수를 줄여야 한다. 다시말해 더 적은 블록을 액세스하는 액세스 패스를 사용해야한다.
• 새로운 액세스(인덱스 등)을 추가하거나 물리적인 레이아웃(파티셔닝)을 구현해야 한다.
• 이러한 결정을 쉽게 하기 위해서 선택도(Selectivity)가 있으며 이에 따라 두가지 주요 범주로 SQL를 구문한다.

1. 약한 선택도 (테이블)
2. 강한 선택도 (인덱스)
3. 중간 선택도 (파티셔닝 테이블/인덱스)|

한 개의 로우 추출

• 모든 액세스 구조에서 rowid를 통한 한 번의 논리적 읽기가 수행된다.
• 힙 테이블에서는 적어도 두 번의 논리적 읽기가 필요한데,한번은 인덱스 읽기 , 다른한번은 테이블 읽기, 로우수의 증가에 따라 인덱스 높이가 증가하고 논리적 읽기 횟수도 증가한다.
• IOT의 경우 하나 줄어든 엑세스를 보여준다.
• 단일 테이블 해시 클러스터에서는 논리적 읽기 횟수가 로우의 수에 독립적일 뿐만 아니라, 항상 한번의 논ㄴ리적 읽기를 일으킨다.|

수천개의 로우 추출

• 인덱스가 없는 비-파티션테이블의 경우 선택도가 약한 경우에만 사용한다.
• 리스트 파티션 테이블을 읽는데 필요한 논리적 읽기 횟수는 선택도에 비래한다. 어떤 상황에서도 최소한의 논리적 읽기가 수행된다.
• 단일 테이블 해쉬 클러스터를 읽는 데 필요한 논리적 읽기 횟수는 선택도가 중간이나 높은 겨우에만 이에 비례한다.
• 인덱스를 통한 테이블 읽기는 논리적 읽기 횟수는 물리적인 분포에 따라 크게 달라진다. 따라서 선택도만으로는 액세스 패스가 데이터를 효율적으로 처리할 수 있는지 확인하기 충분하지 않다.