Bind Variables: The Hard-Parse Storm That Melts Your Shared Pool
The database is pinned at 90% CPU, the app team swears nothing changed, and AWR is topped by cursor: pin S wait on X and a parse-heavy profile. Nobody wrote a slow query. What happened is quieter: somewhere a developer built SQL by pasting the value straight into the string — "...WHERE id = " + orderId — and now every one of a million requests a day is a brand-new statement that Oracle has never…
When developers write SQL queries by directly inserting values into string literals, Oracle has to parse each unique request individually. This process is called a hard parse, which is time-consuming and consumes shared pool memory. A soft parse occurs when Oracle reuses a previously parsed query, saving resources.
To prevent this hard-parse storm, use bind variables as placeholders in SQL strings. Bind variables ensure Oracle parses the query only once, regardless of the actual values used. This approach significantly reduces the number of hard parses, shared pool memory usage, and CPU consumption.
However, bind variables are not always the best option. For data warehouse and reporting queries that run infrequently and with skewed data, using literals may produce more accurate plans. In such cases, parsing once with a specific value can yield the optimal execution plan.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
