반응형

앞의 몇 개의 포스트를 통하여 Result Cache관련 내용을 확인 할 수 있었다. 이제 본격적으로 Result Cache를 어떻게 사용하고, 어떤 제약 조건들이 있는지에 대해서 확인해 보도록 하자.

먼저 MANUAL 방식에 대해서 테스트를 통하여 특징 및 주의 사항에 대해 확인해 보자.

 

- Test Cace#1

현재 DB Level의 Resut_Cache_mode는 Auto로 설정이 되어 있다. 세션 Level에서 Result_Cache_mode를 Manual로 변경해서 사용할 경우 Result Cache의 재 사용여부 확인.

 

- SYS Session

 

11:05:28 SYS 9919>show parameter result_cache_mode

 

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

result_cache_mode string AUTO

 

 

- Session#1

 

11:10:08 HR 10173>show parameter result_cache_mode

 

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

result_cache_mode string AUTO

11:10:15 HR 10173>alter session set result_cache_mode=manual ;

 

Session altered.

 

Elapsed: 00:00:00.00

11:10:26 HR 10173>show parameter result_cache_mode

 

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

result_cache_mode string MANUAL

 

 

- Session#2

 

11:11:39 HR 10094>show parameter result_cache_mode

 

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

result_cache_mode string AUTO

 

 

- Result Cache Object 확인

 

11:15:14 SYS 9919>SELECT * FROM v$result_cache_objects ;

 

no rows selected

 

Elapsed: 00:00:00.00

 

 

세션#1만 Result_Cache_mode를 Manual로 변경 하였다. SQL Result가 어떻게 사용되는지 확인해 보도록 하자.

 

- Session#1

 

11:13:45 HR 10173>SELECT /*+ result_cache */ department_id , MAX( salary ) FROM employees GROUP BY department_id ;

 

DEPARTMENT_ID MAX(SALARY)

------------- -----------

100 12000

30 11000

7000

90 24000

20 13000

70 10000

110 12000

50 8200

80 14000

40 6500

60 9000

10 4400

 

12 rows selected.

 

Elapsed: 00:00:00.35

11:16:35 HR 10173>set autotrace on exp stat

11:16:48 HR 10173>SELECT /*+ result_cache */ department_id , MAX( salary ) FROM employees GROUP BY department_id ;

 

12 rows selected.

 

Elapsed: 00:00:00.01

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1192169904

 

--------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 11 | 77 | 4 (25)| 00:00:01 |

| 1 | RESULT CACHE | 17kdyqzrjw8pgg5pnxz3y365s0

| 2 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |

| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

1 - column-count=2; dependencies=(HR.EMPLOYEES); name="SELECT /*+ result_cache */ department_id , MAX( salary ) FROM

employees GROUP BY department_id "

 

 

 

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

644 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

12 rows processed

 

 

 

- Sys Session

 

11:21:22 SYS 9919>SELECT cache_id , type , name , status , scan_count FROM v$result_cache_objects ;

 

CACHE_ID TYPE NAME STATUS SCAN_COUNT

------------------------------ -------------------- ------------------------------ ------------------ ----------

HR.EMPLOYEES Dependency HR.EMPLOYEES Published 0

17kdyqzrjw8pgg5pnxz3y365s0 Result SELECT /*+ result_cache */ dep Published 1

artment_id , MAX( salary ) FRO

M employees GROUP BY depart

ment_id

 

 

 

- Session#2

 

11:22:51 HR 10094>set autotrace on exp stat

11:23:07 HR 10094>SELECT /*+ result_cache */ department_id , MAX( salary ) FROM employees GROUP BY department_id ;

 

12 rows selected.

 

Elapsed: 00:00:00.01

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1192169904

 

--------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 11 | 77 | 4 (25)| 00:00:01 |

| 1 | RESULT CACHE | 17kdyqzrjw8pgg5pnxz3y365s0 | | | | |

| 2 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |

| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

1 - column-count=2; dependencies=(HR.EMPLOYEES); name="SELECT /*+ result_cache */ department_id , MAX( salary ) FROM

employees GROUP BY department_id "

 

 

 

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

644 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

12 rows processed

 

11:23:15 HR 10094>SELECT department_id , MAX( salary ) FROM employees GROUP BY department_id ;

 

12 rows selected.

 

Elapsed: 00:00:00.01

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1192169904

 

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 11 | 77 | 4 (25)| 00:00:01 |

| 1 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |

| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------------

 

 

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

7 consistent gets

0 physical reads

0 redo size

644 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

12 rows processed

 

 

