Vinny Jiménez

Posted on Feb 24 • Originally published at insightsapex.vinnyum.tech

Caching Strategies: APEX & Database Cache

#orclapex #performance #caching #plsql

From Page Cache to Server Result Cache: A Comprehensive Guide for High-Performance Apps

The Fastest Query is the One You Never Run

🇪🇸 Leer en Español

There is a common misconception in database development: "If it's slow, just add an index."

While indexing is critical, it solves a storage retrieval problem, not a architectural one. In high-concurrency APEX applications, the bottleneck is often not the disk I/O, but the CPU cycles required to compute the same result over and over again for thousands of users.

If 500 users request the exact same "Weekly Sales Report" in a minute, why are we executing the aggregation query 500 times?

In this APEX Insights entry, we explore the Caching Layers available in the Oracle ecosystem—from the browser down to the PL/SQL function—and how to use them to build applications that scale effortlessly.

The Architectural Challenge

Scaling an APEX application is rarely about adding more hardware; it's about reducing waste. Every time your application executes logic that yields the same result as a previous execution, you are wasting resources (CPU, Latch contention, DB Time).

However, caching introduces the most notorious problem in computer science: Cache Invalidation.

The challenge isn't just "storing the result"; it's knowing when to trust it and when to discard it. A dashboard showing yesterday's stock prices is useless (or dangerous). A dashboard showing yesterday's "Total Sales" might be acceptable.

As architects, we must define the Freshness Tolerance for every component.

Mental Models: The 3 Layers of Caching

When a user views an APEX page, data flows through multiple layers. We can inject caching at distinct points:

APEX Page/Region Cache: The rendered HTML is stored. The database query is not executed, and the page rendering engine skips work. (Fastest for the user, least flexible).
Server Result Cache (PL/SQL): The logic executes, but the function result is stored in the Shared Pool. The query might run once, but subsequent calls skip the computation.
Database Cache (Buffer Cache): The standard Oracle mechanism. Blocks are in memory, but the query and logic still execute. (We assume this is always active).

We will focus on the first two, as they are under your direct control.

Caching Flow Diagram

Note on Mermaid: If the diagram below does not render on DEV.to, consider using a static image or verifying if your editor supports the {% mermaid %} liquid tag.

Strategic Patterns

1. APEX Region Caching (The "Low Hanging Fruit")

For static content or heavy reports that don't change per second, Region Caching is powerful.

Mechanism: APEX stores the rendered HTML in a table (WWV_FLOW_PAGE_CACHE).
Best Use Case: "Top Sellers of the Month" reports, navigation lists, or heavy charts that update nightly.
Configuration:
- Cache: Cached by User or Cached for All Users.
- Cache Timeout: For example, 3600 (1 hour).

Consultant Tip: Be extremely careful with "Cached for All Users" if your query includes :APP_USER or VPD policies. You might accidentally show User A's private data to User B.

2. PL/SQL Server Result Cache (The "Scalability Engine")

This is the most underused feature in high-performance APEX apps. It allows a PL/SQL function to store its return value in the database Shared Pool. It is cross-session.

Mechanism: If function(A) returns B, Oracle remembers "A -> B." Next time any session calls function(A), it returns B instantly without executing the body.
Dependency Tracking: If the function queries TABLE_X, and TABLE_X is updated, the cache invalidates automatically.

3. Scalar Subquery Caching

If you call a function inside a SQL query, context switching (SQL <-> PL/SQL) kills performance.

The Naive Approach (Slow):

SELECT e.ename,
       get_dept_name(e.deptno) -- Context switch per row!
  FROM emp e;

The Optimized Approach: Oracle automatically caches scalar subquery results in memory for the duration of the query execution. If get_dept_name(10) is called 100 times, it executes once. This is automatic, but knowing it exists helps you design better SQL.

Technical Implementation

implementing RESULT_CACHE

Here is how to properly implement a result-cached function for a configuration lookup.

CREATE OR REPLACE FUNCTION get_system_param (
    p_param_name IN VARCHAR2
) RETURN VARCHAR2
RESULT_CACHE
RELIES_ON (system_parameters) -- Table dependency
IS
    l_value VARCHAR2(255);
BEGIN
    -- This body only executes if the result is NOT in the cache
    SELECT param_value
      INTO l_value
      FROM system_parameters
     WHERE param_name = p_param_name;

    RETURN l_value;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

Key Explanation:

RESULT_CACHE: Instructs Oracle to cache the result.
RELIES_ON: Explicitly tells Oracle which table's changes should flush this cache.

The "Session Specific" Trap

One common pitfall is caching data that relies on session state (for example, V('APP_USER')) inside a RESULT_CACHE function without passing it as a parameter.

BAD Code (Security Risk):

CREATE OR REPLACE FUNCTION get_user_role RETURN VARCHAR2
RESULT_CACHE -- ❌ DANGEROUS: No dependency on user!
IS
BEGIN
    -- Function has no parameters.
    -- If User A calls it, result is "ADMIN".
    -- If User B calls it, they get "ADMIN" from cache!
    RETURN lookup_role(V('APP_USER'));
END;

GOOD Code:

CREATE OR REPLACE FUNCTION get_user_role(p_username IN VARCHAR2) RETURN VARCHAR2
RESULT_CACHE
IS
BEGIN
    RETURN lookup_role(p_username);
END;

Now the cache key includes the username.

Common Pitfalls

VPD & Row Level Security: As demonstrated above, the Result Cache bypasses standard SQL execution. If your query uses SYS_CONTEXT filtering hidden in a view, the Result Cache might bypass it. Always pass context as parameters.
High Volatility Tables: If system_parameters changes every second, your RESULT_CACHE function will spend more time invalidating and managing overhead than executing. Only cache data that is read frequently but written rarely (Read-Mostly).
Latch Contention: In extremely high-concurrency (thousands of executions/sec), the latch on the Result Cache can becomes a bottleneck.

Consultant's Checklist

Use this Caching Decision Matrix to audit your code before production deployment.

Scenario / Data Type	Scope	Recommended Strategy
Global Configuration	Cross-Session	PL/SQL Result Cache
Heavy Dashboard	Per User	APEX Region Cache (`Cached by User`)
Public Reports	All Users	APEX Region Cache (`Cached for All Users`)
Transactional Data	Per User	NO CACHE (Direct Query)

Download the full "APEX Caching Decision Matrix" (PDF) for your team channel or code reviews.

📥 Download PDF Checklist

Conclusion

Caching is not a "performance pixie dust" you sprinkle on slow code. It is an architectural decision to trade freshness for throughput.

In Oracle APEX, start by optimizing your SQL (tuning). Then, look at Region Caching for heavy, read-only dashboards. Finally, use PL/SQL Result Cache for configuration lookups and reference data.

Done right, you can serve thousands of concurrent users with the hardware footprint of a Raspberry Pi.

Question for the community: Have you ever had a "stale data" incident because of aggressive caching? How do you handle cache invalidation in your apps? Let's discuss in the comments!

🚀 Take the Next Step

Review your app: Identify any read-heavy dashboard and consider applying Region Caching.
Experiment: Create a RESULT_CACHE function for your configuration table lookup.
Subscribe to APEX Insights: Get advanced architectural tips delivered straight to your inbox.
Share the Knowledge: Connect with me on LinkedIn to discuss your caching challenges!

☕ Schedule a Call
💼 Connect on LinkedIn
🐦 Follow on X

References

💖 Support My Work

If you found this APEX Insights helpful, consider supporting the open-source efforts of the APEX community!

GitHub Sponsors
Buy Me a Coffee

DEV Community