Skip to content
hgryoo · Docs

CUBRID XASL Cache — Plan Cache Keyed by SHA-1 of SQL Hash Text with RT Recompile and Per-Class Invalidation

Contents:

Theoretical Background

Section titled “Theoretical Background”

A plan cache exists because compiling a SQL statement is expensive and re-executing it is cheap. The compile pipeline (lex, parse, resolve, type-check, rewrite, cost-based optimise, generate, serialise) has a fixed latency on the order of one to several hundred milliseconds, independent of the data. Database Internals (Petrov, Ch. 12) and Hellerstein, Stonebraker & Hamilton (Architecture of a Database System, 2007) name the cache explicitly: parse-and-plan once, execute many.

Every plan cache answers four questions:

  1. Cache key. Memoise compile : SQL_text → plan. Raw text is correct but brittle. Normalised text (Oracle’s cursor_sharing=force, SQL Server’s forced parameterisation) collapses equivalent statements but creates parameter sniffing. AST hash is equivalent to normalised text but expensive.
  2. Staleness. DDL on a referenced object hard-invalidates; statistics drift soft-invalidates (the plan still runs, just sub-optimally — schedule a recompile); bind-parameter shape change hard-invalidates.
  3. Concurrency. Touched on every execution; a naive lock bottlenecks. Standard fix: latch-free or sharded-locked hashmap with refcounted entries and deferred reclamation — the epoch pattern from Fraser (Practical Lock-Freedom, 2004) and the buffer-pool reader-writer protocol from Mohan et al. (ARIES, TODS 1992).
  4. Eviction. LRU, ARC, time- or size-based. Too aggressive → constant compile cost; too lenient → cache eats the buffer pool.

Comparative anchors: PostgreSQL’s per-backend prepared-statement cache (private, name-keyed); Oracle library cache (server-wide, hash-keyed, parent/child cursors for shape variants); SQL Server plan cache (server-wide, hash-keyed, parameter sniffing). CUBRID sits closest to Oracle: server-wide, shared, hashed on rewritten SQL.

Common DBMS Design

Section titled “Common DBMS Design”

Four moving parts every production plan cache has, even if wire-up varies. CUBRID’s choices in ## CUBRID's Approach are one set of dials in this space, not inventions.

Cache key — hash of normalised SQL text

Section titled “Cache key — hash of normalised SQL text”

A plan-cache key must be cheap to compute and stable across trivial syntactic variation. The dominant pattern: take the post-rewrite SQL text and hash it; keep the original text as a diagnostic secondary field. PostgreSQL skips the hash (its cache is per-backend, keyed by user-supplied name). Oracle uses MD5 plus full text for collision tie-break. SQL Server uses a 64-bit hash. CUBRID uses SHA-1 of the rewritten SQL (“hash text”).

Cache entry — plan plus dependency metadata

Section titled “Cache entry — plan plus dependency metadata”

A plan-cache entry carries the serialised plan, a dependency list of catalog objects the plan touches, the lock requirements for execute, a statistics snapshot at compile time (for drift detection), and refcount + eviction metadata. CUBRID’s XASL_CACHE_ENTRY carries all five.

Concurrency — refcounted entries, latch-free lookup

Section titled “Concurrency — refcounted entries, latch-free lookup”

Modern plan caches use a latch-free or sharded-locked hashmap for lookup. Entries are refcounted: readers increment a fix count, DDL sets a “marked-deleted” bit but cannot reclaim until fix count = 0 — the epoch-based reclamation pattern (Fraser 2004). CUBRID packs fix count and state flags into a single 32-bit cache_flag so the entire atomic CAS fits in one word.

Invalidation — DDL hook + recompile threshold

Section titled “Invalidation — DDL hook + recompile threshold”

Two triggers. DDL on a class: the locator calls into the plan cache and drops every entry whose dependency list mentions the modified OID. PostgreSQL does this via RelationCacheInvalidateEntry; Oracle invalidates every cursor holding the modified object. CUBRID’s hook is xcache_remove_by_oid. Statistics drift: periodic walk of entries comparing stored cardinality to current cardinality; on threshold cross, soft-invalidate and request recompile. Oracle’s adaptive cursor sharing is the analogue. CUBRID calls this the recompile threshold (RT) check in xcache_check_recompilation_threshold (10× factor, 6-minute per-entry cooldown).

Eviction — LRU bounded by entry count and memory

Section titled “Eviction — LRU bounded by entry count and memory”

Two budgets: max entries (soft cap, cleanup trigger) and max memory (hard cap, blocks inserts). Eviction picks the LRU candidates. CUBRID’s xcache_cleanup uses a binary heap on time_last_used, then deletes through the same refcount-aware path DDL invalidation uses.

Theory ↔ CUBRID mapping

Section titled “Theory ↔ CUBRID mapping”
Theoretical conceptCUBRID name
Cache key (hash of normalised SQL)SHA1Hash sha1 in XASL_ID, via SHA1Compute on compile_context::sql_hash_text
Cache entryXASL_CACHE_ENTRY (xasl_cache.h)
Serialised planXASL_STREAM stream inside the entry
Dependency listXCACHE_RELATED_OBJECT *related_objects (oid, lock mode, cardinality)
Statistics snapshottcard field on each related object (heap pages at compile time)
Refcount + state machineINT32 cache_flag on XASL_ID (low 24b = fix count, high 8b = flags)
Latch-free lookupcubthread::lockfree_hashmap<xasl_id, xasl_cache_ent> (xcache_Hashmap)
Mark-for-delete + deferred reclamationXCACHE_ENTRY_MARK_DELETED / _DELETED_BY_ME + xcache_unfix
DDL invalidation hookxcache_remove_by_oid (from locator_sr.c, serial.c)
Statistics-drift recompilexcache_check_recompilation_threshold + XCACHE_RT_FACTOR
Eviction by LRUxcache_cleanup + binary heap on time_last_used
Per-session prepared-statement registryPREPARED_STATEMENT list on SESSION_STATE (session.c)
Plan-stream → in-memory treestx_map_stream_to_xasl → fills XASL_CLONE
Pre-deserialised plan tree poolXASL_CLONE array on entry (cap: xasl_cache_max_clones)

