Skip to main content

Enable Database Result Cache

Prerequisites

  1. Check the Installation checklist. 
  2. Launch the installation
  3. Steps for application tiers with multiple file systems (conditional)
  4. Perform post-requisites
  5. 12.2+ specific instructions (R12.2+ users only)
  6. Enable network connectivity to Applaud Cloud

Enable Database Result Cache

The app uses a database feature called Result Cache to optimize performance. Result Cache:

  • Is new in Oracle 11g and improved in 11gR2
  • Works on SQL query results (local and distributed)
  • Works on PL/SQL function results
  • Stores cached data that is shared across executions
  • Is automatically marked stale if the underlying data is changed
  • Can dramatically increase performance
  • Stores the results of queries and function calls
  • Uses a slice of the Shared Pool
  • Is not affected by flushing the Shared Pool

result_cache_sp.png

If Result Cache is not enabled on your database, the installation process would have presented a warning that it needs to be enabled. You should also review the Result Cache recommendations.

Checking the status of Result Cache

To see if Result Cache is enabled or not, you can also run this SQL statement:

SELECT dbms_result_cache.status
FROM   dual;

Even if result cache is enabled, please ensure you follow the Result Cache recommendations.

You can also retrieve a list of the current result cache parameters:

SQL> show parameter result_cache

NAME                                 TYPE       VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag             big integer 3000
client_result_cache_size             big integer 0
result_cache_max_result             integer     10
result_cache_max_size              big integer 100M
result_cache_mode                   string     MANUAL
result_cache_remote_expiration       integer     0

If you wish to obtain a memory report, please run:

EXECUTE dbms_result_cache.memory_report;

Here is a sample report:

result_cache_memory_stats.png

Please ensure you follow the Result Cache recommendations; running the above utilities does not ensure the recommended parameters are set correctly.

Result Cache recommendations

To enable result cache, you need to set memory_target and shared_pool_size parameters:

Parameter Default Recommended Value
RESULT_CACHE_MODE MANUAL MANUAL
RESULT_CACHE_MAX_SIZE Values of SHARED_POOL_SIZE, SGA_TARGET, and MEMORY_TARGET 100M or more (depends on the SGA/Memory Target values)
RESULT_CACHE_MAX_RESULT 5% 5%

If you are using sga_target instead of memory_target for managing memory, then check the shared_pool_size and set it to its minimum value to enable result cache.

Follow-up tasks

  1. Configure Single sign-on (Optional)
  2. Set the XXAS: Google Maps API Key profile option
  3. Schedule License Management concurrent program
  4. Run Gather Schema Statistics concurrent program
  5. Activate after purchase

Sections in this article