Slide 1

Transcription

1 <Insert Picture Here> Oracle Database 11g Result Cache 한국오라클 TSC 본부김태형

2 Agenda 1. SQL Query Result Cache Server-side Client-side 2. PL/SQL Function Result Cache Server-side 3. Client Side Result Cache 4. Database Resident Connection Pooling (DRCP) Server-side pool 5. 기타유용한기능 Adaptive Cursor Sharing 2/72

3 <Insert Picture Here> 1. SQL Result Cache Server-side Client-side 3/72

4 SQL Result Cache /*+result_cache*/ 힌트를사용하여쿼리를수행하여 result 를저장 동일한쿼리수행시테이블을직접조회하는대신캐시로부터결과를가져오기때문에성능향상기대 데이터변경시 Cache 는자동으로무효화되기때문에 SQL RESULT CACHE 를사용하더라도항상정확한결과를보장함 Query Result Cache SELECT SELECT Session 1 Session 2 4/72

5 Server Results Cache 동작이해 Query 결과, query 블록, PL/SQL function 결과등을 Database 레벨에서자동으로캐쉬 캐쉬는 SQL 문장들, 세션들사이에서서로공유되고, 투명하게사용됨 읽기중심의데이터에대해 2 배이상의성능효과 join result query is 1 executes cached cached Group result by join Table 4 join query 2 uses cached result transparently Group by Table 1 join Table 2 Table 3 join Table 5 Table 5 5/72

6 6/72 SQL Result Cache 의 Memory

7 7/72 SQL Result Cache

8 SQL Result Cache 저장영역 Result cache memory 는 SGA 의 Shared Pool 영역에저장 메모리관리정책에따른기본할당값 MEMORY_TARGET 사용시 : MEMORY_TARGET 의 0.25% SGA_TARGET 사용시 : SGA_TARGET 의 0.5% SHARED_POOL_SIZE 사용시 : SHARED_POOL_SIZE 의 1% RESULT_CACHE_MAX_SIZE 최소값은 0, result caching disable 최대값은 shared pool 크기의 75% 변경시 Instance restart 필요 8/72

9 MV vs. Results Cache 유사한기능으로 Materialized view 와의비교 Materialized view 데이터베이스스토리지에저장 데이터변경시 mv 는알지못하고수동으로리프레시하지않을시최신의정보를반영하지못함 서브테이블에대한 base query 재실행필요 rewrite 알고리즘적용 Results Cache 데이터베이스메모리에저장 Shutdown 또는 result_cache 공간부족시삭제처리됨 데이터변경시자동리프레시 동일한 SQL과데이터가변경되지않을때재사용 9/72

10 Setting up Query Result Cache 매개변수 result_cache_max_size 설명 결과캐시의최대크기 (5 MB 인경우 '5M' 으로입력 ). 이값을 0 으로설정하면결과캐싱은완전히비활성화됩니다. result_cache_max_result result_cache_mode result_cache_remote_expiration 하나의 result 가사용될수있는최대캐시크기를 result_cache_memory 전체크기에대한 % 로설정. 디폴트는 5% 이매개변수를 FORCE 로설정하면모든쿼리결과가 ( 캐시사이즈기준을만족하는한 ) 캐시에저장 특정 SQL 에대해 /*+no_result_cache*/ 로제외설정가능. 디폴트는 MANUAL 로, 힌트 /*+result_cache*/ 를사용한쿼리에대해서만결과가캐시에저장됩니다. 원격오브젝트에대한쿼리결과로캐시된데이터가유효한시간 ( 분단위 ) 을설정합니다. 디폴트값은 0 입니다. 10/72

11 Using the Result_Cache Hint result_cache_mode = MANUAL : select /*+ result_cache */ department_id, avg(salary) from employees group by department_id; result_cache_mode = FORCE : SELECT /*+ NO_RESULT_CACHE */ department_id, avg(salary) from employees group by department_id; 11/72

12 Viewing the Memory Allocation Statistics for Result Cache Create the report set serveroutput on execute dbms_result_cache.memory_report execute dbms_result_cache.flush Flush Cache 12/72

13 dbms_result_cache 패키지 (Report) set serveroutput on size execute dbms_result_cache.memory_report R e s u l t C a c h e M e m o r y R e p o r t [Parameters] Block Size = 1K bytes Maximum Cache Size = 2,560K bytes (2,560 blocks) Maximum Result Size = 128K bytes (128 blocks) [Memory] Total Memory = 126,736 bytes [0.041% of the Shared Pool]... Fixed Memory = 5,132 bytes [0.002% of the Shared Pool]... Dynamic Memory = 121,604 bytes [0.040% of the Shared Pool]... Overhead = 88,836 bytes... Cache Memory = 32K bytes (32 blocks)... Unused Memory = 21 blocks... Used Memory = 11 blocks... Dependencies = 4 blocks (4 count)... Results = 7 blocks... SQL = 5 blocks (4 count)... Invalid = 2 blocks (2 count) PL/SQL procedure successfully completed. 13/72

