'Knowledge/Perfomance Tuning' 카테고리의 글 목록

'Knowledge/Perfomance Tuning'에 해당되는 글 11건

2016.08.08 Bind peeking/Adaptive cursor sharing의 Parameter 변경과 영향
2016.01.02 Adaptive cursor simple test
2015.12.29 통계 export/import
2015.12.29 통계복원(restore_table_stats)
2015.12.29 statistics lock/unlock
2015.12.29 수동통계수집(gathering statistics manually)
2015.12.29 histogram 고려사항
2015.12.29 자동 통계 수집(automatic statistics gathering)
2015.12.29 시스템 통계 수집(gathering system statististics)
2015.12.19 Additional statistics

Bind peeking/Adaptive cursor sharing의 Parameter 변경과 영향

Knowledge/Perfomance Tuning 2016. 8. 8. 18:43

■ 개요

성능고도화나 이번에 나온 트러블슈팅 오라클 퍼포먼스나 오라클 공식 교재의 SQL tuning workshop등에서 adaptive cursor sharing에 대해 효과를 설명하고 있습니다.

몇몇 파라미터를 조정시에 기능이 제대로 사용되는지 확인해보려 합니다.

테스트 계기는 사이트에서 DB 파라미터를 보다 보면 아래 파라미터들이 조정되어 있는 경우를 볼 수 있었기 때문입니다.

_optim_peek_user_binds=false (기본값 true)

_optimizer_extended_cursor_sharing_rel=none (10g 없음. 11g 기본 simple)

_optimizer_extended_cursor_sharing=none (10g,11g 기본 udo)

_optimizer_adaptive_cursor_sharing=false (10g 없음, 11g 기본 true)

버그 때문에 안정화를 위해 조정해두는 경우가 있는데, 위 파라미터들이 모두 조정되었을 경우, 혹은 한개라도 조정되었을 경우 adaptive cursor sharing이 동작 하는지 알기 위해 간단히 테스트합니다.

■ 테스트 준비

1. 현재 버전, 파라미터 확인

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.4.0

--여기 적지 않았지만 PSU도 11.2.0.4.5까지 적용되어 있습니다.

SQL> select a.ksppinm, b.ksppstvl
2 from x$ksppi a, x$ksppsv b
3 where a.indx=b.indx
4 and ksppinm like '%&parametername%';
Enter value for parametername: optim_peek
old 4: and ksppinm like '%&parametername%'
new 4: and ksppinm like '%optim_peek%'

KSPPINM KSPPSTVL
---------------------------------------- ----------------------------------------
_optim_peek_user_binds TRUE

SQL>
SQL> select a.ksppinm, b.ksppstvl
2 from x$ksppi a, x$ksppsv b
3 where a.indx=b.indx
4 and ksppinm like '%&parametername%';
Enter value for parametername: cursor_sharing
old 4: and ksppinm like '%&parametername%'
new 4: and ksppinm like '%cursor_sharing%'

KSPPINM                                  KSPPSTVL
---------------------------------------- ----------------------------------------
_disable_cursor_sharing                  FALSE
cursor_sharing                           EXACT
_optimizer_extended_cursor_sharing       UDO
_optimizer_extended_cursor_sharing_rel   SIMPLE
_optimizer_adaptive_cursor_sharing       TRUE

2. 테스트 테이블, 인덱스 생성, histogram도 수집하게 하여 통계정보 수집

SQL> create table tb1(id int, col2 char(4)) tablespace users;

Table created.

SQL> create index tb1_idx on tb1(id);

Index created.

SQL> insert into tb1 select 1, 'name' from all_objects where rownum <= 100000;

13757 rows created.

SQL> insert into tb1 values(2, 'name');

1 row created.

SQL> commit;

Commit complete.

--테이블의 id 컬럼에 1의 데이터를 13757건, 2의 데이터를 1건 입력

SQL> exec dbms_stats.gather_table_stats(user, 'tb1', estimate_percent=>100, method_opt=>'for columns size 2 id', cascade=>true);

PL/SQL procedure successfully completed.

--통계정보 수집. histogram도 수집

--bind peeking이나 adaptive cursor sharing 모두 histogram이 없으면 동작하지 않기에 반드시 histogram도 수집

■ 테스트

1. Default

SQL> set line 180
SQL> col IBS for a3
SQL> col IBA for a3
SQL> col sql_text for a42
SQL> var id number;
SQL> alter system flush shared_pool;

System altered.

--커서를 비우기 위해 수행

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y N 1364719694

--bind peeking에 의해 full table scan 실행계획이 생성

