song-ps

snippet

8 min read

Sort 수행과 소트 통계(sorts memory / sorts disk)

정렬 대상 집합을 처리할 때 오라클은 우선 PGA의 Sort Area에서 정렬을 시도한다. 메모리 내에서 정렬이 끝나면 성능 부담이 적지만, Sort Area가 부족하면 임시 세그먼트를 TEMP 테이블스페이스에 만들고 중간 정렬 결과를 디스크에 기록/재읽기 하면서 디스크 소트가 발생한다. 이 순간 성능이 크게 나빠진다.

소트 규모에 따라 다음으로 구분한다.

  • Optimal Sort: Sort Area 내에서 정렬 완료

  • Onepass Sort: 디스크에 한 번만 기록하고 마무리

  • Multipass Sort: 디스크에 여러 번 기록/재읽기(가장 비쌈)

AutoTrace/통계에서:

  • 실행계획에 나타난 소트 오퍼레이션 개수 ≈ sorts (memory) + sorts (disk) 합과 대응되는 경향이 있다.

  • 어떤 소트가 메모리로 끝났는지/디스크로 갔는지는 sorts (disk)가 증가하는지로 확인한다.

Top-N / Stopkey와 인덱스 구성

빠른 응답이 목표(페이지 처리 등)라면 부분범위 처리가 가능해야 한다.
ROWNUM <= N 같은 Stopkey 패턴과 인덱스 정렬을 이용하면 소트 부하를 줄이면서 상위 N건만 빠르게 뽑는 실행이 가능해진다.

(마지막 구간) 작업로그 예시: FAIL vs CMPL의 차이(원문 취지 복원)

원문은 :JOB_ST_CD 바인드 값이 보통 'FAIL'인지 'CMPL'인지에 따라 최적 인덱스 전략이 달라진다는 사례를 말한다.

  • 'FAIL'처럼 극히 소수(예: 0.7%) 인 값이면 결과집합이 매우 작아서, 굳이 “소트 생략용 인덱스”를 과하게 구성하기보다 조인 방식/드라이빙 집합/불필요 조인 제거가 더 중요해질 수 있다.

  • 'CMPL'처럼 대부분(예: 99.8%) 인 값이면, Top-N/Stopkey로 50건만 뽑는 상황에서
    작업상태코드 + 작업실행ID(역순) 같은 구성이 매우 효과적일 수 있다.
    작업실행ID가 시간 증가(시작일시와 유사한 증가)를 띠면, 역순으로 읽으면서 “오늘 데이터” 조건을 만족하는 레코드를 상단에서 빨리 만나 Stopkey로 멈출 확률이 높기 때문이다.

47. 부분범위 처리와 Fetch(Array Fetch, Array Size)

프로세스가 쿼리 결과집합을 한 번에 모두 전송하지 않고, 클라이언트의 요청이 올 때마다 일정량씩 나누어 전송하는 방식을 부분범위 처리(Partial Range Processing) 라고 한다. 이때 “요청이 올 때마다”의 단위가 Fetch Call 이다.

  • 서버 프로세스는 한 번 Fetch로 지정된 양을 전송한 뒤에는, CPU를 OS에 반환한 상태로 다음 Fetch Call 을 기다린다.

  • 따라서 클라이언트가 “조금만 읽고 멈출 수 있는” 구조(예: 페이징/Top-N/Stopkey 등)라면, 빠른 응답속도를 얻을 수 있다.

Array Size의 의미

부분범위 처리는 오라클만의 개념이 아니라, DB가 결과를 “일정량씩 나누어 전송”하는 구조라면 공통적으로 활용할 수 있다. 오라클에서는 보통 Array Fetch(배열 Fetch) 로 구현되며, 이때 핵심 설정이 Array Size다.

  • Array Size를 작게 설정할수록

    • Fetch Call 횟수가 늘고(왕복이 잦아짐)

    • 서버는 “조금씩 자주” 전송한다.

  • Array Size를 크게 설정할수록

    • Fetch Call 횟수가 줄고

    • 한 번에 더 많은 Row를 전송한다.

“한 블록을 여러 번 나눠 읽는 현상”

Array Size가 너무 작으면 다음과 같은 비효율이 생길 수 있다.

  • 어떤 Fetch Call에서 마지막 블록을 읽다가 “아직 Array Size만큼 채우지 못한 상태로” 전송이 끝나면,

  • 남은 Row는 다음 Fetch Call에서 다시 읽어야 하므로,

  • 한 블록을 여러 번에 걸쳐 나눠 읽는 현상이 발생할 수 있다.

반대로 Array Size를 크게 하면 이 현상이 줄어든다.

요약: Array Size를 작게 하면 응답은 빨라 보일 수 있지만(Fetch가 자주 오니까), Fetch/전송/재읽기 오버헤드가 커질 수 있고, 크게 하면 Fetch 횟수는 줄지만 한 번에 더 가져오므로 “조금만 보고 멈추는” UI에서는 오히려 불리할 수 있다.

49. Multiblock I/O 단위와 Full Table Scan의 I/O Call

Full Table Scan에서 I/O는 보통 Multiblock I/O 로 수행된다.

  • Multiblock I/O 단위를 작게 설정할수록 I/O Call 횟수는 늘고

  • Multiblock I/O 단위를 크게 설정할수록 I/O Call 횟수는 줄어든다

다만 익스텐트(extent) 경계에서 다음 같은 이유로 I/O Call이 더 늘 수 있다.

  • 한 익스텐트의 마지막 블록을 읽다가,

  • 다음 익스텐트로 넘어가야 하는데

  • 아직 Multiblock 단위만큼을 채우지 못한 상태에서 경계를 만나면,

  • 결과적으로 I/O Call이 더 많이 발생할 수 있다.

즉, Multiblock 단위만 키운다고 해서 “항상 선형적으로 Call이 줄어든다”는 식으로 단순화하면 안 되고, 익스텐트 크기/배치/경계까지 고려해야 한다.

87. “조인 대신 코드명/상품명/고객명 조회” 관련 포인트(요지 복원)

코드명, 상품명, 고객명 같은 “명칭 테이블”을 조회할 때, 조인을 무조건 피하려고(예: 함수/서브쿼리/캐시 등) 바꾸면 오히려 부하로 인해 성능이 더 느려질 수 있다는 취지다.

(이 문단은 앞쪽 내용이 일부 손상돼 있어서, 원문 의도는 “부분범위/Fetch 단위 최적화와 함수/조인 선택이 충돌할 수 있다” 정도로 복원 가능해.)

Graph View