[성능고도화1]바인딩 변수 &세션,어플리케이션 커서 캐시

1. 바인드 변수의 부작용과 해법

1.1. 바인드 변수 사용 시

  - 최초 수행 시 : 최적화를 거친 실행계획을 캐시에 적재(하드 파싱)

  - 실행 시점  : 캐시의 실행계획을 그대로 가져와 값을 다르게 바인딩하면서 반복 재사용(소프트파싱)

  - 바인딩 시점 (최적화 이후인 실행시점)

  - SQL 최적화 시점에 조건절 컬럼의 데이터 분포도(컬럼 히스토그램) 활용을 못함(부작용)

  - 옵티마이저는 평균 분포를 가정한 실행계획을 생성

  - 컬럼의 데이터 분포가 균일 시 문제가 없음, 그렇지 않을 시 시행 시점에 바인딩 되는 값에 따라 최적이 아닌 실행계획 수행가능(부작용)

  - 등치(=) 조건이 아닌 부등호나 범위기반 검색 조건 시 고정 된 규칙을 사용하여 더 부정확한 예측의 실행계획 생성가능(부작용)

1.2 바인딩 변수 사용에 따른 계산 방법 

  - 계산식 : 카디널리티(출력 예상 건수) = 선택도 x 전체 레코드 수

  - 표에 따라 변경되는 선택도 적용 계산 : 1~4번 선택도 5%, 5~8번 선택도 0.25%

[표]

no	범 위 조 건	선택도	no	범 위 조 건	선택도
1	번호 > : NO	5%	5	번호 between :NO1 and :NO2	0.25%
2	번호 < : NO	5%	6	번호 > : NO1 and 번호 <= :NO2	0.25%
3	번호 >= : NO	5%	7	번호 >= : NO1 and 번호 <= :NO2	0.25%
4	번호 <= : NO	5%	8	번호 > : NO1 and 번호 < :NO2	0.25%

[SQL]

/*테이블 생성*/
CREATE TABLE t
AS SELECT ROWNUM NO FROM dual CONNECT BY LEVEL <= 1000;

/*T테이블의 모든 컬럼 분석*/
ANALYZE TABLE t COMPUTE STATISTICS FOR TABLE FOR ALL COLUMNS;

/*실행계획 확인*/
EXPLAIN PLAN FOR SELECT * FROM t WHERE NO <= :NO;

SELECT * FROM TABLE(dbms_xplan.display(NULL,NULL,'basic rows'));

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

/*1-4번에 해당하는 조회조건의 결과는 5%이다 */

/*실행계획 확인*/
EXPLAIN PLAN FOR SELECT * FROM T WHERE NO BETWEEN :NO1 AND :NO2;

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

| ID  | OPERATION          | NAME | ROWS  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |
|   1 |  FILTER            |      |       |
|   2 |   TABLE ACCESS FULL| T    |     3 |

/*5-8번에 해당하는 조회조건의 결과는 5%이다 */

1.3 바인드 변수 Peeking(몰래 엿보기) 

  - 바인드 변수의 부작용을 극복하기 위해 9i부터 도입. SQL 첫 번째 수행 시 하드파싱 될 때 바인드 변수 값을 살짝 훔쳐보고, 그 값에 대한 컬럼 분포를 이용해 실행계획 결정. 다른 DBMS에서도 같은 기능 제공 (ex. SQL Server 의 'Parameter Sniffing') sniff : 냄새를 맡다, 냄새로 알아채다

[예제1]

  - 전제조건 : 서울시 : 50%, 경기도 : 20%, 충청도, 전라도, 경상도, 강원, 제주 : 10% 미만

  - 상황1 : 쿼리를 처음 수행되는 시점에 바인드 변수의 값이 선택도가 높은 데이터(서울시, 경기도)일 경우 옵티마이저가 Full Scan 실행계획 수립