SQL> exec :id := 2;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y N 1364719694

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID        SQL_TEXT                                   IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   N        1364719694
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   Y         589386258

--두번째 실행에서 새로운 실행계획이 생성. index scan

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

정상 동작은 위와 같습니다.(bind peeking -> adaptive cursor sharing)

2. _optim_peek_user_binds=false

SQL> alter session set "_optim_peek_user_binds"=false;

Session altered.

SQL> var id number;

SQL> alter system flush shared_pool;

System altered.

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> exec :id := 2;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> alter session set "_optim_peek_user_binds"=true;

Session altered.

_optim_peek_user_binds 를 false로 설정하면 adaptive cursor sharing도 사용되지 않는 다는 것을 알 수 있습니다.

사실 위 테스트로는 bind peeking이 제대로 동작 안한건지 확인할 수가 없습니다. 왜냐면 bind peeking을 안써도 bind변수를 썼기에 optimizer는 col2 컬럼이 2개의 NDV로 가정하기 때문에 full table scan을 선택하거든요.

그래서 이번 테스트만 bind 변수 순서를 바꾸어서 한번 더 해봅니다.

SQL> alter session set "_optim_peek_user_binds"=false;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL> exec :id := 2;

PL/SQL procedure successfully completed.

--id=2 로 먼저 시도해봅니다.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> exec :id := 2;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> alter session set "_optim_peek_user_binds"=true;

Session altered.

--다시 bind peeking을 키고 id=2 일때 다시 수행해봅니다.

SQL> alter system flush shared_pool;

System altered.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y N 589386258

--예상하던대로 플랜이 달라졌습니다.

3. _optimizer_extended_cursor_sharing_rel=none

SQL> alter session set "_optimizer_extended_cursor_sharing_rel"=none;

Session altered.

SQL> var id number;

SQL> alter system flush shared_pool;

System altered.

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> exec :id := 2;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id N N 1364719694

SQL> alter session set "_optimizer_extended_cursor_sharing_rel"='simple';

Session altered.

_optimizer_extended_cursor_sharing_rel 를 none으로 설정하면 adaptive cursor sharing도 사용되지 않는 다는 것을 알 수 있습니다.

4. _optimizer_extended_cursor_sharing=none

SQL> alter session set "_optimizer_extended_cursor_sharing"=none;

Session altered.

SQL> var id number;

SQL> alter system flush shared_pool;

System altered.

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y N 1364719694

SQL> exec :id := 2;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y N 1364719694

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL> alter session set "_optimizer_extended_cursor_sharing"='UDO';

Session altered.

_optimizer_extended_cursor_sharing 을 none으로 변경해도 adaptive cursor sharing은 동작합니다.

5. _optimizer_adaptive_cursor_sharing=false

SQL> alter session set "_optimizer_adaptive_cursor_sharing"=false;

Session altered.

SQL> var id number;

SQL> alter system flush shared_pool;

System altered.

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID SQL_TEXT IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y Y 1364719694

SQL> exec :id := 2;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID        SQL_TEXT                                   IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   Y        1364719694
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   Y         589386258

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
1

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID        SQL_TEXT                                   IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   Y        1364719694
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   Y         589386258

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select count(col2) from tb1 where id = :id;

COUNT(COL2)
-----------
13757

SQL> select sql_id, sql_text,is_bind_sensitive AS IBS, is_bind_aware AS IBA,plan_hash_value
2 from v$sql where sql_text like 'select count(col2) from tb1%';

SQL_ID        SQL_TEXT                                   IBS IBA PLAN_HASH_VALUE
------------- ------------------------------------------ --- --- ---------------
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   Y        1364719694
4q3p69cucf7m4 select count(col2) from tb1 where id = :id Y   Y         589386258

예상하기론 optimizer_adaptive_cursor_sharing을 false로 변경하면 adaptive cursor sharing 기능이 사용되지 않을 줄 알았는데 오히려 더 적극적으로 사용됩니다.

마치 bind_aware 힌트를 사용한 것과 같은 효과를 가집니다.

저작자표시 비영리 변경금지

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

Adaptive cursor simple test (0)	2016.01.02
통계 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

Posted by neo-orcl

Adaptive cursor simple test

Knowledge/Perfomance Tuning 2016. 1. 2. 19:36

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 수집하도록 수집하는법

exec dbms_stats.gather_table_stats('sys',tabname=>'t',method_opt=>'FOR ALL COLUMNS SIZE auto, for columns size 1 col1');

2. 컬럼과 히스토그램 수집은 하지 않도록 수집하는 법

exec dbms_stats.gather_table_stats(user,tabname=>'t',method_opt=>'for columns ',estimate_percent=>'100');