초기 테스트 목적이 세션레벨에서 RESULT_CACHE_MODE를 변경해서 사용할 경우 다른 세션에서 캐시된 결과를 재 사용하는지 여부를 파악하기 위함 이었다. 하지만 실제 /*+result_cache*/ 힌트 자체가 RESULT_CACHE_MODE에 관계없이 캐시된 데이터를 찾아 보고 있으면 재 사용하고 없으면 등록하는 것이기에 result_cache_mode가 어떤 것으로 설정이 되어 있더라도 캐시된 데이터를 찾아 보고 없으면 등록을 하게 된다.

세션#2에서 AUTO 모드임에도 /*+ result_cache*/힌트를 사용함으로써 Result Cache영역에 대한 탐색이 들어감으로 세션#1에서 등록한 결과 값을 사용하게 되었다. 세션#2에서 힌트를 사용하지 않을 경우 기본적으로 AUTO 모드임으로 Result Cache에 대한 확인을 하여야 한다. 하지만 현재 Result Cache 영역에는 AUTO모드로 등록되어 있는 SQL문들이 존재 하지 않음으로 Result Cache영역에 대한 엑세스 자체가 없다. 이에 대한 것은 SQL문 실행 전/후의 Latch 사용횟수를 확인해 보면 알 수 있다.

 

- Session#2

 

12:53:48 HR 10094>SELECT NAME , GETS, MISSES FROM v$latch WHERE name like 'Result%' ;

 

NAME GETS MISSES

------------------------------ ---------- ----------

Result Cache: Latch 39 0

Result Cache: SO Latch 4 0

 

Elapsed: 00:00:00.01

12:53:51 HR 10094>SELECT department_id , MAX( salary ) FROM employees GROUP BY department_id ;

 

DEPARTMENT_ID MAX(SALARY)

------------- -----------

100 12000

30 11000

7000

90 24000

20 13000

70 10000

110 12000

50 8200

80 14000

40 6500

60 9000

10 4400

 

12 rows selected.

 

Elapsed: 00:00:00.00

12:54:23 HR 10094>SELECT NAME , GETS, MISSES FROM v$latch WHERE name like 'Result%' ;

 

NAME GETS MISSES

------------------------------ ---------- ----------

Result Cache: Latch 39 0

Result Cache: SO Latch 4 0

 

Elapsed: 00:00:00.00

 

 

AUTO로 등록된 것이 있을 경우

 

- Session#3

 

12:55:45 SCOTT 10846>set autotrace on exp stat

12:56:50 SCOTT 10846>SELECT count(*) FROM test_result_cache ;

 

COUNT(*)

----------

1000000

 

Elapsed: 00:00:00.01

 

Execution Plan

----------------------------------------------------------

Plan hash value: 972491529

 

------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 932 (2)| 00:00:12 |

| 1 | RESULT CACHE | byca6t4zkrfpt4nwab6p2965ys

| 2 | SORT AGGREGATE | | 1 | | |

| 3 | TABLE ACCESS FULL| TEST_RESULT_CACHE | 1000K| 932 (2)| 00:00:12 |

------------------------------------------------------------------------------------------

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

1 - column-count=1; type=AUTO; dependencies=(SCOTT.TEST_RESULT_CACHE); attributes=(single-row); name="SELECT count(*)

FROM test_result_cache "

 

 

 

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

418 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

 

 

 

- Sys Session

 

12:59:22 SYS 9919>SELECT cache_id , type , namespace, name , status , scan_count FROM v$result_cache_objects ;

 

CACHE_ID TYPE NAMESPACE NAME STATUS SCAN_COUNT

------------------------------ ---------- ---------- ------------------------------ ------------------ ----------

SCOTT.TEST_RESULT_CACHE Dependency SCOTT.TEST_RESULT_CACHE Published 0

HR.EMPLOYEES Dependency HR.EMPLOYEES Published 0

byca6t4zkrfpt4nwab6p2965ys Result SQL SELECT count(*) FROM test_resu Published 2

lt_cache

byca6t4zkrfpt4nwab6p2965ys Result AUTO Published 2

17kdyqzrjw8pgg5pnxz3y365s0 Result SQL SELECT /*+ result_cache */ dep Published 2

artment_id , MAX( salary ) FRO

M employees GROUP BY depart

ment_id

 

 

Elapsed: 00:00:00.00

 

 

- Session#4

 

12:48:54 SCOTT 10878>SELECT NAME , GETS, MISSES FROM v$latch WHERE name like 'Result%' ;

 

NAME GETS MISSES

------------------------------ ---------- ----------

Result Cache: Latch 46 0

Result Cache: SO Latch 4 0

 

Elapsed: 00:00:00.00

13:00:23 SCOTT 10878>SELECT count(*) FROM test_result_cache ;

 

COUNT(*)

----------

1000000

 

Elapsed: 00:00:00.00

 

Execution Plan

----------------------------------------------------------

Plan hash value: 972491529

 

------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 932 (2)| 00:00:12 |