Full Scan 실행계획이 캐시에 적재. 캐시에 실행계획이 남아있는 동안 선택도가 낮은 데이터(제주도, 강원도)의 쿼리 수행도 Full Scan 처리

  - 상황2 : 쿼리를 처음 수행되는 시점에 바인드 변수의 값이 선택도가 낮은 데이터(제주도)일 경우 옵티마이저가 Index Range Scan 실행계획 수립

Index Range Scan 실행계획이 캐시에 적재. 캐시에 실행계획이 남아있는 동안 선택도가 높은 데이터(서울, 경기도)의 쿼리 수행도 Index Range Scan 처리. 처음 쿼리 수행에 따라 캐시에 적재된 실행계획이 달라 사용자가 느려진 성능으로 불편함. 해당 쿼리 수행빈도가 낮아 캐시에서 자주 밀려날 경우(LRU 알고리즘) 하루에도 실행계획이 수시로 변경

  

1.3.1 Peek 문제점 

  - Explain Plan 실행계획 확인 시 바인딩 값이 없어서 peeking 불가능 .평균 분포를 가정한 실행계획으로 그 실행계획의 결과를 가지고 배포한 SQL이 실제 실행시점에서 바인드 변수 Peeking을 발생하여 다른 방식으로 수행 가능

 

1.4 적응적 커서 공유(Adaptive Cursor Sharing) - 바인드 변수 Peeking 부작용 개선을 위해 11g에 추가된 기능

  - 입력된 바인드 변수 값의 분포에 따라 다른 실행계획 사용

  - 쿼리 처음 수행 시점 선택도가 높은 '서울시' 입력  -->테이블 Full Scan 실행계획 수립 (1번 커서 생성)이후 '서울시' 입력의 쿼리가 재실행시 1번 커서 반복 재사용. 선택도가 매우 낮은 '제주도'입력 시 컬럼 히스토그램 확인 --> 인덱스를 이용하는 새로운 실행계획 생성(2번 커서 생성)

  - '서울시' 와 유사한 선택도의 '경기도' 입력 시 히스토그램 확인 --> 1번 커서 사용

  - '제주도' 와 유사한 선택도의 '강원도' 입력 시 히스토그램 확인- -> 2번 커서 사용

  - 기본적으로 조건절 컬럼에 히스토그램이 생성되어있어야 적응적 커서 공유 작동. 현재 입력된 바인드 값을 처리할 커서의 캐싱 유무 확인 -> 캐시에 해당 커서가 없을 시 하드파싱을 통해 새로운 실행계획 생성->새로운 커서가 기존 캐싱 된 커서의 실행계획과 같을 시 그 중 하나만 사용, 나머지는 버림 (중복 실행계획 커서 생성 방지)

1.4.1 적응적 커서의 문제점

 - 위의 예제를 보면 2번에 해당되는 '제주도' 처음 입력 시 새로운 실행계획(2번 커서)을 바로 만들지 않고 기존의 커서(1번 커서)를 그대로 사용해보고 성능이 나쁘다고 판단될 시 Bind Aware 모드로 전환 되어 새로운 실행계획(2번 커서)을 생성함. 

1.4.2 예외적으로, Litreal 상수값 사용

  - 조건절 컬럼의 값 종류(Distinct Value)가 소수 일 시 바인드 변수보다 Litreal 상수가 나을 수 있음. 하드파싱 부하 미미, 옵티마이저가 더 나의 선택을 할 가능성이 있음, 범위 검색 시 최적화 측면에서 유리함

