오라클 성능 고도화 원리와 해법 I (2012년)
cursor_sharing
- 바인드 변수 사용 원칙을 잘 지키지 않아 시스템에서 정상 가동이 어려운 경우 cursor_sharing파라미터를 변경하는것으로 일시적으로 해결 가능
- cursor_sharing = ( SIMILAR , EXACT , FORCE )
설정값
- FORCE : SQL내의 리터럴 상수를 시스템이 생성한 바인드 변수(:SYS_B_0)로 변경한다.
- SIMILAR : 오라클은 먼저 리터럴 상수를 바인드 변수로 변경하고 바인드 변수를Peeking할지 결정하기 때문에 별도로 최적화하는 것이 좋다고 생각한 문장에 대해서 single parse call을 최적화 시킬 수 있음
- EXACT : 같은 문장에 대해서만 cursor share를 함.
- DEFAULT 값 : EXACT
- 이 옵션을 사용하면 Shared Pool과 라이브러리 캐시 경합을 줄이는 데는 분명 효과를 보겠지만 기존 실행계획이 틀어져 이전보다 더 느리게 수행되는 쿼리들이 여기저기 속출하게 될 가능성이 높다.
SQL> create table emp_t
2 as
3 select * from emp
SQL> /
테이블이 생성되었습니다.
SQL> create index emp_empno_idx on emp_t(empno)
SQL> /
인덱스가 생성되었습니다.
SQL> analyze table emp_t compute statistics
2 for table for all indexes for columns empno size 1
통계정보 : 히스토그램이 생성되지 않게 수집
SQL> /
테이블이 분석되었습니다.
SQL> alter session set cursor_sharing = force;
세션이 변경되었습니다.
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> set serveroutput on;
SQL> declare
2 l_condition varchar2(20);
3 l_ename emp_t.ename%type;
4 begin
5 for c in ( select empno from emp_t )
6 loop
7 l_condition := 'empno = '|| c.empno;
8 execute immediate 'select ename ' || 'from emp_t where ' || l_condition
9 into l_ename;
10 dbms_output.put_line( l_condition ||' : ' || l_ename );
11 end loop;
12 end;
13 /
empno = 7329 : SMITH
empno = 7499 : ALLEN
- 중 략 -
empno = 7902 : FORD
empno = 7934 : MILLER
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select sql_id, sql_text, version_count
2 from v$sqlarea
3 where sql_text like 'select ename%'
SQL> /
SQL_ID SQL_TEXT VERSION_COUNT
------------- -------------------------------------------------- -------------
6y7xpur0d4jty select ename from emp_t where empno = :"SYS_B_0" 1
SQL> select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions, fetches
2 from v$sql
3 where sql_text like 'select ename%'
SQL> /
SQL_ID CHLD_NO PLAN_HASH LOADS PARSE_CALLS EXECUTIONS FETCHES
------------- ---------- ---------- ---------- ----------- ---------- ----------
6y7xpur0d4jty 0 2443308290 1 14 14 14
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> alter session set cursor_sharing = similar;
세션이 변경되었습니다.
SQL> declare
2 l_condition varchar2(20);
3 l_ename emp_t.ename%type;
4 begin
5 for c in ( select empno from emp_t )
6 loop
7 l_condition := 'empno = '|| c.empno;
8 execute immediate 'select ename ' || 'from emp_t where ' || l_condition
9 into l_ename;
10 dbms_output.put_line( l_condition ||' : ' || l_ename );
11 end loop;
12 end;
13 /
empno = 7329 : SMITH
- 중 략 -
empno = 7934 : MILLER
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select sql_id, sql_text, version_count
2 from v$sqlarea
3 where sql_text like 'select ename%'
SQL> /
SQL_ID SQL_TEXT VERSION_COUNT
------------- -------------------------------------------------- -------------
6y7xpur0d4jty select ename from emp_t where empno = :"SYS_B_0" 1
SQL> select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions, fetches
2 from v$sql
3 where sql_text like 'select ename%';
4 /
SQL_ID CHLD_NO PLAN_HASH LOADS PARSE_CALLS EXECUTIONS FETCHES
------------- ---------- ---------- ---------- ----------- ---------- ----------
6y7xpur0d4jty 0 2443308290 1 14 14 14
SQL> insert into emp_t( empno, ename, job, mgr, hiredate, sal, comm, deptno )
2 select empno, ename, job, mgr, hiredate, sal, comm, deptno
3 from emp_t, ( select * from dual connect by level <= 9999 )
4 where emp_t.empno = 7788
5 /
9999 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> analyze table emp_t compute statistics
2 for table for all indexes for columns empno size 8
통계정보 : 히스토그램 생성
SQL> set autotrace traceonly exp;
SQL> select ename from emp_t where empno = 7788;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=9)
1 0 TABLE ACCESS (FULL) OF 'EMP_T' (TABLE) (Cost=4 Card=1 Bytes= 9)
SQL> delete from emp_t where empno = 7788 and rownum <= 9999;
9999 행이 삭제되었습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> declare
2 l_condition varchar2(20);
3 l_ename emp_t.ename%type;
4 begin
5 for c in ( select empno from emp_t )
6 loop
7 l_condition := 'empno = '|| c.empno;
8 execute immediate 'select ename ' || 'from emp_t where ' || l_condition
9 into l_ename;
10 dbms_output.put_line( l_condition ||' : ' || l_ename );
11 end loop;
12 end;
13 /
empno = 7329 : SMITH
- 중 략 -
empno = 7788 : SCOTT
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select sql_id, sql_text, version_count
2 from v$sqlarea
3 where sql_text like 'select ename%'
SQL_ID SQL_TEXT VERSION_COUNT
------------- ----------------------------------------------------- -------------
6y7xpur0d4jty select ename from emp_t where empno = :"SYS_B_0" 14
SQL> select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions, fetches
2 from v$sql
3 where sql_text like 'select ename%'
SQL> /
SQL_ID CHLD_NO PLAN_HASH LOADS PARSE_CALLS EXECUTIONS FETCHES
------------- ---------- ---------- ---------- ----------- ---------- ----------
6y7xpur0d4jty 0 2617457611 1 1 1 1
6y7xpur0d4jty 1 2617457611 1 1 1 1
6y7xpur0d4jty 2 2617457611 1 1 1 1
6y7xpur0d4jty 3 2617457611 1 1 1 1
6y7xpur0d4jty 4 2617457611 1 1 1 1
6y7xpur0d4jty 5 2617457611 1 1 1 1
6y7xpur0d4jty 6 2617457611 1 1 1 1
6y7xpur0d4jty 7 2617457611 1 1 1 1
6y7xpur0d4jty 8 2617457611 1 1 1 1
6y7xpur0d4jty 9 2617457611 1 1 1 1
6y7xpur0d4jty 10 2617457611 1 1 1 1
6y7xpur0d4jty 11 2617457611 1 1 1 1
6y7xpur0d4jty 12 2617457611 1 1 1 1
6y7xpur0d4jty 13 2443308290 1 1 1 1
14 개의 행이 선택되었습니다.
SIMILAR로 설정하면 히스토그램이 없을 때 FORCE와 똑같이 작동함
- 값 분포가 균등한 컬럼은 히스토그램 없이 통계를 만들어 커서를 공유
- 값 분포가 균등하지 않은 컬럼에 대해서는 히스토그램을 만들어 옵티마이저가 좋은 실행계획을 만들도록 유도(라이브러리 캐시 효율에는 도움이 되지 않음)
SQL> drop table t purge;
테이블이 삭제되었습니다.
경 과: 00:00:01.95
SQL> create table t
2 nologging
3 as
4 select rownum no from all_objects
5 where rownum < 10000;
테이블이 생성되었습니다.
SQL> create index t_idx on t(no);
인덱스가 생성되었습니다.
SQL> analyze table t compute statistics
2 for table for all indexes for all columns size 1;
테이블이 분석되었습니다.
SQL> alter session set cursor_sharing = similar;
세션이 변경되었습니다.
SQL> set timing on
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1..10000
5 loop
6 execute immediate
7 'select /* similar */ count(*) from t where no = ' || i
8 into l_cnt;
9 end loop;
10 end;
11 /
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:01.03
SQL> select sql_id, sql_text, version_count
2 from v$sqlarea
3 where sql_text like 'select /* similar%'
4 /
SQL_ID SQL_TEXT VERSION_COUNT
-------------- ------------------------------------------------------------- -------------
9a5hvy0j9y82q select /* similar */ count(*) from t where no = :"SYS_B_0" 1
SQL> analyze table t compute statistics
2 for table for all indexes for all columns size 100;
통계수집 : 히스토그램 생성
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1..10000
5 loop
6 execute immediate
7 'select /* similar */ count(*) from t where no = ' || i
8 into l_cnt;
9 end loop;
10* end;
SQL> /
PL/SQL 처리가 정상적으로 완료되었습니다.
히스토 그램을 생성했으므로 각 입력값별로 CHILD커서를 만들어야 했고, 이것은 아래 처럼 매번 하드파싱할 때 보다 더 나쁜 결과를 초해한다.
경 과: 00:00:09.09
SQL>
SQL> select sql_id, sql_text, version_count
2 from v$sqlarea
3 where sql_text like 'select /* similar%'
4 /
SQL_ID SQL_TEXT VERSION_COUNT
------------- -------------------------------------------------------------------------------- -------------
9a5hvy0j9y82q select /* similar */ count(*) from t where no = :"SYS_B_0" 766
9a5hvy0j9y82q select /* similar */ count(*) from t where no = :"SYS_B_0" 1026
9a5hvy0j9y82q select /* similar */ count(*) from t where no = :"SYS_B_0" 1026
9a5hvy0j9y82q select /* similar */ count(*) from t where no = :"SYS_B_0" 1026
SQL> alter session set cursor_sharing = exact;
세션이 변경되었습니다.
##하드파싱
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1 .. 10000
5 loop
6 execute immediate
7 'select /* exact */ count(*) from t where no = ' || i
8 into l_cnt;
9 end loop;
10 end;
11 /
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:06.89
SQL>
SQL> select count(*)
2 from v$sqlarea
3 where sql_text like 'select /* exact%'
SQL> /
COUNT(*)
----------
4852
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
경 과: 00:00:00.34
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1..10000
5 loop
6 execute immediate
7 'select /* bind */ count(*) from t where no = :no '
8 into l_cnt
9 using i;
10 end loop;
11 end;
12 /
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.43
SQL> ed
file afiedt.buf(이)가 기록되었습니다
1 select sql_id, sql_text, version_count
2 from v$sqlarea
3* where sql_text like 'select /* bind%'
SQL> /
SQL_ID SQL_TEXT VERSION_COUNT
------------- ------------------------------------------------------------------------ -------------
ctu0jcy938aqg select /* bind */ count(*) from t where no = :no 1
코어 오라클 데이터베이스 스터디 모임 에서 2012년에 오라클 성능 고도화 원리와 해법 I 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3127
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.