Question: Will query result cache work on Encrypted tablespaces.
I asked this question to Tom Kyte after his All About Encryption presentation at UKOUG 2008. His answer was ” I think it will work but you need to try”
After 4 months, here is the answer to my question;
SQL> ---Encrypted tablespace creation
SQL> CREATE TABLESPACE encryptedtst
2 DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\ORACOS\encryptedtst_01.dbf' SIZE 128K
3 AUTOEXTEND ON NEXT 64K
4 ENCRYPTION USING 'AES256'
5 DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> connect hr/hr;
Connected.
SQL> alter table employees enable row movement;
Table altered.
SQL> alter table employees
2 move tablespace encryptedtst;
Table altered.
SQL> select manager_id,sum(salary) from employees group by manager_id;
MANAGER_ID SUM(SALARY)
---------- -----------
100 155400
27000
123 25900
120 22100
.. ...
19 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 144 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 18 | 144 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 856 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
SQL> select /*+ result_cache*/manager_id,sum(salary) from employees group by manager_id;
MANAGER_ID SUM(SALARY)
---------- -----------
100 155400
27000
123 25900
120 22100
.. ...
19 rows selected.
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 144 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | 1u45jnmw74kd60brbww9gr7ss8 | | | | |
| 2 | HASH GROUP BY | | 18 | 144 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 856 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id"
SQL> select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id;
MANAGER_ID SUM(SALARY)
---------- -----------
100 155400
27000
123 25900
120 22100
.. ...
19 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 144 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | 1u45jnmw74kd60brbww9gr7ss8 | | | | |
| 2 | HASH GROUP BY | | 18 | 144 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 856 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id"
SQL>
SQL>--Check if it is really cached
SQL> select name from v$result_cache_objects;
NAME
-----------------------------------------------------------------------------------------------
HR.EMPLOYEES
select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id
As you see on the explain plan and v$result_cache_table, the result is cached
Conclusion is; Oracle did not forget to combine two new features together by the help of keeping objects of encrypted tablespaces, unencrypted in SGA for better performance
