Notes on Eddie Awad‘s webinar, “See How Easily You Can Improve Performance by Using These Five Data Caching Techniques”.
- SQL Query Server Result Cache
- PL/SQL Function Result Cache
- Determininistic Function Cache
- Scalar Subquery
Server Result Cache
11G EE only – shared pool
SHOW PARAMETERS result_cache
OCM 11G Upgrade topic – see Use Result Cache.
SQL Query Result Cache
Enables explicit caching of a query result set in the server result cache. Results are shared across sessions and automatically when data in dependent database objects is changed.
11.2+ RESULT_CACHE table annotation.
ALTER TABLE t RESULT_CACHE (MODE DEFAULT); ALTER SESSION SET result_cache_mode = force;
Cached results parameterised by bind variables.
Easiest to use
/*+ RESULT_CACHE */ hint.
Can cache in-line views as well.
PL/SQL Function Result Cache
Depends on Server Result Cache
In 11.2, results cached across RAC for PL/SQL functions.
In Oracle 11.2, RELIES_ON is deprecated.
Deterministic Function Cache
Function declared with DETERMINISTIC keyword.
Function is availabled to be cached within a SQL query, not in PL/SQL block.
10G and later.
Package data is cached per session. Dependencies has to be managed manually.
Scalar Subquery Cache
Available in Oracle 10G and later. Results from scalar subquery are stored in a hash table. Done automatically by CBO.
Hash table is limited to 255 entries.
- Be aware of context switching between SQL and PL/SQL – adds CPU overhead
- Wrap function calls in
(SELECT fn(x) FROM DUAL).
Oracle Support documents: