Comparison of Caching in all Top RDBMS:
In relational database management systems (RDBMS),
caching mechanisms are strategically implemented to eliminate redundant
computational overhead and disk I/O.
Because parsing, normalizing, generating multiple
Query plans, optimizing, and executing SQLs are highly resource-intensive,
RDBMS split caching into two distinct phases:
1) Query Plan Caching (the compilation layer) and
2) Query Result Caching (the execution layer).
Here is a breakdown of the exclusive advantages of each Caching.
1. Advantages of Query Plan Caching:
Query plan caching (such as the Plan Cache in MS
SQL Server and Sybase ASE, the Library Cache in Oracle, or Prepared Statements
in PostgreSQL) stores the compiled execution plan of a query. When an identical
query is issued, the engine skips the parsing, binding, and optimization
phases. All Stored Procedures Query plans are reused unless recompile option is
explicitly specified.
- Significant Reduction in CPU
Overhead: The
query optimizer is heavily CPU-bound. It must evaluate statistics,
calculate cardinality, assess join orders, and cost multiple access paths.
Caching the plan bypasses this entire exercise, freeing up CPU cycles for
actual Query processing.
- Lower Latency for OLTP
Workloads: In
high-throughput transactional systems where the same query (e.g.,
regular/routine DMLs or a primary key SELECT) is executed
thousands of times per second, skipping the compilation phase reduces
response times from milliseconds to microseconds.
- Higher System Throughput: By reducing the time each query spends
in the compilation phase, the database engine can handle a significantly
higher number of Queries Per Second (QPS) before reaching CPU saturation.
- Reduced Memory Allocation
Churn: Parsing and optimizing
require allocating and deallocating memory structures (parse trees,
execution trees). Reusing a plan minimizes memory fragmentation and the
overhead of constant allocation.
2. Advantages of Query Result Caching
(Only Oracle supports the Results caching):
Query result caching stores the final, materialized
output of a query in memory (e.g., Oracle's Result Cache). If the same query is
executed and the underlying data has not changed, the database returns the
cached payload without ever touching the storage engine.
- Complete Elimination of
Logical and Physical I/O: Retrieving
a result directly from a memory cache means the database does not need to
scan the Buffer Pool/Shared Buffers, nor does it need to read from the
physical disk.
- Zero Execution CPU Cost: The database completely bypasses the
execution engine. It does not have to perform aggregations, sorts, hash
joins, or data filtering. This is exceptionally beneficial for heavy
analytical queries (OLAP) or complex views.
- Protection Against
"Thundering Herds": In
scenarios where an application experiences a massive spike in traffic
requesting the exact same dataset (e.g., the Stock price of a new
surprising entity), the result cache absorbs the load instantly,
preventing the underlying storage and CPU from being overwhelmed by
identical, concurrent read requests.
- Resource Liberation for
Writers: By offloading heavy,
repetitive read operations to the result cache, the physical disk
subsystem and execution engine are freed up to process all other workloads
like DDLs, DMLs (INSERT, UPDATE, and DELETE) and MAINTAINANCE operations
much faster.
Oracle is the leader and maintains a distinct architectural advantage in memory
management by natively supporting both execution plan caching and deterministic
query result caching.
In contrast, competing major RDBMS platforms primarily
depend on plan reuse mechanisms coupled with standard logical I/O reduction via
data caching only.
