Adaptive Cursor Sharing
adaptive cursor sharing은 히스토그램이 수집되어 있어야 작동한다.
col1 열에 1 값이 1개, 10000 값이 100000개라고 하면 바인드 변수 사용시 1값은 인덱스를 타고 10000값은 FTS를 타는게 최적이다.
하지만 adaptive cursor sharing을 사용한다고 해서 처음부터 실행계획을 별도로 사용하진 않는다.
이를 테스트를 통해 확인해본다.
11.2.0.1 버전에서 진행하였다.
SQL> create table t (col1 number, col2 varchar2(1) default 'a') tablespace users;
SQL> begin
insert into t(col1) values(1);
for i in 1..100000 loop
insert into t(col1) values(10000);
end loop;
commit;
end;
/
SQL> create index t_x2 on t(col1);
SQL> select last_analyzed from user_tables where table_name='T';
LAST_ANALYZE
------------ <--통계정보 수집이 되지 않았다.
SQL> exec dbms_stats.gather_table_stats('sys',tabname=>'t',method_opt=>'for columns col1',estimate_percent=>'100');
--col1에 대해 컬럼 통계와 히스토그램을 수집한다.
SQL> col low_value for a10
SQL> col high_value for a10
SQL> col histogram for a15
SQL> col column_name for a10
SQL> select column_name, num_distinct, low_value, high_value, histogram
from user_tab_col_statistics
where table_name='T';
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE HISTOGRAM
------------------------------ ------------ ---------- ---------- ---------------
COL1 2 C102 C302 FREQUENCY
COL2 1 61 61 NONE
--col1의 histogram 열 값이 frequency로 되어있다.
--select * from user_tab_histograms where table_name='T' and column_name='COL1'; 으로 히스토그램의 자세한 정보를 알 수 있다.
바인드변수를 선언하고 1을 입력한 후 select를 진행해본다.
SQL> var val number
SQL> exec :val := 1;
SQL> select * from t where col1=:val;
SQL> col sql_text for a35
SQL> select sql_id, sql_text, child_number
from v$sql
where sql_text like 'select * from t where col1=:val';
SQL_ID SQL_TEXT CHILD_NUMBER
------------- ----------------------------------- ------------
bv63hazkv7ab1 select * from t where col1=:val 0
SQL> select * from table(dbms_xplan.display_cursor('bv63hazkv7ab1',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID bv63hazkv7ab1, child number 0
-------------------------------------
select * from t where col1=:val
Plan hash value: 844497715
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 5 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_X | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"=:VAL)
--index를 사용했음을 알 수 있다. 참고로 실제 실행계획이 아닌 예상실행계획으론 테이블 스캔이 나온다. 이때 bind peeking을 사용했음을 짐작할 수 있다.
SQL> select sql_id, sql_text,is_bind_sensitive, is_bind_aware from v$sql
where sql_text like 'select * from t where col1=:val';
SQL_ID SQL_TEXT I I
------------- ----------------------------------- - -
bv63hazkv7ab1 select * from t where col1=:val Y N
--is_bind_sensitive값은 Y이고 is_bind_aware 값은 N이다.
바인드변수값을 10000으로 바꿔서 재실행해본다.
SQL> exec :val := 10000;
SQL> select * from t where col1=:val;
SQL> select sql_id, sql_text,child_number,is_bind_sensitive, is_bind_aware from v$sqlwhere sql_text like 'select * from t where col1=:val';
SQL_ID SQL_TEXT CHILD_NUMBER I I
------------- ----------------------------------- ------------ - -
bv63hazkv7ab1 select * from t where col1=:val 0 Y N
--아직 차일드가 하나이다. is_bind_aware값은 N이다.
다시 한번 실행해본다.
SQL> select * from t where col1=:val;
SQL> select sql_id, sql_text, child_number, is_bind_sensitive, is_bind_aware from v$sql
where sql_text like 'select * from t where col1=:val';
SQL_ID SQL_TEXT CHILD_NUMBER I I
------------- ----------------------------------- ------------ - -
bv63hazkv7ab1 select * from t where col1=:val 0 Y N
bv63hazkv7ab1 select * from t where col1=:val 1 Y Y
--is_bind_aware 값이 Y인 커서가 하나 더 생성되었다.
SQL> select * from table(dbms_xplan.display_cursor('bv63hazkv7ab1',1));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID bv63hazkv7ab1, child number 1
-------------------------------------
select * from t where col1=:val
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 51 (100)| |
|* 1 | TABLE ACCESS FULL| T | 100K| 488K| 51 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1"=:VAL)
풀테이블 스캔 실행계획이 추가됨을 확인할 수 있다.
히스토그램 없이 컬럼 통계만 있을 경우 동작을 확인
SQL> exec dbms_stats.delete_column_stats('sys',tabname=>'t',colname=>'col1', col_stat_type=>'HISTOGRAM');
--히스토그램만 삭제한다.
SQL> select column_name, num_distinct, low_value, high_value, histogram
from user_tab_col_statistics
where table_name='T';
COLUMN_NAM NUM_DISTINCT LOW_VALUE HIGH_VALUE HISTOGRAM
---------- ------------ ---------- ---------- ---------------
COL1 2 C102 C302 NONE
COL2 1 61 61 NONE
--histogram 컬럼이 none으로 변경되었다.
--참고로 user_tab_histograms 컬럼에는 여전히 데이터들이 남아있다. 하지만 histogram이 none이기에 사용되지 않는다.
테스트를 위해 shared_pool도 비워준다.
SQL> alter system flush shared_pool;
System altered.
SQL> exec :val:=1;
SQL> select * from t where col1=:val;
SQL> select sql_id, sql_text, child_number, is_bind_sensitive, is_bind_aware from v$sql
where sql_text like 'select * from t where col1=:val';
SQL_ID SQL_TEXT CHILD_NUMBER I I
------------- ----------------------------------- ------------ - -
bv63hazkv7ab1 select * from t where col1=:val 0 Y N
SQL> select * from table(dbms_xplan.display_cursor('bv63hazkv7ab1',0));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID bv63hazkv7ab1, child number 0
-------------------------------------
select * from t where col1=:val
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 51 (100)| |
|* 1 | TABLE ACCESS FULL| T | 50001 | 244K| 51 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1"=:VAL)
히스토그램이 없으니 user_tab_col_statistics의 num_distict 위주로 실행계획을 생성하여 col1 값이 1인 경우가 50%, 10000인 경우가 50%로 가정하여 FTS 실행계획을 생성하였다.
컬럼과 히스토그램 수집 관련 참고 사항
1. col1 컬럼에 대한 히스토그램은 수집하지 않게 하고 나머지는 auto 수집하도록 수집하는법
2. 컬럼과 히스토그램 수집은 하지 않도록 수집하는 법
3. 컬럼 통계와 히스토그램도 같이 수집하는 방법
4. 테이블과 관련 인덱스, 히스토그램, 컬럼 통계 삭제
5. 히스토그램만 삭제하고 컬럼통계는 유지
6. 테이블에 대한 기본 수집 옵션 변경
dbms_stats.set_table_prefs('sys', 't','METHOD_OPT', 'FOR ALL COLUMNS SIZE auto, for columns size 1 col1');
END;
/
확인:
select * from user_tab_stat_prefs where table_name='T';
TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
---------- ------------------------------ --------------------------------------------------
T METHOD_OPT FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 COL1
결론
바인드피킹도 히스토그램을 사용한다.
adaptive cursor sharing은 한번은 실행을 해보고 난 뒤에 필요할 경우 실행계획을 추가로 생성한다.
'Knowledge > Perfomance Tuning' 카테고리의 다른 글
| Bind peeking/Adaptive cursor sharing의 Parameter 변경과 영향 (0) | 2016.08.08 |
|---|---|
| 통계 export/import (0) | 2015.12.29 |
| 통계복원(restore_table_stats) (0) | 2015.12.29 |
| statistics lock/unlock (0) | 2015.12.29 |
| 수동통계수집(gathering statistics manually) (0) | 2015.12.29 |