1.5. 세션 커서 캐싱

  - 소프트 파싱. 즉, 파싱 된 SQL구문을 또 다시 분석하지 말고, 공유함으로 하드 파싱의 횟수를 낮춰서 효율을 올릴수 있다. 쿼리 수행 후 커서를 닫고, 세션 커서가 할당 받은 메모리 및 해당포인터는 해제된다. 커서 오픈을 위한작업, library cache래치를 획득하여 라이브러리 캐시를 탐색하는 등의 작업이 발생하며, 이것은 부담스러운 작업이다. 결국, 소프트 파싱을 해도, SQL수행횟수가 많아지면 경합이 발생하게 된다. 좀 더 부하를 줄이기 위해서는( 3작업도 최소화하려면）, 소프트 파싱 이외에도 세션커서를 공유해야 한다.

 

1.5.1 세션커서의 장점

  - SQL문을 파싱, 구문분석하고 라이브러리 캐시에서 커서를 탐색하는 과정 감소. (CPU사용량감소). 소프트 파싱 과정에서 발생하는 래치 요청 횟수를 감소

  - 세션 커시 캐싱 기능은 Parse Call을 대체하기보다 Parse Call 부하를 감소시키는 기능으로 이해해야 한다.

1.5.2 세켠커서 파라미터 및 컬럼

  - session_cached_cursors  캐싱할 커서의 갯수. 0보다 큰값을 설정하면 Parse Call이 발생 할 때 마다 세션커서캐시를 먼저 살펴봄.

  - users_opening (v$sql항목)  공유커서를 참조하고 있는 세션커서의 수.

  - users_executing (v$sql항목)  해당 SQL을 현재 실행 중인, 즉 커서가 열려있는 세션커서의 수를 보여준다

   

1.6 어플리케이션 커서 캐싱

  - 어플리케이션 커서 캐싱이란? 세션 커서 캐싱을 이용 하면 SGA이 공유 커서를 빠르게 찾아서 커서를 오픈 할 수 있다. 그렇지만, 공유 커서 힙을 Pin 하고 실행에 필요한 메모리 공간을 PGA에 할당하는 등의 작업은 반복하게 한다. 애플리케이션 커서 캐싱을 이용하여 이 과정마저 생략하는 것이 가능하다.

[SQL]

/*패턴1　： 바인드 변수를 사용하지 않을 경우*/
select /* no_binding */ 1,1,'test',a.* FROM scott.emp a WHERE a.ename LIKE'W%';
select /* no_binding */ 2,2,'test',a.* FROM scott.emp a WHERE a.ename LIKE'W%';
select /* no_binding */ 3,3,'test',a.* FROM scott.emp a WHERE a.ename LIKE'W%';
select /* no_binding */ 5000,5000,'test',a.* FROM scott.emp a WHERE a.ename 	LIKE 'W%;
결과 : 하드파싱이 대입값만큼 발생 (1～5000

/*패턴2 : 바인드 변수를 사용、커서캐싱을 하지않을 경우*/
select /* no_binding */ 1,1,'test',a.* FROM scott.emp a WHERE a.ename LIKE'W%';
select /* no_binding */ 2,2,'test',a.* FROM scott.emp a WHERE a.ename LIKE'W%';
select /* no_binding */ 3,3,'test',a.* FROM scott.emp a WHERE a.ename LIKE'W%';
select /* no_binding */ 5000,5000,'test',a.* FROM scott.emp a WHERE a.ename LIKE 'W%;

/*패턴3 : 커서를 닫지 않고 재사용할 경우*/

select /* no_binding */ ?,?,?,a.* FROM scott.emp a WHERE a.ename LIKE 'W%'

/*결과 : 즉, 하드파싱이 한번만 일어난다.또한, 커서를 루프가 종료 후에 닫고 있다. 이것을 애플리케이션 커서라 한다.*/

Parse Count : 1
Execute Count : 5000
Fetch Count : 5000

1.7 Static vs. Dynamic SQL

1.7.1 Static SQL

  - SQL 문을 string 형 변수에 담지 않고 코드에 직접 기술한 경우(Embedded SQL). EX) select * from emp where empno=:empno;

1.7.2 Dynamic SQL

  - SQL 문을 string 형 변수에 담아 기술한 경우(Embedded SQL). EX) char select_stmt 50 = "select * from emp where empno=:empno";