3. 컬럼 통계와 히스토그램도 같이 수집하는 방법

exec dbms_stats.gather_table_stats('sys',tabname=>'t',method_opt=>'FOR ALL COLUMNS SIZE auto, for columns size 1 col1');

4. 테이블과 관련 인덱스, 히스토그램, 컬럼 통계 삭제

exec dbms_stats.delete_table_stats(user,tabname=>'t');

5. 히스토그램만 삭제하고 컬럼통계는 유지

exec dbms_stats.delete_column_stats('sys',tabname=>'t',colname=>'col1', col_stat_type=>'HISTOGRAM');

6. 테이블에 대한 기본 수집 옵션 변경

BEGIN
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

Posted by neo-orcl

통계 export/import

Knowledge/Perfomance Tuning 2015. 12. 29. 19:48

■dbms_stats 프로시저 사용한다.

1. create_stat_table 로 통계테이블을 만든다.

PROCEDURE CREATE_STAT_TABLE
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2                IN
STATTAB                        VARCHAR2                IN
TBLSPACE                       VARCHAR2                IN     DEFAULT
GLOBAL_TEMPORARY               BOOLEAN                 IN     DEFAULT

$ sqlplus hr/hr

orcl@HR> exec dbms_stats.create_stat_table('hr','stat');

PL/SQL procedure successfully completed.

2. export_*_stats 를 통해 통계 테이블로 저장

* 위치에 들어갈 수 있는 값
database, column, dictionary, fixed_object, index, pending, schema, system, table

3. 다른 db로 통계 테이블 이동(datapump등 사용)

4. import_*_stats 를 통해 통계를 딕셔너리에 입력

* 위치에 들어갈 수 있는 값
database, column, dictionary, fixed_object, index, pending, schema, system, table

■LAB

1. export_table_stats를 통해 통계를 테이블에 저장

PROCEDURE EXPORT_TABLE_STATS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2                IN
TABNAME                        VARCHAR2                IN
PARTNAME                       VARCHAR2                IN     DEFAULT
STATTAB                        VARCHAR2                IN
STATID                         VARCHAR2                IN     DEFAULT
CASCADE                        BOOLEAN                 IN     DEFAULT
STATOWN                        VARCHAR2                IN     DEFAULT

$ sqlplus hr/hr

orcl@HR> exec dbms_stats.export_table_stats('hr','employees',null,'stat');

PL/SQL procedure successfully completed.

※stat은 위 1단계에서 만든 통계 저장용 테이블이다.

orcl@SYS> exec print_table('select * from hr.stat where rownum=1');
STATID                        :
TYPE                          : C
VERSION                       : 5
FLAGS                         : 2
C1                            : EMPLOYEES
C2                            :
C3                            :
C4                            : LAST_NAME
C5                            : HR
N1                            : 103
N2                            : .00462962962962963
N3                            : 103
N4                            : 108
N5                            : 0
N6                            : 339495007414717000000000000000000000
N7                            : 469506047861463000000000000000000000
N8                            : 8
N9                            : 1
N10                           : 21
N11                           : 355127267452184000000000000000000000
N12                           :
D1                            : 23-nov-2015 11:00:08
R1                            : 4162656C
R2                            : 5A6C6F746B6579
CH1                           :
CL1                           :

2. import_table_stats를 통해 통계를 dictionary에 저장. 만약 다른 DB라면 export/import or empdp/impdp를 통해 테이블을 넣었을 것이다.

PROCEDURE IMPORT_TABLE_STATS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2                IN
TABNAME                        VARCHAR2                IN
PARTNAME                       VARCHAR2                IN     DEFAULT
STATTAB                        VARCHAR2                IN
STATID                         VARCHAR2                IN     DEFAULT
CASCADE                        BOOLEAN                 IN     DEFAULT
STATOWN                        VARCHAR2                IN     DEFAULT
NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
FORCE                          BOOLEAN                 IN     DEFAULT

orcl@HR> exec dbms_stats.import_table_stats('hr', 'employees', null, 'stat');

PL/SQL procedure successfully completed.

저작자표시 비영리 변경금지

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

Bind peeking/Adaptive cursor sharing의 Parameter 변경과 영향 (0)	2016.08.08
Adaptive cursor simple test (0)	2016.01.02
통계복원(restore_table_stats) (0)	2015.12.29
statistics lock/unlock (0)	2015.12.29
수동통계수집(gathering statistics manually) (0)	2015.12.29

Posted by neo-orcl

통계복원(restore_table_stats)

Knowledge/Perfomance Tuning 2015. 12. 29. 19:32

