Wednesday, March 30, 2011

Result Cache Can Not Be Enabled, Really?

I reviewed about Result Cache on 11g and found my DBMS_RESULT_CACHE.STATUS=BYPASS. However, I found Result Cache Can Not Be Enabled [ID 563828.1] : that write about 11.1 but my database 11.2. then test test test:
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> show parameter memory_max_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 1000M

SQL> show parameter memory_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 1000M

SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 0

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
DISABLED

SQL> show parameter result_cache_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 0

SQL> alter system set result_cache_max_size=5120000;

System altered.

SQL> show parameter result_cache_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 5000K

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
DISABLED

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 698892288 bytes
Fixed Size 1346412 bytes
Variable Size 356517012 bytes
Database Buffers 335544320 bytes
Redo Buffers 5484544 bytes
Database mounted.
Database opened.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
BYPASS

SQL> show parameter result_cache_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 0
I enabled result cache by changing result_cache_max_size nonzero. then tested to restart instance. Result Cache status was BYPASS and result_cache_max_size = 0 :(
However, tested Result Cache and it's ok.
SQL> SELECT /*+ RESULT_CACHE */ department_id, AVG(salary) FROM hr.employees GROUP BY department_id;

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 | ccphn1vt8g8dk2wadcy16drzn3 | | | | |
| 2 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Just curious about "result_cache_max_size" value and dbms_result_cache.status() and then tested to change " shared_pool_size" initialization parameter.
***The Result Cache memory area is located in the Shared Pool so, the value of result_cache_max_size is consumed from the Shared Pool size.***
SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0

SQL> alter system set shared_pool_size=200M;

System altered.

SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 200M

SQL> show parameter result_cache_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 0

SQL> alter system set result_cache_max_size=5120000;

System altered.

SQL> show parameter result_cache_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 5000K
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 698892288 bytes
Fixed Size 1346412 bytes
Variable Size 356517012 bytes
Database Buffers 335544320 bytes
Redo Buffers 5484544 bytes
Database mounted.
Database opened.

SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 200M

SQL> show parameter result_cache_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size big integer 5024K

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL> SELECT /*+ RESULT_CACHE */ department_id, AVG(salary) FROM hr.employees GROUP BY department_id;
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 | ccphn1vt8g8dk2wadcy16drzn3 | | | | |
| 2 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
After changed " shared_pool_size" initialization parameter. Result Cache status is ENABLED and result_cache_max_size nonzero. So In BYPASS status, we can use Result Cache. Really?

Link Related:
http://surachartopun.com/2010/06/result-cache-resultcachemaxsize.html
ลิงก์

No comments: