The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
In a busy Oracle application, repeatedly building SQL statements with different literal values can create many distinct statements and force avoidable hard parses. Use bind variables so the same SQL text can run with changing values, then verify that Oracle is reusing cursors before adjusting shared-pool memory or instance settings.
What causes a hard parse in Oracle?
A parse call asks Oracle to locate and validate a SQL statement and its executable representation. When Oracle finds a suitable shareable cursor in the library cache, it can reuse that cursor with a soft parse. If no suitable match exists, Oracle must hard parse the statement, doing additional work such as optimization and loading executable structures.
Hard parsing consumes more CPU and shared-pool and library-cache synchronization resources than soft parsing. Oracle calls hard parses “the most resource-intensive and unscalable” parse type because they perform all the operations involved in parsing. The goal is not zero hard parses: new statements, invalidations, and aged-out cursors can require them. The goal is to reduce avoidable repeated parsing. Oracle Database 19c SQL Performance Methodology
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11With exact cursor sharing, SQL text that differs only in a literal value may be treated as distinct text. For example, these statements can require separate cursors:
#1 Best Overall
SELECT employee_id FROM employees WHERE department_id = 10;
SELECT employee_id FROM employees WHERE department_id = 20;In a high-concurrency workload, many such statements can add parsing work and contention around shared memory. A hard-parse spike is not proof that the shared pool is too small; first determine whether the application is generating statements that Oracle cannot share.
How bind variables reduce hard parsing
A bind variable keeps the changing value out of the SQL text. The application sends one statement and supplies the value separately:
SELECT employee_id FROM employees WHERE department_id = :dept_id;For this to be a real bind, the application must bind :dept_id through its database driver or API. Concatenating a value into a SQL string and calling the result a bind does not make the statement shareable. Proper parameter binding also avoids the SQL-injection exposure associated with inserting untrusted input directly into SQL text. Oracle Database 19c: Improving Real-World Performance Through Cursor Sharing
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsOracle’s Real-World Performance group strongly suggests that enterprise applications use bind variables, as quoted in the Oracle Database 26 SQL Tuning Guide. Reusing the SQL text gives Oracle an opportunity to reuse a cursor rather than hard parse a new literal-specific statement each time.
What must match for cursor reuse
Matching SQL text is important, but it is not the only sharing criterion. Bind metadata and the session environment also matter. Keep bind names, data types, and lengths consistent where appropriate, and check that relevant session settings and object resolution are not making statements non-shareable. A statement that looks identical in application code may still fail to match a reusable cursor if its metadata or environment differs. Oracle Database 19c: Tuning the Shared Pool and the Large Pool
How to diagnose a hard-parse problem
Measure before changing configuration. Oracle’s performance-view guidance covers using system and SQL performance views to investigate parsing and workload behavior. Compare hard-parse activity with executions and inspect the statements responsible; ratios are diagnostic clues, not universal pass/fail thresholds. Oracle Database 26: Instance Tuning Using Performance Views
- Check whether hard parsing is elevated. Examine the
parse count (hard)statistic relative to execute counts, along with relevant session and system statistics. Look for statements with disproportionate parse calls rather than relying on one threshold. - Find statements that are not being shared. Compare their SQL text for literal variation. Review bind naming, data types and lengths, schema or object resolution, and session optimizer settings for differences that can prevent cursor sharing.
- Check application and connection behavior. Look for statements that are repeatedly prepared instead of reused, short-lived cursors, frequent logins and logoffs, and connection-pool or application cursor-cache behavior that creates unnecessary parse calls.
- Change the application pattern. Bind changing values and reuse prepared statements or open cursors where appropriate. Keep parameter metadata and relevant session settings consistent.
- Measure after deployment. Recheck hard parses, execution plans, and response time. A lower parse count alone does not prove every query received a better plan.
When should you increase shared-pool size?
Increase shared-pool size only when the evidence points to memory pressure or cursors being aged out before they can be reused. Undersizing is one possible contributor to shared-pool problems, but it is not the only one: literal-heavy SQL, poor cursor reuse, and connection behavior can also create excess work. Adding memory without addressing a statement-generation problem may leave the underlying cause intact. Oracle’s shared-pool tuning guidance discusses both sizing and cursor-sharing considerations.
Should you set CURSOR_SHARING=FORCE?
CURSOR_SHARING=FORCE can reduce some hard-parse overhead for legacy applications that issue literal-heavy SQL when the code cannot be changed immediately. Oracle describes this as a narrow, temporary mitigation—not a substitute for explicit application binding or a permanent fix. Scope the change carefully, test execution-plan behavior, and plan to correct the application so it binds values itself. Oracle Database 26 cursor-sharing guidance
Best Value
Can bind variables affect execution plans?
Yes. Values can have different selectivity, so one plan may not suit every data distribution. Oracle supports adaptive cursor sharing for bind-sensitive cases, allowing multiple plans where appropriate; that is a reason to validate plans and performance, not to avoid binds by default. Oracle Database 26 cursor-sharing guidance
There is a narrow workload exception: Oracle’s Database 19c shared-pool guide notes that unshared literal SQL may be recommended in low-concurrency, high-resource data-warehouse cases where literal-specific selectivity estimates help. This does not overturn the usual guidance for highly concurrent applications, where avoiding needless parse work is important. Oracle Database 19c shared-pool guidance
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.
