I have a SQL script

Question

I have a SQL script that looks like this:

Variable nb number;
Variable var1   varchar2(30);
Variable var2   varchar2(30);
EXEC :var1 := '&1';
EXEC :var2 := '&2';

BEGIN
    SELECT count(*) into :nb FROM some_table where col1=:var1 and col2=:var2;
END;
/
print :nb;
exit :nb;

This script is executed multiple times using the commands like:

sqlplus @myscript.sql LITERAL_A1 LITERAL_B1
sqlplus @myscript.sql LITERAL_A2 LITERAL_B2
sqlplus @myscript.sql LITERAL_A3 LITERAL_B3
sqlplus @myscript.sql LITERAL_A4 LITERAL_B4

Now my question is related to shared pool access.

When I run the query:

select executions,sql_text 
from v$sqlarea 
where ( sql_text like '%var1%' or  sql_text like '%var2%' ) 

I get output like:

BEGIN SELECT count(*) into :nb1 FROM some_table where col1=:var1 and col2=:var2; END; [ Execution=4]
BEGIN :var1 := 'LITERAL_A1' ; END;[ Execution=1]
BEGIN :var2 := 'LITERAL_B1'; END; [ Execution=1]
BEGIN :var1 := 'LITERAL_A2' ; END;[ Execution=1]
BEGIN :var2 := 'LITERAL_B2'; END; [ Execution=1]
BEGIN :var1 := 'LITERAL_A3' ; END;[ Execution=1]
BEGIN :var2 := 'LITERAL_B3'; END; [ Execution=1]
BEGIN :var1 := 'LITERAL_A4' ; END;[ Execution=1]
BEGIN :var2 := 'LITERAL_B4'; END; [ Execution=1]

This shows that contention on the main select query is removed but the initialization of bind variables been added contention. Is there any way of removing this?

Answer 1

"the initialization of bind variables been added contention"

Contention doesn't mean how you use it here. There is no contention, no competing for resource.

Rather, what you have is a number of similar statements. They are similar because SQL*Plus has substitution variables; these are not bind variables and resolve to hard-coded values. Hence, each execution is different, and so a different statement in the cache.

All of this is completely expected behaviour. If you think you have a genuine problem you're probably mistaken. Oracle will age out these one shot statements, so they're unlikely to be preventing the caching of more regularly used queries.

If you really want to make them go away, stop using SQL*Plus scripts and move to stored procedures instead.