#30_140724_WSHOP2_SHARED POOL

# SHARED POOL

• shared pool 의 라이브러리 캐쉬 영역을 
• 효율적으로 사용할 수 있는 방법

l Library cache의 역할

- parsing 된 정보를 메모리에 올려놓고 똑 같은 SQL이 들어오면 parsing을 생략(soft parse)을 위한 역할

- 만약 다시 parsing(hard parse) 한다면 똑 같은 SQL이 없기 때문이다

l 똑 같은 SQL 이란(parsing 생략을 위한)

1.     대소문자 구문

2.     공백이나 들여쓰기 구분

3.     테이블의 소유자가 틀릴떄도 구분

4.     Literal SQL 구분

5.     예를 들면

SQL> select ename, sal from emp where empno = 7788;

SQL> select ename, sal from     emp where empno = 7788;

SQL> connect /as sysdba

SQL> select sql_text

from v$sql

where sql_text like '%7788%';

- 7788이 포함돼 있는 SQL을 linrary cache에서 검색

SQL_TEXT --------------------------------------------------------------------------------------------------------------------------------- select ename, sal from emp where empno = 7788 select sql_text from v$sql where sql_text like '%7788%' select ename, sal from     emp where empno = 7788 - 위에서 수행항 select 문에 띄어쓰기가 다르기 때문에 2개가 library cache 에 포함되어 있는 것을 볼 수 있다

6.     또 다른 예(literal SQL 구분)

SQL> select ename, sal from emp where empno = 7788;

SQL> select ename, sal from emp where empno = 7902;

- 위 2개의 SQL은 서로 다른 문장이다. 그래서 hard parsing을 수행한다

l Literal과 바인딩의 차이

- 리터럴은 select * from table where empno=7788 처럼 값을 직접 쿼리에 적용시켜 대입시키는 방법

- 바인딩은 select * from table where empno=:number 처럼 작성되어지며 프롬프트가 :number를 입력을 기다린다

n Literal과 바인딩 변수로 쿼리를 수행할 때 차이

- 아래의 수행결과를 통해 바인딩 변수를 이용했을 때 literal 보다 훨씬 빠른 수행결과를 가져온다

SQL> declare  type rc is ref cursor;  l_rc rc;  l_dummy all_objects.object_name%type;  l_start number default dbms_utility.get_time; begin  for i in 1 .. 20000  loop   open l_rc for   'select object_name from all_objects where object_id = ' || i;   fetch l_rc into l_dummy;   close l_rc;  end loop;  dbms_output.put_line ( round( (dbms_utility.get_time - l_start)/100, 2) || 'seconds'); end; /

SQL>select sql_text from v$sql where sql_text like 'select object_name%'; ………………………………………………………. select object_name from all_objects where object_id = 642 select object_name from all_objects where object_id = 559 select object_name from all_objects where object_id = 673 select object_name from all_objects where object_id = 712 select object_name from all_objects where object_id = 714 select object_name from all_objects where object_id = 562 select object_name from all_objects where object_id = 584 select object_name from all_objects where object_id = 628 select object_name from all_objects where object_id = 586 select object_name from all_objects where object_id = 716 select object_name from all_objects where object_id = 574 ………………………………………………………. - 수많은 SQL로 인해 library cache가 꽉차게 되고 느려진다

declare  type rc is ref cursor;  l_rc rc;  l_dummy all_objects.object_name%type;  l_start number default dbms_utility.get_time; begin  for i in 1 .. 1000  loop   open l_rc for 'select object_name from all_objects where object_id = :x' using i;  --바인드변수를 사용함 - parsing을 한번만 수행하기 떄문에 위의 PL/SQL 보다 훨씬 빨리 끝난다   fetch l_rc into l_dummy;   close l_rc;  end loop;  dbms_output.put_line ( round ( (dbms_utility.get_time - l_start) / 100, 2) || 'seconds..'); end; /

l 만약 바인드 변수를 사용하지 않고 리터럴 SQL로 수행되도록 프로그래밍이 됐다면 해결방법은?

- 오라클 파라미터인 cursor_sharing 을 force로 지정하면 오라클이 알아서 literal SQL을 bind variable로 변환한다

l cursor_sharing 파라미터를 조절해서 literal SQL을 

     bind variable로 변경하는 테스트

1.     SQL> show parameter cursor_sharing

- exact : literal SQL을 자동으로 bind 변수로 변경하지 않는다

2.     SQL> alter system flush shared_pool;

3.     SQL> select ename, sal from scott.emp where empno = 7788;

4.     SQL> select ename, sal from scott.emp where empno = 7902;

5.     SQL> select sql_text from v$sql where sql_text like 'select ename%';

6.     SQL> connect /as sysdba

7.     SQL> alter system set cursor_sharing = force scope=spfile;

   - force로 변경하면 자동으로 bind 변수로 변경한다

8.     SQL> shutdown immediate

9.     SQL> startup

10.   3번 ~ 4번까지 수행하고 확인

SQL> show parameter cursor_sharing NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ cursor_sharing                       string      EXACT SQL> alter system flush shared_pool; SQL> select ename, sal from scott.emp where empno = 7788; ENAME             SAL ---------- ---------- SCOTT            8000 SQL> select ename, sal from scott.emp where empno = 7902; ENAME             SAL ---------- ---------- FORD             3000 SQL> select sql_text from v$sql where sql_text like 'select ename%'; SQL_TEXT ------------------------------------------------------------------------------------------------------------------------------------------ select ename, sal from scott.emp where empno = 7788 select ename, sal from scott.emp where empno = 7902 SQL> alter system set cursor_sharing = force scope=spfile; SQL> shutdown immediate; SQL> startup SQL> show parameter cursor_sharing NAME                                 TYPE                             VALUE ------------------------------------ -------------------------------- ------------------------------ cursor_sharing                       string                           FORCE SQL> select ename, sal from scott.emp where empno = 7788; ENAME             SAL ---------- ---------- SCOTT            8000 SQL> select ename, sal from scott.emp where empno = 7902; ENAME             SAL ---------- ---------- FORD             3000 SQL> select sql_text from v$sql where sql_text like 'select ename%';                                  SQL_TEXT ------------------------------------------------------------------------------------------------------------------------------------------ select ename, sal from scott.emp where empno = :"SYS_B_0" - 오라클이 알아서 bind variable 로 변경한 것을 확인할 수 있다