바인드 변수의 사용
바인드 변수의 기본적인 사용
USER01@orcl2> variable job_id varchar2(10)
USER01@orcl2> exec :job_id :='SA_REP';
PL/SQL procedure successfully completed.
USER01@orcl2 select count(*) from employees where job_id = :job_id;
COUNT(*)
----------
30
USER01@orcl2> exec :job_id := 'AD_VP';
USER01@orcl2> select count(*) from employees where job_id = :job_id;
COUNT(*)
----------
2
USER01@orcl2> col sql_text for a60
USER01@orcl2> select sql_id, executions, loads, sql_text
from v$sql
where sql_text like 'select count(*) from employees%';
SQL_ID EXECUTIONS LOADS SQL_TEXT
------------- ---------- ---------- ------------------------------------------------------------
cff1x2mxarz40 1 1 select count(*) from employees where job_id = :job_id
-- 상수 => 바인드 변수로 변환함으로 커서를 공유한다.
USER01@orcl2> select /*example_lee*/ count(*) from employees where job_id = 'SA_REP';
USER01@orcl2> select /*example_lee*/ count(*) from employees where job_id = 'AD_VP';
-- 바인드 변수가 아닌 상수
USER01@orcl2> select sql_id, executions, loads, sql_text
from v$sql
where sql_text like '%example_lee%';
SQL_ID EXECUTIONS LOADS SQL_TEXT
------------- ---------- ---------- ------------------------------------------------------------
7xdunjkm6h14t 1 1 select /*example_lee*/ count(*) from employees where job_id
= 'AD_VP'
bt0rq6jczg329 1 1 select /*example_lee*/ count(*) from employees where job_id
= 'SA_REP'
USER01@orcl2> select /*example_lee*/ count(*) from employees where job_id = 'SA_REP';
USER01@orcl2> select /*example_lee*/ count(*) from employees where job_id = 'AD_VP';
USER01@orcl2> select sql_id, executions, loads, sql_text
from v$sql
where sql_text like '%example_lee%';
SQL_ID EXECUTIONS LOADS SQL_TEXT
------------- ---------- ---------- ------------------------------------------------------------
7xdunjkm6h14t 2 1 select /*example_lee*/ count(*) from employees where job_id
= 'AD_VP'
bt0rq6jczg329 2 1 select /*example_lee*/ count(*) from employees where job_id
= 'SA_REP'
-- executions 가 올라간 것을 확인
Adaptive cursor sharing
- 선택도(SELECTIVITY)에 따라서 여러 플랜을 갖고 골라 쓰겠다.
- WHERE DEPTNO = :B1; 일경우
- 1ST: DEPTNO = 10 0.1%(INDEX PLAN) --실행통계 0.1%
- 2ND: DEPTNO = 20 ?%(INDEX PLAN) --실행통계 50% -- BIND SENSITIVE CURSOR
- 3RD: DEPTNO = 30 40%(FULL)_파싱을 새로 한다. --실행통계 40% --BIND AWARE CURSOR
- 4TH: DEPTNO = 40 10%(AWARE는 무조건 예측_실행계획이 위랑 같으면 공유 FULL) -- 실행통계 10%(BIND AWARE CURSOR가 10%으로 변경)
- 적용하기 위한 조건
- BIND VARIABLE 사용
- BIND VARIABLE PEEKING 사용
- HISTOGRAM정보가 있거나(OR) 범위 연산일 경우
/*
Adaptive Cursor Sharing 기능을 사용하기 전(10g)과 후(11g)를 비교해 봅니다.
연습문제 4~10 : 10g
13~ : 11g
*/
/*
1. 사용자 생성 : acs/acs
2. 테이블 생성 : create table emp
(
empno number,
ename varchar2(20),
phone varchar2(20),
deptno number
);
3. 테이블에 데이터 입력
4. deptno 컬럼에 인덱스
5. 통계 수집 : deptno 컬럼에 대해 histogram 수집
*/
----acs_setup.sql-----------------
set echo on
drop user acs cascade;
create user acs identified by acs default tablespace users temporary tablespace temp;
grant dba, connect to acs;
connect acs/acs
drop table emp purge;
create table emp
(
empno number,
ename varchar2(20),
phone varchar2(20),
deptno number
);
insert into emp
with tdata as
(select rownum empno
from all_objects
where rownum <= 1000)
select rownum,
dbms_random.string ('u', 20),
dbms_random.string ('u', 20),
case
when rownum/100000 <= 0.001 then mod(rownum, 10)
else 10
end
from tdata a, tdata b
where rownum <= 100000;
create index emp_i1 on emp(deptno);
exec dbms_stats.gather_table_stats(null, 'EMP', METHOD_OPT => 'FOR COLUMNS DEPTNO SIZE 10', CASCADE => TRUE);
alter system flush shared_pool;
exit;
----------------------------
$ sqlplus acs/acs
ACS@orcl2> select num_rows, blocks from user_tab_statistics
where table_name='EMP';
NUM_ROWS BLOCKS
---------- ----------
100000 874
ACS@orcl2> select count(distinct empno), count(distinct ename), count(distinct phone), count(distinct deptno) from emp;
COUNT(DISTINCTEMPNO) COUNT(DISTINCTENAME) COUNT(DISTINCTPHONE) COUNT(DISTINCTDEPTNO)
-------------------- -------------------- -------------------- ---------------------
100000 100000 100000 11
ACS@orcl2> alter system flush shared_pool;
System altered.
ACS@orcl2> @check_emp_histogram
ACS@orcl2>
ACS@orcl2> select column_name, histogram, num_buckets
from user_tab_columns
where table_name='EMP';
COLUMN_NAME HISTOGRAM NUM_BUCKETS
------------------------------ --------------- -----------
EMPNO NONE
ENAME NONE
PHONE NONE
DEPTNO HEIGHT BALANCED 10
ACS@orcl2> @show_deptno_distribution
set echo on
select deptno, count(*) cnt_per_deptno, (count(*)*100)/nr deptno_percent
from emp, (select max(empno) nr
from emp)
group by deptno, nr
order by deptno;
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select deptno, count(*) cnt_per_deptno, (count(*)*100)/nr deptno_percent
from emp, (select max(empno) nr
from emp)
group by deptno, nr
order by deptno;
DEPTNO CNT_PER_DEPTNO DEPTNO_PERCENT
---------- -------------- --------------
0 10 .01
1 10 .01
2 10 .01
3 10 .01
4 10 .01
5 10 .01
6 10 .01
7 10 .01
8 10 .01
9 10 .01
10 99900 99.9
11 rows selected.
/*
이제부터는 10g 환경입니다
*/
ACS@orcl2> alter session set optimizer_features_enable="10.2.0.1";
Session altered.
ACS@orcl2> set lines 200 pages 10000
ACS@orcl2> @select_deptno_literal_9 /* 상수값 9를 검색하는 select -> index range scan */
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select /*ACS_L9*/ count(*), max(empno)
from emp
where deptno = 9;
COUNT(*) MAX(EMPNO)
---------- ----------
10 99
ACS@orcl2> @show_latest_exec_plan.sql
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 11 | 176 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_I1 | 11 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=9)
ACS@orcl2> @select_deptno_literal_10 /* 상수값 10을 사용하는 select -> full table scan */
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select /*ACS_L10*/ count(*), max(empno)
from emp
where deptno = 10;
COUNT(*) MAX(EMPNO)
---------- ----------
99900 100000
ACS@orcl2> set echo on
ACS@orcl2> @show_latest_exec_plan
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 239 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| EMP | 95000 | 1484K| 239 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=10)
/*
10g : 상수값을 사용하여 조건문을 작성하는 경우 데이터의 분포도까지 고려한 수행 계획을 결정해 준다.
*/
ACS@orcl2> variable deptno number;
ACS@orcl2> exec :deptno := 9;
PL/SQL procedure successfully completed.
ACS@orcl2> @select_deptno_bind
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select /*ACS_1*/ count(*), max(empno) /* Bind 변수를 사용하여 작성한 명령수행 */
from emp
where deptno = :deptno;
COUNT(*) MAX(EMPNO)
---------- ----------
10 99
ACS@orcl2> set echo on
ACS@orcl2> @show_latest_exec_plan
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 11 | 176 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_I1 | 11 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :DEPTNO (NUMBER): 9
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=:DEPTNO)
/*
첫 수행시 결정된 execution plan이 계속 사용됩니다.
plan 결정시 bind variable peeking 에 의해 변수에 이미 지정되어 있는 값 deptno=9 인 명령의 execution plan이 결정됩니다
*/
ACS@orcl2> @show_latest_exec_stats
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select child_number, executions, buffer_gets
from v$sql
where sql_text like 'select /*ACS_1%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS
------------ ---------- -----------
0 1 3
ACS@orcl2> exec :deptno := 10
PL/SQL procedure successfully completed.
ACS@orcl2> @select_deptno_bind
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select /*ACS_1*/ count(*), max(empno)
from emp
where deptno = :deptno;
COUNT(*) MAX(EMPNO)
---------- ----------
99900 100000
ACS@orcl2>
ACS@orcl2> @show_latest_exec_plan
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 11 | 176 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_I1 | 11 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Peeked Binds (identified by position):
-------------------------------------
1 - :DEPTNO (NUMBER): 9
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=:DEPTNO)
25 rows selected.
/*
변수에 지정된 값이 바뀌어 deptno=10 (분포도상 99%)인 데이터를 검색해야 하는 명령이나 첫 parse시 결정된 Index Range Scan으로 수행되고 있습니다.
*/
ACS@orcl2>
ACS@orcl2> @show_latest_exec_stats
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select child_number, executions, buffer_gets
from v$sql
where sql_text like 'select /*ACS_1%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS
------------ ---------- -----------
0 2 957
ACS@orcl2> alter system flush shared_pool; /* LC를 모두 비워 사용 중인 execution plan을 없앱니다. */
System altered.
ACS@orcl2> exec :deptno := 10; /* 이번에는 첫 수행시 변수에 10을 지정하여 10인 테이터를 검색하는 execution plan이 결정되도록 합니다.*/
PL/SQL procedure successfully completed.
ACS@orcl2> @select_deptno_bind
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select /*ACS_1*/ count(*), max(empno)
from emp
where deptno = :deptno;
COUNT(*) MAX(EMPNO)
---------- ----------
99900 100000
ACS@orcl2>
ACS@orcl2> @show_latest_exec_plan
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 239 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| EMP | 95000 | 1484K| 239 (1)| 00:00:03 |
---------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :DEPTNO (NUMBER): 10
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=:DEPTNO)
24 rows selected.
ACS@orcl2>
ACS@orcl2> @show_latest_exec_stats
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select child_number, executions, buffer_gets
from v$sql
where sql_text like 'select /*ACS_1%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS
------------ ---------- -----------
0 1 872
ACS@orcl2> exec :deptno :=9
PL/SQL procedure successfully completed.
ACS@orcl2> @select_deptno_bind
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select /*ACS_1*/ count(*), max(empno)
from emp
where deptno = :deptno;
COUNT(*) MAX(EMPNO)
---------- ----------
10 99
ACS@orcl2>
ACS@orcl2> @show_latest_exec_plan
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 239 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| EMP | 95000 | 1484K| 239 (1)| 00:00:03 |
---------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :DEPTNO (NUMBER): 10
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=:DEPTNO)
24 rows selected.
ACS@orcl2>
ACS@orcl2> @show_latest_exec_stats
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select child_number, executions, buffer_gets
from v$sql
where sql_text like 'select /*ACS_1%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS
------------ ---------- -----------
0 2 1693
/*
10g 버젼에서의 경우 bind var. peeking + histogram 정보에 의해 첫 수행시 변수값에 따라 결정된 수행 계획을 계속 사용함으로써
변수값에 따라 수행 계획이 달라져야 하는 경우 최악의 수행 계획이 사용되는 경우가 발생할 수 있었읍니다.
이를 보완하기 위해 나온 기능이
11g의 Adaptive Cursor Sharing 입니다.
*/
/*
이제부터는 11g 환경입니다
*/
ACS@orcl2> alter session set optimizer_features_enable="11.2.0.1";
Session altered.
ACS@orcl2> alter system flush shared_pool;
System altered.
ACS@orcl2> var
variable deptno
datatype NUMBER
/*
작업 중이던 세션을 종료하고 새로운 세션에서 작업을 할 경우 전의 세션에서 정의해 놓은 bind variable는 사용할 수 없습니다.
정의된 변수 deptno가 없으면 다음과 같이 다시 정의하세요.
ACS@orcl2> var deptno number
*/
1. 첫번째 수행 -- 9: 0.01 index 수행
ACS@orcl2> exec :deptno :=9
PL/SQL procedure successfully completed.
ACS@orcl2> @select_deptno_bind
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select /*ACS_1*/ count(*), max(empno)
from emp
where deptno = :deptno;
COUNT(*) MAX(EMPNO)
---------- ----------
10 99
ACS@orcl2>
ACS@orcl2> @show_latest_exec_plan
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 16 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_I1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :DEPTNO (NUMBER): 9
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=:DEPTNO)
ACS@orcl2> ed show_latest_exec_stats /* select list에 다음과 같이 컬럼들을 추가하세요. */
set echo on
select child_number, executions, buffer_gets, sql_id, is_bind_sensitive, is_bind_aware
from v$sql
where sql_text like 'select /*ACS_1%';
/
~
:wq
ACS@orcl2> @show_latest_exec_stats
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select child_number, executions, buffer_gets, sql_id, is_bind_sensitive, is_bind_aware
from v$sql
where sql_text like 'select /*ACS_1%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS SQL_ID I I
------------ ---------- ----------- ------------- - -
0 1 54 272gr4hapc9w1 Y N
2. 두번째 수행 10: index range 스캔 하고나서 보니 99.9%
ACS@orcl2> exec :deptno := 10; /* 두 번째 수행시는 일단 첫 수행시 생성된 execution plan을 사용합니다. */
PL/SQL procedure successfully completed.
ACS@orcl2> @select_deptno_bind
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select /*ACS_1*/ count(*), max(empno)
from emp
where deptno = :deptno;
COUNT(*) MAX(EMPNO)
---------- ----------
99900 100000
ACS@orcl2>
ACS@orcl2> @show_latest_exec_plan
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 16 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_I1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :DEPTNO (NUMBER): 9
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=:DEPTNO)
ACS@orcl2> @show_latest_exec_stats
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select child_number, executions, buffer_gets, sql_id, is_bind_sensitive, is_bind_aware
from v$sql
where sql_text like 'select /*ACS_1%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS SQL_ID I I
------------ ---------- ----------- ------------- - -
0 2 1008 272gr4hapc9w1 Y N
3. 세번째 수행 10 : 예측 99.9% --> 다시 플랜을 작성해서 FULL
ACS@orcl2> exec :deptno := 10 /* 이번 수행시는 변수 값에 따라 plan이 달라져야 한다는 것을 직전 수행에서 알아냈으므로 새 plan이 결정됩니다.*/
PL/SQL procedure successfully completed.
ACS@orcl2> @select_deptno_bind
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select /*ACS_1*/ count(*), max(empno)
from emp
where deptno = :deptno;
COUNT(*) MAX(EMPNO)
---------- ----------
99900 100000
ACS@orcl2>
ACS@orcl2> @show_latest_exec_plan
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 239 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| EMP | 95000 | 1484K| 239 (1)| 00:00:03 |
---------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :DEPTNO (NUMBER): 10
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=:DEPTNO)
24 rows selected.
ACS@orcl2>
ACS@orcl2> @show_latest_exec_stats
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select child_number, executions, buffer_gets, sql_id, is_bind_sensitive, is_bind_aware
from v$sql
where sql_text like 'select /*ACS_1%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS SQL_ID I I
------------ ---------- ----------- ------------- - -
0 2 1008 272gr4hapc9w1 Y N
1 1 821 272gr4hapc9w1 Y Y
ACS@orcl2> select * from v$sql_cs_selectivity where sql_id='272gr4hapc9w1'; /* 선택도에 따라 달라지는 정보를 Fixed Table에 기록 */
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
-------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
3FFAA8B8 357967745 272gr4hapc9w1 1 =DEPTNO 0 0.855000 1.045000
4. 네번째 수행 5 예측 0.01%, 처음과 같으므로 이미 갖고 있던 IRS 수행
ACS@orcl2> exec :deptno := 5;
PL/SQL procedure successfully completed.
ACS@orcl2> @select_deptno_bind
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select /*ACS_1*/ count(*), max(empno)
from emp
where deptno = :deptno;
COUNT(*) MAX(EMPNO)
---------- ----------
10 95
ACS@orcl2>
ACS@orcl2> @show_latest_exec_plan
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL +PEEKED_BINDS'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 16 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_I1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :DEPTNO (NUMBER): 5
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=:DEPTNO)
25 rows selected.
ACS@orcl2>
ACS@orcl2> @show_latest_exec_stats
ACS@orcl2> set echo on
ACS@orcl2>
ACS@orcl2> select child_number, executions, buffer_gets, sql_id, is_bind_sensitive, is_bind_aware
from v$sql
where sql_text like 'select /*ACS_1%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS SQL_ID I I
------------ ---------- ----------- ------------- - -
0 2 1008 272gr4hapc9w1 Y N
1 1 821 272gr4hapc9w1 Y Y
2 1 3 272gr4hapc9w1 Y Y
ACS@orcl2> select * from v$sql_cs_selectivity where sql_id='272gr4hapc9w1';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
-------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
3FFAA8B8 357967745 272gr4hapc9w1 2 =DEPTNO 0 0.000005 0.000006
3FFAA8B8 357967745 272gr4hapc9w1 1 =DEPTNO 0 0.855000 1.045000
cursor sharing(DEFAULT = EXACT)
- 상수 => 바인드 변수로 대체해서 실행 계획을 공유하기 위한 파라미터
- EXACT면 대체하지 않는다.
- FORCE
- SIMILAR
/*
1. 사용자 생성 : cs/cs
2. 테이블 생성 : create table emp
(
empno number,
ename varchar2(20),
phone varchar2(20),
deptno number
);
3. 테이블에 데이터 입력
4. deptno 컬럼에 인덱스
5. 통계 수집 : deptno 컬럼에 대해 histogram 수집하지 않음
*/
-----------------------------cs_setup.sql------------------------------
set echo on
drop user cs cascade;
create user cs identified by cs default tablespace users temporary tablespace temp;
grant dba, connect to cs;
connect cs/cs
drop table emp purge;
create table emp
(
empno number,
ename varchar2(20),
phone varchar2(20),
deptno number
);
insert into emp
with tdata as
(select rownum empno
from all_objects
where rownum <= 1000)
select rownum,
dbms_random.string ('u', 20),
dbms_random.string ('u', 20),
case
when rownum/100000 <= 0.001 then mod(rownum, 10)
else 10
end
from tdata a, tdata b
where rownum <= 100000;
create index emp_i1 on emp(deptno);
execute dbms_stats.gather_table_stats(null, 'EMP', cascade => true);
--alter system flush shared_pool;
--connect / as sysdba
--shutdown immediate;
--startup;
exit;
--------------------------------------------------------------
$ sqlplus cs/cs
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 19 05:00:43 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
CS@orcl2> set lines 200 pages 1000
CS@orcl2> select column_name, histogram, num_buckets
from user_tab_columns
where table_name='EMP';
COLUMN_NAME HISTOGRAM NUM_BUCKETS
------------------------------ --------------- -----------
EMPNO NONE 1
ENAME NONE 1
PHONE NONE 1
DEPTNO NONE 1
CS@orcl2> select deptno, count(*) cnt_per_deptno, (count(*)*100)/nr deptno_percent
from emp, (select max(empno) nr
from emp)
group by deptno, nr
order by deptno;
DEPTNO CNT_PER_DEPTNO DEPTNO_PERCENT
---------- -------------- --------------
0 10 .01
1 10 .01
2 10 .01
3 10 .01
4 10 .01
5 10 .01
6 10 .01
7 10 .01
8 10 .01
9 10 .01
10 99900 99.9
11 rows selected.
CS@orcl2> alter system flush shared_pool;
System altered.
CS@orcl2> alter session set cursor_sharing=force; /* default 'EXACT'에서 변경 : literal 값을 bind variable로 재작성합니다. */
Session altered.
CS@orcl2> select /*CS*/ count(*), max(empno) from emp where deptno = 9;
COUNT(*) MAX(EMPNO)
---------- ----------
10 99
CS@orcl2> select /*CS*/ count(*), max(empno) from emp where deptno = 10;
COUNT(*) MAX(EMPNO)
---------- ----------
99900 100000
CS@orcl2> col sql_text format a70
CS@orcl2> select sql_text,hash_value
from v$sql
where sql_text like '%select /*CS%';
SQL_TEXT HASH_VALUE
---------------------------------------------------------------------- ----------
select /*CS*/ count(*), max(empno) from emp where deptno = :"SYS_B_0" 3434097775 /* plan 결정하기 전에 변형된 명령어 */
CS@orcl2> col child_number Heading 'CHILD|NUMBER'
CS@orcl2> col object_name format a6
CS@orcl2> col operation format a16
CS@orcl2> col options format a15
CS@orcl2> select address,hash_value,child_number, operation,options,object_name
from v$sql_plan
where (address,hash_value) in
(select address,hash_value
from v$sql
where sql_text like '%select /*CS%');
CHILD
ADDRESS HASH_VALUE NUMBER OPERATION OPTIONS OBJECT /* 분포도 정보가 제공되지 않았으므로 고른 분포라는 가정하에 사용 계획이 동일합니다.*/
-------- ---------- ---------- ---------------- --------------- ------
4896D644 3434097775 0 SELECT STATEMENT
4896D644 3434097775 0 SORT AGGREGATE
4896D644 3434097775 0 TABLE ACCESS BY INDEX ROWID EMP
4896D644 3434097775 0 INDEX RANGE SCAN EMP_I1
CS@orcl2> exec dbms_stats.gather_table_stats(null,'emp',method_opt=>'for columns deptno size 10', cascade=>true); /* 분포도 정보 수집 */
PL/SQL procedure successfully completed.
CS@orcl2> select column_name, histogram, num_buckets
from user_tab_columns
where table_name='EMP';
COLUMN_NAME HISTOGRAM NUM_BUCKETS
------------------------------ --------------- -----------
EMPNO NONE 1
ENAME NONE 1
PHONE NONE 1
DEPTNO HEIGHT BALANCED 10
CS@orcl2> alter system flush shared_pool;
System altered.
CS@orcl2> select /*CS*/ count(*), max(empno) from emp where deptno = 9;
COUNT(*) MAX(EMPNO)
---------- ----------
10 99
CS@orcl2> select /*CS*/ count(*), max(empno) from emp where deptno = 10;
COUNT(*) MAX(EMPNO)
---------- ----------
99900 100000
CS@orcl2> col sql_text format a70
CS@orcl2> select sql_text,hash_value
from v$sql
where sql_text like '%select /*CS%';
SQL_TEXT HASH_VALUE
---------------------------------------------------------------------- ----------
select /*CS*/ count(*), max(empno) from emp where deptno = :"SYS_B_0" 3434097775
CS@orcl2> col child_number Heading 'CHILD|NUMBER'
CS@orcl2> col object_name format a6
CS@orcl2> col operation format a16
CS@orcl2> col options format a15
CS@orcl2> select address,hash_value,child_number, operation,options,object_name
from v$sql_plan
where (address,hash_value) in
(select address,hash_value
from v$sql
where sql_text like '%select /*CS%');
CHILD
ADDRESS HASH_VALUE NUMBER OPERATION OPTIONS OBJECT /* 그래도 사용 계획은 동일합니다. */
-------- ---------- ---------- ---------------- --------------- ------
4896D644 3434097775 0 SELECT STATEMENT
4896D644 3434097775 0 SORT AGGREGATE
4896D644 3434097775 0 TABLE ACCESS BY INDEX ROWID EMP
4896D644 3434097775 0 INDEX RANGE SCAN EMP_I1
CS@orcl2> alter session set cursor_sharing=similar; /* similar로 변경 */
Session altered.
CS@orcl2> select /*CS*/ count(*), max(empno) from emp where deptno = 9;
COUNT(*) MAX(EMPNO)
---------- ----------
10 99
CS@orcl2> select /*CS*/ count(*), max(empno) from emp where deptno = 10;
COUNT(*) MAX(EMPNO)
---------- ----------
99900 100000
CS@orcl2> col sql_text format a70
CS@orcl2> select sql_text,hash_value
from v$sql
where sql_text like '%select /*CS%';
SQL_TEXT HASH_VALUE
---------------------------------------------------------------------- ----------
select /*CS*/ count(*), max(empno) from emp where deptno = :"SYS_B_0" 3434097775
select /*CS*/ count(*), max(empno) from emp where deptno = :"SYS_B_0" 3434097775
CS@orcl2> col child_number Heading 'CHILD|NUMBER'
CS@orcl2> col object_name format a6
CS@orcl2> col operation format a16
CS@orcl2> col options format a15
CS@orcl2>
CS@orcl2> select address,hash_value,child_number, operation,options,object_name
from v$sql_plan
where (address,hash_value) in
(select address,hash_value
from v$sql
where sql_text like '%select /*CS%');
CHILD
ADDRESS HASH_VALUE NUMBER OPERATION OPTIONS OBJECT /* 분포도에 따라 다른 plan을 사용합니다. */
-------- ---------- ---------- ---------------- --------------- ------
4896D644 3434097775 1 SELECT STATEMENT
4896D644 3434097775 1 SORT AGGREGATE
4896D644 3434097775 1 TABLE ACCESS FULL EMP
4896D644 3434097775 0 SELECT STATEMENT
4896D644 3434097775 0 SORT AGGREGATE
4896D644 3434097775 0 TABLE ACCESS BY INDEX ROWID EMP
4896D644 3434097775 0 INDEX RANGE SCAN EMP_I1
7 rows selected.
CS@orcl2> alter system flush shared_pool;
System altered.
CS@orcl2> exit
'DATABASE(oracleDB 11g) > Tuning' 카테고리의 다른 글
| [Performance Tuning]기본 튜닝 진단 (0) | 2019.05.20 |
|---|---|
| [Performance Tuning]Performance Tuning (0) | 2019.05.20 |
| [SQL Tuning]옵티마이저 통계 (0) | 2019.05.20 |
| [SQL Tuning]Partitioned table (0) | 2019.05.20 |
| [SQL Tuning]Subquery operation(서브쿼리 오퍼레이션) (0) | 2019.05.20 |