14 dbms_result_cache 패키지 (flush) Cache 전체삭제 begin dbms_result_cache.flush; end; Cache 부분삭제 begin dbms_result_cache.invalidate('arup','customers'); end; 14/72

15 서브쿼리 (Cache 유효성 ) select prod_subcategory, revenue from ( select /*+ result_cache */ p.prod_category, p.prod_subcategory, sum(s.amount_sold) revenue from products p, sales s where s.prod_id = p.prod_id and s.time_id between to_date('01-jan-1990','dd-mon-yyyy') and to_date('31-dec-2007','dd-mon-yyyy') group by rollup(p.prod_category, p.prod_subcategory) ) where prod_category = 'software/other' / 15/72

16 Cache-in 조건 Cache-in 조건 현재쿼리의결과가 cache 되어있는않을때 그리고그쿼리의결과가 RESULT_CACHE_MAX_RESULT 보다작을때 /*+result_cache*/ 힌트를주더라도무시되는제약조건 Dictionary 및 temporary 테이블에대한쿼리 시퀀스의 CURRVAL / NEXTVAL 에대한쿼리 - 쿼리수행마다결과값이달라지는일시적쿼리이기에제외함 current_date, current_timestamp, local_timestamp, userenv/sys_context (with non-constant variables), sys_guid, sysdate, sys_timestamp 등의함수호출이포함된 query Non-deterministic PL/SQL 함수를호출하는 query 16/72

17 Cache-hit 조건 Cache-hit 이란? 실제로실행했을때동일한결과를 result cache 로부터얻었을때 Cache-hit 조건 동일한 SQL 과동시에동일한 parameter 동일한 parameter 란? Bind 변수 dbtimezone, sessiontimezone,userenv/sys_context (with constant variables), uid, user 등의보다정적인함수호출결과 NLS 등의환경 parameters 17/72

18 Cache-out 조건 Cache 된 result 는다음의경우에 result cache 로부터 out Age-out. Result cache 역시 LRU cache 이기때문 Invalidation. 해당 query 가참조하는 object 에 DML 등의변경이일어나는경우 18/72

19 SQL Result Cache ( 딕셔너리뷰 ) 뷰 (G)V$RESULT_CACHE_STATISTICS 설명 메모리사용량등을포함하는다양한설정을확인 (G)V$RESULT_CACHE_MEMORY SQL Result Cache 의메모리구성을확인 (G)V$RESULT_CACHE_OBJECTS SQL Result Cache 를구성하는오브젝트들을확인 (G)V$RESULT_CACHE_DEPENDENCY SQL Result Cache 를구성하는다양한오브젝트간의종속성을확인 19/72

20 SQL Result Cache 초기화 : DBMS_RESULT_CACHE connect / as sysdba Connected. SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 150 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 1000 SET SERVEROUTPUT ON show parameter result_cache_mode NAME FORCE로설정하면 TYPE 모든쿼리결과가 VALUE 캐시에저장되고 , 디폴트 MANUAL은 result_cache_mode 힌트를사용한string 쿼리에대해서만 MANUAL show parameter memory_target 결과가캐시에저장됩니다. NAME TYPE VALUE memory_target big integer 412M show parameter result_cache_max_size NAME 결과캐시의 TYPE 최대크기 VALUE result_cache_max_size big integer 1056K default; memory_target의약0.25% show parameter result_cache_max_result NAME 하나의결과에 TYPE 사용될수있는 VALUE 최대캐시크기를 결정합니다.(%) result_cache_max_result integer 5 20/72

21 SQL Result Cache 초기화 : DBMS_RESULT_CACHE execute dbms_result_cache.flush; -- result cache 를 flush PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 alter system flush shared_pool; System altered. Elapsed: 00:00:00.34 execute dbms_result_cache.memory_report; -- result cache 현황파악 R e s u l t C a c h e M e m o r y R e p o r t [Parameters] Block Size = 1K bytes -- 내부적으로 1KB 단위로관리 (not configurable) Maximum Cache Size = 1056K bytes (1056 blocks) -- result_cache_max_size에대응 Maximum Result Size = 52K bytes (52 blocks) -- result_cache_max_result에대응 [Memory] Total Memory = 5132 bytes [0.003% of the Shared Pool]... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]... Dynamic Memory = 0 bytes [0.000% of the Shared Pool] -- 0; 현재 cache된 result가없음 PL/SQL procedure successfully completed. Elapsed: 00:00: /72

