Hard Parse vs Soft Parse, Cursor Sharing, Bind Variables, and SQL Injection Defense

Learning outcomes

ServiceHub's ingest service submits 20,000 inserts per minute, embedding every ID and status as literals. CPU rises while storage I/O remains moderate. Security review also finds user-entered text concatenated into search predicates. The same application bug is hurting two different properties: literalized SQL prevents efficient sharing, and concatenation lets untrusted data alter SQL syntax. Proper bind variables address both mechanisms—but only when the application actually binds through a driver/API.

1. Hard parse builds executable code; soft parse reuses it

When Oracle cannot reuse an existing compatible cursor, it performs a hard parse: semantic checks, optimization, executable row-source generation, library-cache/data-dictionary work, and memory allocation. A soft parse is a parse that can reuse existing executable code after checking compatibility. Soft parse is cheaper, but it is not free.

High-throughput OLTP should minimize parse calls per execution through stable SQL, binds, and appropriate application/driver statement caching. A database that soft-parses every execution can still waste CPU and serialization resources compared with reusing an already-open/prepared statement.

SELECT n.name, m.valueFROM v$mystat mJOIN v$statname n  ON n.statistic# = m.statistic#WHERE n.name IN (  'parse count (total)',  'parse count (hard)',  'parse time cpu',  'parse time elapsed',  'execute count')ORDER BY n.name;

2. Literal SQL multiplies parent statements

With CURSOR_SHARING=EXACT—the current default—statements that differ in literal text do not share the same exact cursor identity. A loop that generates ... WHERE work_order_id=1, then 2, then 3 produces many statement texts and repeated hard-parse opportunity.

BEGIN  FOR i IN 1..200 LOOP    EXECUTE IMMEDIATE      'SELECT /* sh10_l2_literal */ COUNT(*) ' ||      'FROM servicehub_bind_case WHERE work_order_id = ' || TO_CHAR(i);  END LOOP;END;/

This is intentionally poor application behavior. The exact hard-parse delta depends on which statements are already in the shared pool, so capture before/after session statistics instead of asserting a fixed number.

3. Bind values keep SQL text stable

DECLARE  n NUMBER;BEGIN  FOR i IN 1..200 LOOP    EXECUTE IMMEDIATE      'SELECT /* sh10_l2_bind */ COUNT(*) ' ||      'FROM servicehub_bind_case WHERE work_order_id = :id'      INTO n      USING i;  END LOOP;END;/

Now Oracle can reuse the same parent/child cursor for many values when compatibility permits. A driver-level prepared statement or statement cache can reduce even soft-parse overhead further by reusing a prepared cursor across executions.

4. Binding is also a security boundary

When a client passes a value through a bind placeholder, Oracle treats the value as data rather than parsing its contents as SQL syntax. This is why bind variables are one of Oracle's primary SQL-injection defenses.

