Before Oracle runs a SQL statement it parses it. When the exact same text was parsed before, it reuses the shared cursor, a soft parse; when the text is new, it checks the syntax, the objects and privileges, and runs the optimizer, a hard parse. Hard parses cost CPU, take latches on the shared pool, and fill it with statements used once. An application that builds its SQL with literal values, such as WHERE order_id = 42, makes every statement new. This guide measures the difference between literals and bind variables, and shows what CURSOR_SHARING does.
Sample Table
Example:
create table demo_orders ( order_id number constraint demo_orders_pk primary key, amount number(10,2) not null); insert into demo_orders select level, mod(level * 13, 500) + 10 from dual connect by level <= 50000; commit; exec dbms_stats.gather_table_stats(user, 'DEMO_ORDERS')
Output:
Table DEMO_ORDERS created. 50,000 rows inserted. Commit complete. PL/SQL procedure successfully completed.
The examples read the session's hard parse count from V$MYSTAT before and after 1,000 lookups, which needs the SELECT_CATALOG_ROLE role or SELECT privileges on the V$ views.
1,000 Lookups with Literals
Each lookup builds its statement by concatenating the order ID into the text:
Example:
set serveroutput on
declare
l_amount number;
l_start number := dbms_utility.get_time;
l_hard0 number;
function hard_parses return number is
l_value number;
begin
select s.value into l_value
from v$mystat s join v$statname n on n.statistic# = s.statistic#
where n.name = 'parse count (hard)';
return l_value;
end;
begin
l_hard0 := hard_parses;
for i in 1 .. 1000 loop
execute immediate
'select /* hp_literal */ amount from demo_orders where order_id = ' || i
into l_amount;
end loop;
dbms_output.put_line('hard parses: ' || (hard_parses - l_hard0));
dbms_output.put_line('elapsed: ' || (dbms_utility.get_time - l_start) * 10 || ' ms');
end;
/Output:
hard parses: 1000 elapsed: 200 ms PL/SQL procedure successfully completed.
Every statement text is different, so all 1,000 were hard parsed.
1,000 Lookups with a Bind Variable
Example:
set serveroutput on
declare
l_amount number;
l_start number := dbms_utility.get_time;
l_hard0 number;
function hard_parses return number is
l_value number;
begin
select s.value into l_value
from v$mystat s join v$statname n on n.statistic# = s.statistic#
where n.name = 'parse count (hard)';
return l_value;
end;
begin
l_hard0 := hard_parses;
for i in 1 .. 1000 loop
execute immediate
'select /* hp_bind */ amount from demo_orders where order_id = :id'
into l_amount using i;
end loop;
dbms_output.put_line('hard parses: ' || (hard_parses - l_hard0));
dbms_output.put_line('elapsed: ' || (dbms_utility.get_time - l_start) * 10 || ' ms');
end;
/Output:
hard parses: 1 elapsed: 10 ms PL/SQL procedure successfully completed.
One hard parse for the first run; the other 999 reused the cursor. The loop took 10 ms instead of 200 ms: the work was the same, the parsing was not.
The Effect on the Shared Pool
Example:
select case when sql_text like '%hp_literal%' then 'literals' else 'bind variable' end
as version,
count(*) as statements, sum(executions) as executions,
round(sum(sharable_mem) / 1024) as memory_kb
from v$sql
where sql_text like 'select /* hp_literal */%' or sql_text like 'select /* hp_bind */%'
group by case when sql_text like '%hp_literal%' then 'literals' else 'bind variable' end;Output:
VERSION STATEMENTS EXECUTIONS MEMORY_KB ________________ _____________ _____________ ____________ literals 1000 1000 26959 bind variable 1 1000 27
The literal version left 1,000 cursors in the shared pool, each executed once, taking about 27 MB. The bind variable version has one cursor, executed 1,000 times, in 27 KB. On a busy system, literal SQL pushes useful cursors out of the shared pool, which causes even more hard parses, and the latch contention it causes limits how many sessions can work at once.
CURSOR_SHARING = FORCE
When an application cannot be changed, CURSOR_SHARING = FORCE makes Oracle replace literals with system-generated binds before parsing:
Example:
alter session set cursor_sharing = force;
set serveroutput on
declare
l_amount number;
l_start number := dbms_utility.get_time;
l_hard0 number;
function hard_parses return number is
l_value number;
begin
select s.value into l_value
from v$mystat s join v$statname n on n.statistic# = s.statistic#
where n.name = 'parse count (hard)';
return l_value;
end;
begin
l_hard0 := hard_parses;
for i in 1 .. 1000 loop
execute immediate
'select /* hp_force */ amount from demo_orders where order_id = ' || i
into l_amount;
end loop;
dbms_output.put_line('hard parses: ' || (hard_parses - l_hard0));
dbms_output.put_line('elapsed: ' || (dbms_utility.get_time - l_start) * 10 || ' ms');
end;
/
select sql_text, executions from v$sql where sql_text like 'select /* hp_force */%';Output:
Session altered. hard parses: 1 elapsed: 10 ms PL/SQL procedure successfully completed. SQL_TEXT EXECUTIONS ____________________________________________________________________________ _____________ select /* hp_force */ amount from demo_orders where order_id = :"SYS_B_0" 1000
The literal SQL was hard parsed only once, and V$SQL shows the shared text with :"SYS_B_0" in place of the order ID. It is a workaround, not a fix: every statement is rewritten, including those where the literal mattered for the plan, and the bind peeking issues of real binds apply to the generated ones too. Set it for a session or an application's service, rather than for the whole database, and change the code when possible.
Things to Know
- Find literal SQL in a running system by grouping V$SQL on FORCE_MATCHING_SIGNATURE: many statements with the same signature differ only in their literals.
- In PL/SQL, static SQL with PL/SQL variables uses binds automatically; only dynamic SQL built by concatenation has this problem.
- In Java, Python, and other clients, use prepared statements with placeholders instead of building SQL strings.
- Bind variables also protect against SQL injection, since values are never part of the statement text.
Related Guides
- How Bind Variables Affect Execution Plans: Bind Peeking and Adaptive Cursor Sharing
- How to Find Slow SQL Statements with V$SQL and V$SQLSTATS
Conclusion
SQL built with literals is hard parsed for every new value, which costs CPU and floods the shared pool with single-use cursors. Use bind variables, which here cut 1,000 hard parses to one and 27 MB of cursors to 27 KB, and keep CURSOR_SHARING = FORCE as a stopgap for code you cannot change.