22 SQL Result Cache Plan 비교 connect hr/hr Connected. explain plan for 2 select /*+ result_cache */ * from departments; Explained. Elapsed: 00:00:00.30 set echo off select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:01 1 RESULT CACHE ggq8ztnxqw8ft4ucg3art21sjm 2 TABLE ACCESS FULL DEPARTMENTS (0) 00:00: Result Cache Information (identified by operation id): column-count=4; dependencies=(hr.departments); name="select /*+ result_cache */ * from departments" 14 rows selected. Elapsed: 00:00:02.17 디폴트 MANUAL 은힌트를사용한쿼리에대해서만결과가캐시에저장됩니다. 22/72

23 SQL Result Cache (Inline view) Plan 비교 connect hr/hr Connected. explain plan for 2 select department_name, emp_count 3 from (select /*+ result_cache */ department_id, count(*) emp_count from employees group by department_id) e, 4 departments d 5 where e.department_id = d.department_id; Explained. Elapsed: 00:00:00.12 set echo off select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT Plan hash value: /72

24 SQL Result Cache (Inline view) Plan 비교 Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (29) 00:00:01 1 MERGE JOIN (29) 00:00:01 2 TABLE ACCESS BY INDEX ROWID DEPARTMENTS (0) 00:00:01 3 INDEX FULL SCAN DEPT_ID_PK 27 1 (0) 00:00:01 * 4 SORT JOIN (40) 00:00:01 5 VIEW (25) 00:00:01 6 RESULT CACHE 7n7dsf9ukwqcv7cwbupmmdntaj 7 HASH GROUP BY (25) 00:00:01 8 TABLE ACCESS FULL EMPLOYEES (0) 00:00: Predicate Information (identified by operation id): access("e"."department_id"="d"."department_id") filter("e"."department_id"="d"."department_id") Result Cache Information (identified by operation id): column-count=2; dependencies=(hr.employees); name="select /*+ result_cache */ department_id, count(*) emp_count from employees group by departm ent_id" 26 rows selected. Elapsed: 00:00: /72

25 Test : 실제수행 1 connect hr/hr Connected. select /*+ result_cache */ * from departments; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID Administration Marketing Purchasing Human Resources Shipping IT Public Relations Sales Executive Finance Accounting Treasury Corporate Tax Control And Credit Shareholder Services Benefits Manufacturing Construction Contracting Operations IT Support /72

26 26/72 Test : 실제수행 2 27 rows selected. 220 NOC IT Helpdesk Government Sales Retail Sales Recruiting Payroll 1700 Elapsed: 00:00:00.05 select department_name, emp_count 2 from (select /*+ result_cache */ department_id, count(*) emp_count 3 from employees 4 group by department_id) e, departments d 5 where e.department_id = d.department_id; DEPARTMENT_NAME EMP_COUNT Administration 1 Marketing 2 Purchasing 6 Human Resources 1 Shipping 45 IT 5 Public Relations 1 Sales 34 Executive 3 Finance 6 Accounting 처음쿼리돌렸을때의속도입니다 rows selected. Elapsed: 00:00:00.05 set echo off

27 Test : 수행확인 (v$result_cache_statistics) connect / as sysdba Connected. col name format a55 select * from v$result_cache_statistics; ID NAME VALUE Block Size (Bytes) Block Count Maximum Block Count Current 32 4 Result Size Maximum (Blocks) 52 5 Create Count Success 2 Result_Cache 6 Create Count Failure 0 7 Find Count 등록확인 0 8 Invalidation Count 0 9 Delete Count Invalid 0 10 Delete Count Valid 0 10 rows selected. Elapsed: 00:00: /72

28 28/72 Test 결과 : 실제수행 1 ( 재실행 ) connect hr/hr Connected. select /*+ result_cache */ * from departments; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID Administration Marketing Purchasing Human Resources Shipping IT Public Relations Sales Executive Finance Accounting Treasury Corporate Tax Control And Credit Shareholder Services Benefits Manufacturing Construction Contracting Operations IT Support NOC IT Helpdesk Government Sales Retail Sales Recruiting 다시쿼리돌렸을때의속도입니다 Payroll rows selected. Elapsed: 00:00:00.01

29 Test 결과 : 실제수행 2 ( 재실행 ) Elapsed: 00:00:00.01 select department_name, emp_count 2 from (select /*+ result_cache */ department_id, count(*) emp_count 3 from employees 4 group by department_id) e, departments d 5 where e.department_id = d.department_id; DEPARTMENT_NAME EMP_COUNT Administration 1 Marketing 2 Purchasing 6 Human Resources 1 Shipping 45 IT 5 Public Relations 1 Sales 34 Executive 3 Finance 6 Accounting 2 11 rows selected. Elapsed: 00:00:00.01 set echo off 다시쿼리돌렸을때의속도입니다. 29/72

