andersswanson.dev

Recently, I’ve been exploring different caching database caching strategies with Oracle AI Database and came to the following conclusion: Choosing the right mix of caching and read-scaling options doesn’t have to be a one-feature or one-service decision.

Teams have a rich toolbox of caching and scaling options available, but also real trade-offs. This article provides an overview of database caching options, including related options that aren’t caches (Data Guard).

Oracle True Cache

• Offload read traffic to a near–real-time, read-only cache replica(s) synchronized from a primary database(s).
• Runs as Separate node(s) from the primary; designed to serve data from memory. Memory-first with a minimal disk footprint (not a full on-disk copy).
• High consistency by applying redo log files from primary database(s).
• Best for Scaling read-heavy workloads, geo/edge read fan-out, isolating analytics/reads from OLTP on the primary.

Read more on True Cache: https://www.oracle.com/database/truecache/

Try out True Cache with containers: https://docs.oracle.com/en/database/oracle/oracle-database/26/odbtc/deploying-true-cache-containers.html

Oracle Active Data Guard (read-only standby)

• Physical standby database(s) for HA/DR that can serve read-only queries with real-time replication.
• Runs as separate database host(s).
• Best for HA/DR plus read offload, large-scale reporting off primary.
• Full database offload, protection modes, redo log apply.
• I/O and storage footprint are like a full DB. Consider True Cache if memory-first serving is the goal without the storage or Data Guard requirements.

Read more on Data Guard: https://www.oracle.com/database/data-guard/

Database In-Memory (In-Memory Column Store)

• Columnar, in-memory copy of data for ultra-fast scans and aggregations.
• Lives inside the primary DB and/or distributed across RAC nodes.
• Transactionally consistent with the row store: dual-format (row + column) in the same database, columnar copies are refreshed automatically.
• Best for Analytics/OLAP.
• No app changes needed. The dual-format (row+column) keeps OLTP fast while accelerating analytics.
• Scales out with Database In-Memory options.

Read more about the In-Memory Column Store: https://docs.oracle.com/en/database/oracle/oracle-database/26/inmem/intro-to-in-memory-column-store.html

Buffer Cache (SGA buffer cache, keep/recycle pools)

• Purpose: Core block cache for allreads/writes.
• Exists per instance, clustered databases (RAC) use Cache Fusion across nodes.
• Fully consistent, cornerstone of OLTP.
• Always on, tuneable (size, keep/recycle pools).
• It’s not a strategy by itself for analytics at scale; use IM column store for scan-heavy workloads.

Explore Buffer Cache architecture: https://docs.oracle.com/en/database/oracle/oracle-database/26/dbiad/db_dbbuffercache.html

Database Smart Flash Cache (OS-level extension to buffer cache)

• Extend buffer cache to SSD to reduce disk I/O on Linux.
• Local flash device(s) on DB server.
• Transparent extension of buffer cache.
• Best for large working sets that don’t fit in RAM.
• Low-touch; good cost/perf when RAM is tight.
• Not a substitute for DRAM, has specific platform and configuration requirements.

Read more about Database Smart Flash Cache: https://docs.oracle.com/en/database/oracle/oracle-database/26/odbtc/database-smart-flash-cache.html

Exadata Smart Scan (storage-tier)

• Cache and accelerate I/O at the storage server layer and push down scans.
• Runs on Exadata storage servers.
• Consistent and transparent to the DB.
• Big scan and I/O offload gains without app changes.
• Best for mixed workloads at scale, scan-heavy queries, database offload.
• Note that this feature is Exadata-specific.

Read more about Exadata Smart Scan: https://www.oracle.com/database/technologies/exadata/software/smartscan/

Server-Side Result Cache

• Purpose: Cache query results or function return values.
• Server SGA (server cache), client driver memory (client cache), or PL/SQL layer.
• Best for repeated, deterministic queries, lookups, and service-layer memoization.

Read more about Server-Side Result Cache: https://docs.oracle.com/en/database/oracle/oracle-database/26/odbtc/server-side-result-set-cache.html

Materialized Views (MVs) with Query Rewrite

• Precompute and store results (aggregations/joins) and let the optimizer transparently rewrite queries to use those stored results.
• Runs inside and is stored by the database.
• Consistent on refresh (on commit or scheduled fast/complete refresh).
• Best for heavy aggregations, complex joins, and predictable analytic patterns.
• Transparent to apps with query rewrite.
• Storage and refresh requirements, design for refresh windows and DML patterns.

Read more about Query Rewrite for Materialized Views: https://docs.oracle.com/en/database/oracle/oracle-database/26/dwhsg/basic-query-rewrite-materialized-views.html

External Caches (Oracle Coherence, Redis, Memcached, etc.)

• App-tier caching to offloaded from the database.
• Runs outside the DB; network hop from app and db.
• Application-managed consistency, from TTL to versioning and invalidation).
• Consider Coherence for Oracle-specific environments.

Cache Connect with Oracle Times Ten In-Memory Database

• Low-latency in-memory database that can cache subsets of Oracle AI Database tables with bi-directional sync options.
• Runs as a separate in-memory DB process/node(s) with tight Oracle integration.
• Consistent and configurable with read-only caches or write-through/write-back.
• Read more: https://www.oracle.com/technetwork/products/timesten/ds-timesten-cache-128569.pdf

Can you combine caching strategies?

Yes. Common patterns:

• OLTP + analytics: Row store for OLTP + IM column store for scans + selective materialized views for complex aggregates.
• Read scaling: Primary for writes + True Cache/ADG for reads + Server-Side Result Cache for repetitive queries.
• Platform: Exadata Smart Scan + IM Column Store for maximum analytic acceleration.

Operational Notes

• Some features require specific licenses or platforms (e.g., Database In-Memory, TimesTen, Exadata). Make sure you validate the best fit and costs for your use case.
• If you use an external caches, it may hold sensitive data. Ensure encryption, access controls, data retention, and monitoring meet organizational security and compliance guidelines.
• Test your caching strategy! Observe DML-driven invalidation and refresh windows for result caches and materialized views.
• Size memory carefully and monitor hit ratios, IM column store usage, and cache eviction patterns. Use OSS monitoring tools like the Oracle Database Metrics Exporter: https://github.com/oracle/oracle-db-appdev-monitoring