메뉴 건너뛰기

bysql.net

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

2012.04.22 17:55

시와처 조회 수:4812

- SQL 수행 절차

SQL Parsing > Optimization > Row-Source Generation > Execution

- 바인드 변수 사용시 

  . 최초 수행시 최적화처리후 실행 계획을 캐시에 적재

  . 실행시점에서 값을 바인딩하며 반복 재사용 => 변수를 바인딩하는 시점 = 실행 시점

     즉 최적화 하는 시점에는 조건절 컬럼의 데이터 분포도를 활용하지 못한다.

  . 평균 분포를 가정한 실행 계획을 생성한다.


  . 카디널리티(Cardinality)

    카디널리티(Cardinality) = 선택도(Selecivity) * 전체 레코드수


오후 5:41:18 SQL> create table t as select rownum no from dual connect by level <= 1000;                             

 

Table created

 

오후 5:41:35 SQL> analyze table t compute statistics for table for all columns;

 

Table analyzed

 

오후 5:41:37 SQL> 

오후 5:41:45 SQL> explain plan for select * from t where no <= :no;

 

Explained

 

오후 5:42:00 SQL> select * from table(dbms_xplan.display(null,null,'basic rows'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2153619298

------------------------------------------

| Id  | Operation         | Name | Rows  |

------------------------------------------

|   0 | SELECT STATEMENT  |      |    50 |

|   1 |  TABLE ACCESS FULL| T    |    50 |

------------------------------------------

 

8 rows selected

 

오후 5:42:02 SQL> 

오후 5:42:19 SQL> 

오후 5:42:19 SQL> 

오후 5:42:19 SQL> explain plan for select * from t where no between :no1 and :no2;

explain plan for select * from t where no between :no1 and :no2;

 

Explained

오후 5:42:20 SQL> select * from table(dbms_xplan.display(null,null,'basic rows'));

select * from table(dbms_xplan.display(null,null,'basic rows'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1401356643

-------------------------------------------

| Id  | Operation          | Name | Rows  |

-------------------------------------------

|   0 | SELECT STATEMENT   |      |     3 |

|   1 |  FILTER            |      |       |

|   2 |   TABLE ACCESS FULL| T    |     3 |

-------------------------------------------

 

9 rows selected

 

오후 5:42:20 SQL>  


 바인드 변수를 사용한 경우 연산 종류에 따른 카디널리티를 유추 하는 것을 볼수 있다.



오후 5:46:47 SQL> explain plan for select * from t where no <= 100;

explain plan for select * from t where no <= 100;

 

Explained

오후 5:46:47 SQL> select * from table(dbms_xplan.display(null,null,'basic rows'));                                   

select * from table(dbms_xplan.display(null,null,'basic rows'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2153619298

------------------------------------------

| Id  | Operation         | Name | Rows  |

------------------------------------------

|   0 | SELECT STATEMENT  |      |    99 |

|   1 |  TABLE ACCESS FULL| T    |    99 |

------------------------------------------

 

8 rows selected

오후 5:46:47 SQL> explain plan for select * from t where no between 500 and 600;

explain plan for select * from t where no between 500 and 600;

 

Explained

오후 5:46:47 SQL> select * from table(dbms_xplan.display(null,null,'basic rows'));

select * from table(dbms_xplan.display(null,null,'basic rows'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2153619298

------------------------------------------

| Id  | Operation         | Name | Rows  |

------------------------------------------

|   0 | SELECT STATEMENT  |      |    99 |

|   1 |  TABLE ACCESS FULL| T    |    99 |

------------------------------------------

 

8 rows selected

 

오후 5:46:48 SQL>  


리터럴 sql를 사용하는 경우에는 정확한 결과 건수를 추출 한다.


- 바인드 변수를 사용할 때는 컬럼 히스토그램에 근거 하지 않고 카디널리트를 구하는 정해진 계산식에 기초해 비용을 계산한다.

- 변수 값의 변화에 따라 최적이 아닌 실행 계획을 수행 할 수 있다.


(1) 바인드 변수 Peeking (SQL Server : Parameter Sniffing)

 - 바인드 변수의 부작용을 극복하기 위한  oracle 9i  부터 바인드 변수 peeking 기능을 도입

 - 첫 번째 수행 되면서 하드파싱 될때 딸려온 바인드 변수 값을 살짝 훔쳐 보고 실행 계획을 수립

 - 문제점

   . 하드 파싱된  Query가 선택도가 높은 데이철를 액세스 하고 나면 선택도가 낮은 다음 Query는 최악의 실행 계획이 된다.

   . 반대로 처음 선택도가 낮은 Query 로 하드 파싱 되고 추후 선택도가 높은  Query가 처리 되어도 최악의 실행 게획이 된다.

   . 운영중에 실행 계획이 자주 바뀌면서 성능이 들쭉 날쭉 한 것이 더 큰 문제가 될 수 있다.

- 이러한 이유로 바인드 변수 Peeking는  비활성화 시켜 놓은 상태

   ater system set "_optim_peek_user_binds"=FALSE;



(2) 적응적 커서 공유(Adaptive Cursor Sharing)

 - Peeking  기능의 부작용을 해결 하기 위해 11g에서 걔선된 기능

 - 처리 방법

    . 처음 선택도가 높은 변수 값으로 하드 파싱 => Full Scan 

    . 선택도가 낮은 값이 입력되면 Child커서 캐싱 => Index Scan 

    . 선택도가 높은 값이 들어오면 Full Scan 실행 계획으로 수행

    . 선택도가 낮은 값이 들어오면 Index Scan  실행 계획으로 수행


 - 해당 정보의 관련 View

   . v$sql_cs_statistics

   . v$sql_cs_histogram

   . v$sql_cs_selectivity


- 사용 

  . 조건절 컬럼에 히스토그램이 생성되 있어야 한다.

  .v$sql.is_bind_sensitive = 'Y' : Bind Sensitive 커서

    옵티마이저가 변수값에 따라 실행계획을 달리 가져갈 필요가 있다고 판단되는  SQL

  .v$sql.is_bind_aware = 'Y' : Bind Aware 커서 

    이전일량에 비해 많은 일량을 처리한 것으로 판단되면 bind Aware모드로 저환

    기존 커서 사용중지및 새로운 커서 생성, 선택도가 비슷한 것끼리 같은 커서 공유


- 수행 예



오전 7:05:42 SQL> select 도시,count(1) from 아파트매물 group by 도시;
select 도시,count(1) from 아파트매물 group by 도시;
 
도시     COUNT(1)
------ ----------
서울시     152345
경기도     110311
강원도        159
충청도      11475
전라도       4665
제주도        244
경상도       1222
 
7 rows selected
 


(3) 입력값에 따라 SQL 분리

 - Access path 의 중요한 컬럼의 데이터 분포가 균일하지 않은 상황에서 바인드 변수의 부작용을 피하기 위한 실행 계획 분리

예) select /*+ FULL(아파트매물) */ * from 아파트매물 where :city in ('서울시','경기도') and 도시 = :city

     union all

     select /*+ INDEX(아파트매물 IX_아파트매물) */ * from 아파트매물 where :city not in ('서울시','경기도') and 도시 = :city

     ;

 - 또다른 문제점

    oltp  상에서  union all 등에 의해 query 문이 길어지만 라이브러리 캐쉬 효율이 떨어진다.

    Application  상에서  query를 분리하여 사용하는것이 바람직하다


(4) 예외적으로 Literal  상수값 사용

 - 입력값에 따라 SQL을 분리의 한계 존재 : 값의 종류가 많은 경우, 종류가 변하는 경우 source  수정 필요

 - 값의 종류가 소수 일때는 바인드 변수보다 오히려 Literal 상수를 사용하는 것이 바람직

 



이 게시물을
목록
목록
쓰기
태그