■통계 복원

만약 실행이 정상적이던 SQL이 특정 시점부터 느려졌다면 실행계획의 변경도 의심해보아야 한다. 만약 실행계획의 변경이 통계정보의 갱신에 의한 변경이고 최적화되지 않았다면 통계의 복원을 사용할 기회이다.

dbms_stats.restore_*_stats 프로시저로 이전 통계 복원 가능하다

예:
begin
dbms_stats.restore_table_stats(ownname=>'oe'
, tabname=>'inventories'
, as_of_timestamp=>to_date('2015-12-28 10:00', 'yyyy-mm-dd hh24:mi'));
end;
/

begin
dbms_stats.restore_table_stats('oe','inventories',to_date('2015-12-28 10:00', 'yyyy-mm-dd hh24:mi'));
end;
/

■테이블 통계 변경 내역 확인(테이블만 존재)

단, dbms_stats를 통해 수집한 내역만 확인 가능하다. analyze를 통한것은 기록이 남지 않는다.

select * from dba_tab_stats_history
where owner = 'HR'
and table_name='EMP'

orcl@SYS> exec print_table('select * from dba_tab_stats_history where owner = ''HR'' and table_name=''EMP''');
OWNER                         : HR
TABLE_NAME                    : EMP
PARTITION_NAME                :
SUBPARTITION_NAME             :
STATS_UPDATE_TIME             : 29-DEC-15 05.47.24.880458 PM +09:00
-----------------
OWNER                         : HR
TABLE_NAME                    : EMP
PARTITION_NAME                :
SUBPARTITION_NAME             :
STATS_UPDATE_TIME             : 29-DEC-15 06.07.28.041455 PM +09:00
-----------------
OWNER                         : HR
TABLE_NAME                    : EMP
PARTITION_NAME                :
SUBPARTITION_NAME             :
STATS_UPDATE_TIME             : 22-DEC-15 10.00.16.870032 PM +09:00
-----------------

PL/SQL procedure successfully completed.

■보관주기

기본값은 31일

변경은 dmbs_stats.alter_stats_history_retention 으로 설정한다.

15일로 변경 예시

orcl@SYS> exec dbms_stats.alter_stats_history_retention(15);

PL/SQL procedure successfully completed.

확인

orcl@HR> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
15

■ 프로시저 정보

PROCEDURE RESTORE_DATABASE_STATS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
FORCE                          BOOLEAN                 IN     DEFAULT
NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
PROCEDURE RESTORE_DICTIONARY_STATS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
FORCE                          BOOLEAN                 IN     DEFAULT
NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
PROCEDURE RESTORE_FIXED_OBJECTS_STATS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
FORCE                          BOOLEAN                 IN     DEFAULT
NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
PROCEDURE RESTORE_SCHEMA_STATS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2                IN
AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
FORCE                          BOOLEAN                 IN     DEFAULT
NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
PROCEDURE RESTORE_SYSTEM_STATS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
PROCEDURE RESTORE_TABLE_STATS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2                IN
TABNAME                        VARCHAR2                IN
AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
RESTORE_CLUSTER_INDEX          BOOLEAN                 IN     DEFAULT
FORCE                          BOOLEAN                 IN     DEFAULT
NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT

저작자표시 비영리 변경금지

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

Adaptive cursor simple test (0)	2016.01.02
통계 export/import (0)	2015.12.29
statistics lock/unlock (0)	2015.12.29
수동통계수집(gathering statistics manually) (0)	2015.12.29
histogram 고려사항 (0)	2015.12.29

Posted by neo-orcl

statistics lock/unlock

Knowledge/Perfomance Tuning 2015. 12. 29. 19:31

■통계 lock

자동 수집 방지 목적

예: 매번 동적 샘플링하도록 설정
begin
dbms_stats.delete_table_stats('oe','orders');
dbms_stats.lock_table_stats('oe','orders');
end;
/

예: 대표 값에 대한 통계가 있는 테이블에 대한 통계 lock
begin
dbms_stats.gather_table_stats('oe','orders');
dbms_stats.lock_table_stats('oe','orders');
end;
/

■ lock 방법

lock_partition_stats
lock_schema_stats
lock_table_stats

■unlock 방법

unlock_partition_stats
unlock_schema_stats
unlock_table_stats

■통계 lock 되었는지 확인하는 방법

select table_name, stattype_locked from user_tab_statistics
where stattype_locked='ALL';

※stattype은 기본값이 ALL이며 아직까진 다른 옵션은 개발되지 않은 것으로 보인다. 매뉴얼에도 없다.

■통계가 lock되어있더라도 강제 갱신 방법

