RESULT CACHE HINT IS NOT WORKING FOR A QUERY TO CACHE
When using the following database objects or functions in your SQL query, you cannot cache the results.
- Temporary tables and dictionaries
- CURRVAL and NEXTVAL pseudo columns for sequences
- SQL functions sys_guid, sys_date, sys_timestamp, userenv/sys_context (with non-constant variables), and local_timestamp
- Unpredictable PL/SQL functions
Check if result cache is enabled for the database
SQL> SELECT dbms_result_cache.status() FROM dual;
DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED
Test if the result cache is used for a query or not with result cache hints. For this query, a fixed baseline plan is used and so even though we specify the hint, the result set is not cached in result cache.
Also make sure that you notice this important “Note” section during troubleshooting of why result cache is not used for the query.
SQL> SELECT /*+ result_cache */ * from dspm;
291053 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3837617297
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 219K| 452M| 1570 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DSPM | 219K| 452M| 1570 (1)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline "SQL_PLAN_41x3ck184jygqb495c64e" used for this statement
SQL> SELECT * from table(dbms_xplan.display_cursor(null,null,FORMAT => 'ALLSTATS ADVANCED LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 32cu3gtu98t6c, child number 1
-------------------------------------
SELECT /*+result_cache*/ * from dspm where type='PROCEDURE'
Plan hash value: 2382681726
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CP
U)| E-Time |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | | | 405 (10
0)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DSPM | 15536 | 1729K| 405 (
0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DTYPE | 15536 | | 44 (
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DSPM@SEL$1
2 - SEL$1 / DSPM@SEL$1
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
INDEX_RS_ASC(@"SEL$1" "DSPM"@"SEL$1" ("DSPM"."TYPE"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "DSPM"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TYPE"='PROCEDURE')
Column Projection Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------
1 - "DSPM"."OWNER"[VARCHAR2,128], "DSPM"."NAME"[VARCHAR2,128],
"TYPE"[VARCHAR2,12], "DSPM"."LINE"[NUMBER,22], "DSPM"."TEXT"[VARCHAR2,400
0],
"DSPM"."ORIGIN_CON_ID"[NUMBER,22]
2 - "DSPM".ROWID[ROWID,10], "TYPE"[VARCHAR2,12]
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- SQL plan baseline SQL_PLAN_f91shd5a09z5087226bf8 used for this statement
- Warning: basic plan statistics not available. These are only collected when
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
SQL> show parameter baseline
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> ALTER system set optimizer_capture_sql_plan_baselines=FALSE;
System altered.
SQL> show parameter result
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
multishard_query_partial_results string not allowed
result_cache_max_result integer 5
result_cache_max_size big integer 400M
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
SQL> ALTER system set result_cache_max_size=10;
System altered.
SQL> set autot traceonly
SQL> SELECT /*+ result_cache */ * from dspm;
291053 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3837617297
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 219K| 452M| 1570 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DSPM | 219K| 452M| 1570 (1)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline "SQL_PLAN_41x3ck184jygqb495c64e" used for this statement
SQL> set autot traceonly
SQL> SELECT /*+ result_cache */ * from dspm;
291053 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3837617297
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 219K| 452M| 1570 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DSPM | 219K| 452M| 1570 (1)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL> ALTER session set tracefile_identifier='RC';
Session altered.
SQL> ALTER SESSION SET EVENTS '43905 trace name context forever, level 12';
Session altered.
SQL> SELECT /*+result_cache */ count(*) from dspm;
COUNT(*)
----------
291053
SQL> ALTER SESSION SET EVENTS '43905 trace name context off';
Session altered.