30 Test 결과 : Result Cache 적용확인 (v$result_cache_statistics) connect / as sysdba Connected. col name format a55 select * from v$result_cache_statistics; ID NAME VALUE Block Size (Bytes) Block Count Maximum Block Count Current 32 4 Result Size Maximum (Blocks) 52 5 Create Count Success 2 6 Create Count Failure cache-hit! 0 7 Find Count 2 8 Invalidation Count 0 9 Delete Count Invalid 0 10 Delete Count Valid 0 10 rows selected. Elapsed: 00:00: /72

31 <Insert Picture Here> 2. PL/SQL Function Result Cache 31/72

32 PL/SQL Function Cache First Query Select Calculate_Comp ( ) Cached results Select Calculate_Comp() Select Select Calculate_ Calculate_Comp( Comp( ) Subsequent Queries 32/72

33 PL/SQL Function Cache First Query Y:= HR.Calculate_Com p Cached results Y:= Y:= HR.Calculate_Com p p Y:= HR.Calculate_Com HR.Calculate_Com p Subsequent Queries 33/72

34 PL/SQL Function Result Cache 함수가반복실행되더라도동일한결과가반환될것으로예상될때적합 connect hr/hr Connected. create or replace function get_tax_rate ( p_cust_id customers.cust_id%type ) return sales_tax_rate.tax_rate%type result_cache relies_on (sales_tax_rate, customers) is l_ret sales_tax_rate.tax_rate%type; begin select tax_rate into l_ret from sales_tax_rate t, customers c where c.cust_id = p_cust_id and t.state_code = c.state_code; -- simulate some time consuming -- processing by sleeping for 1 sec dbms_lock.sleep (1); return l_ret; exception when NO_DATA_FOUND then return NULL; when others then raise; end; 34/72 / 하부테이블에대한종속관계설정. 데이터변경시캐시무효화

35 PL/SQL Function Result Cache select get_tax_rate(1) from dual; GET_TAX_RATE(1) row selected. 최초실행시 1.21 초 Elapsed: 00:00:01.21 select get_tax_rate(1) from dual; GET_TAX_RATE(1) row selected. 재실행시 0.01 초 캐시에저장된 Result 사용 Elapsed: 00:00: /72

36 PL/SQL Function Result Cache select get_tax_rate(&n) from dual; Enter value for n: 5 old 1: select get_tax_rate(&n) from dual new 1: select get_tax_rate(5) from dual GET_TAX_RATE(5) row selected. Elapsed: 00:00:01.18 n: 5 일때 리프레시자동수행됨 최초실행시 0.18 초 / Enter value for n: 5 old 1: select get_tax_rate(&n) from dual new 1: select get_tax_rate(5) from dual GET_TAX_RATE(5) row selected. n: 5 일때 재실행시 0.00 초 캐시에저장된 Result 사용 36/72 Elapsed: 00:00:00.00 /

37 캐시 vs. 패키지변수 SQL Result Cache 와 PL/SQL Function Cache 메모리기반으로구현 데이터베이스인스턴스단위로관리되기때문에서로다른세션에서사용가능 데이터벤경시자동리프레시 패키지변수 애플리케이션은테이블로우또는함수가아닌변수에접근 동일세션내에서만사용가능 ( 제한적 ) 데이터변경시수동으로리프레시수행 수동리프레시미적용시변경이전의데이터가조회될가능발생 37/72

38 PL/SQL Function Result Cache (Cache-in) RESULT_CACHE 절 RELIES_ON 절 : dependent object 명시 PL/SQL function 의 caching 제약사항 데이터타입상의제한 : IN parameter 중 BLOB, CLOB, NCLOB, REF CURSOR, Collection, Object, Record 타입이있는경우. 그리고 Return 타입이 BLOB, CLOB, NCLOB, REF CURSOR, Object 이거나 BLOB, CLOB, NCLOB, REF CURSOR, Object 타입을포함하는 Record 또는 Collection 인경우 OUT/IN OUT parameter 를가진경우. Invoker s right 으로정의된경우또는 anonymous block 내에서정의된경우 Error 예시 ) LINE/COL ERROR /0 PL/SQL: Compilation unit analysis terminated 1/39 PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms with OUT or IN OUT parameters 38/72

39 Cache-hit 조건 동일한함수, 동일한 parameter parameter 란? function 의 parameter 를의미 PL/SQL 코드내에서 A = A 와같은두값이 parameter 로왔을때동일한 parameter 로취급되지않는다. (bit for bit 로동일 ) 39/72

