Oracle 21c — Using Result Cache in Standby

Another new feature introduced in Oracle Database 21c is the ability to use the RESULT_CACHE hint in a Physical Standby environment. The following section explains how to use this feature.

The execution time of the following query in the primary database is about one minute:

–Primary:

SQL> select count(*) from usef.tbl1;

  COUNT(*)

———-

  62775296

Elapsed: 00:01:06.45

SQL> /

  COUNT(*)

———-

  62775296

Elapsed: 00:01:03.61

By using the RESULT_CACHE hint, this time is reduced to less than one second!

–Primary:

SQL> select /*+ result_cache */  count(*) from usef.tbl1;

  COUNT(*)

———-

  62775296

Elapsed: 00:01:20.74

SQL> select /*+ result_cache */  count(*) from usef.tbl1;

  COUNT(*)

———-

  62775296

Elapsed: 00:00:00.00

Executing this query in the standby environment takes about 30 seconds (our standby database has better resources than the primary one!):

–Physical Standby:

SQL> select count(*) from usef.tbl1;

  COUNT(*)

———-

  62775296

Elapsed: 00:00:35.75

SQL> /

  COUNT(*)

———-

  62775296

Elapsed: 00:00:34.53

Using the RESULT_CACHE hint in the standby environment initially has no effect on query execution time:

–Physical Standby:

SQL> select /*+ result_cache */  count(*) from usef.tbl1;

  COUNT(*)

———-

  62775296

Elapsed: 00:00:26.42

SQL> select /*+ result_cache */  count(*) from usef.tbl1;

  COUNT(*)

———-

  62775296

Elapsed: 00:00:28.15

To enable result cache in a Data Guard environment, the RESULT_CACHE attribute must first be activated for the table:

–Primary:

SQL> alter table usef.tbl1 RESULT_CACHE (STANDBY ENABLE);

Table altered.

After this change, the query runs significantly faster in the Data Guard environment:

–Physical Standby:

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ——————–

PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> select /*+ result_cache */  count(*) from usef.tbl1;

  COUNT(*)

———-

  62775296

Elapsed: 00:00:18.02

SQL> select /*+ result_cache */  count(*) from usef.tbl1;

  COUNT(*)

———-

  62775296

Elapsed: 00:00:00.00