CUBRID’s Approach

Section titled “CUBRID’s Approach”

The XASL cache is a server-side, server-wide hashmap of serialised plans, keyed by SHA-1 of the rewritten SQL. Six moving parts: the key (XASL_ID: SHA-1 + cache_flag + time_stored); the entry (XASL_CACHE_ENTRY with plan stream and dependency list); the hashmap (cubthread::lockfree_hashmap); the fix/unfix state machine packed into the 32-bit cache_flag; the recompile triggers (DDL OID hook plus RT threshold); the eviction path (LRU via binary heap). Walked in that order, then prepare/execute wire flow.

Overall structure

Section titled “Overall structure”
flowchart LR
  subgraph CLIENT["Client (CAS / driver process)"]
    USR["User SQL text"]
    PARSE["parser_main.c<br/>parse + name-resolve<br/>· semantic-check<br/>· rewrite"]
    SHA["SHA1Compute over<br/>sql_hash_text<br/>(execute_statement.c)"]
    PREP["network_interface_cl.c<br/>net_prepare_query"]
  end
  subgraph SERVER["Server (cub_server)"]
    SQMGR["sqmgr_prepare_query<br/>(network_interface_sr.cpp)"]
    XQMGR["xqmgr_prepare_query<br/>(query_manager.c)"]
    FIND["xcache_find_sha1<br/>(xasl_cache.c)"]
    INSERT["xcache_insert<br/>(xasl_cache.c)"]
    HM[("xcache_Hashmap<br/>(latch-free)")]
    EXEC["xqmgr_execute_query<br/>· xcache_find_xasl_id_for_execute<br/>· stx_map_stream_to_xasl"]
    RUNNER["query_executor.c<br/>qexec_execute_mainblock"]
    DDL["DDL / locator_sr.c<br/>serial.c"]
    INVAL["xcache_remove_by_oid"]
  end
  USR --> PARSE
  PARSE --> SHA
  SHA --> PREP
  PREP --> SQMGR
  SQMGR --> XQMGR
  XQMGR --> FIND
  FIND -->|hit| HM
  FIND -->|miss| INSERT
  INSERT --> HM
  XQMGR -->|return XASL_ID| PREP
  PREP -->|EXECUTE: send XASL_ID| EXEC
  EXEC --> HM
  EXEC --> RUNNER
  DDL --> INVAL
  INVAL --> HM