통계가 lock된 테이블이나 테이블의 종속(인덱스, 열, 히스토그램)된 객체에 통계 갱신이나 수집, import를 시도하면
아래처럼 ora-20005를 만난다.

SQL> exec dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX')
BEGIN dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 10872
ORA-06512: at "SYS.DBMS_STATS", line 10896
ORA-06512: at line 1

이때 force 옵션을 사용하면 갱신 가능하다.

SQL> exec dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX', force=>true);
PL/SQL procedure successfully completed.

저작자표시 비영리 변경금지

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

통계 export/import (0)	2015.12.29
통계복원(restore_table_stats) (0)	2015.12.29
수동통계수집(gathering statistics manually) (0)	2015.12.29
histogram 고려사항 (0)	2015.12.29
자동 통계 수집(automatic statistics gathering) (0)	2015.12.29

Posted by neo-orcl

수동통계수집(gathering statistics manually)

Knowledge/Perfomance Tuning 2015. 12. 29. 19:24

■ 통계를 수동으로 수집해야 하는 시기

다음에 대해 수동으로 수집해야 한다.
- 휘발성 객체

- batch 처리 작업에서 수정되는 객체: 배치 처리 작업의 일부로 통계를 수집

- external table, system통계, fixed object

- 새로운 객체: 객체 생성 후 통계 수집

external 테이블은 샘플링이 지원되지 않아 estimate_percent 옵션을 명시적으로 null 설정해야 한다.
external 테이블에 대한 데이터 조작이 허용되지 않기에 해당 파일이 변경될 때만 통계 수집하면 충분한다.

■ 수동 통계 수집

dbms_stats는 optimize에 필요한 통계만 수집하고 다른 통계 수집은 하지 않는다.

- 예를 들어 테이블이라면 num_rows, 데이터가 있는 block count, 평균 행 길이는 포함되지만 체인된 row수, 평균 사용 가능 공간, 사용되지 않은 데이터 블럭 수는 미포함한다.

analyze문의 compute와 estimate 절은 역호환성을 위해서만 지원된다.

analyze문의 validate, list chained, free list 블록에 대한 정보 수집은 계속 사용 가능하다.

■ 수동 통계 수집 프로시저

-gather_index_stats
-gather_table_stats
-gather_schema_stats
-gather_dictionary_stats
-gather_database_stats
-gather_system_stats

-gather_fixed_objects_stats

PROCEDURE GATHER_DATABASE_STATS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
BLOCK_SAMPLE                   BOOLEAN                 IN     DEFAULT
METHOD_OPT                     VARCHAR2                IN     DEFAULT
DEGREE                         NUMBER                  IN     DEFAULT
GRANULARITY                    VARCHAR2                IN     DEFAULT
CASCADE                        BOOLEAN                 IN     DEFAULT
STATTAB                        VARCHAR2                IN     DEFAULT
STATID                         VARCHAR2                IN     DEFAULT
OPTIONS                        VARCHAR2                IN     DEFAULT
STATOWN                        VARCHAR2                IN     DEFAULT
GATHER_SYS                     BOOLEAN                 IN     DEFAULT
NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
GATHER_TEMP                    BOOLEAN                 IN     DEFAULT
GATHER_FIXED                   BOOLEAN                 IN     DEFAULT
STATTYPE                       VARCHAR2                IN     DEFAULT
OBJ_FILTER_LIST                OBJECTTAB               IN     DEFAULT

PROCEDURE GATHER_SCHEMA_STATS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2                IN
ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
BLOCK_SAMPLE                   BOOLEAN                 IN     DEFAULT
METHOD_OPT                     VARCHAR2                IN     DEFAULT
DEGREE                         NUMBER                  IN     DEFAULT
GRANULARITY                    VARCHAR2                IN     DEFAULT
CASCADE                        BOOLEAN                 IN     DEFAULT
STATTAB                        VARCHAR2                IN     DEFAULT
STATID                         VARCHAR2                IN     DEFAULT
OPTIONS                        VARCHAR2                IN     DEFAULT
STATOWN                        VARCHAR2                IN     DEFAULT
NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
GATHER_TEMP                    BOOLEAN                 IN     DEFAULT
GATHER_FIXED                   BOOLEAN                 IN     DEFAULT
STATTYPE                       VARCHAR2                IN     DEFAULT
FORCE                          BOOLEAN                 IN     DEFAULT
OBJ_FILTER_LIST                OBJECTTAB               IN     DEFAULT