| 1 | RESULT CACHE | byca6t4zkrfpt4nwab6p2965ys

| 2 | SORT AGGREGATE | | 1 | | |

| 3 | TABLE ACCESS FULL| TEST_RESULT_CACHE | 1000K| 932 (2)| 00:00:12 |

------------------------------------------------------------------------------------------

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

1 - column-count=1; type=AUTO; dependencies=(SCOTT.TEST_RESULT_CACHE); attributes=(single-row); name="SELECT count(*)

FROM test_result_cache "

 

 

 

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

418 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

 

13:00:37 SCOTT 10878>SELECT NAME , GETS, MISSES FROM v$latch WHERE name like 'Result%' ;

 

NAME GETS MISSES

------------------------------ ---------- ----------

Result Cache: Latch 48 0

Result Cache: SO Latch 4 0

 

Elapsed: 00:00:00.00

 

 

TestCase#1에서 하고자 했던 것은 Manual 특성상 힌트를 사용해야 하므로 Session Level에서 변경한 것의 특이성을 테스트 하기 힘듦.

 

- Test Case#2

기존에 Library cache에 저장되는 SQL문장일 경우 Text 자체가 조금(대소문자, 띄어쓰기) 이라도 틀리면 다른 SQL문장으로 인식되어서 하드 파싱이 일어 나게 된다. Result Cache에 저장되어 있는 SQL과 같지만 대소 문자나 띄어 쓰기가 틀리거나 할 경우 다른 SQL문으로 인정이 되는 지 아닌지에 대한 테스트를 하여 보도록 하자.

 

- Session#1

 

- Result Cache에 등록

 

13:09:48 HR 11104>set autotrace on exp stat

13:09:59 HR 11104>SELECT /*+ result_cache */ count(*) FROM jobs;

 

COUNT(*)

----------

19

 

Elapsed: 00:00:00.03

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3870222678

 

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

| 1 | RESULT CACHE | 81752sfkskaxxfsq9qtwmakwu0

| 2 | SORT AGGREGATE | | 1 | | |

| 3 | INDEX FULL SCAN| JOB_ID_PK | 19 | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

1 - column-count=1; dependencies=(HR.JOBS); attributes=(single-row); name="SELECT /*+ result_cache */ count(*) FROM j

obs"

 

 

 

Statistics

----------------------------------------------------------

300 recursive calls

0 db block gets

61 consistent gets

6 physical reads

0 redo size

418 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

1 rows processed

 

13:10:23 HR 11104>SELECT /*+ result_cache */ count(*) FROM jobs;

 

COUNT(*)

----------

19

 

Elapsed: 00:00:00.00

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3870222678

 

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

| 1 | RESULT CACHE | 81752sfkskaxxfsq9qtwmakwu0

| 2 | SORT AGGREGATE | | 1 | | |

| 3 | INDEX FULL SCAN| JOB_ID_PK | 19 | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

1 - column-count=1; dependencies=(HR.JOBS); attributes=(single-row); name="SELECT /*+ result_cache */ count(*) FROM j

obs"

 

 

 

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

418 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

 

 

 

- Session#2

 

 

-동일 SQL문 수행

 

13:12:59 HR 11160>SELECT /*+ result_cache */ count(*) FROM jobs;

 

중략..

 

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

| 1 | RESULT CACHE | 81752sfkskaxxfsq9qtwmakwu0

| 2 | SORT AGGREGATE | | 1 | | |

| 3 | INDEX FULL SCAN| JOB_ID_PK | 19 | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

 

- 쿼리를 모두 소문자로 수행 함

 

13:13:13 HR 11160>select /*+ result_cache */ count(*) from jobs ;

 

중략….

 

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

| 1 | RESULT CACHE | 81752sfkskaxxfsq9qtwmakwu0

| 2 | SORT AGGREGATE | | 1 | | |

| 3 | INDEX FULL SCAN| JOB_ID_PK | 19 | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

 

- 쿼리를 모두 대 문자로 수행 함

 

13:16:55 HR 11160>SELECT /*+ RESULT_CACHE */ count(*) FROM JOBS ;

 

중략..

 

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

| 1 | RESULT CACHE | 81752sfkskaxxfsq9qtwmakwu0

| 2 | SORT AGGREGATE | | 1 | | |

| 3 | INDEX FULL SCAN| JOB_ID_PK | 19 | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

 

- 띄어쓰기를 틀리게 해서 수행 함

 

13:20:43 HR 11160>select /*+ result_cache */ count(*) from jobs ;

 

중략..

 

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

| 1 | RESULT CACHE | 81752sfkskaxxfsq9qtwmakwu0

| 2 | SORT AGGREGATE | | 1 | | |

| 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

 

 

