In a busy Oracle application, embedding changing values directly in SQL can turn one logical query into many distinct statements. Oracle may have to hard parse each one instead of reusing a cursor, adding CPU work and contention in the shared pool and library cache. Bind variables and statement reuse are the usual durable fix; check the workload before changing memory settings or relying on CURSOR_SHARING=FORCE.
What causes a hard parse in Oracle?
When an application sends SQL to Oracle, a parse call asks the database to locate and validate the statement and its executable representation. If Oracle finds a suitable shareable cursor, it can reuse it with a soft parse. If no suitable cursor is available, Oracle must hard parse the statement, doing more work, including optimization and loading executable structures.
| # | Preview | Product | Price | |
|---|---|---|---|---|
| 1 |
|
Oracle SQL and Pl/Sql | $50.50 | Buy on Amazon |
| 2 |
|
Oracle PL / SQL For Dummies | $15.95 | Buy on Amazon |
| 3 |
|
Mastering Oracle SQL, 2nd Edition | $20.80 | Buy on Amazon |
| 4 |
|
Oracle PL/SQL by Example (The Oracle Press Database and Data Science) | $48.81 | Buy on Amazon |
| 5 |
|
Oracle PL/SQL Programming: Covers Versions Through Oracle Database 12c | $70.14 | Buy on Amazon |
As an Amazon Associate I earn from qualifying purchases.
With exact cursor sharing, different literal values make different SQL text. For example, department_id = 10 and department_id = 20 can become separate parent cursors even though the queries express the same operation. At high concurrency, repeated hard parsing consumes CPU and increases coordination around shared-memory structures. Oracle describes hard parses as “the most resource-intensive and unscalable” parses because they perform all the operations involved in a parse, in its 19c SQL Performance Methodology.
Free tools Windows power users keep installed
One-click scans. No signup required.
Not every hard parse signals a defect: new SQL, invalidations, and executable representations that have aged out can require parsing. The goal is to reduce avoidable repeated parsing, not eliminate all parse operations.
#1 Best Overall
How bind variables reduce hard parsing
A bind variable keeps the SQL text stable while the application supplies a changing value separately. Instead of generating a new statement for each department, the application can prepare and execute a statement such as:
SELECT employee_id FROM employees WHERE department_id = :dept_id;The application must bind the value through its database driver or API. Concatenating input into a SQL string is not binding, even if the code or variable is described as a parameter. Actual binding can improve cursor reuse and avoids the SQL-injection exposure associated with building SQL from untrusted input. Oracle’s 19c cursor-sharing guidance recommends bind variables for applications.
Rank #2
What must match for cursor sharing
Stable text helps, but does not guarantee sharing. Oracle’s 19c shared-pool guidance identifies sharing criteria that include matching SQL text and bind metadata, as well as an identical session environment. Keep bind names, types, and lengths consistent, and check whether schema resolution or session optimizer settings differ between executions. Otherwise, statements that look equivalent in application code may not share a cursor.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →How to diagnose and fix excessive hard parsing
- Establish whether hard parsing is elevated. Compare the
parse count (hard)statistic with execution counts and other relevant session or system statistics. Use performance views to find statements with disproportionate parse calls. Treat ratios as diagnostic clues, not universal pass/fail thresholds; Oracle documents performance-view analysis in its 26 Instance Tuning Using Performance Views. - Find statements that are not being shared. Inspect their text for changing literals, then compare bind names and types, object or schema resolution, and session settings. These differences can prevent reuse even when the application regards the statements as the same query.
- Correct the application pattern. Bind changing values and reuse prepared statements or open cursors where appropriate. Review connection pooling and application cursor-cache behavior as well: frequent logins and logoffs or a short-lived cursor lifecycle can add unnecessary parse calls.
- Measure after the change. Check whether hard parses fall, and also examine execution plans and response times. Fewer parses alone do not prove that every query has a better plan.
- Change shared-pool sizing only with evidence. Undersizing can contribute to pressure or cursor aging, but it is only one possible cause. First determine whether memory pressure or aged-out cursors are present rather than treating a larger pool as a substitute for SQL reuse.
Should you use CURSOR_SHARING=FORCE?
Oracle documents CURSOR_SHARING=FORCE as a possible temporary mitigation for some legacy applications that generate literal-heavy SQL when their code cannot be changed immediately. It is not equivalent to explicit application binding and should not be treated as a permanent repair. Scope and test it against the workload, including execution-plan behavior, while planning to correct the application. See Oracle’s 26 cursor-sharing guidance.
Rank #3
Bind reuse can raise a plan-quality question when different values have very different data distributions. Oracle supports adaptive cursor sharing, which can allow multiple plans for bind-sensitive statements; binding does not mean every execution must use one unsuitable plan. Conversely, Oracle’s 19c shared-pool guide describes a narrow exception: in low-concurrency, high-resource data-warehouse workloads, literal SQL may help Oracle estimate value-specific selectivity. That workload-specific case does not change the usual advice for highly concurrent applications.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.What the performance gain does—and does not—mean
Oracle’s 19c SQL Performance Methodology includes an illustrative test on a four-CPU computer: it reports support for 270 users with no parsing of statements, 150 with soft parsing, and 60 with hard parsing in an OLTP test. The source does not establish the experiment’s publication date, so these figures are not a current benchmark or a general capacity expectation. The useful takeaway is qualitative: repeated hard parsing can become a scalability bottleneck, and the impact depends on the system and workload.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →
