[1] 바인드 변수 사용 시 sql의 수행 절차

1. 최초 수행 시점에 최적화를 거친 실행계획이 캐시에 적재된다.
2. 실행시점에 공유커서를 가져와 조건값만 다르게 바인딩 하면서 반복 재사용한다.

[2] 바인드 변수 사용 시 문제점

(1) "평균 분포"를 가정하여 실행계획을 수립한다.

• 이 때 , 컬럼 분포가 균일할 경우 문제될 것은 없다.
• 컬럼 분포가 균일하지 않을 경우 실행계획은 바인딩 되는 값에 따라 최적일수도 최악이 될 수도 있다.

(2) 등치(=)조건이 아닌 "부등호"나 "범위검색"일 경우 "고정된 규칙"을 사용하여 실행계획을 수립한다.

• Cardinality : 비용 계산시 특정 엑세스 단계를 거치고 출력될 것으로 예상되는 건수이다.
  • Cardinality = 선택도(Selectivity) * 전체레코드수
• 부등호나 범위 검색 조건이 사용되면 옵티마이저는 고정된 규칙으로 선택도를 추정한다.

✅ 바인딩 조건 형태와 선택도

• 1~4번은 선택(Selectivity)를 5%로 계산하고, 5~8번까지는 0.25%로 계산한다.

번호	조건절	번호	조건절
1	번호 > :no	5	번호 between :no1 and :no2
2	번호 < :no	6	번호 > :no1 and 번호 <= :no2
3	번호 >= :no	7	번호 >= :no1 and 번호 < :no2
4	번호 <= :no	8	번호 > :no1 and 번호 < :no2

• 예를 들어 테이블에 1,000개 로우가 있다면 옵티마이저는 1~4번 조건절에 대해서는 50개 로우가 출력될 것으로 예상하고, 5~8번 조건절에 대해서는 약 3개의 로우가 출력될 것으로 예상한다.

(3) 바인드 변수를 사용하면 최적이 아닌 실행계획을 수립할 가능성이 높다.

• 바인드 변수를 사용할때 정확한 컬럼 히스토그램에 근거하지 않고 정해진 계산식에 기초해 비용을 계산한다. (바로 위에서 볼 수 있듯이...😥)
• 파티션 테이블 쿼리 시 파티션 레벨의 통계정보를 이용하지 못하고 테이블 레벨의 통계정보를 이용해야 한다.
  • 파티션 레벨의 통계정보 : ❌
  • 테이블 레벨의 통계정보 : ⭕️

(4) [예시] 바인딩 조건형태에 따른 카디널리티 예측하기

• 실제와 많이 다를 수 있다.

[예시1] 검색 Row 수 예측하기

1. 테이블 생성

   CREATE TABLE t
   AS SELECT ROWNUM no FROM dual CONNECT BY LEVEL <= 1000 ;

2. 테이블 분석

   ANALYZE TABLE t COMPUTE STATISTICS FOR TABLE FOR ALL COLUMNS;

3. 테이블 해석

   EXPLAIN PLAN FOR SELECT * FROM t WHERE no <= :no;

4. 첫번째 조회 및 조회 결과

   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic rows'));

   ===========================================
   Plan hash value: 1601196873
   
   ------------------------------------------
   | Id  | Operation         | Name | Rows  |
   ------------------------------------------
   |   0 | SELECT STATEMENT  |      |    50 |
   |   1 |  TABLE ACCESS FULL| T    |    50 |
   ------------------------------------------
   ===========================================

• 부등호 검색 조건이 적용된 카디널리티 👉 1000 X ( 5/100 ) = 50

[예시2] 검색 Row 수 예측하기

1. 테이블 생성

   CREATE TABLE t
   AS SELECT ROWNUM no FROM dual CONNECT BY LEVEL <= 1000 ;

2. 테이블 분석

   ANALYZE TABLE t COMPUTE STATISTICS FOR TABLE FOR ALL COLUMNS;

3. 테이블 해석

   EXPLAIN PLAN FOR SELECT * FROM t WHERE no BETWEEN :no1 AND :no2;

4. 두번째 조회 및 조회 결과

   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic rows'));

   ===========================================
   Plan hash value: 1322348184
   
   -------------------------------------------
   | Id  | Operation          | Name | Rows  |
   -------------------------------------------
   |   0 | SELECT STATEMENT   |      |     3 |
   |   1 |  FILTER            |      |       |
   |   2 |   TABLE ACCESS FULL| T    |     3 |
   -------------------------------------------
   ===========================================