40 TEST : PL/SQL Function Result Cache 설정및초기화 connect / as sysdba Connected. SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 150 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 1000 SET SERVEROUTPUT ON show parameter result_cache_mode NAME TYPE VALUE result_cache_mode string MANUAL show parameter result_cache_max_size NAME TYPE VALUE result_cache_max_size big integer 1056K show parameter result_cache_max_result NAME TYPE VALUE result_cache_max_result integer 5 40/72

41 TEST : PL/SQL Function Result Cache 설정및초기화 execute dbms_result_cache.flush; PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 alter system flush shared_pool; System altered. Elapsed: 00:00:00.67 execute dbms_result_cache.memory_report; R e s u l t C a c h e M e m o r y R e p o r t [Parameters] Block Size = 1K bytes Maximum Cache Size = 1056K bytes (1056 blocks) Maximum Result Size = 52K bytes (52 blocks) [Memory] Total Memory = 5132 bytes [0.003% of the Shared Pool]... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]... Dynamic Memory = 0 bytes [0.000% of the Shared Pool] - 아직 cache 된것이없음 PL/SQL procedure successfully completed. Elapsed: 00:00: /72

42 TEST : PL/SQL Function Result Cache 함수생성 connect hr/hr Connected. create or replace function EMP_COUNT(dept_no number) 2 return number 3 result_cache relies_on (employees) - result cache 를지정하는 syntax 4 is 5 v_count number; 6 begin 7 select count(*) into v_count 8 from employees 9 where department_id = dept_no; return v_count; 12 end; 13 / Function created. Elapsed: 00:00: /72

43 TEST : PL/SQL Function Result Cache 함수호출 1 차 connect hr/hr Connected. select department_name, emp_count(department_id) no_of_emps 2 from departments 3 where department_name = 'Accounting' 4 / DEPARTMENT_NAME NO_OF_EMPS Accounting 2 1 row selected. Elapsed: 00:00:00.09 처음 Cache 에등록하면서걸린시간 43/72

44 TEST : PL/SQL Function Result Cache 함수호출 1 차 ( 현황파악 ) SET ECHO ON SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 150 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 1000 connect / as sysdba Connected. --- Establish the cache content set serveroutput on execute dbms_result_cache.memory_report R e s u l t C a c h e M e m o r y R e p o r t [Parameters] Block Size = 1K bytes Maximum Cache Size = 1056K bytes (1056 blocks) Maximum Result Size = 52K bytes (52 blocks) [Memory] Total Memory = bytes [0.054% of the Shared Pool]... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]... Dynamic Memory = bytes [0.051% of the Shared Pool]... Overhead = bytes... Cache Memory = 32K bytes (32 blocks)... Unused Memory = 29 blocks... SET ECHO ON Used Memory = 3 blocks SET FEEDBACK 1... Dependencies = 2 blocks (2 count)... Results = 1 blocks... PLSQL = 1 blocks (1 count) PL/SQL procedure successfully completed. 44/72 Elapsed: 00:00:00.06

45 TEST : PL/SQL Function Result Cache 함수호출 1 차 connect / as sysdba Connected. col name format a55 select type, namespace,status, scan_count,name 2 from v$result_cache_objects 3 / TYPE NAMESPACE STATUS SCAN_COUNT NAME Dependency Published 0 HR.EMP_COUNT Dependency Published 0 HR.EMPLOYEES Result PLSQL Published 0 "HR"."EMP_COUNT"::8."EMP_COUNT"#fac892c7867b54c6 #1 3 rows selected. Elapsed: 00:00:00.01 지금처음 cache 되었고아직참조되지않았음 45/72

46 TEST : PL/SQL Function Result Cache 함수호출 1 차 (v$result_cache_statistics) connect / as sysdba Connected. col name format a55 select * from v$result_cache_statistics; ID NAME VALUE Block Size (Bytes) Block Count Maximum Block Count Current 32 4 Result Size Maximum (Blocks) 52 5 Create Count Success 1 - Cache-in! 6 Create Count Failure 0 7 Find Count 0 8 Invalidation Count Cache 가 1개생성된 0 9 Delete Count Invalid 것을확인 0 10 Delete Count Valid 0 10 rows selected. Elapsed: 00:00: /72

47 TEST 결과 : PL/SQL Function Result Cache 함수호출 2 차 connect hr/hr Connected. select department_name, emp_count(department_id) no_of_emps 2 from departments 3 where department_name = 'Accounting' 4 / DEPARTMENT_NAME NO_OF_EMPS Accounting 2 1 row selected. Elapsed: 00:00:00.00 등록된 Cache 사용으로속도가감소되었다. Elapsed: 00:00: /72

