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?

Your script does not show any "contention on the bind variables". It merely shows the SQL Oracle keeps in the shared pool. Do you see the corresponding wait events? Plus: this should be better asked on dba.stackoverflow — , Nov 26 '14 at 07:31
If you're talking about contention that means you're worried about performance. Are you actually seeing any performance problem? — Jeffrey Kemp, Nov 26 '14 at 11:07
No my main concern is - do statements like BEGIN :var1 := 'LITERAL_A1' ; END have anything to do with library cache. Are these parsed and locks taken on library cache for these. Actually I am not very sure of difference between v$sqlarea and library cache. Does every query mentioned in v$sqlarea with execution=1 hit the library cache performance? — Rohit, Nov 26 '14 at 12:07

score 4 · Answer 1 · answered Nov 26 '14 at 08:17

"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.

I am assuming that v$sqlarea shows queries stored in library cache. If thats true than adding startements like BEGIN :var1 := 'LITERAL_A1' ; END; to it every time would require locks. Am I right? or is library cache different from v$sqlarea and statement like bind variable assignments have no entries in the cache? — Rohit, Nov 26 '14 at 08:24

I have a SQL script

1 Answers1