• 범위 검색 카디널리티 👉 1000 X (0.25 / 100) = 2.5 = 3

(5) [예시] 리터럴 상수 조건에 따른 카디널리티 예측하기

• 거의 정확한 로우수를 예측한다.

[예시1] 검색 Row 수 예측하기

1. 테이블 생성

   CREATE TABLE t
   AS SELECT ROWNUM no FROM dual CONNECT BY LEVEL <= 1000 ;

2. 테이블 분석

   ANALYZE TABLE t COMPUTE STATISTICS FOR TABLE FOR ALL COLUMNS;

3. 테이블 해석

   EXPLAIN PLAN FOR SELECT * FROM t WHERE no <= 100;

4. 첫번째 조회 및 조회 결과

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic rows'));

===========================================
Plan hash value: 1601196873

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |    99 |
|   1 |  TABLE ACCESS FULL| T    |    99 |
------------------------------------------
===========================================

[예시2] 검색 Row 수 예측하기

1. 테이블 생성

   CREATE TABLE t
   AS SELECT ROWNUM no FROM dual CONNECT BY LEVEL <= 1000 ;

2. 테이블 분석

   ANALYZE TABLE t COMPUTE STATISTICS FOR TABLE FOR ALL COLUMNS;

3. 테이블 해석

   EXPLAIN PLAN FOR SELECT * FROM t WHERE no BETWEEN 500 AND 600;

4. 두번째 조회 및 조회 결과

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic rows'));

===========================================
Plan hash value: 1601196873

-----------------------------------------
| Id | Operation          | Name | Rows |
-----------------------------------------
|  0 | SELECT STATEMENT   |      |   98 |
|  1 |  TABLE ACCESS FULL | T    |   98 |
-----------------------------------------
===========================================

[3] 바인드 변수 Peeking

• 바인드 변수의 부작용을 극복하기 위해 9i 부터 peeking 기능을 도입하였다.

(1) 바인드 변수 Peeking의 특징

• Sql의 첫번째 수행시 입력된 바인딩 값을 살짝 훔쳐보고 그 값에 대한 분포도를 이용해 실행계획을 수립한다.
  • SQL Server에서는 이와 같은 기능을 Parameter Sniffing 이라 한다.

(2) 바인드 변수 Peeking의 부작용

[예시] 아파트매물과 같이 분포도가 고르지 못한 자료 조회하기

SELECT * FROM 아파트매물 WHERE 도시 = :City;

처음 실행시 '서울'로 조회할 때

1. 최초 Sql 실행시 '서울'로 조회하면 넓은 범위를 인덱스 스캔하는 것보다 풀스캔이 유리하므로 풀스캔으로 실행계획을 세운다고 가정한다!
2. 이후 어떤 값이 들어오든지 무조건 풀스캔으로 실행계획을 공유한다.
3. 후에 '제주도'나 '강원도'로 조회할 때 인덱스 스캔을 통해 빠른 성능을 발휘할 수 있음에도 불구하고 풀스캔을 하게 된다.

처음 실행시 '강원도'로 조회할 때

1. 최초 Sql 실행시 '강원도'로 조회하면 인덱스 스캔으로 실행계획을 세운다고 가정한다!
2. 이후 어떤 값이 들어오든지 무조건 인덱스 스캔으로 실행계획을 공유하게 된다.
3. 다음에 '서울' 조회시 인덱스 스캔으로 넓은 범위를 조회하게 되어 성능 저하가 발생한다.

(3) 10G에서의 Peeking

• 10G부터는 dbms_stats의 기본설정이 히스토그램을 설정할지 여부를 오라클이 판단하게끔 바뀌었다.
• 이전에 히스토그램이 있는지도 모르고 사용을 안해왔던 사용자들이 대부분이다.
• 컬럼에 대한 히스토그램이 더 많이 생성되면서 Peeking의 부작용이 더 심각하게 나타나게 되었으며 이로 인해 10g에서는 바인드 변수를 쓰지 말고 리터럴 상수로 회귀하자는 움직임도 나타났다고 한다.
• EXPLAIN PLAN 명령을 통해 확인하는 사전 실행계획은 바인드 변수값이 주어지지 않은 상태에서의 평균분포로 비용을 계산한다.
• 이 후 실행계획을 실제로 수행할 땐 변수값이 바인딩 되면서 Peeking 기능으로 인해 계산된 비용이 달라져 확인했던 실행계획과 다른 실행계획으로 풀릴 수 있다.

✅ Peeking 기능 비 활성화

ALTER SYSTEM SET "_optim_peek_user_binds" = FALSE;