오라클 카디널리티 CARDINALITY
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 카디널리티 CARDINALITY[편집]
- SQL 쿼리 실행결과 출력되는 row 건수를 말함
- 전체로우수 * 선택도 = 카디널리티
- 인덱스 사용시 선택도 와 카디널리티는 중요한 기준이 됨.
- 오라클 옵티마이져가 기존 테이블이나 인덱스 통계에서 이런 수치를 가지고 실행계획을 세우게 됨
- 일반적으로 옵티마이져는 여러 인덱스가 있을때 선택도가 낮은 인덱스를 사용
- 예시
- 사원테이블의 전체 레코드수가 1000개 이고 WHERE 절에서 부서='인사팀'로 조회 해서 결과가 10개의 row 가 나온다면 선택도는 0.01이 된다
- 즉, 전체로우수 * 선택도 = 결과수 => 카디널리티가 됨.
1000 x 0.01 = 102 COST 와 카디널리티 CARDINALITY 의미[편집]
2.1 선택도 Selectivity[편집]
- Selectivity는 0.0에서 1.0 사이의 값을 가지고 있습니다.
- 0.0은 아무런 행도 행 집합에서 선택되지 않은 것이고, 1.0은 모든 행들이 선택된 것입니다.
- Selectivity는 행 집합으로부터의 행들의 일부분을 나타냅니다. 행 집합은 기본 테이블, 뷰, 조인이나 GROUP BY의 결과일 수도 있습니다.
- Selectivity는 last_name='Smith'와 같은 제한자(predicate)에 묶여 있습니다.
- 제한자는 행 집합에서 행들의 특정 수를 걸러내는 필터의 역할을 합니다.
- 그러므로, 제한자의 Selectivity는 행 집합에서 얼마나 많은 행들이 제한자 테스트를 지나가게 될 것인가를 보여줍니다.
- 만약, 통계가 존재하지 않는 다면, Estimator는 Selectivity 를 위해 내부의 기본값을 사용합니다.
- 제한자의 타입에 따라 다른 내부 기본값이 사용됩니다.
- 예를 들어, 동등 제한자(last_name = 'Smith')를 위한 내부 기본값은 범위 제한자(last_name > 'Smith') 보다 내부 기본값이 더 적습니다.
- Estimator는 동등 제한자는 범위 제한자 보다 더 적은 부분을 반환할 것이라고 예상하기 때문에 이러한 추정을 합니다.
- 그러나, 통계가 존재한다면, Estimator는 selectivity를 위한 그것들을 사용합니다.
- 예를 들면, 동등 연산에서 selectivity는 해당 컬럼의 유일 값(distinct value)의 갯수에 따라 반비례 하여 설정됩니다.
- 만약, 컬럼에 대한 히스토그램(histogram)이 사용가능 하다면, 유일 값 대신 그것을 사용합니다.
- 히스토그램은 컬럼의 다른 값의 분산도를 저장해 놓습니다.
- 분포가 불균형인 컬럼에 히스토그램을 사용하면, CBO가 더 낳은 selectivity를 결정하는데 상당하게 도움을 줍니다.
2.2 카디널리티 Cardinality 종류[편집]
- Cardinality 는 행 집합에서 행의 수를 나타냅니다.
- 여기에 행 집합은 기본 테이블, 뷰, 조인이나 GROUP BY의 결과일 수도 있습니다.
2.2.1 Base cardinality[편집]
- 기본 테이블에 행의 수입니다.
- Base cardinality는 테이블을 분석함으로써 나타날 수 있습니다.
- 통계가 가능하지 않다면, Estimator는 테이블에 할당된 익스텐트의 수를 근거로 해서 행의 수를 추정합니다.
- 예시) Base cardinality : 전체 테이블 풀 스캔을 할 경우 조회가 가능합니다.
- Query
SELECT * from TB_XXX;
- PLAN_TABLE_OUTPUT
cardinality는 2382K(2,382,000) 이고, 풀 스캔에 대한 Cost는 8834 입니다.
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2382K| 597M| 8834 |
| 1 | TABLE ACCESS FULL | TB_XXX | 2382K| 597M| 8834 |
--------------------------------------------------------------------
2.2.2 Effective cardinality[편집]
- 기본 테이블로 부터 선택되어진 행의 수를 나타냅니다.
- Effective cardinality는 기본 테이블의 행들에 연속적인 필터로써 작용하는 각각의 제한자와 함께 기본 테이블의 다른 컬럼에 정해진 제한자에 의존적입니다.
- 이것은 기본 테이블의 행 생산물로써 계산되어지고, 테이블에 정해진 모든 제한자들의 selectivity를 합칩니다.
- 테이블에 어떠한 제한자가 없다면 그것은 base cardinality와 같습니다.
- 예시) Effective cardinality : 제한자를 주었을 때 입니다.
- Query
SELECT * FROM TB_XXX
WHERE processstatus = :b1;
- PLAN_TABLE_OUTPUT
cardinality는 118K(118,000)로 줄었으며, 제한자는 모든 데이터를 읽은 각각에 대한 Filter로써만 작용했으므로 Cost는 8834 위와 같습니다.
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 118K| 29M| 8834 |
|* 1 | TABLE ACCESS FULL | TB_XXX | 118K| 29M| 8834 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TB_XXX"."PROCESSSTATUS"=:Z)2.2.3 Join cardinality[편집]
- 두 행 집합이 함께 조인되었을 때, 생산된 행의 수입니다.
- 조인은 결과에 필터로써 적용되는 제한자와 함께 두 행 집합들의 카테시안 곱입니다.
- 그러므로, Join cardinality는 조인 제한자의 selectivity에 의해 곱해진 두 행 집합의 cardinality 곱입니다.
2.2.4 Distinct cardinality[편집]
- 행 집합의 컬럼에 유일한 값의 수입니다.
- 행 집합의 distinct cardinality는 컬럼의 데이터에 근거합니다.
- 100개의 행이 있는 행 집합에서 유일한 값의 수가 20 이라면, 이 값은 20이 됩니다.
2.2.5 Group cardinality[편집]
- GROUP BY 연산 후에 행 집합에서 생성된 행의 수입니다.
- GROUP BY연산은 행 집합에서 행의 수를 줄여줍니다.
- Group cardinality는 그룹화 되는 컬럼의 distinct cardinality와 행 집합의 행의 수에 의존적으로 변경됩니다.
- 그룹화 되는 컬럼이 하나라면 그 컬럼의 distinct cardinality와 같습니다.
- 그러나, 그룹화 되는 컬럼이 두개 이상인 경우, 최대 각 컬럼의 distinct cardinality이 될 것이고, 최소로 각 컬럼의 distinct cardinality의 곱에서 행 집합의 행의 수로 제한될 것입니다.
- 결과는 총 행 집합의 행의 수를 넘지 못하므로, distinct cardinality가 가장 낮은 컬럼의 값과 행 집합의 수 사이가 될 것입니다.
2.3 Cost 비용[편집]
- 비용은 사용된 자원이나 작업의 단위로 나타납니다.
- CBO는 작업의 단위로써 disk I/O, CPU 사용, 메모리 사용을 사용합니다.
- 그래서, CBO에서 사용되는 비용은 연산을 수행하는데 사용되는 디스크 I/O의 수, CPU의 총량, 사용된 메모리의 추청된 값을 보여줍니다.
- 연산은 인덱스를 사용하여 테이블의 행들에 접근하거나, 두 테이블을 조인하거나, 행 집합을 정렬하는 등과 같은 방법으로 테이블에 접근할 수 있습니다.
- 쿼리 계획의 비용은 쿼리가 수행되고 결과를 생산할 때 초래되는 것을 예상하는 작업 유닛의 수입니다.
- 접근 경로는 기본 테이블로 부터 데이터를 가져오는데 요구되는 작업 유닛의 수를 결정합니다.
- 접근 경로는 테이블 스캔이나 fast full 인덱스 스캔, 범위 인덱스 스캔일 수 있습니다.
- 테이블 스캔이나 fast full 인덱스 스캔은 여러 블록들이 한번의 I/O 연산에 의해 디스크로 부터 읽어옵니다.
- 그러므로, 이것들의 비용은 스캔되어질 블록의 수와 multiblock read count값에 따라 달라지게 됩니다.
- 인덱스 스캔의 경우에는 B-tree의 레벨과 스캔될 leaf블록의 수, 인덱스 키의 rowid를 사용하여 페치될 행의 수에 따라서도 달라집니다.
- 행들을 페치하는 비용은 인덱스 클러스터링 팩터에 의존적입니다.
- 클러스터링 팩터가 인덱스의 속성이더라도, 클러스터링 팩터는 실제로 테이블의 데이터블록안에 유사한 인덱스된 컬럼 값의 분산도에 관계됩니다.
- 낮은 클러스터링 팩터는 개개 행들이 테이블에 더 적은 블록에 모여있다는 것을 나타냅니다.
- 반대로, 높은 클러스터링 팩터는 개개 행들은 그 테이블에 랜덤하게 블록들에 흩어져 있다는 것을 나타냅니다.
- 그러므로, 높은 클러스터링 팩터는 rowid에 의해 행들을 뽑아낼 때 범위 스캔을 하는 것은 더 많은 비용이 들 것이라는 것을 나타냅니다.
- 테이블에 더 많은 블록이 데이터를 반환하는데 필요하기 때문입니다.
- 조인 비용은 조인되고 있는 두 행 집합의 각각의 비용의 합산입니다.
- [Nested loop join] 에서 outer 행 집합에 모든 행에 대해, inner 행 집합은 조인하는 일치하는 행들을 찾기 위해 접근됩니다.
- 그러므로, inner 행 집합은 outer 행 집합의 갯수 만큼 여러번 접근됩니다.
- cost = outer access cost + ( inner access cost * outer cardinality )
- [Sort merge join] 에서 두 행 집합이 키 순서대로 이미 정렬되지 않았다면, 조인 키에 의해 정렬되어져야 합니다.
- cost = outer access cost + inner access cost + sort costs
- [Hash join] 의 경우 inner 행 집합은 메모리에 해싱됩니다.
- 그리고, 해시 테이블이 조인 키와 함께 만들어 집니다.
- Outer 행 집합으로부터의 각각의 행은 해시되고, 해시 테이블은 모든 매칭하는 행들을 조인으로써 입증합니다.
- Inner 행 집합이 매우 크다면, 그것의 일부분만이 해시 될 것입니다. 이것은 해시 파티션 이라고 부릅니다.
- cost = ( outer access cost * # of hash partitions ) + inner access cost