48 Test 결과 : PL/SQL Function Result Cache 함수호출 2 차 connect / as sysdba Connected. col name format a55 select * from v$result_cache_statistics; ID NAME VALUE Block Size (Bytes) Block Count Maximum Block Count Current 32 4 Result Size Maximum (Blocks) 52 5 Create Count Success 1 6 Create Count Failure 0 7 Find Count 1 - Cache-hit! 8 Invalidation Count Cache 가 1개사용으로 0 9 Delete Count Invalid Cache-hit 성공 0 10 Delete Count Valid 0 10 rows selected. Elapsed: 00:00: /72

49 Test 결과 : PL/SQL Function Result Cache 함수호출 2 차 Elapsed: 00:00:00.00 connect / as sysdba Connected. col name format a55 select type, namespace,status, scan_count,name 2 from v$result_cache_objects 3 / TYPE NAMESPACE STATUS SCAN_COUNT NAME Dependency Published 0 HR.EMP_COUNT Dependency Published 0 HR.EMPLOYEES Result PLSQL Published 1 - cache-hit! "HR"."EMP_COUNT"::8."EMP_COUNT"#fac892c7867b54c6 #1 3 rows selected. Elapsed: 00:00: /72

50 <Insert Picture Here> 3. Client Query Result Cache 50/72

51 OCI Consistent Client Cache 서버와클라이언트사이의 cache fusion 과같은효과 Application Server Consistent Caching Database Query 결과를클라이언트에캐쉬 읽기위주테이블에대한성능향상 Network round trip 제거에따른응답속도의향상 서버의 CPU 사용률절약 서버와클라이언트사이의데이터일관성 Result set 이변경되면캐쉬는능동적으로갱신됨 51/72

52 Client Query Result Cache 설정 클라이언트가대역폭이제한된네크웍링크를통해데이터를조회해야하는경우 데이터베이스캐시로부터전달받은결과를 Client 캐시로저장후사용하고자할때 이기능을사용하려면초기화매개변수를아래와같이수정해주기만하면됩니다 : CLIENT_RESULT_CACHE_SIZE = 1G 위구문은클라이언트캐시를 1GB 로설정하고있습니다. 이값은모든클라이언트의캐시사이즈를합산한결과로정의됩니다. ( 이매개변수는정적으로정의되므로데이터베이스를재시작해주어야합니다.) 클라이언트쪽에서는 SQLNET.ORA 파일의매개변수를수정하여캐시를설정합니다. 52/72

53 SQL Result Cache 파라미터 Using Client configuration file (sqlnet.ora) 매개변수 OCI_RESULT_CACHE_MAX_SIZE 설명 특정클라이언트내부의캐시사이즈를설정합니다. OCI_RESULT_CACHE_MAX_RSET_SIZE 결과셋의최대크기를설정합니다. OCI_RESULT_CACHE_MAX_RSET_ROWS 결과셋의최대로우수를설정합니다. 53/72

54 OCI statement caching : enable You can enable client-side query caching by: JDBC OCI, OCCI, ODP.Net, PHP, ODBC Server CLIENT_RESULT_CACHE_SIZE (default 0, cache disabled) 하나의클라이언트프로세스가가질수있는 result cache 의최대크기를지정한다. 0 으로지정하면클라이언트단의 result caching 이 disable 된다. Sqlnet.ora 파라미터인 OCI_RESULT_CACHE_MAX_SIZE 를통해 override 할수있다. CLIENT_RESULT_CACHE_LAG (optional, 3000ms default) 클라이언트 result cache 가서버단의변경에대해가질수있는 lag 의최대값이다. 디폴트는 3 초이다. OCI 캐쉬의일관성은기본적으로 OCIStmtExecute() call 에의한 check 를통해이루어진다. 만일서버호출이한동안없다면? 그렇다하더라도이파라미터에의해주기적으로 sync 를맞추게되는것이다. 54/72 Client (set in sqlnet.ora) OCI_RESULT_CACHE_MAX_SIZE (optional) OCI_RESULT_CACHE_MAX_RSET_SIZE (optional) OCI_RESULT_CACHE_MAX_RSET_ROWS (optional) Executing the CREATE TABLE or ALTER TABLE statements Embedding a SQL hint - /*+ result_cache */

55 OCI Consistent Client Cache Caveats Some restrictions views VPD DBlinks Monitor usage client_result_cache_stats$ Control the lag client_result_cache_lag (default 3000ms) 55/72

56 Using Client-side Query Cache Example init.ora example: CLIENT_RESULT_CACHE_SIZE - this to accommodate the -- total size of all queries that my be cached on the -- client ALTER TABLE statement: ALTER TABLE products RESULT_CACHE; SQL hint: SELECT /*+ result_cache */ product_name FROM products ORDER BY product_name; 56/72

