Summary
Oracle 11g has many new features one of particular interest in the idea of a results cache. Results cache allows a query’s result to be cached allowing for large performance improvements for queries that are executed multiple times. The results cache works best for tables with mostly static data and has particular queries that return the same results over and over.
One particular point of interest for the result cache is that fact that the result cache does not work when logged in as sysdba.
We can see in the explain plan of a query when the RESULT CACHE is being used as shown below:
----------------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
----------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 85120 1745K 660 (1) 00:00:08
1 RESULT CACHE 5g4sqrbmnk7tw4ct7s8ak9vdgd
2 SORT ORDER BY 85120 1745K 6696K 660 (1) 00:00:08
3 PARTITION REFERENCE ALL 85120 1745K 107 (1) 00:00:02
4 TABLE ACCESS FULL ORDER_ITEMS 85120 1745K 107 (1) 00:00:02
----------------------------------------------------------------------------------------------------------------
Test Results
SQL> set linesize 150
SQL> set timing on
SQL> set echo on
SQL>
SQL> drop table results_cache_test ;
Table dropped.
Elapsed: 00:00:00.10
SQL>
SQL> -- run create a table for our testing
SQL> -- so not to skew results
SQL> create table results_cache_test
2 as
3 select *
4 from dba_tables ;
Table created.
Elapsed: 00:00:00.56
SQL>
SQL> create unique index results_cache_test_pk
2 on results_cache_test (owner, table_name) ;
Index created.
Elapsed: 00:00:00.04
SQL>
SQL> execute dbms_stats.gather_table_stats ('MRMESSIN', 'RESULTS_CACHE_TEST') ;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.95
SQL>
SQL> -- set autotrace on for first round with index
SQL> set autotrace on ;
SQL>
SQL> -- run to see the results
SQL> select table_name, cache, read_only
2 from results_cache_test
3 where owner = 'MRMESSIN' ;
TABLE_NAME CACHE REA
------------------------------ ----- ---
DEPT N NO
EMP N NO
DEMO_USERS N NO
DEMO_CUSTOMERS N NO
DEMO_ORDERS N NO
DEMO_ORDER_ITEMS N NO
DEMO_PRODUCT_INFO N NO
DEMO_STATES N NO
DEMO_PAGE_HIERARCHY N NO
EAGLE N NO
HELP_TABLE N NO
TABLE_NAME CACHE REA
------------------------------ ----- ---
BIG_EMP N NO
DEMO_IMAGES N NO
BIG_EMP2 N YES
14 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2738702378
----------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------------
0 SELECT STATEMENT 95 3515 25 (0) 00:00:01
* 1 TABLE ACCESS FULL RESULTS_CACHE_TEST 95 3515 25 (0) 00:00:01
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='MRMESSIN')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
85 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
SQL> -- run a second time to cache the results
SQL> select /*+ result_cache */ table_name, cache, read_only
2 from results_cache_test
3 where owner = 'MRMESSIN' ;
TABLE_NAME CACHE REA
------------------------------ ----- ---
DEPT N NO
EMP N NO
DEMO_USERS N NO
DEMO_CUSTOMERS N NO
DEMO_ORDERS N NO
DEMO_ORDER_ITEMS N NO
DEMO_PRODUCT_INFO N NO
DEMO_STATES N NO
DEMO_PAGE_HIERARCHY N NO
EAGLE N NO
HELP_TABLE N NO
TABLE_NAME CACHE REA
------------------------------ ----- ---
BIG_EMP N NO
DEMO_IMAGES N NO
BIG_EMP2 N YES
14 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2738702378
-------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 95 3515 25 (0) 00:00:01
1 RESULT CACHE 6qrad2hzvx6xm6rwrx23ncuwwg
* 2 TABLE ACCESS FULL RESULTS_CACHE_TEST 95 3515 25 (0) 00:00:01
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='MRMESSIN')
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(MRMESSIN.RESULTS_CACHE_TEST); parameters=(nls); name="select /*+ result_cache */ table_name, cache, read_only
from results_cache_test
where owner = 'MRMESSIN' "
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
85 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
SQL> -- run the third time to see query with results cache in place
SQL> select /*+ result_cache */ table_name, cache, read_only
2 from results_cache_test
3 where owner = 'MRMESSIN' ;
TABLE_NAME CACHE REA
------------------------------ ----- ---
DEPT N NO
EMP N NO
DEMO_USERS N NO
DEMO_CUSTOMERS N NO
DEMO_ORDERS N NO
DEMO_ORDER_ITEMS N NO
DEMO_PRODUCT_INFO N NO
DEMO_STATES N NO
DEMO_PAGE_HIERARCHY N NO
EAGLE N NO
HELP_TABLE N NO
TABLE_NAME CACHE REA
------------------------------ ----- ---
BIG_EMP N NO
DEMO_IMAGES N NO
BIG_EMP2 N YES
14 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2738702378
-------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 95 3515 25 (0) 00:00:01
1 RESULT CACHE 6qrad2hzvx6xm6rwrx23ncuwwg
* 2 TABLE ACCESS FULL RESULTS_CACHE_TEST 95 3515 25 (0) 00:00:01
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='MRMESSIN')
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(MRMESSIN.RESULTS_CACHE_TEST); parameters=(nls); name="select /*+ result_cache */ table_name, cache, read_only
from results_cache_test
where owner = 'MRMESSIN' "
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> -- drop the index to start second round without index
SQL> drop index results_cache_test_pk ;
Index dropped.
Elapsed: 00:00:00.01
SQL>
SQL> set autotrace on ;
SQL>
SQL> -- run to see the results
SQL> select table_name, cache, read_only
2 from results_cache_test
3 where owner = 'MRMESSIN' ;
TABLE_NAME CACHE REA
------------------------------ ----- ---
DEPT N NO
EMP N NO
DEMO_USERS N NO
DEMO_CUSTOMERS N NO
DEMO_ORDERS N NO
DEMO_ORDER_ITEMS N NO
DEMO_PRODUCT_INFO N NO
DEMO_STATES N NO
DEMO_PAGE_HIERARCHY N NO
EAGLE N NO
HELP_TABLE N NO
TABLE_NAME CACHE REA
------------------------------ ----- ---
BIG_EMP N NO
DEMO_IMAGES N NO
BIG_EMP2 N YES
14 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2738702378
----------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------------
0 SELECT STATEMENT 95 3515 25 (0) 00:00:01
* 1 TABLE ACCESS FULL RESULTS_CACHE_TEST 95 3515 25 (0) 00:00:01
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='MRMESSIN')
Statistics
----------------------------------------------------------
234 recursive calls
0 db block gets
116 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
SQL> -- run a second time to cache the results
SQL> select /*+ result_cache */ table_name, cache, read_only
2 from results_cache_test
3 where owner = 'MRMESSIN' ;
TABLE_NAME CACHE REA
------------------------------ ----- ---
DEPT N NO
EMP N NO
DEMO_USERS N NO
DEMO_CUSTOMERS N NO
DEMO_ORDERS N NO
DEMO_ORDER_ITEMS N NO
DEMO_PRODUCT_INFO N NO
DEMO_STATES N NO
DEMO_PAGE_HIERARCHY N NO
EAGLE N NO
HELP_TABLE N NO
TABLE_NAME CACHE REA
------------------------------ ----- ---
BIG_EMP N NO
DEMO_IMAGES N NO
BIG_EMP2 N YES
14 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2738702378
-------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 95 3515 25 (0) 00:00:01
1 RESULT CACHE 6qrad2hzvx6xm6rwrx23ncuwwg
* 2 TABLE ACCESS FULL RESULTS_CACHE_TEST 95 3515 25 (0) 00:00:01
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='MRMESSIN')
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(MRMESSIN.RESULTS_CACHE_TEST); parameters=(nls); name="select /*+ result_cache */ table_name, cache, read_only
from results_cache_test
where owner = 'MRMESSIN' "
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
85 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
SQL> -- run the third time to see query with results cache in place
SQL> select /*+ result_cache */ table_name, cache, read_only
2 from results_cache_test
3 where owner = 'MRMESSIN' ;
TABLE_NAME CACHE REA
------------------------------ ----- ---
DEPT N NO
EMP N NO
DEMO_USERS N NO
DEMO_CUSTOMERS N NO
DEMO_ORDERS N NO
DEMO_ORDER_ITEMS N NO
DEMO_PRODUCT_INFO N NO
DEMO_STATES N NO
DEMO_PAGE_HIERARCHY N NO
EAGLE N NO
HELP_TABLE N NO
BIG_EMP N NO
DEMO_IMAGES N NO
BIG_EMP2 N YES
14 rows selected.
Elapsed: 00:00:00.04
Execution Plan
--------------------------------------------------------
Plan hash value: 2738702378
-------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 95 3515 25 (0) 00:00:01
1 RESULT CACHE 6qrad2hzvx6xm6rwrx23ncuwwg
* 2 TABLE ACCESS FULL RESULTS_CACHE_TEST 95 3515 25 (0) 00:00:01
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------
2 - filter("OWNER"='MRMESSIN')
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(MRMESSIN.RESULTS_CACHE_TEST); parameters=(nls); name="select /*+ result_cache */ table_name, cache, read_only
from results_cache_test
where owner = 'MRMESSIN'"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
SQL> spool off
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment