오라클 성능 고도화 원리와 해법 I (2012년)
Cursor_Sharing 0 0 57,766

by 구루비스터디 Cursor Sharing [2018.03.20]


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

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입