57 Client side Cache-hit Client 란? JDBC-OCI, ODP.Net, OCCI, Pro*C/C++, Pro*COBOL, ODBC 등의 OCI 클라이언트를의미한다. OCI Statement Caching Client-side SQL query result cache 를사용하기위해서는반드시 OCI statement caching 이 enable 되어있어야한다. CLIENT_RESULT_CACHE_STATS$ 클라이언트단의 result cache 에대한통계를보여주는 view 이다. OCI 클라이언트가주기적으로이 view 를 update 하게된다. 내용은 V$RESULT_CACHE_STATISTICS 와거의흡사하다. Client-side query result cache 사용예 $ORACLE_HOME/rdbms/demo/cdemoqc.sql 및 $ORACLE_HOME/rdbms/demo/cdemoqc.c 에예제가주어진다. 57/72

58 Client Query Result Cache 적용가능환경및장점 적용가능환경 OCI8 드라이버를이용하는데이터베이스클라이언트스택 (C, C++, JDBC-OCI 등 ) 적용시이점 애플리케이션개발자들이 SQL Result Cache 를일관성있게구현해야할필요성을제거합니다 비용면에서보다저렴한클라이언트메모리를활용하여애플리케이션워킹셋을로컬에저장함으로써, 서버 - 사이드쿼리캐싱을클라이언트메모리로확장합니다. 서버리소스절감을통해서버확장성을개선합니다. 메모리관리, 결과셋의동시접근등투명한캐시관리기능을제공합니다. 서버의데이터가변경되는경우에도투명하고일관성있는방식으로캐시를유지합니다. RAC 환경에서의일관성을보장합니다. 58/72

59 예제 : Client Query Result Cache (OCI8) import java.sql.statement; public class CacheTest { private String jdbcurl = "jdbc:oracle:oci8:@prone3"; private Connection conn = null; public CacheTest( ) throws ClassNotFoundException { Class.forName("oracle.jdbc.driver.OracleDriver"); } public static void main(string[] args) throws ClassNotFoundException, SQLException { CacheTest check = new CacheTest(); check.dbconnect(); check.dosomething(); } public void dbconnect() throws SQLException { System.out.println("Connecting with URL="+jdbcURL+" as arup/arup"); try { conn = DriverManager.getConnection( jdbcurl, "arup", "arup"); System.out.println("Connected to Database"); } catch (SQLException sqlex) { System.out.println(" Error connecting to database : " + sqlex.tostring()); } 59/72

60 예제 : Client Query Result Cache (OCI8) } public void dosomething() throws SQLException { Statement stmt = null; ResultSet rset = null; try { stmt = conn.createstatement(); System.out.println("Created Statement object"); rset = stmt.executequery("select /*+ result_cache */ * from customers"); System.out.println("Retrieved ResultSet object"); if(rset.next()) System.out.println("Result:"+rset.getString(1)); } catch (SQLException sqlex) { } finally { try { System.out.println("Closing Statment & ResultSet Objects"); if (rset!= null) rset.close(); if (stmt!= null) stmt.close(); if (conn!= null) { System.out.println("Disconnecting..."); conn.close(); System.out.println("Disconnected from Database"); } } catch (Exception e) { } } } } 60/72

61 예제 : Client Query Result Cache (OCI8) 위의코드를 CacheTest.java 파일에저장하고컴파일을수행합니다 : $ORACLE_HOME/jdk/bin/javac CacheTest.java 이제컴파일된클래스를실행합니다 : $ORACLE_HOME/jdk/bin/java -classpath.:$oracle_home/jdbc/lib/ojdbc5.jar CacheTest Connecting with URL=jdbc:oracle:oci8:@PRONE3 as arup/arup Connected to Database Created Statement object Retrieved ResultSet object Result :M Closing Statment & ResultSet Objects Disconnecting... Disconnected from Database 실행작업을몇차례반복합니다. 실행이여러차례반복되면서, 아래의다이내믹뷰를통해클라이언트캐시에결과값이저장되었음을확인할수있습니다. select * from client_result_cache_stats$ / select * from v$client_result_cache_stats / 61/72

62 Client Query Result Cache Client-Side Query Result Cache 는일반적으로자주변경되지않는정적테이블에서 유용하게활용됩니다. ( 물론데이터가변경된다면캐시는다시리프레시됩니다.) Client-side Query Result Cache는캐시가서버가아닌클라이언트에저장된다는점에서 SQL Result Cache와차이를갖습니다. 따라서클라이언트가데이터를얻기위해서버에직접접촉할필요가없으며, 그결과로네트워크대역폭과서버 CPU를동시에절감할수있습니다. 62/72

63 <Insert Picture Here> 4. Database Resident Connection Pooling 63/72

