Oracle Database 21c introduces a powerful enhancement for Data Guard environments: the ability to utilize the RESULT_CACHE hint in physical standby databases. This feature allows for significant acceleration of frequently executed queries on standby instances, which are often used for reporting and read-intensive operations.

Understanding RESULT_CACHE on the Primary Database

The RESULT_CACHE hint is renowned for its ability to dramatically cut down query execution times by storing the results of a query in a cache. For instance, a complex query that might take over a minute to complete on a primary database can be reduced to mere milliseconds on subsequent executions once its results are cached.

Consider a scenario where a COUNT(*) query on a large table takes approximately 60 seconds.

SQL> select count(*) from usef.tbl1;
-- Elapsed: 00:01:06.45

By simply adding the /*+ result_cache */ hint, the initial execution might still take time, but subsequent runs, benefiting from the cache, become almost instantaneous:

SQL> select /*+ result_cache */  count(*) from usef.tbl1;
-- Elapsed: 00:01:20.74 (initial run)
SQL> select /*+ result_cache */  count(*) from usef.tbl1;
-- Elapsed: 00:00:00.00 (subsequent run from cache)

Initial Behavior on Physical Standby

While primary databases readily benefit, the RESULT_CACHE hint does not inherently provide this performance boost on a physical standby database. Even if your standby environment has superior resources and a query runs faster (e.g., 30 seconds compared to 60 seconds on primary), applying RESULT_CACHE initially shows no improvement in subsequent executions.

SQL> select count(*) from usef.tbl1;
-- Elapsed: 00:00:35.75 (on standby)
SQL> select /*+ result_cache */  count(*) from usef.tbl1;
-- Elapsed: 00:00:26.42 (no caching effect on subsequent runs initially)

Enabling RESULT_CACHE for Standby Environments

To unlock the full potential of RESULT_CACHE in a Data Guard setup, a simple but crucial step is required on the primary database. You must explicitly enable the RESULT_CACHE attribute for the relevant table specifically for standby environments.

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

This command signals to the database that result caching for usef.tbl1 should also be active and managed when the data is replicated to standby instances.

Realizing Performance Gains on Physical Standby

Once the STANDBY ENABLE attribute is set, the RESULT_CACHE hint becomes effective on the physical standby database. After the first execution of a cached query, subsequent identical queries will retrieve results from the cache almost instantly, mirroring the performance observed on the primary.

SQL> select database_role,open_mode from v$database;
-- PHYSICAL STANDBY READ ONLY WITH APPLY
SQL> select /*+ result_cache */  count(*) from usef.tbl1;
-- Elapsed: 00:00:18.02 (initial run after enabling)
SQL> select /*+ result_cache */  count(*) from usef.tbl1;
-- Elapsed: 00:00:00.00 (subsequent run from cache)

Conclusion

The introduction of RESULT_CACHE (STANDBY ENABLE) in Oracle Database 21c is a valuable feature for organizations leveraging Data Guard for read-heavy workloads or reporting. By enabling this attribute, administrators can significantly enhance query performance on their physical standby databases, providing faster access to data and a more responsive environment for users. This simple configuration change can lead to substantial improvements in application performance and user experience within a Data Guard ecosystem.