PROCEDURE GATHER_TABLE_STATS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2                IN
TABNAME                        VARCHAR2                IN
PARTNAME                       VARCHAR2                IN     DEFAULT
ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
BLOCK_SAMPLE                   BOOLEAN                 IN     DEFAULT
METHOD_OPT                     VARCHAR2                IN     DEFAULT
DEGREE                         NUMBER                  IN     DEFAULT
GRANULARITY                    VARCHAR2                IN     DEFAULT
CASCADE                        BOOLEAN                 IN     DEFAULT
STATTAB                        VARCHAR2                IN     DEFAULT
STATID                         VARCHAR2                IN     DEFAULT
STATOWN                        VARCHAR2                IN     DEFAULT
NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
STATTYPE                       VARCHAR2                IN     DEFAULT
FORCE                          BOOLEAN                 IN     DEFAULT

PROCEDURE GATHER_INDEX_STATS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2                IN
INDNAME                        VARCHAR2                IN
PARTNAME                       VARCHAR2                IN     DEFAULT
ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
STATTAB                        VARCHAR2                IN     DEFAULT
STATID                         VARCHAR2                IN     DEFAULT
STATOWN                        VARCHAR2                IN     DEFAULT
DEGREE                         NUMBER                  IN     DEFAULT
GRANULARITY                    VARCHAR2                IN     DEFAULT
NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
STATTYPE                       VARCHAR2                IN     DEFAULT
FORCE                          BOOLEAN                 IN     DEFAULT

PROCEDURE GATHER_DICTIONARY_STATS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID                        VARCHAR2                IN     DEFAULT
ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
BLOCK_SAMPLE                   BOOLEAN                 IN     DEFAULT
METHOD_OPT                     VARCHAR2                IN     DEFAULT
DEGREE                         NUMBER                  IN     DEFAULT
GRANULARITY                    VARCHAR2                IN     DEFAULT
CASCADE                        BOOLEAN                 IN     DEFAULT
STATTAB                        VARCHAR2                IN     DEFAULT
STATID                         VARCHAR2                IN     DEFAULT
OPTIONS                        VARCHAR2                IN     DEFAULT
STATOWN                        VARCHAR2                IN     DEFAULT
NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
STATTYPE                       VARCHAR2                IN     DEFAULT
OBJ_FILTER_LIST                OBJECTTAB               IN     DEFAULT

PROCEDURE GATHER_FIXED_OBJECTS_STATS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
STATTAB                        VARCHAR2                IN     DEFAULT
STATID                         VARCHAR2                IN     DEFAULT
STATOWN                        VARCHAR2                IN     DEFAULT
NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT

PROCEDURE GATHER_SYSTEM_STATS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
GATHERING_MODE                 VARCHAR2                IN     DEFAULT
INTERVAL                       NUMBER(38)              IN     DEFAULT
STATTAB                        VARCHAR2                IN     DEFAULT
STATID                         VARCHAR2                IN     DEFAULT
STATOWN                        VARCHAR2                IN     DEFAULT

저작자표시 비영리 변경금지

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

통계복원(restore_table_stats) (0)	2015.12.29
statistics lock/unlock (0)	2015.12.29
histogram 고려사항 (0)	2015.12.29
자동 통계 수집(automatic statistics gathering) (0)	2015.12.29
시스템 통계 수집(gathering system statististics) (0)	2015.12.29

Posted by neo-orcl

histogram 고려사항

Knowledge/Perfomance Tuning 2015. 12. 29. 19:21

■ 히스토그램 고려사항

-특정 열의 비대칭 정도가 높을 때 유용하다

-열의 데이터 분산이 자주 변경될 경우 해당 히스토그램을 자주 재계산해야 한다.

■ 히스토그램이 유용하지 않은 경우

- 열이 where나 join절에 사용되지 않을 경우

- 균등하게 분산된 열(컬럼 통계만으로 충분하다)

- pk나 uk등으로 unique한 값이 있는 = 술어

실질적인 성능 개선이 없다면 히스토그램을 사용하지 않아야 한다. 히스토그램 수집은 통계 수집 중 가장 Load가 높은 작업이기 때문이다.

※ 문자열의 처음 32바이트에 대해서만 히스토그램 데이터가 저장되므로 문자 열에는 일부 예외적인 동작이 있을 수 있다.

열에 표현식을 포함하고 있는 술어는 query 옵티마이저에서 문제(인덱스를 사용하지 못하거나)를 일으킨다.

※ function(column) = constant 형태의 술어에 대한 선택성을 계산할 때 옵티마이저는 정적 선택성 값이 1%라고 가정한다.

■히스토그램 정보 확인

dba_tab_histogram
dba_part_histogram
dba_subpart_histogram