대/소 문자나 띄어쓰기가 틀리더라도 동일 SQL문으로 간주 하여서 등록되어 있는 Result Cache를 재 사용하고 있다.

 

- TestCase#3

Bind변수를 사용할 경우 Result Cache 사용

 

- Session#1

 

15:05:51 HR 11663>var deptno number ;

15:06:47 HR 11663>exec :deptno := 10 ;

15:06:56 HR 11663>SELECT /*+ result_cache */ max(salary) FROM employees WHERE department_id = :deptno ;

 

MAX(SALARY)

-----------

4400

 

Elapsed: 00:00:00.01

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3341463733

 

------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |

| 1 | RESULT CACHE | fxsnxk385n9srbfy26kgvmrk9x

| 2 | SORT AGGREGATE | | 1 | 7 | | |

| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 70 | 2 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

4 - access("DEPARTMENT_ID"=TO_NUMBER(:DEPTNO))

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

1 - column-count=1; dependencies=(HR.EMPLOYEES); attributes=(single-row, ordered); parameters=(:DEPTNO); name="SELECT

/*+ result_cache */ max(salary) FROM employees WHERE department_id = :deptno "

 

 

 

Statistics

----------------------------------------------------------

8 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

421 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

 

15:07:44 HR 11663>exec :deptno := 20 ;

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:00.01

15:09:45 HR 11663>SELECT /*+ result_cache */ max(salary) FROM employees WHERE department_id = :deptno ;

 

MAX(SALARY)

-----------

13000

 

Elapsed: 00:00:00.00

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3341463733

 

------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |

| 1 | RESULT CACHE | fxsnxk385n9srbfy26kgvmrk9x

| 2 | SORT AGGREGATE | | 1 | 7 | | |

| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 70 | 2 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

4 - access("DEPARTMENT_ID"=TO_NUMBER(:DEPTNO))

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

1 - column-count=1; dependencies=(HR.EMPLOYEES); attributes=(single-row, ordered); parameters=(:DEPTNO); name="SELECT

/*+ result_cache */ max(salary) FROM employees WHERE department_id = :deptno "

 

 

 

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

422 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

 

15:09:51 HR 11663>exec :deptno := 30 ;

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:00.00

15:10:36 HR 11663>SELECT /*+ result_cache */ max(salary) FROM employees WHERE department_id = :deptno ;

 

MAX(SALARY)

-----------

11000

 

Elapsed: 00:00:00.00

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3341463733

 

------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |

| 1 | RESULT CACHE | fxsnxk385n9srbfy26kgvmrk9x

| 2 | SORT AGGREGATE | | 1 | 7 | | |

| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 70 | 2 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

4 - access("DEPARTMENT_ID"=TO_NUMBER(:DEPTNO))

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

1 - column-count=1; dependencies=(HR.EMPLOYEES); attributes=(single-row, ordered); parameters=(:DEPTNO); name="SELECT

/*+ result_cache */ max(salary) FROM employees WHERE department_id = :deptno "

 

 

 

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

422 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

 

15:10:45 HR 11663>SELECT cache_id , type , namespace, name , status , scan_count FROM v$result_cache_objects ;

 

CACHE_ID TYPE NAMESPACE NAME STATUS SCAN_COUNT

------------------------------ ---------- ---------- ------------------------------ ------------------ ----------

HR.EMPLOYEES Dependency HR.EMPLOYEES Published 0

fxsnxk385n9srbfy26kgvmrk9x Result SQL SELECT /*+ result_cache */ max Published 0

(salary) FROM employees WHERE

department_id = :deptno

 

fxsnxk385n9srbfy26kgvmrk9x Result SQL SELECT /*+ result_cache */ max Published 0

(salary) FROM employees WHERE

department_id = :deptno

 

fxsnxk385n9srbfy26kgvmrk9x Result SQL SELECT /*+ result_cache */ max Published 0

(salary) FROM employees WHERE

department_id = :deptno

 

 

Elapsed: 00:00:00.00

 

Result Cache영역에는 바인드 변수와 바인드 값이 같이 저장이 된다. 그래서 세 번의 쿼리가 같은 바인드 변수를 사용하지만 실제 바인드 변수 값은 틀리므로 세 개의 데이터가 Result Cache 영역 안에 등록되었다. 하지만 동일 SQL문이므로 Cache_id값은 변하지 않았음을 확인 할 수 있다.

그럼 PL/SQL안에서 사용될 경우도 같은 결과를 나타내는지 확인해 보도록 하자

 

- Session#1

 

- PL/SQL 스크립트 (pl_sql.sql)

 

declare

v_cnt number ;

v_loop number ;

begin

for i in 1..5 loop

v_loop := i * 10 ;

SELECT /*+ result_cache */ count(*) into v_cnt

FROM employees

WHERE department_id = v_loop ;

end loop;

end;

/

 

15:34:52 HR 11663>@pl_sql.sql

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:00.00

15:34:00 HR 11663>SELECT cache_id , type , namespace, name , status , scan_count FROM v$result_cache_objects ;

 

CACHE_ID TYPE NAMESPACE NAME STATUS SCAN_COUNT

------------------------------ ---------- ---------- ------------------------------ ------------------ ----------

HR.EMPLOYEES Dependency HR.EMPLOYEES Published 0

arczhp1wc9rc73tfgss3xr1jxd Result SQL SELECT /*+ result_cache */ COU Published 0

NT(*) FROM EMPLOYEES WHERE DEP

ARTMENT_ID = :B1

 

arczhp1wc9rc73tfgss3xr1jxd Result SQL SELECT /*+ result_cache */ COU Published 0

NT(*) FROM EMPLOYEES WHERE DEP

ARTMENT_ID = :B1

 

arczhp1wc9rc73tfgss3xr1jxd Result SQL SELECT /*+ result_cache */ COU Published 0

NT(*) FROM EMPLOYEES WHERE DEP

ARTMENT_ID = :B1

 

arczhp1wc9rc73tfgss3xr1jxd Result SQL SELECT /*+ result_cache */ COU Published 0

NT(*) FROM EMPLOYEES WHERE DEP

ARTMENT_ID = :B1

 

arczhp1wc9rc73tfgss3xr1jxd Result SQL SELECT /*+ result_cache */ COU Published 0

NT(*) FROM EMPLOYEES WHERE DEP

ARTMENT_ID = :B1

 

fxsnxk385n9srbfy26kgvmrk9x Result SQL SELECT /*+ result_cache */ max Published 0

(salary) FROM employees WHERE

department_id = :deptno

 

fxsnxk385n9srbfy26kgvmrk9x Result SQL SELECT /*+ result_cache */ max Published 0

(salary) FROM employees WHERE

department_id = :deptno

 

fxsnxk385n9srbfy26kgvmrk9x Result SQL SELECT /*+ result_cache */ max Published 0

(salary) FROM employees WHERE

department_id = :deptno

 

 

9 rows selected.

 

 

PL/SQL안에서 Loop를 돌면서 5번 수행 하였더니 실제 Result Cache 영역에 5개의 Result값이 캐시 되었다. 물론 캐시 아이디는 동일하다.

두 개의 테스트를 통해서 알 수 있듯이 바인드 변수를 사용하는 경우 바인드 변수 값이 틀리면 새로운 Result Cache가 하나씩 추가 됨을 확인 할 수 있다. 물론 펑션을 사용할 경우도 펑션의 변수 값에 따라서 동일 펑션이지만 여러 개가 Result Cache에 등록 될 수 있다.

 

- Test Case#4

Inline view, View에서의 Result Cache 사용

 

- Session#1

 

15:55:16 HR 11901>SELECT /*+ result_cache */ department_id , department_name FROM departments ;

 

DEPARTMENT_ID DEPARTMENT_NAME

------------- ------------------------------------------------------------

10 Administration

20 Marketing

30 Purchasing

40 Human Resources

50 Shipping

60 IT

70 Public Relations

80 Sales

90 Executive

100 Finance

110 Accounting

120 Treasury

130 Corporate Tax

140 Control And Credit

150 Shareholder Services

160 Benefits

170 Manufacturing

180 Construction

190 Contracting

200 Operations

210 IT Support

220 NOC

230 IT Helpdesk

240 Government Sales

250 Retail Sales

260 Recruiting

270 Payroll

 

27 rows selected.

 

16:01:13 HR 11901>SELECT cache_id , type , namespace, name , status , scan_count FROM v$result_cache_objects

16:01:15 2 ;

 

CACHE_ID TYPE NAMESPACE NAME STATUS SCAN_COUNT

------------------------------ ---------- ---------- ------------------------------ ------------------ ----------

HR.DEPARTMENTS Dependency HR.DEPARTMENTS Published 0

5hg10c46n9ux9czz6z5wz4dc0y Result SQL SELECT /*+ result_cache */ dep Published 0

artment_id , department_name F

ROM departments

 

 

Elapsed: 00:00:00.01

 

15:59:48 HR 11901>set autotrace on exp

15:59:57 HR 11901>SELECT a.first_name , b.department_name , hire_date

FROM employees a ,

( SELECT /*+ result_cache */ department_id , department_name FROM departments ) b

WHERE a.department_id = b.department_id ;

 

결과 생략…

 

106 rows selected.

 

Elapsed: 00:00:00.01

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1817473301

 

---------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 106 | 5088 | 7 (15)| 00:00:01 |

|* 1 | HASH JOIN | | 106 | 5088 | 7 (15)| 00:00:01 |