64 Database Resident Connection Pooling 1: M 의관계 : 웹기반시스템에서과같은환경에서형성. 많은수의동시연결관리필요 전통적인클라이언트 - 사이드 / 미들 - 티어커넥션풀링의문제점 : 각각의풀은단일미들-티어노드로제한 풀의크기가증가하는경우데이터베이스서버의메모리리소스를소진 워크로드의분산이효율적으로수행되기어려움 64/72

65 Database Resident Connection Pooling Oracle Database 11g 에는 Database Resident Connection Pool(DRCP) 이라는이름의서버 - 사이드풀이새로추가되었습니다. DRCP 는 C, C++, PHP 등 OCI 드라이버를사용하는모든데이터베이스 클라이언트에서사용가능합니다. execute dbms_connection_pool.start_pool; tnsnames.ora PRONE3_POOL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prolin3.proligence.com)(port = 1521)) (CONNECT_DATA = (SERVER = POOLED) (SID = PRONE3) ) ) 65/72

66 Database Resident Connection Pool No Connection Pooling 11g Database Resident Connection Pooling Note: will require a new PHP driver to be released post /72

67 Database Resident Connection Pooling 이제데이터베이스연결을위한 connect 문자열을변경합니다. Client-Side Result Cache 섹션의예제코드를다시활용해보겠습니다 : private String jdbcurl = "jdbc:oracle:oci8:@prone3_pool"; 이것으로모든작업이완료되었습니다. 이제애플리케이션은서버가아닌풀로연결됩니다. 씬클라이언트와표준 JDBC 연결문자열을사용하는경우라면 POOLED 구문을사용할수있습니다 : prolin3. proligence.com:1521/prone3:pooled 위의구문을통해오라클에기본설정된디폴트풀이시작됩니다. DBMS_CONNECTION_POOL 패키지에포함된 CONFIGURE_POOL 프로시저를사용하여풀의설정을변경할수있습니다. 67/72

68 Connection Pooling (Parameter) 매개변수 설명 POOL_NAME 풀의이름 ( 작은따옴표기호 ('') 를사용합니다 ) MINSIZE MAXSIZE INCRSIZE 풀에유지되는세션에최소수 풀에서허용되는최대세션수 폴링 (polling) 대상서버가접근불가능한경우, 이매개변수에설정된값만큼서버의수를증가시킵니다. SESSION_CACHED_CURSORS 'session cached cursor' 를활성화합니다. INACTIVITY_TIMEOUT MAX_THINK_TIME MAX_USE_SESSION MAX_LIFETIME_SESSION 지정된시간만큼세션이유휴상태를유지하면세션연결이해제됩니다. 클라이언트가풀로부터서버를가져온뒤, 이매개변수에설정된시간안에 SQL 구문을실행하지않으면서버연결이해제됩니다. 연결을풀에서가져올수있는최대횟수 세션의최대지속시간 68/72

69 <Insert Picture Here> 5. Adaptive Cursor Sharing 바인딩변수처리방안 69/72

70 Adaptive Cursor Sharing 과거바인트변수의문제점 => 정적인실행계획 바인드변수를사용하는 SQL 이처음실행될때의 Plan 으로고정된다는문제점발생 극복하기위해 Oracle Database 11g New Features 로 Adaptive Cursor Sharing 기능제공함으로써바인드변수의변경에따라개발자나관리자의별도의설정없이오라클데이터베이스 11g 의자체기능으로변경된실행계획을적용받게됩니다. CURSOR_SHARING = EXACT EXACT : (default) 완전히일치하는 SQL 에대해서만커서공유 ( 바인드변수사용 ) SIMILAR : SQL 의리터럴값이다른값으로대체되더라도 SQL 의미변화없고, 실행계획변화없고, Oracle 이자동으로생성한바인드변수로대체가능 FORCE : SQL 문장에사용된리터럴값이다른값으로대체될때 SQL 의미변화없다면, Oracle 이자동으로생성한바인드변수로대체가능 70/72

71 Adaptive Cursor Sharing Oracle database 11g 에서바인딩변수를사용하기위해서는별도의설정이필요하지않습니다. 테이블의통계정보가생성되고컬럼에대한히스토그램이생성되어있으면됩니다. 따라서오라클 11g 자체기능으로서기본적으로활성화됩니다. show user USER is "SCOTT" variable v_deptno number exec :v_deptno := 10 alter system flush shared_pool ; System altered. select /*TAGGING*/ count(*), max(sal) 2 from emp 3 where deptno = :v_deptno; COUNT(*) MAX(SAL) select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT SQL_ID 73mcauwj27cdv, child number select /*TAGGING*/ count(*), max(sal) from emp where deptno = :v_deptno Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT 2 (100) 1 SORT AGGREGATE TABLE ACCESS BY INDEX ROWID EMP (0) 00:00:01 * 3 INDEX RANGE SCAN EMP_IX 3 1 (0) 00:00:01 71/72

72 72/72