Notes on “See How Easily You Can Improve Performance by Using These Five Data Caching Techniques” #oracledatabase


Notes on Eddie Awad‘s webinar, “See How Easily You Can Improve Performance by Using These Five Data Caching Techniques”.

Topics:

  • SQL Query Server Result Cache
  • PL/SQL Function Result Cache
  • Determininistic Function Cache
  • Package-based
  • Scalar Subquery

Server Result Cache

11G EE only – shared pool

DBMS_RESULT_CACHE package

  • memory_report
  • flush
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.

RESULT_CACHE clause

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-Based Cache

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.

Demo showed:

  • 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:

  • 1108133.1
  • 1142314.1
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s