Implementing the result cache in SQL

As we learned earlier, the database must be configured to enable server-side result caching. Let us now go through illustrations of the result cache in SQL.

Manual result cache

If the result cache operation mode is set as MANUAL, the caching feature is known as manual result cache. Here, the user has to explicitly specify the RESULT_CACHE hint in order to cache the query result. The Oracle server would not automatically cache any result set.

The RESULT_CACHE_MODE parameter can be set by the DBA to enable manual result caching:

/*Connect as SYSDBA*/
Conn sys/system as sysdba
Connected.

/*Set the parameter as Manual*/
ALTER SYSTEM SET RESULT_CACHE_MODE=MANUAL
/

System altered.

We will flush the cache memory and shared pool to clear all the earlier cached results:

/*Flush all the earlier cached results*/
SQL> EXEC DBMS_RESULT_CACHE.FLUSH
/

PL/SQL procedure successfully completed.

/*Flush the shared pool*/
SQL> alter system flush shared_pool
/

System...