Neo-Oracle Home

12c의 ORA_STIG_PROFILE

Knowledge/12c New Feature 2016. 8. 26. 15:25

12c에선 보안용으로 ORA-STIG_PROFILE 이라는 profile을 기본으로 생성해준다.

SQL> select * from dba_profiles;

PROFILE           RESOURCE_NAME                  RESOURCE LIMIT                          COM
----------------- ------------------------------ -------- ------------------------------ ---
DEFAULT           COMPOSITE_LIMIT                KERNEL   UNLIMITED                      NO
DEFAULT           SESSIONS_PER_USER              KERNEL   UNLIMITED                      NO
DEFAULT           CPU_PER_SESSION                KERNEL   UNLIMITED                      NO
DEFAULT           CPU_PER_CALL                   KERNEL   UNLIMITED                      NO
DEFAULT           LOGICAL_READS_PER_SESSION      KERNEL   UNLIMITED                      NO
DEFAULT           LOGICAL_READS_PER_CALL         KERNEL   UNLIMITED                      NO
DEFAULT           IDLE_TIME                      KERNEL   UNLIMITED                      NO
DEFAULT           CONNECT_TIME                   KERNEL   UNLIMITED                      NO
DEFAULT           PRIVATE_SGA                    KERNEL   UNLIMITED                      NO
DEFAULT           FAILED_LOGIN_ATTEMPTS          PASSWORD UNLIMITED                      NO
DEFAULT           PASSWORD_LIFE_TIME             PASSWORD UNLIMITED                      NO
DEFAULT           PASSWORD_REUSE_TIME            PASSWORD UNLIMITED                      NO
DEFAULT           PASSWORD_REUSE_MAX             PASSWORD UNLIMITED                      NO
DEFAULT           PASSWORD_VERIFY_FUNCTION       PASSWORD NULL                           NO
DEFAULT           PASSWORD_LOCK_TIME             PASSWORD UNLIMITED                      NO
DEFAULT           PASSWORD_GRACE_TIME            PASSWORD UNLIMITED                      NO
ORA_STIG_PROFILE COMPOSITE_LIMIT                KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE SESSIONS_PER_USER              KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE CPU_PER_SESSION                KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE CPU_PER_CALL                   KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE LOGICAL_READS_PER_SESSION      KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE LOGICAL_READS_PER_CALL         KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE IDLE_TIME                      KERNEL   15                             NO
ORA_STIG_PROFILE CONNECT_TIME                   KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE PRIVATE_SGA                    KERNEL   DEFAULT                        NO
ORA_STIG_PROFILE FAILED_LOGIN_ATTEMPTS          PASSWORD 3                              NO
ORA_STIG_PROFILE PASSWORD_LIFE_TIME             PASSWORD 60                             NO
ORA_STIG_PROFILE PASSWORD_REUSE_TIME            PASSWORD 365                            NO
ORA_STIG_PROFILE PASSWORD_REUSE_MAX             PASSWORD 10                             NO
ORA_STIG_PROFILE PASSWORD_VERIFY_FUNCTION       PASSWORD ORA12C_STRONG_VERIFY_FUNCTION NO
ORA_STIG_PROFILE PASSWORD_LOCK_TIME             PASSWORD UNLIMITED                      NO
ORA_STIG_PROFILE PASSWORD_GRACE_TIME            PASSWORD 5                              NO

STIG란 Security Technical Implementation Guidelines 의 약자이다.

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

'Knowledge > 12c New Feature' 카테고리의 다른 글

19c long term support 기간 (0)	2019.07.22
19c 기대했던 기능들. automatic indexing, realtime-statistics. Exa/Cloud only (0)	2019.03.08
DDL log 별도 저장 (0)	2018.12.19
Oracle 12c Default audit policy include release 2 (0)	2017.09.26
RMAN recover table point in time (0)	2016.03.06

Posted by neo-orcl

Data guard와 Active data guard license

Knowledge/Oracle 2016. 8. 12. 13:29

Oracle Enterprise Edition에서 아래 DG 옵션이 무료이다.(11gR2 license 문서에서 나옴)

Oracle Data Guard—Redo Apply
Oracle Data Guard—SQL Apply
Oracle Data Guard—Snapshot Standby

그런데 Active Data guard는 Enterprise Edition 옵션에서 추가 비용을 지불하는 옵션이다.

하여 active data guard가 정확히 무엇을 해야 active data guard를 사용한다고 인식되는지 조금 뒤져보았다.

11g문서는 없었고(있을지도 모르지만 못찾았다)

12c기준으로 링크에서 아래처럼 나와있다.(http://www.oracle.com/technetwork/database/options/active-data-guard/overview/index.html)

Data Guard Architecture Overview

Data Guard provides the management, monitoring, and automation software to create and maintain one or more synchronized copies of a production database to protect Oracle data from failures, disasters, human error, and data corruptions while providing high availability for mission critical applications. Data Guard is included with Oracle Database Enterprise Edition.

Active Data Guard Functionality

Active Data Guard is an option license for Oracle Database Enterprise Edition. Active Data Guard enables advanced capabilities that that extend basic Data Guard functionality. These include:

· Real-Time Query - offload read-only workloads to an up-to-date standby database => 즉 DB read-only 모드로 오픈

· Automatic Block Repair - automatic repair of physical corruption transparent to the user => 예전부터 있던 기능

· Far Sync - zero data loss protection across any distance => 12c new feature이다

· Standby Block Change Tracking - enable incremental backups on an active standby => 예전부터 있던 기능

· Active Data Guard Rolling Upgrade - make it simple to reduce planned downtime => 12c new feature. 단, 10.1.0.3부터 DG sql apply는 db rolling upgrade가 가능하다

· Global Database Services - load balancing and service management across replicated databases. See Global Data Services => 12c new feature이다

· Application Continuity - make outages transparent to users. See Application Continuity => (rac one node도 가능) => Requires the Active Data Guard option or the Real Application Clusters option

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

'Knowledge > Oracle' 카테고리의 다른 글

Local Partition INDEX Test (0)	2018.04.24
Oracle DBA와 개발자의 필독서 (2)	2016.09.23
sqlplus 접속 hang시에 접속 방법 (0)	2016.05.27
impdp, imp 주의사항 (0)	2016.03.20
Oracle Create Table 내부 절차 (0)	2015.10.21

Posted by neo-orcl

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

이전 1 2 3 4 5 6 7 8 ··· 40 다음

Neo-Oracle Home

12c의 ORA_STIG_PROFILE

'Knowledge > 12c New Feature' 카테고리의 다른 글

Data guard와 Active data guard license

'Knowledge > Oracle' 카테고리의 다른 글

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

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

카테고리

태그목록

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

달력

링크

티스토리툴바