Query Parsing Pipeline: Soft vs Hard Parse

Query parsing transforms a SQL string into an executable plan through lexical analysis (tokenizing), syntax analysis (building Abstract Syntax Tree (AST)), semantic analysis (name resolution, type checking, permissions), view expansion, and query rewriting (predicate pushdown, constant folding, subquery decorrelation). The critical performance distinction is between soft parse and hard parse.

A soft parse happens when the query plan cache contains a matching entry. The system normalizes the query (replacing literals with parameter placeholders), hashes it, and finds a cached plan. This bypasses expensive optimization and completes in microseconds. Amazon Aurora OLTP workloads achieve over 95% plan cache hit rates with proper parameterization, reducing parse CPU overhead by 10 to 30% at throughput rates of 10,000 to 50,000 transactions per second (TPS).

A hard parse occurs on cache miss and triggers full cost based optimization, which can add 2 to 10 milliseconds of latency per query due to shared metadata latching and optimization computation. Under high query per second (QPS) workloads with 10,000 to 50,000 concurrent queries, hard parse storms create contention on system catalogs and can elevate CPU usage by 20 to 40%. Systems that fail to parameterize queries (using literals instead of bind variables) pollute the plan cache with unique entries, forcing repeated hard parses and degrading throughput.

💡 Key Takeaways

•Soft parse reuses cached plans and completes in microseconds versus 2 to 10 milliseconds for hard parse with full optimization

•Amazon Aurora achieves over 95% cache hit rates with parameterized queries, reducing parse CPU by 10 to 30% at 10,000 to 50,000 TPS

•Hard parse storms occur when non parameterized queries pollute the cache, causing metadata latch contention and 20 to 40% CPU increases

•Query normalization replaces literals with parameters to create cache keys, enabling plan reuse across similar queries

•Trade off: parameterization improves reuse but can cause parameter sniffing issues when selectivity varies dramatically across parameter values

📌 Examples

Good parameterization: SELECT * FROM orders WHERE user_id = ? AND status = ? (cacheable plan)

Bad non parameterized: SELECT * FROM orders WHERE user_id = 12345 AND status = 'shipped' (unique cache entry per literal combination)

Amazon Aurora customers typically see parse CPU drop from 30% to under 5% of total CPU after fixing parameterization issues in high throughput OLTP applications

← Back to Query Parsing & Optimization Overview