| 2 | VIEW | | 27 | 810 | 3 (0)| 00:00:01 |

| 3 | RESULT CACHE |5hg10c46n9ux9czz6z5wz4dc0y

| 4 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

1 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

3 - column-count=2; dependencies=(HR.DEPARTMENTS); name="SELECT /*+ result_cache */ department_id , department_name F

ROM departments "

 

 

위와 같이 inline view에서는 의도했던 대로 Result cache를 사용하였다.

실제 view를 만든 후에 어떻게 사용이 되는지에 대해서도 테스트를 진행해 보자.

 

- Session#1

 

- view 생성(create_view.sql)

 

CREATE OR REPLACE VIEW v_emp

AS

SELECT /*+ result_cache */

a.first_name ,

b.department_name ,

hire_date

FROM employees a ,

(

SELECT department_id ,

department_name

FROM departments

) b

WHERE a.department_id = b.department_id ;

 

16:29:31 HR 12217>@create_view.sql

 

View created.

 

16:29:31 HR 12217>set autotrace traceonly exp

16:31:17 HR 12217>SELECT * FROM v_emp ;

Elapsed: 00:00:00.01

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3905418259

 

-------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 106 | 4028 | 6 (17)| 00:00:01 |

| 1 | VIEW | V_EMP | 106 | 4028 | 6 (17)| 00:00:01 |

| 2 | RESULT CACHE | gcrx04fsu1rtj5ya5gznu0fzsn

| 3 | MERGE JOIN | | 106 | 3604 | 6 (17)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |

| 5 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |

|* 6 | SORT JOIN | | 107 | 1926 | 4 (25)| 00:00:01 |

| 7 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

6 - access("A"."DEPARTMENT_ID"="DEPARTMENT_ID")

filter("A"."DEPARTMENT_ID"="DEPARTMENT_ID")

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

2 - column-count=3; dependencies=(HR.DEPARTMENTS, HR.EMPLOYEES); attributes=(ordered); name="HR.V_EMP"

 

 

16:32:18 HR 12217>SELECT cache_id , type , namespace, name , status , scan_count FROM v$result_cache_objects

16:32:22 2 ;

 

CACHE_ID TYPE NAMESPACE NAME STATUS SCAN_COUNT

------------------------------ ---------- ---------- ------------------------------ ------------------ ----------

HR.EMPLOYEES Dependency HR.EMPLOYEES Published 0

HR.DEPARTMENTS Dependency HR.DEPARTMENTS Published 0

gcrx04fsu1rtj5ya5gznu0fzsn Result SQL HR.V_EMP Published 0

 

16:35:49 HR 12217>set autotrace on exp stat

16:36:26 HR 12217> SELECT * FROM v_emp where first_name = 'Jennifer' ;

 

FIRST_NAME DEPARTMENT_NAME HIRE_DATE

---------------------------------------- ------------------------------------------------------------ ------------

Jennifer Administration 17-SEP-87

Jennifer Shipping 13-AUG-97

 

Elapsed: 00:00:00.01

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3905418259

 

-------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 106 | 4028 | 6 (17)| 00:00:01 |

|* 1 | VIEW | V_EMP | 106 | 4028 | 6 (17)| 00:00:01 |

| 2 | RESULT CACHE | gcrx04fsu1rtj5ya5gznu0fzsn

| 3 | MERGE JOIN | | 106 | 3604 | 6 (17)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |

| 5 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |

|* 6 | SORT JOIN | | 107 | 1926 | 4 (25)| 00:00:01 |

| 7 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

1 - filter("FIRST_NAME"='Jennifer')

6 - access("A"."DEPARTMENT_ID"="DEPARTMENT_ID")

filter("A"."DEPARTMENT_ID"="DEPARTMENT_ID")

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

2 - column-count=3; dependencies=(HR.DEPARTMENTS, HR.EMPLOYEES); attributes=(ordered); name="HR.V_EMP"

 

 

Statistics

----------------------------------------------------------

87 recursive calls

0 db block gets

43 consistent gets

0 physical reads

0 redo size

626 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

9 sorts (memory)

0 sorts (disk)

2 rows processed

 

16:45:06 HR 12217> SELECT cache_id , type , namespace, name , status , scan_count FROM v$result_cache_objects

 

CACHE_ID TYPE NAMESPACE NAME STATUS SCAN_COUNT

------------------------------ ---------- ---------- ------------------------------ ------------------ ----------

HR.EMPLOYEES Dependency HR.EMPLOYEES Published 0

HR.DEPARTMENTS Dependency HR.DEPARTMENTS Published 0

gcrx04fsu1rtj5ya5gznu0fzsn Result SQL HR.V_EMP Published 1

 

16:46:11 HR 12217> SELECT * FROM v_emp where first_name = 'Jennifer' ;

 