1. 먼저 해당 컬럼이 히스토그램을 가지고 있는지와 종류를 확인하기 위해서 DBA_TAB_COL_STATISTICS를 조회한다.

select owner, table_name, column_name, histogram
from DBA_TAB_COL_STATISTICS
where table_name='SALES'
and column_name='PROD_ID';

OWNER TABLE_NAME COLUMN_NAME HISTOGRAM
------------------------------ ------------------------------ ------------------------------ ---------
SH SALES PROD_ID FREQUENCY

2. 히스토그램을 확인한다.

col column_name for a10
col owner for a10
col table_name for a10

select owner, table_name, column_name, endpoint_number, endpoint_value
from DBA_TAB_HISTOGRAMS
where table_name='SALES'
and column_name='PROD_ID';

OWNER      TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- ---------- ---------- --------------- --------------
SH         SALES      PROD_ID               6002             13
SH         SALES      PROD_ID              12012             14
SH         SALES      PROD_ID              17778             15
SH         SALES      PROD_ID              24707             16
SH         SALES      PROD_ID              30867             17
SH         SALES      PROD_ID              40458             18
SH         SALES      PROD_ID              50888             19
SH         SALES      PROD_ID              61791             20
SH         SALES      PROD_ID              66996             21
SH         SALES      PROD_ID              70437             22
SH         SALES      PROD_ID              90079             23
....

저작자표시 비영리 변경금지

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

statistics lock/unlock (0)	2015.12.29
수동통계수집(gathering statistics manually) (0)	2015.12.29
자동 통계 수집(automatic statistics gathering) (0)	2015.12.29
시스템 통계 수집(gathering system statististics) (0)	2015.12.29
Additional statistics (0)	2015.12.19

Posted by neo-orcl

자동 통계 수집(automatic statistics gathering)

Knowledge/Perfomance Tuning 2015. 12. 29. 19:14

■ 개요

자동 통계 수집은 gather_stats_prog 를 통해 자동화된다

■ 프로시저

exec dbms_stats.set_global_prefs
exec dbms_stats.set_database_prefs
exec dbms_stats.set_schema_prefs
exec dbms_stats.set_table_prefs

■ 프로시저 속성

- cascade: 인덱스에 대한 통계도 수집한다. 단, 이는 병렬화되지 않는다

- estimate_percent

통계 계산에 사용되는 행의 예측 비율(null은 모든 행을 의미)로 0.000001~100 설정 가능하다.
시스템이 자동으로 적정 샘플 크기를 결정하도록 하게 하려면 dbms_stats.auto_sample_size 사용한다.(기본값)

- no_invalidate

통계 수집할 테이블의 종속 커서 무효화를 제어한다.
true-종속 커서가 무효화되지 않음, false-종속 커서가 무효화됨
시스템이 무효화해야 하는 시기를 결정하도록 하려면 dbms_stats.auto_invalidate를 사용한다(기본값)

- publish

통계를 딕셔너리에 저장하거나 업데이트 할 지, pending area에 저장할 것인지 결정한다.
pending statistics 기능과 연관이 있다

- stale_percent: 객체 통계가 오래된 기준을 정한다. 마지막 통계 수집 이후 수정된 행의 비율이다. 기본값 10%

- degree

통계 계산에 사용되는 병렬도이다. 기본값은 null이며 이는 create table이나 alter table 문에서 degree 절로 지정된 테이블의 기본값을 사용함을 의미한다.
init parameter값을 기반으로 기본값 지정하려면 dbms_stats.default_degree 사용한다.
auto_degree 값은 병렬도를 자동 결정한다. 객체 크기에 따라 1 또는 default_degree(cpu수와 init parameter 기반하는 시스템 기본값)이다

- method_opt: 히스토그램 수집에 사용되는 sql 문자열. 기본값은 for all coluns size auto이다.

- granulity: partition 테이블에 대해 수집할 통계의 단위이다. global이거나 partition단위 일 수 있다.

- incremental

partition 테이블의 global 통계를 incremental 방식으로 수집하는데 사용한다.

※ 만약 테이블 단위로 수집 관련 속성을 dbms_stats.set_table_prefs 통해 설정했다면 dba_tab_stat_prefs 뷰를 사용하여 확인할 수 있다.

저작자표시 비영리 변경금지

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

수동통계수집(gathering statistics manually) (0)	2015.12.29
histogram 고려사항 (0)	2015.12.29
시스템 통계 수집(gathering system statististics) (0)	2015.12.29
Additional statistics (0)	2015.12.19
Common wait events (0)	2015.12.19

Posted by neo-orcl

시스템 통계 수집(gathering system statististics)

