Query result caching for fast database applications – Christopher Jones

Oracle Database’s built-in “Client Result Cache” is an efficient, integrated, managed cache that can dramatically improve query performance and significantly reduce database load when repeatedly querying mostly-static tables, such as postal codes or part numbers. No application changes are needed. No separate mid-tier cache needs to be installed. CRC is available to any “Thick” client that uses Oracle Client libraries, such as drivers for Python, Node.js, Go, PHP, Rust, Ruby, and Oracle’s C API. It is also available in JDBC. This blog post demos an example in Python.

CRC unlocks performance (Photo by Maria Ziegler on Unsplash)

Benefits of Client Result Caching

Can be used without needing to modify application codeImproved query response timeStatements are not sent to the database to be executedBetter performance by eliminating server round-tripsImproved database server scalability by saving server resourcesAutomatically managed cache invalidation, keeping the cache consistent with database changesNo mid-tier cache server requiredDevelopers don’t need to build, or use, a custom cache

What is Client Result Caching?

The Client Result Cache (CRC) is a memory area inside a client process (i.e. the application process) that caches SELECT query results. Its presence is invisible to application code, which just executes queries and gets results as normal. The cache is internal to the Oracle client libraries used by the application. The client libraries know whether a query’s result set is already cached. If so, the SELECT statement gets the results from the cache immediately without involving the database. If the results aren’t in the cache, then the SELECT is sent to the database for execution — which has obvious network and database costs, and contributes to overall system load. Magically the client libraries know whether the cache is outdated and needs to be refreshed. This makes CRC very attractive because application logic doesn’t need to be changed to give performance benefits.

Connections in the process share the Client Result Cache

Oracle Database has supported CRC for many versions. When we first released it back in the Oracle Database 11g timeframe, we ran the ‘Niles benchmark’ and saw these material improvements:

Benefits of Oracle Database Client Result Caching in the Niles benchmark

How does Client Result Caching work?

Oracle Client libraries manage a result cache for each client process. It is shared by all sessions (i.e. connections) inside that process. The cache can be enabled, and its size specified, with a database initialization parameter.

Client result caching stores the results of the outermost query, which are the columns defined by application.

Oracle Database transparently keeps the client result cache consistent with session state or database changes that affect it. When any database transaction changes the data or metadata of database objects used to build the cached result, the database sends an invalidation flag to the client as part of its response to the application’s next round-trip. (A round-trip is defined as the travel of a message from the application to the database and back. Calling each driver function, or accessing a driver attribute, will require zero or more round-trips.)

If the application is idle and hasn’t initiated a round-trip to the database in a certain amount of time, then cached values are assumed to be invalid. This invalidation time is configurable.

Oracle recommends using CRC for queries from small, read-only or read-mostly tables, however some customers have used it for relatively large tables.

Enabling Client Result Caching in Oracle Database

Caching is disabled by default. It can be enabled by setting the database parameter CLIENT_RESULT_CACHE_SIZE to 32K or greater, and optionally tuning the cache entry invalidation time parameter

Truncated by Planet PHP, read more at the original (another 11640 bytes)