Three boundaries. (parser ↔ cache) parser/optimizer/XASL generator run client-side (#if !defined(SERVER_MODE)); the cache lives on the server. SHA-1 is computed client-side from the rewritten sql_hash_text and shipped in the prepare request. (prepare ↔ execute) prepare returns or installs an XASL_ID; execute refinds via that XASL_ID, failing with ER_QPROC_INVALID_XASLNODE if the entry vanished — the client re-prepares. (executor ↔ DDL) DDL fires xcache_remove_by_oid, which marks dependent entries deleted; the refcount path completes deletion when readers retire.

Cache key — XASL_ID

Section titled “Cache key — XASL_ID”
// XASL_ID — src/storage/storage_common.h
struct xasl_id
{
  SHA1Hash sha1;              /* SHA-1 of rewritten SQL text */
  INT32 cache_flag;           /* refcount + state flags */
  CACHE_TIME time_stored;     /* when this plan was stored */
};

Bucket selection uses only sha1.h[0] (xcache_hash_key); xcache_compare_key re-checks all 160 bits plus state flags. time_stored is not part of the prepare key but is part of execute lookup: between prepare and execute, the entry may have been invalidated and replaced by a fresh recompile. The client’s cached (sha1, time_stored) then mismatches the current entry’s time_stored; xcache_find_xasl_id_for_execute detects the mismatch and forces re-prepare. cache_flag is the concurrency-state word (next subsection).

SHA-1 computation — client-side, after rewrite

Section titled “SHA-1 computation — client-side, after rewrite”

The client computes sha1 after parser rewrites, before sending the prepare request. Five call sites in execute_statement.c (one per cached statement kind: SELECT, INSERT, UPDATE, DELETE, MERGE) follow the same pattern:

// do_execute_update — src/query/execute_statement.c (condensed)
PT_NODE_PRINT_TO_ALIAS (parser, statement,
                        CUSTOM_PRINT_4_SHA_COMPUTE | PT_PRINT_LOWER);
contextp->sql_hash_text = (char *) statement->alias_print;
err = SHA1Compute ((unsigned char *) contextp->sql_hash_text,
                   (unsigned) strlen (contextp->sql_hash_text),
                   &contextp->sha1);

CUSTOM_PRINT_4_SHA_COMPUTE strips bind values and comment noise; PT_PRINT_LOWER lower-cases identifiers. The hash thus covers the rewritten, normalised, lower-cased SQL — two clients whose rewrites agree share an entry. The hash plus the SQL text ship in the prepare request via or_pack_sha1 in network_interface_cl.c::net_prepare_query; the server unpacks into COMPILE_CONTEXT::sha1 in sqmgr_prepare_query.

Cache entry — XASL_CACHE_ENTRY

Section titled “Cache entry — XASL_CACHE_ENTRY”
// XASL_CACHE_ENTRY (condensed) — src/query/xasl_cache.h
struct xasl_cache_ent
{
  XASL_ID xasl_id;                        /* embedded key */
  XASL_STREAM stream;                     /* serialised plan bytes */
  XASL_CACHE_ENTRY *stack, *next;         /* lockfree freelist + bucket chain */
  UINT64 del_id;                          /* deferred-delete txn id */
  EXECUTION_INFO sql_info;                /* hash/user/plan text (diagnostics) */
  int xasl_header_flag;
  XCACHE_RELATED_OBJECT *related_objects; /* dependency list */
  int n_related_objects;
  struct timeval time_last_used;          /* LRU timestamp */
  INT64 ref_count, clr_count;             /* stats / qfile clears */
  int list_ht_no;                         /* result-cache hash slot */
  bool free_data_on_uninit;
  XASL_CLONE *cache_clones, one_clone;    /* deserialised plan pool */
  int n_cache_clones, cache_clones_capacity;
  pthread_mutex_t cache_clones_mutex;
  INT64 time_last_rt_check;               /* RT cooldown */
  bool initialized;
};

Five field groups deserve unpacking.

The plan itself. stream carries the byte-packed XASL the generator produced (see cubrid-xasl-generator.md). On execute it is fed to stx_map_stream_to_xasl to rebuild the runnable node tree — a non-trivial cost amortised by plan clones (below).

The dependency list. related_objects is an array of XCACHE_RELATED_OBJECT { OID oid, LOCK lock, int tcard } — class or serial OID the plan touches, lock the executor must take to run, and table cardinality (heap pages) at compile time. It has three consumers:

  1. Lock acquisition at execute. xcache_find_xasl_id_for_execute walks the list and calls lock_object on each OID. Without the lock, schema-modify holders (SCH_M_LOCK) could be invalidating the entry concurrently; after the lock is held, the XCACHE_ENTRY_MARK_DELETED flag is re-checked.
  2. DDL invalidation. xcache_remove_by_oid matches the OID against this list via xcache_entry_is_related_to_oid.
  3. RT recompile. xcache_check_recompilation_threshold walks the list, fetches current cardinality from catalog_get_class_info, and compares against tcard.

The hashmap plumbing. stack, next, del_id are owned by the lockfree hashmap — freelist link, bucket chain link, and deferred-delete transaction id. The LF_ENTRY_DESCRIPTOR at the top of xasl_cache.c (xcache_Entry_descriptor) gives the hashmap their offsets plus the alloc/free/init/uninit/compare/ hash callbacks.

Sql info for diagnostics. The three strings in sql_info (sql_hash_text, sql_user_text, sql_plan_text) are not used for lookup — only the SHA-1 is. They drive xcache_dump, SHOW PLAN_CACHE, and trace logs.

Plan clones. cache_clones is an array of pre-deserialised plan trees, XASL_CLONE { xasl_buf, xasl }. The first execute deserialises; on retire (xcache_retire_clone), the clone is parked back into the array. Subsequent executes pop a ready clone instead of walking the byte stream. Gated by xasl_cache_max_clones; zero disables.

The hashmap — latch-free, server-wide

Section titled “The hashmap — latch-free, server-wide”
// using declaration — src/query/xasl_cache.c
using xcache_hashmap_type = cubthread::lockfree_hashmap<xasl_id, xasl_cache_ent>;

The XCACHE global (xcache_Global) bundles the hashmap with sizing parameters, the cleanup binary heap, the cleanup-array backing store for the timeout path, the cleanup_flag CAS gate (one cleaner at a time), the memory accounting counters (memory_usage_cache, memory_usage_clone), and stats. Initial size is PRM_ID_XASL_CACHE_MAX_ENTRIES; the hashmap auto-resizes via the freelist. The freelist is split into two blocks of size max(1, soft_capacity / 2) to reduce CAS contention on freelist claim:

// xcache_initialize — src/query/xasl_cache.c
const int freelist_block_count = 2;
const int freelist_block_size = std::max (1, xcache_Soft_capacity / freelist_block_count);
xcache_Hashmap.init (xcache_Ts, THREAD_TS_XCACHE, xcache_Soft_capacity,
                     freelist_block_size, freelist_block_count, xcache_Entry_descriptor);

Concurrency — packed cache_flag

Section titled “Concurrency — packed cache_flag”

cache_flag packs the entry’s concurrency state into one 32-bit word: high 8 bits are state flags, low 24 bits are the fix count (number of active readers).

// flag bit layout — src/query/xasl_cache.c
#define XCACHE_ENTRY_MARK_DELETED            ((INT32) 0x80000000)
#define XCACHE_ENTRY_TO_BE_RECOMPILED        ((INT32) 0x40000000)
#define XCACHE_ENTRY_WAS_RECOMPILED          ((INT32) 0x20000000)
#define XCACHE_ENTRY_SKIP_TO_BE_RECOMPILED   ((INT32) 0x10000000)
#define XCACHE_ENTRY_CLEANUP                 ((INT32) 0x08000000)
#define XCACHE_ENTRY_RECOMPILED_REQUESTED    ((INT32) 0x04000000)
#define XCACHE_ENTRY_FLAGS_MASK               0xFF000000
#define XCACHE_ENTRY_FIX_COUNT_MASK           0x00FFFFFF

All transitions are CAS on the whole word, so flag and refcount updates serialise. xcache_compare_key not only compares — on success it fixes the entry by atomic-incrementing the fix count via the same CAS. xcache_find_sha1 therefore returns a fixed entry; the caller must call xcache_unfix.

The state machine in the high bits encodes five entry lifetimes plus cleanup:

stateDiagram-v2
  [*] --> NEW: xcache_Hashmap.freelist_claim
  NEW --> READY: insert_given (CAS into bucket)
  READY --> IN_USE: xcache_compare_key CAS \n fix_count++
  IN_USE --> READY: xcache_unfix CAS \n fix_count--
  READY --> RECOMP_PENDING: xcache_check_recompilation_threshold \n sets RECOMPILED_REQUESTED
  RECOMP_PENDING --> READY: client re-prepares, clears RR flag
  READY --> RECOMPILING: xcache_insert with recompile_xasl \n sets TO_BE_RECOMPILED
  RECOMPILING --> WAS_RECOMPILED: new entry inserted \n sets WAS_RECOMPILED, clears TBR
  WAS_RECOMPILED --> MARKED_DELETED: last unfix promotes \n WAS_RECOMPILED to MARK_DELETED
  READY --> MARKED_DELETED: xcache_remove_by_oid \n or xcache_drop_all
  IN_USE --> MARKED_DELETED: as above, reclamation deferred
  MARKED_DELETED --> DELETED_BY_ME: last unfix CAS \n only one thread wins
  DELETED_BY_ME --> [*]: xcache_Hashmap.erase + free

Subtlety: when xcache_unfix decrements to fix_count = 0 with MARK_DELETED set (this caller was the last fixer), the flag is promoted to XCACHE_ENTRY_DELETED_BY_ME, encoded with the current transaction’s index so concurrent deleters distinguish their own entries:

#define XCACHE_ENTRY_DELETED_BY_ME \
  ((XCACHE_ENTRY_MARK_DELETED | XCACHE_ENTRY_FIX_COUNT_MASK) - logtb_get_current_tran_index ())

The compare function then matches only the DELETED_BY_ME encoding belonging to the current transaction — the same lock- free trick used elsewhere in CUBRID, permitting multiple concurrent deleters without a shared lock.

Lookup — xcache_find_sha1 / xcache_find_xasl_id_for_execute

Section titled “Lookup — xcache_find_sha1 / xcache_find_xasl_id_for_execute”

There are two find functions because prepare and execute have different requirements.

Prepare path — xcache_find_sha1. Called when the client sends a SQL string and asks “do you have a plan for this?”. Key is just the SHA-1; success returns a fixed entry. The mode flag XASL_CACHE_SEARCH_FOR_PREPARE vs XASL_CACHE_SEARCH_FOR_EXECUTE governs which RT-check arm runs.

// xcache_find_sha1 — src/query/xasl_cache.c (condensed)
*xcache_entry = xcache_Hashmap.find (thread_p, lookup_key);
if (*xcache_entry == NULL)              { XCACHE_STAT_INC (miss); return NO_ERROR; }
xcache_Hashmap.end_tran (thread_p);     XCACHE_STAT_INC (hits);


if (rt_check)
  {
    if (search_mode == XASL_CACHE_SEARCH_FOR_PREPARE
        && ((*xcache_entry)->xasl_id.cache_flag & XCACHE_ENTRY_RECOMPILED_REQUESTED) != 0)
      *rt_check = XASL_CACHE_RECOMPILE_PREPARE;
    else if (xcache_check_recompilation_threshold (thread_p, *xcache_entry))
      {
        xcache_unfix (thread_p, *xcache_entry);
        *xcache_entry = NULL;
        *rt_check = (search_mode == XASL_CACHE_SEARCH_FOR_EXECUTE)
                       ? XASL_CACHE_RECOMPILE_EXECUTE : XASL_CACHE_RECOMPILE_PREPARE;
      }
  }

Execute path — xcache_find_xasl_id_for_execute. The client has a previously-handed-out XASL_ID and wants the runnable plan. Four ordered steps:

  1. Find by SHA-1 via xcache_find_sha1 (FOR_EXECUTE).
  2. Validate time_stored — the client cached (sha1, time_stored); mismatch means the entry was replaced by recompile. Unfix, return null, force re-prepare.
  3. Take object locks — walk related_objects and call lock_object on each. Mandatory: schema-modify lock holders could be invalidating the entry right now. After locks are held, re-check XCACHE_ENTRY_MARK_DELETED.
  4. Get a clone — try to pop a deserialised plan from cache_clones; on miss, call stx_map_stream_to_xasl to deserialise from stream.buffer, accounted against xcache_Memory_usage_clone.
// xcache_find_xasl_id_for_execute — src/query/xasl_cache.c (condensed)
xcache_find_sha1 (thread_p, &xid->sha1, XASL_CACHE_SEARCH_FOR_EXECUTE,
                  xcache_entry, &recompile_due_to_threshold);
if ((*xcache_entry)->xasl_id.time_stored.sec  != xid->time_stored.sec ||
    (*xcache_entry)->xasl_id.time_stored.usec != xid->time_stored.usec)
  { xcache_unfix (thread_p, *xcache_entry); *xcache_entry = NULL; return NO_ERROR; }


for (oid_index = 0; oid_index < (*xcache_entry)->n_related_objects; oid_index++)
  lock_object (thread_p, &(*xcache_entry)->related_objects[oid_index].oid,
               oid_Root_class_oid,
               (*xcache_entry)->related_objects[oid_index].lock, LK_UNCOND_LOCK);


if (ATOMIC_INC_32 (&((*xcache_entry)->xasl_id.cache_flag), 0) & XCACHE_ENTRY_MARK_DELETED)
  { xcache_unfix (thread_p, *xcache_entry); *xcache_entry = NULL; return NO_ERROR; }


if (xcache_uses_clones () && (*xcache_entry)->n_cache_clones > 0)
  *xclone = (*xcache_entry)->cache_clones[--(*xcache_entry)->n_cache_clones];   /* clone hit */
else
  stx_map_stream_to_xasl (thread_p, &xclone->xasl, use_xasl_clone,              /* deserialise */
                          (*xcache_entry)->stream.buffer,
                          (*xcache_entry)->stream.buffer_size, &xclone->xasl_buf);

Insert — xcache_insert and the recompile loop

Section titled “Insert — xcache_insert and the recompile loop”

xcache_insert is the only path that adds entries. The shape is claim-init-CAS, with one twist: if recompile is in flight, the loop installs the new entry beside the to-be-recompiled old entry, then atomically promotes the old entry to “was recompiled”.

// xcache_insert — src/query/xasl_cache.c (condensed)
XASL_ID_SET_NULL (&xid);  xid.sha1 = context->sha1;


while (true)
  {
    *xcache_entry = xcache_Hashmap.freelist_claim (thread_p);
    /* init: copy sha1, related_objects, stream, sql_info; fix_count = 1 */
    inserted = xcache_Hashmap.insert_given (thread_p, xid, *xcache_entry);


    if (inserted || !context->recompile_xasl) break;


    /* mark existing as TO_BE_RECOMPILED via CAS on cache_flag */
    do { cache_flag = (*xcache_entry)->xasl_id.cache_flag;
         new_cache_flag = cache_flag | XCACHE_ENTRY_TO_BE_RECOMPILED; }
    while (!XCACHE_ATOMIC_CAS_CACHE_FLAG (&(*xcache_entry)->xasl_id, cache_flag, new_cache_flag));


    to_be_recompiled = *xcache_entry;  *xcache_entry = NULL;
    xid.cache_flag = XCACHE_ENTRY_SKIP_TO_BE_RECOMPILED;   /* next iter skips it */
  }


if (to_be_recompiled != NULL)        /* promote old → WAS_RECOMPILED */
  {
    do { cache_flag = to_be_recompiled->xasl_id.cache_flag;
         new_cache_flag = (cache_flag & XCACHE_ENTRY_FIX_COUNT_MASK) | XCACHE_ENTRY_WAS_RECOMPILED; }
    while (!XCACHE_ATOMIC_CAS_CACHE_FLAG (&to_be_recompiled->xasl_id, cache_flag, new_cache_flag));
    xcache_unfix (thread_p, to_be_recompiled);
  }


if (xcache_need_cleanup () != XCACHE_CLEANUP_NONE && xcache_Cleanup_flag == 0)
  xcache_cleanup (thread_p);

The skip-flag pattern handles concurrent recompiles: the first caller marks TO_BE_RECOMPILED and proceeds, while the second either inserts beside the in-flight recompile or backs off (thread_sleep(1) and retry). The old entry stays usable for any executor still holding a pointer.

Recompile threshold — statistics-drift detection

Section titled “Recompile threshold — statistics-drift detection”

xcache_check_recompilation_threshold is the soft-invalidation path. Called from xcache_find_sha1 once per entry per cooldown window, it walks related_objects, fetches current heap pages from catalog_get_class_info, and compares against the compile-time tcard:

// xcache_check_recompilation_threshold — src/query/xasl_cache.c (condensed)
if (crt_time.tv_sec - xcache_entry->time_last_rt_check < XCACHE_RT_TIMEDIFF_IN_SEC) return false;
if (!ATOMIC_CAS_64 (&xcache_entry->time_last_rt_check, save_secs, crt_time.tv_sec)) return false;


for (relobj = 0; relobj < xcache_entry->n_related_objects; relobj++)
  {
    if (xcache_entry->related_objects[relobj].tcard < 0) continue;
    if (xcache_entry->related_objects[relobj].tcard >= XCACHE_RT_MAX_THRESHOLD) continue;
    cls_info_p = catalog_get_class_info (thread_p, &xcache_entry->related_objects[relobj].oid, NULL);
    npages = cls_info_p->ci_tot_pages;
    if (npages > XCACHE_RT_FACTOR * xcache_entry->related_objects[relobj].tcard
     || npages < xcache_entry->related_objects[relobj].tcard / XCACHE_RT_FACTOR)
      if (xcache_entry_set_request_recompile_flag (thread_p, xcache_entry, true)) recompile = true;
    catalog_free_class_info_and_init (cls_info_p);
  }

Constants: XCACHE_RT_TIMEDIFF_IN_SEC = 360 (per-entry cooldown), XCACHE_RT_MAX_THRESHOLD = 10000 (skip RT on tables ≥ 10k pages — relative drift is small for huge tables), XCACHE_RT_FACTOR = 10 (drift ≥ 10× triggers).

When the threshold crosses, the function sets XCACHE_ENTRY_RECOMPILED_REQUESTED — it does not invalidate. The next prepare returns ER_QPROC_XASLNODE_RECOMPILE_REQUESTED; the client re-prepares with the recompile bit, and the new compile replaces the old plan via the recompile loop in xcache_insert. Soft invalidation: in-flight executes continue using the old plan until they unfix.

DDL invalidation — per-class deletion

Section titled “DDL invalidation — per-class deletion”

DDL on a class fires xcache_remove_by_oid (thread_p, oid). The function delegates to xcache_invalidate_entries, which walks the hashmap (via xcache_hashmap_iterator), marks every matching entry deleted via xcache_entry_mark_deleted, batches the keys into a 1024-element delete_xids[] buffer (because each lock-free hashmap transaction can touch only one entry), and erases them after each batch — re-iterating until exhausted. Three variants share the engine:

  • xcache_remove_by_oid — match related_objects against an OID (used by DDL).
  • xcache_remove_by_sha1 — match xasl_id.sha1 (admin command DROP CACHED PLAN <sha1>).
  • xcache_drop_all — null check, matches everything.

The DDL hook lives in locator_sr.c — after every catalog update that completes a schema-modify, the locator fires the cache hook. Three call sites: locator_sr.c:5674, :6297, :13933, plus serial.c:1422 for ALTER SERIAL. Serials appear in related_objects alongside classes, so altering a serial that a plan binds must invalidate the plan.

flowchart TB
  subgraph CACHE["xcache_Hashmap"]
    E1["entry A<br/>related_objects = [c1, c2]"]
    E2["entry B<br/>related_objects = [c2, c3]"]
    E3["entry C<br/>related_objects = [c3]"]
    E4["entry D<br/>related_objects = [c1, s4]"]
  end
  DDL_C2["ALTER TABLE c2..."] --> XRBO["xcache_remove_by_oid(c2)"]
  XRBO --> SCAN["scan all entries"]
  SCAN --> E1
  SCAN --> E2
  SCAN --> E3
  SCAN --> E4
  E1 -.->|c2 ∈ list → MARK_DELETED| INV1["invalidated"]
  E2 -.->|c2 ∈ list → MARK_DELETED| INV2["invalidated"]
  E3 -.->|c2 ∉ list → keep| KEEP3["kept"]
  E4 -.->|c2 ∉ list → keep| KEEP4["kept"]

Eviction — LRU via binary heap

Section titled “Eviction — LRU via binary heap”

xcache_cleanup is fired from xcache_insert whenever xcache_need_cleanup() returns non-NONE. Two reasons: FULL_MEMORY (memory_usage_cache + memory_usage_clone > soft_limit, 80% of hard) and TIMEOUT (6h since last cleanup; PRM_ID_XASL_CACHE_TIME_THRESHOLD_IN_MINUTES).

Cleanup is two-stage. Collect: iterate the hashmap, pick entries with cache_flag == 0 (unfixed, no flags). For FULL_MEMORY push into a binary heap on time_last_used, keep the K oldest. For TIMEOUT push into a flat array filtered by “last used > time_threshold seconds ago”. Erase: for each candidate, set xid.cache_flag = XCACHE_ENTRY_CLEANUP and call xcache_Hashmap.erase. The compare function recognises the CLEANUP flag and CASes to DELETED_BY_ME only if the entry is still unfixed and unflagged — cleanup never wins races against in-flight readers or recompilers, it just moves on.

Only one cleanup runs at a time, gated by atomic CAS on xcache_Cleanup_flag. Concurrent inserters that observe an in-flight cleanup skip the trigger — no blocking.

End-to-end PREPARE / EXECUTE flow

Section titled “End-to-end PREPARE / EXECUTE flow”
sequenceDiagram
  participant CL as Client
  participant SQ as sqmgr_prepare
  participant XQ as xqmgr_prepare
  participant XC as xasl_cache
  participant EX as xqmgr_execute

  CL->>CL: PT_NODE_PRINT_TO_ALIAS<br/>· SHA1Compute
  CL->>SQ: PREPARE (sql, sha1, [stream]?)
  SQ->>XQ: xqmgr_prepare_query
  XQ->>XC: xcache_find_sha1(sha1, FOR_PREPARE)
  alt cache hit, no RT
    XC-->>XQ: entry (fixed) → unfix → return XASL_ID
  else cache hit, RT triggered
    XC-->>XQ: entry + RT_PREPARE → set recompile_xasl
  else cache miss
    XC-->>XQ: NULL → client compiles, ships stream
    XQ->>XC: xcache_insert(stream, related_objects)
  end
  SQ-->>CL: XASL_ID (sha1 + time_stored)

  CL->>EX: EXECUTE (XASL_ID, params)
  EX->>XC: xcache_find_xasl_id_for_execute
  XC->>XC: find_sha1 → check time_stored → lock_object<br/>→ pop clone OR stx_map_stream_to_xasl
  XC-->>EX: entry + XASL_CLONE
  EX-->>CL: list_id (result)
  EX->>XC: xcache_retire_clone + xcache_unfix

First prepare runs the full client-side compile (parser → semantic-check → optimizer → XASL generator → serialise → ship) plus server-side xcache_insert. Subsequent prepares for the same SHA-1 return the cached XASL_ID after a hashmap lookup only. Execute takes the XASL_ID, refinds, locks, deserialises or pops a clone, and runs. The fast path — two CAS operations plus a mutex-guarded clone pop — is microseconds; the slow path is milliseconds to hundreds of milliseconds. That ratio is what makes the cache load-bearing.

Filter-predicate and function-index sister caches

Section titled “Filter-predicate and function-index sister caches”

Two sister caches share this design:

  • Filter-predicate cache (fpcache), src/query/filter_pred_cache.c. Caches compiled predicates for partial/function-based indexes. SHA-1-keyed latch-free hashmap; per-class invalidation via fpcache_remove_by_class (called next to XASL invalidation in locator_sr.c).
  • Function-index expression cache — narrower; caches the compiled key-expression for a function-based index.

Dropped together with the XASL cache by xqmgr_drop_all_query_plans. The per-XASL-entry list_ht_no slot links into a third cache, the qfile result cache, which memoises the plan’s output by parameter shape. A planned cubrid-runtime-memoization.md will cover all three.

Per-session prepared-statement cache

Section titled “Per-session prepared-statement cache”

CUBRID’s session module (src/session/session.c) maintains a per-connection registry distinct from the XASL cache:

// PREPARED_STATEMENT — src/session/session.c
struct prepared_statement
{
  char *name;             /* user-given name, e.g. "stmt1" */
  char *alias_print;      /* normalised SQL text */
  SHA1Hash sha1;          /* same SHA-1 as the XASL cache key */
  int info_length;
  char *info;             /* serialised statement metadata */
  PREPARED_STATEMENT *next;
};

A linked list hangs off SESSION_STATE::statements, capped at MAX_PREPARED_STATEMENTS_COUNT = 20. APIs: session_create_prepared_statement, session_get_prepared_statement, session_delete_prepared_statement.

The two caches are asymmetric. The session registry maps per-session name → SHA-1 + statement metadata (no plan). The XASL cache maps SHA-1 → plan stream (server-wide). Two clients preparing the same statement under different names produce the same SHA-1 and converge on the shared XASL entry, even though their session-registry entries diverge. The XASL cache thus survives connection drops: closing a session clears the registry but leaves XASL entries in place for any other session that prepares the same statement.

Memory budget and parameters

Section titled “Memory budget and parameters”

System parameters: xasl_cache_max_entries sets xcache_Soft_capacity (hashmap initial size); xasl_cache_time_threshold_in_minutes sets the cache-level cleanup cooldown (default 360 minutes = 6 h); xasl_cache_max_clones caps per-entry clones; xasl_cache_logging toggles xcache_log. The hard memory limit is xcache_Soft_capacity * 1024 * 128; soft is 80% of hard; per-plan cap is (hard − soft) / UNPACK_SCALE.

Source Walkthrough

Section titled “Source Walkthrough”

Symbols grouped by subsystem (file is src/query/xasl_cache.c unless noted).

  • Types / globals. XASL_ID (storage_common.h), XASL_CACHE_ENTRY, XCACHE_RELATED_OBJECT, XASL_CLONE, EXECUTION_INFO (xasl_cache.h); xcache_hashmap_type, xcache_Global, xcache_* macros, XCACHE_STATS, XCACHE_CLEANUP_CANDIDATE, xcache_Entry_descriptor.
  • State flags / constants. XCACHE_ENTRY_MARK_DELETED, _TO_BE_RECOMPILED, _WAS_RECOMPILED, _SKIP_TO_BE_RECOMPILED, _CLEANUP, _RECOMPILED_REQUESTED, _FLAGS_MASK, _FIX_COUNT_MASK, _DELETED_BY_ME; XCACHE_ATOMIC_CAS_CACHE_FLAG. RT: XCACHE_RT_TIMEDIFF_IN_SEC, _MAX_THRESHOLD, _FACTOR, _CLASS_STAT_NEED_UPDATE.
  • Lifecycle. xcache_initialize, xcache_finalize, xcache_entry_alloc/_free/_init/_uninit, xasl_cache_ent::xasl_cache_ent/~xasl_cache_ent, init_clone_cache.
  • Hashing / key compare. xcache_hash_key, xcache_compare_key, xcache_copy_key. Macros in xasl.h: XASL_ID_SET_NULL, XASL_ID_COPY, XASL_ID_EQ, OR_PACK_XASL_ID, OR_UNPACK_XASL_ID.
  • Find / insert / unfix. xcache_find_sha1, xcache_find_xasl_id_for_execute, xcache_insert, xcache_unfix, xcache_entry_mark_deleted, xcache_entry_set_request_recompile_flag.
  • Invalidation. xcache_remove_by_oid, xcache_remove_by_sha1, xcache_drop_all, xcache_invalidate_qcaches, xcache_invalidate_entries, xcache_entry_is_related_to_oid, xcache_entry_is_related_to_sha1, xcache_check_recompilation_threshold.
  • Eviction. xcache_cleanup, xcache_need_cleanup, xcache_compare_cleanup_candidates, XCACHE_CLEANUP_{RATIO,MIN_NUM_ENTRIES,NUM_ENTRIES}.
  • Plan clones. xcache_uses_clones, xcache_retire_clone, xcache_clone_decache.
  • Sizing / diagnostics. xcache_entry_get_entrysize, _get_clonesize, _get_one_clonesize; xcache_dump, xcache_get_entry_count, xcache_can_entry_cache_list, xcache_check_logging, xcache_log, xcache_log_error.
  • Server prepare/execute integration. xqmgr_prepare_query, xqmgr_execute_query, xqmgr_drop_all_query_plans, xqmgr_drop_query_plans_by_sha1, xqmgr_dump_query_plans, qmgr_clear_relative_cache_entries, qmgr_is_related_class_modified (all query_manager.c).
  • Network integration. sqmgr_prepare_query, sqmgr_execute_query, sqmgr_prepare_and_execute_query, sqmgr_drop_all_query_plans, sqmgr_drop_query_plans_by_sha1 (network_interface_sr.cpp); net_prepare_query (network_interface_cl.c).
  • Parser / executor integration. SHA1Compute and PT_NODE_PRINT_TO_ALIAS (.. CUSTOM_PRINT_4_SHA_COMPUTE | PT_PRINT_LOWER) in execute_statement.c::do_execute_{select,update,delete,insert,merge}; stx_map_stream_to_xasl (stream_to_xasl.c).
  • DDL hooks (callers of xcache_remove_by_oid). locator_sr.c:5674, :6297, :13933; serial.c:1422.
  • Per-session prepared statements (sister system). PREPARED_STATEMENT (session.c), SESSION_STATE::statements, MAX_PREPARED_STATEMENTS_COUNT = 20, session_{create,get,delete,free,dump}_prepared_statement.

Position hints (as of 2026-05-01). Line numbers decay; use symbols as the canonical anchor.

SymbolFileLine
XASL_ID structsrc/storage/storage_common.h910
XASL_CACHE_ENTRY structsrc/query/xasl_cache.h91
XCACHE_ENTRY_* flag constantssrc/query/xasl_cache.c49–58
XCACHE_RT_* constantssrc/query/xasl_cache.c217–222
xcache_Entry_descriptorsrc/query/xasl_cache.c186
xcache_initializesrc/query/xasl_cache.c303
xcache_compare_keysrc/query/xasl_cache.c614
xcache_hash_keysrc/query/xasl_cache.c768
xcache_find_sha1src/query/xasl_cache.c818
xcache_find_xasl_id_for_executesrc/query/xasl_cache.c915
xcache_unfixsrc/query/xasl_cache.c1136
xcache_entry_mark_deletedsrc/query/xasl_cache.c1235
xcache_insertsrc/query/xasl_cache.c1406
xcache_invalidate_entriessrc/query/xasl_cache.c1801
xcache_remove_by_oidsrc/query/xasl_cache.c1988
xcache_drop_allsrc/query/xasl_cache.c2058
xcache_retire_clonesrc/query/xasl_cache.c2239
xcache_cleanupsrc/query/xasl_cache.c2311
xcache_check_recompilation_thresholdsrc/query/xasl_cache.c2575
xqmgr_prepare_querysrc/query/query_manager.c1003
xqmgr_execute_querysrc/query/query_manager.c1298
sqmgr_prepare_querysrc/communication/network_interface_sr.cpp5107
net_prepare_query SHA-1 packsrc/communication/network_interface_cl.c6786
SHA1Compute (do_execute_update)src/query/execute_statement.c9378
xcache_remove_by_oid (locator)src/transaction/locator_sr.c5674, 6297, 13933
xcache_remove_by_oid (serial)src/query/serial.c1422
PREPARED_STATEMENT / session_*_prepared_*src/session/session.c92, 1751, 1863, 1942
compile_contextsrc/xasl/compile_context.h37

Cross-check Notes

Section titled “Cross-check Notes”

Versus cubrid-server-session.md. The session-registry maps name → SHA-1 + statement info; the XASL cache maps SHA-1 → plan stream. DEALLOCATE PREPARE stmt1 clears the session entry only; connection close clears the registry but leaves the XASL cache intact. The XASL cache dies only on server restart, DROP CACHED PLAN, DDL, or eviction.

Versus cubrid-xasl-generator.md. The generator builds the in-memory XASL_NODE tree client-side and serialises via xts_map_xasl_to_stream to produce the byte buffer the cache stores. On hit, stx_map_stream_to_xasl rebuilds the tree. Generator and cache are decoupled — the cache treats the stream as opaque bytes plus SHA-1 plus related_objects. The dependency list is populated by the generator (each ACCESS_SPEC contributes its class OID) and shipped with the stream.

Versus cubrid-catalog-manager.md. The DDL invalidation hook lives on the locator, not the catalog manager — because the locator is the chokepoint that holds the schema-modify lock and therefore knows “this OID just changed”. The XASL cache depends on the locator’s discipline of firing the hook before releasing the schema-modify lock; bypassing the hook would leave stale plans alive.

Source-version drift. Line numbers in the position-hint table reflect the tree at updated:; symbol names are stable, line numbers should be treated as decaying hints.

Memory accounting. Hard cap is `xcache_Soft_capacity * 1024

  • 128. Soft cap is 80% of hard. Per-plan cap is (hard - soft) / UNPACK_SCALE. With most plans in the kilobyte range, the cache typically holds far more than xcache_Soft_capacity` entries before memory pressure triggers eviction.

Two cooldowns, different units. The per-entry RT cooldown XCACHE_RT_TIMEDIFF_IN_SEC is 360 seconds. The cache-level cleanup cooldown xasl_cache_time_threshold_in_minutes defaults to 360 minutes (6 hours). Same number, different units.

xcache_invalidate_qcaches vs xcache_invalidate_entries. The first only clears the result-list cache (qfile_clear_list_cache) for entries touching an OID — used at commit to drop stale results without losing plans. The second actually marks XASL entries deleted. Easy to confuse on casual read.

Open Questions

Section titled “Open Questions”
  • No parameter sniffing or parameter-shape variants. The key is SHA-1 of normalised SQL. Two executions with wildly different selectivity on the same parameter share one plan. Oracle and SQL Server detect this via parent/child cursors or plan guides; CUBRID has only the RT recompile, which fires on cardinality drift over time, not per-execution parameter skew.
  • No per-tenant or per-database partitioning. xcache_Global is one global. A multi-tenant deployment shares the cache across tenants — possible cross-tenant eviction or RT poisoning, depending on workload mix.
  • Off-heap / NUMA-aware cache? All entries are malloc memory; no NUMA awareness for the hot path.
  • Hash bucket selection uses only sha1.h[0] (32 bits). Full comparison uses all 160 bits, but bucket-prefix collisions at 100K entries are non-trivial; chain length under that load is not benchmarked in the repo tests.
  • time_stored granularity is microseconds. Two recompiles in the same microsecond on the same SHA-1 (a theoretical edge case) would collide; the recompile loop assumes one recompile at a time per entry.
  • Filter-predicate cache co-ordination. Both caches invalidate together on DDL, but eviction is independent — whether stranded fpcache entries occur in practice is not visible from xasl_cache.c alone.

Sources

Section titled “Sources”

Code: src/query/xasl_cache.{c,h}, query_manager.c, execute_statement.c, xasl.h, serial.c; src/storage/storage_common.h; src/xasl/compile_context.h; src/communication/network_interface_{sr.cpp,cl.c}; src/session/session.c; src/transaction/locator_sr.c.

Textbook / paper references:

  • Petrov, Database Internals (O’Reilly, 2019), Ch. 12.
  • Hellerstein, Stonebraker, Hamilton, Architecture of a Database System (FnT Databases 1(2), 2007).
  • Graefe, Query Evaluation Techniques for Large Databases (ACM CSUR 25(2), 1993).
  • Mohan et al., ARIES (ACM TODS 17(1), 1992) — buffer-manager reader-writer protocol adapted here.
  • Fraser, Practical Lock-Freedom (Cambridge UCAM-CL-TR-579, 2004) — epoch-based reclamation.

Comparative engines: PostgreSQL src/backend/utils/cache/plancache.c (per-backend, name-keyed, relcache callbacks); Oracle Library Cache (server-wide, hash- keyed, parent/child cursors); SQL Server plan cache (hash-keyed, parameter-sniffing-prone).

Knowledge-base cross-references: cubrid-xasl-generator.md (xts_map_xasl_to_stream produces the stream this cache stores); cubrid-server-session.md (per- session prepared registry); cubrid-catalog-manager.md (catalog path the DDL hook fires on); cubrid-query-optimizer.md (input to the generator); cubrid-page-buffer-manager.md (parallel cache pattern: refcounted entries, deferred reclamation, LRU via binary heap, lock-free identifier hashmap).