Knowledge/Perfomance Tuning 2015. 12. 29. 19:08

■ 개요

시스템 통계 수집은 정기적으로 이루어져야 한다.

시스템 통계 수집은 기존 실행계획을 무효화하지 않는다.

■ 프로시저

dbms_stats.gather_system_stats: 시스템 통계 수집
dbms_stats.set_system_stats: 명시적으로 직접 설정
dbms_stats.get_system_stats: 시스템 통계 정보를 확인

gathering_mode:

- noworkload | interval
- start | stop

noworkload: 기본값, 몇분 정도 걸릴 수 있고 DB크기에 따라 다르다.
DB와 테이블스페이스 생성 후 gather_system_stats('noworkload')를 실행할 것을 권장한다.
interval: 지정된 간격동안 시스템 작업을 캡쳐한다.

start | stop: 시작 정지를 지정할 수 있다. 예를 들어 시작 후 작업을 걸고 stop하여 정확한 통계를 수집할 수 있다.

※10gR2부터는 시작 시 시스템 통계 필수 부분이 자동으로 수집된다

■ 시스템 통계 수집 예제1

exec dbms_stats.gather_system_stats(interval => 120, stattab => 'mystats',statid => 'OLTP'); --첫째날 낮
exec dbms_stats.gather_system_stats(interval => 120, stattab => 'mystats',statid => 'BATCH'); --첫째날 밤
exec dbms_stats.import_system_stats(stattab='mystats',statid='OLTP'); --낮에는 이 통계를 사용하도록 dictionary에 import
exec dbms_stats.import_system_stats(stattab='mystats',statid='DW'); --밤에는 이 통계를 사용하도록 dictionary에 import

※위 작업 전에 먼저 통계용 테이블을 dbms_stats.create_stat_table 을 통해 만들어야 한다.

■ 시스템 통계 수집 예제2

exec dbms_stats.gather_system_stats(gathering_mode => 'start');

workload 수행

exec dbms_stats.gather_system_stats(gathering_mode => 'stop');

위 예제는 해당 워크로드가 수행될 때의 시스템 통계를 수집하는 것이 목적이다.

저작자표시 비영리 변경금지

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

수동통계수집(gathering statistics manually) (0)	2015.12.29
histogram 고려사항 (0)	2015.12.29
자동 통계 수집(automatic statistics gathering) (0)	2015.12.29
Additional statistics (0)	2015.12.19
Common wait events (0)	2015.12.19

Posted by neo-orcl

Additional statistics

Knowledge/Perfomance Tuning 2015. 12. 19. 18:45

statistics name	description	recommend action
redo log space requests	리두 로그 파일에 공간 확보를 하기 위해 대기한 횟수	tune checkpoint, dbwr, archive process use bigger log file
consistent changes	consistent read를 위해 롤백된 횟수	Automatic undo management 사용 tune work load
consistent gets	consistent read모드로 읽은 블럭 수	Automatic undo management 사용 tune work load
table fetch by continued row	migration or chained rows	reorganization

조회 방법.

select name, value from v$sysstat where name like 'redo log space reque%'

union all

select name, value from v$sysstat where name like 'consistent changes%'

union all

select name, value from v$sysstat where name like 'con%'

union all

select name, value from v$sysstat where name like 'table fetch by continuted row';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo log space requests                                                 124
consistent changes                                                   930117
concurrency wait time                                                  3854
consistent gets                                                     4241014
consistent gets from cache                                          4191819
consistent gets from cache (fastpath)                               2249943
consistent gets - examination                                       1670409
consistent gets direct                                                49195
consistent changes                                                   930117

저작자표시 비영리 변경금지

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

수동통계수집(gathering statistics manually) (0)	2015.12.29
histogram 고려사항 (0)	2015.12.29
자동 통계 수집(automatic statistics gathering) (0)	2015.12.29
시스템 통계 수집(gathering system statististics) (0)	2015.12.29
Common wait events (0)	2015.12.19

Posted by neo-orcl

이전 1 2 다음

Neo-Oracle Home

'Knowledge/Perfomance Tuning'에 해당되는 글 11건

Bind peeking/Adaptive cursor sharing의 Parameter 변경과 영향

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

Adaptive cursor simple test

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

통계 export/import

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

통계복원(restore_table_stats)

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

statistics lock/unlock

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

수동통계수집(gathering statistics manually)

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

histogram 고려사항

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

자동 통계 수집(automatic statistics gathering)

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

시스템 통계 수집(gathering system statististics)

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

Additional statistics

'Knowledge > Perfomance Tuning' 카테고리의 다른 글

카테고리

태그목록

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

달력

링크

티스토리툴바