결과 생략 ..

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3905418259

 

-------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 106 | 4028 | 6 (17)| 00:00:01 |

|* 1 | VIEW | V_EMP | 106 | 4028 | 6 (17)| 00:00:01 |

| 2 | RESULT CACHE | gcrx04fsu1rtj5ya5gznu0fzsn

| 3 | MERGE JOIN | | 106 | 3604 | 6 (17)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |

| 5 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |

|* 6 | SORT JOIN | | 107 | 1926 | 4 (25)| 00:00:01 |

| 7 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

1 - filter("FIRST_NAME"='Jennifer')

6 - access("A"."DEPARTMENT_ID"="DEPARTMENT_ID")

filter("A"."DEPARTMENT_ID"="DEPARTMENT_ID")

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

2 - column-count=3; dependencies=(HR.DEPARTMENTS, HR.EMPLOYEES); attributes=(ordered); name="HR.V_EMP"

 

 

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

626 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2 rows processed

 

16:49:41 HR 12217>SELECT * FROM v_emp where first_name = 'Shipping' ;

 

생략…

 

Statistics

----------------------------------------------------------

87 recursive calls

4 db block gets

43 consistent gets

0 physical reads

0 redo size

410 bytes sent via SQL*Net to client

409 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

9 sorts (memory)

0 sorts (disk)

0 rows processed

 

16:50:31 HR 12217>/

 

생략…

 

 

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

410 bytes sent via SQL*Net to client

409 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

 

16:50:33 HR 12217>SELECT * FROM v_emp where first_name = 'Jennifer' ;

 

생략…

 

 

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

626 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2 rows processed

 

16:54:25 HR 12217> SELECT cache_id , type , namespace, name , status , scan_count FROM v$result_cache_objects

 

CACHE_ID TYPE NAMESPACE NAME STATUS SCAN_COUNT

------------------------------ ---------- ---------- ------------------------------ ------------------ ----------

HR.EMPLOYEES Dependency HR.EMPLOYEES Published 0

HR.DEPARTMENTS Dependency HR.DEPARTMENTS Published 0

gcrx04fsu1rtj5ya5gznu0fzsn Result SQL HR.V_EMP Published 5

 

16:56:12 HR 12217>SELECT distinct department_name FROM v_emp ;

 

결과 생략…

 

11 rows selected.

 

Elapsed: 00:00:00.01

 

Execution Plan

----------------------------------------------------------

Plan hash value: 788728450

 

----------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 27 | 324 | 4 (25)| 00:00:01 |

| 1 | HASH UNIQUE | | 27 | 324 | 4 (25)| 00:00:01 |

| 2 | VIEW | V_EMP | 106 | 1272 | 3 (0)| 00:00:01 |

| 3 | RESULT CACHE | a2gf4r5gm2gb02cnnffx7c2kru

| 4 | NESTED LOOPS | | 106 | 2014 | 3 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |

|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 4 | 12 | 0 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

6 - access("A"."DEPARTMENT_ID"="DEPARTMENT_ID")

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

3 - column-count=1; dependencies=(HR.DEPARTMENTS, HR.EMPLOYEES); name="HR.V_EMP"

 

 

Statistics

----------------------------------------------------------

87 recursive calls

4 db block gets

53 consistent gets

1 physical reads

0 redo size

619 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

9 sorts (memory)

0 sorts (disk)

11 rows processed

 

16:56:37 HR 12217> SELECT cache_id , type , namespace, name , status , scan_count FROM v$result_cache_objects

 

CACHE_ID TYPE NAMESPACE NAME STATUS SCAN_COUNT

------------------------------ ---------- ---------- ------------------------------ ------------------ ----------

HR.EMPLOYEES Dependency HR.EMPLOYEES Published 0

HR.DEPARTMENTS Dependency HR.DEPARTMENTS Published 0

a2gf4r5gm2gb02cnnffx7c2kru Result SQL HR.V_EMP Published 0

gcrx04fsu1rtj5ya5gznu0fzsn Result SQL HR.V_EMP Published 5

 

16:56:40 HR 12217> SELECT distinct department_name FROM v_emp ;

 

결과 생략…

 

Elapsed: 00:00:00.01

 

Execution Plan

----------------------------------------------------------

Plan hash value: 788728450

 

----------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 27 | 324 | 4 (25)| 00:00:01 |

| 1 | HASH UNIQUE | | 27 | 324 | 4 (25)| 00:00:01 |

| 2 | VIEW | V_EMP | 106 | 1272 | 3 (0)| 00:00:01 |

| 3 | RESULT CACHE | a2gf4r5gm2gb02cnnffx7c2kru | | | | |

| 4 | NESTED LOOPS | | 106 | 2014 | 3 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |

|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 4 | 12 | 0 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

6 - access("A"."DEPARTMENT_ID"="DEPARTMENT_ID")

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

3 - column-count=1; dependencies=(HR.DEPARTMENTS, HR.EMPLOYEES); name="HR.V_EMP"

 

 

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

619 bytes sent via SQL*Net to client

420 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

11 rows processed

 

 

16:56:51 HR 12217> SELECT cache_id , type , namespace, name , status , scan_count FROM v$result_cache_objects

 

CACHE_ID TYPE NAMESPACE NAME STATUS SCAN_COUNT

------------------------------ ---------- ---------- ------------------------------ ------------------ ----------

HR.EMPLOYEES Dependency HR.EMPLOYEES Published 0

HR.DEPARTMENTS Dependency HR.DEPARTMENTS Published 0

a2gf4r5gm2gb02cnnffx7c2kru Result SQL HR.V_EMP Published 1

gcrx04fsu1rtj5ya5gznu0fzsn Result SQL HR.V_EMP Published 5

 

 

 

재미 있는 결과가 나왔다.

뷰를 이용해서 사용할 경우 Where 절을 추가 하면 최초 수행 쿼리에 한하여 실제 데이터 엑세스가 발생을 하고 추후의 쿼리에 대해서는 result cache를 사용한다는 것을 확인 할 수 있다. 언뜻 보기에는 뷰를 사용하면 모든 것이 해결 될 듯 싶다. 실제 v$result_cache_objects를 보게되면 scan_count가 증가 한다.

그러나 Result Cache가 사용하는 메모리 영역을 확인해 보면 v$result_cache_objects에는 하나의 row만 존재 하나 이와 관련된 메모리 영역은 where 조건을 사용한 만큼 ( 조건이 없는경우 , Jennifer , Shipping ) 실제 메모리 영역을 사용을 하고 있다. 메모리 영역에 대한 사용현황을 확인하지 않고 단순히 v$result_cache_objects뷰만 확인 한다면 감쪽같이 속아 넘어갈 만한 부분이다.

실제 메모리 사용량을 확인해 보기 위해서는 v$result_cache_memory뷰나 dbms_result_cache.memory_report 프로시져를 확인하면 된다.

 

 

17:04:36 SYS 9919>exec dbms_result_cache.memory_report(true) ;

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 = 1536K bytes (1536 blocks)

Maximum Result Size = 76K bytes (76 blocks)

[Memory]

Total Memory = 103528 bytes [0.057% of the Shared Pool]

... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]

....... Cache Mgr = 108 bytes

....... Memory Mgr = 124 bytes

....... Bloom Fltr = 2K bytes

....... State Objs = 2852 bytes

... Dynamic Memory = 98396 bytes [0.055% of the Shared Pool]

....... Overhead = 65628 bytes

........... Hash Table = 32K bytes (4K buckets)

........... Chunk Ptrs = 12K bytes (3K slots)

........... Chunk Maps = 12K bytes

........... Miscellaneous = 8284 bytes

....... Cache Memory = 32K bytes (32 blocks)

........... Unused Memory = 26 blocks

........... Used Memory = 6 blocks

............... Dependencies = 2 blocks (2 count)

............... Results = 4 blocks

................... SQL = 4 blocks (2 count)

 

PL/SQL procedure successfully completed.

 

 

위의 결과를 해석해 보면 SQL = 4 blocks ( 2count) 라는 뜻은 Result Cache에 등록된 SQL은 두 개 이지만 이와 관련되어 사용된 블록은 4 블록이라는 뜻이 된다. 즉 where조건을 사용한 2경우와 사용하지 않은 경우 , SELECT절에 distinct를 주어 컬럼에 대한 변형을 가한 경우 이렇게 4가지 경우를 뜻하게 된다.

 

 

또, 뷰에 where조건을 추가할 때와는 다르게 SELECT 절에 대한 변경이 있게 되면 새로운 Result Cache가 등록이 되고 사용이 된다.

 

- 요약

 

위의 테스트를 통하여 알 수 있는 것은 RESULT_CACHE_MODE에 관계없이 /*+ result_cache*/힌트가 우선 적용된다는 점과 Result Cache에 저장되는 값은 SQL문 , SQL문에서 사용한 컬럼 Count , Bind변수 값 등등이 같이 저장됨을 확인 할 수 있다.

그래서 컬럼의 개수를 변경하는 작업이 있거나 바인드 변수가 틀릴 경우 새로운 Result값이 등록이 되고 사용됨을 확인 할 수 있다.

컬럼의 개수나 바인드 변수의 변경이 없이 단순히 대/소문자 , 띄어쓰기만 틀릴 경우는 아무런 제약이 없이 Result Cache영역의 데이터가 사용되고 있다.

반응형

+ Recent posts