소트 수행 원리

Sort area

할당 방식

• Sort 수행하면서 공간이 부족 할 때 마다 Chunk단위(DB Block Size) 로 할당

참조 파라메터

• workarea_size_policy: 세션마다 사용하는 소팅 영역 크기, Default: Auto
• sort_area_retained_size: 데이터 정렬 끝낸 후 유지할 소트 영역 크기(결과집함 Fetch, 다음 수행 단계로 결과집합 전달하기 전까지 유지)
• 이 크기 초과 시 Disk(TEMP TBS) 저장 후 Fetch 시 다시 읽음
• Default: 0 (결과집합 전체를 유지)

Sort Area 종류

• PGA - 서버 프로세스가 할당받는 공간
  • 프로세스 종속적인 고유 데이터 저장 공간
  • 타 프로세스와 공유하지 않아 Latch 없어 SGA 보다 빠름

• UGA - 각 세션이 할당받는 공간
  • 전용 서버 방식일 경우 PGA 내부 존재(공유일 경우 SGA)
  • 마지막 Row Fetch 시 할당공간 반환 됨

• CGA(Call global area)
  • 한번의 DB Call 시에 사용되는 공간, 끝나면 바로 PGA 반환(다음 Call 에도 계속 참조되는 데이터는 UGA 로)
  • Parse, Execute, Fetch call 시에 매번 할당됨(Recursive call 일 경우도 동일)

Sort area 할당 위치

• Insert, Update, Delete - UGA
  • Execute call 이 끝나면 모든 데이터 처리 완료하고 커서가 닫히기 때문에 Call 을 넘어서 저장 할 필요 없음

• SELECT
  • 중간 졍렬: CGA(처음 Fetch call에서만 사용하므로)
  • 최종 정렬: UGA(계속 이어지는 Fetch call 에서 사용해야 하므로 UGA 할당)
  • sort_area_retained_size 제약 있으면 CGA에서 정렬 후 이 사이즈만큼의 공간을 UGA 로...
  • 이후 Fetch call 에서 Array 단위만큼 조금씩 전송

소트 수행 분류

메모리(PGA) 처리 가능 여부 따른 분류

• Memory Sort(Interal Sort)
• Disk Sort(External Sort)

소트 수행 상 분류

• 양이 많을 때는 중간 소팅 결과값을 TEMP TBS 에 저장 후 중간 결과값을 머지 해서 최종 결과 생성
• Sort run - 임시 저장한 중간 결과 값

Optimal Sort : Memory Sort

Onepass : Disk Sort

• 소트 영역이 TEMP TBS 에 저장한 각각의 Sort Run 에서 하나의 Chunk 씩 읽을 수 있으면 추가 Disk I/O 없이 단발성 Disk Sort 만으로 가능함

Multipass : Disk Sort

• 각각의 Sort Run 에서 Chunk 하나씩 빼서 읽는 것도 안되면 Disk 에서 읽은 데이터를 다시 Disk 에 정렬하는 작업을 반복

소트 수행 측정

Trace

• Statistics 트레이스 통계에서 sort(memory), sort(disk) 항목 참조하면 됨
• Physical read >= Consistance gets + db block gets: Trace 의 Disk 블록 수가 Memory 블록 수를 초과할 수 없으나, Sort I/O 까지 Disk I/O 로 계산 됨
• Buffer 에 데이터가 다 올라와 있어도 Sort로 인한 Disk I/O 발생 가능
• Sort I/O 는 Disrect path 이므로 일반적으로 가벼움

소팅 참조 가능한 뷰

• v$sysstat (sesstat, mystat)
• v$sort_segment
• v$sort_usage
• v$temp_extent_pool

소트 튜닝은

• 모델측면 검토
• 소팅 발생 안하게 SQL
• 인덱스 이용한 소팅 대체
• 소트 Area 사이즈 튜닝