-- Vulnerable shape: untrusted text changes the SQL text itself.sql_text :=  'SELECT COUNT(*) FROM servicehub_bind_case ' ||  'WHERE status_code = ''' || user_status || '''';EXECUTE IMMEDIATE sql_text INTO n;

sql_text :=  'SELECT COUNT(*) FROM servicehub_bind_case ' ||  'WHERE status_code = :status';EXECUTE IMMEDIATE sql_text INTO n USING user_status;

Binds cannot substitute a table name, column name, keyword, ORDER BY direction, or arbitrary query text. If identifiers truly must be dynamic, constrain them to an application allowlist and/or validate with appropriate DBMS_ASSERT functions before concatenating the identifier. Do not quote/escape arbitrary user input and call the result equivalent to binding.

5. CURSOR_SHARING=FORCE is compatibility machinery, not application design

CURSOR_SHARING currently supports EXACT and FORCE, with EXACT as the default. FORCE allows Oracle to replace safe literals with system-generated binds to improve sharing for legacy literal-heavy applications. It can alter selectivity/plan behavior, does not bind identifiers, and is not a SQL-injection defense.

SELECT name, value, isdefault, isses_modifiable, ispdb_modifiableFROM v$parameterWHERE name = 'cursor_sharing';-- Disposable session-only experiment if desired:ALTER SESSION SET cursor_sharing = FORCE;SELECT /* sh10_l2_force */ COUNT(*)FROM servicehub_bind_caseWHERE work_order_id = 10;SELECT /* sh10_l2_force */ COUNT(*)FROM servicehub_bind_caseWHERE work_order_id = 20;ALTER SESSION SET cursor_sharing = EXACT;

Use FORCE only after measuring a legacy workload and documenting plan/regression risk. The primary fix remains changing application code to bind correctly.

6. Hands-on lab: measure literal versus bind behavior without flushing caches

DROP TABLE servicehub_bind_case IF EXISTS PURGE;CREATE TABLE servicehub_bind_case (  work_order_id NUMBER PRIMARY KEY,  status_code   VARCHAR2(12) NOT NULL,  amount        NUMBER(10,2) NOT NULL);INSERT INTO servicehub_bind_caseSELECT LEVEL,       CASE MOD(LEVEL,3)         WHEN 0 THEN 'OPEN'         WHEN 1 THEN 'CLOSED'         ELSE 'HOLD'       END,       MOD(LEVEL*29,10000)/10FROM dualCONNECT BY LEVEL <= 5000;COMMIT;

SELECT n.name, m.valueFROM v$mystat mJOIN v$statname n ON n.statistic#=m.statistic#WHERE n.name IN ('parse count (total)','parse count (hard)','execute count')ORDER BY n.name;

DECLARE  n NUMBER;BEGIN  FOR i IN 1..300 LOOP    EXECUTE IMMEDIATE      'SELECT /* sh10_l2_literal */ COUNT(*) ' ||      'FROM servicehub_bind_case WHERE work_order_id=' || TO_CHAR(i)    INTO n;  END LOOP;END;/

DECLARE  n NUMBER;BEGIN  FOR i IN 1..300 LOOP    EXECUTE IMMEDIATE      'SELECT /* sh10_l2_bind */ COUNT(*) ' ||      'FROM servicehub_bind_case WHERE work_order_id=:id'    INTO n    USING i;  END LOOP;END;/

SELECT  CASE    WHEN sql_text LIKE 'SELECT /* sh10_l2_literal */%' THEN 'LITERAL'    WHEN sql_text LIKE 'SELECT /* sh10_l2_bind */%' THEN 'BOUND'  END AS workload,  COUNT(DISTINCT sql_id) AS parent_sql_ids,  SUM(executions) AS executions,  SUM(parse_calls) AS parse_callsFROM v$sqlWHERE sql_text LIKE 'SELECT /* sh10_l2_literal */%'   OR sql_text LIKE 'SELECT /* sh10_l2_bind */%'GROUP BY  CASE    WHEN sql_text LIKE 'SELECT /* sh10_l2_literal */%' THEN 'LITERAL'    WHEN sql_text LIKE 'SELECT /* sh10_l2_bind */%' THEN 'BOUND'  ENDORDER BY workload;DROP TABLE servicehub_bind_case PURGE;

The exact counters depend on what has aged into or out of the shared pool. The qualitative expectation is many literal parent identities versus one stable bound statement identity.

7. Production judgment

Bind application data through the driver API. Keep bind data types stable across executions. Reuse prepared/statement handles in connection pools. Use explicit allowlists for dynamic identifiers. Monitor hard parses, total parses, executions per parse, and version counts. Do not solve an injection vulnerability with CURSOR_SHARING=FORCE; Oracle can only substitute literals after the SQL text has already been constructed.

No restart, pack, or COMPATIBLE change is required. Lesson 3 addresses the legitimate concern behind “binds always force one plan”: skewed values can need different selectivities, and Oracle's bind peeking plus Adaptive Cursor Sharing can adapt when the evidence justifies it.

Authoritative references

• Designing Applications for Oracle Real-World Performance — bind variables, parse scalability and injection guidance
• SQL Injection — bind-variable and validation defenses
• CURSOR_SHARING — EXACT/FORCE semantics and current default
• Statistics Descriptions — parse count and parse time session/system statistics
• Tuning the Shared Pool and the Large Pool — shared-cursor reuse guidance