DBA Hub

📋Steps in this guide1/5

Literals, Substitution Variables and Bind Variables

The article compares the affect of using literals, substitution variables and bind variables on memory and CPU usage.

oracle miscconfigurationintermediate
by OracleDba
14 views
1

Literals

The following example shows the affect of using literals on the shared pool. First the shared pool is cleared of previously parsed statements. Then two queries are issued, both specifying literal values in the clause. Finally the contents of the shared pool is displayed by querying the view. From this we can see that both queries were parsed separately.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
alter system flush shared_pool;

select * from dual where dummy = 'LITERAL1';
select * from dual where dummy = 'LITERAL2';


column sql_text format a60
select sql_text,
       executions
from   v$sqlarea
where  instr(sql_text, 'select * from dual where dummy') > 0
and    instr(sql_text, 'sql_text') = 0
order by sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
select * from dual where dummy = 'LITERAL1'                           1
select * from dual where dummy = 'LITERAL2'                           1

2 rows selected.

SQL>
2

Substitution Variables

Substitution variables are a feature of the SQL*Plus tool. They have nothing to do with the way SQL is processed by the database server. When a substitution variable is used in a statement, SQL*Plus requests an input value and rewrites the statement to include it. The rewritten statement is passed to the database. As a result, the database server knows nothing of the substitution variable. The following example illustrates this by repeating the previous test, this time using substitution variables. Once again, both statements were parsed separately. As far as the database server is concerned, literals and substitution variables are the same thing. Exactly the same behavior occurs when scripts contain placeholders to allow parameters to be sent to them from the command line. So for example, imagine a script called "dummy.sql" containing the following. This can be called from SQL*Plus like this. When run, the placeholder '&1' will be replaced by the value 'MyValue'. This is just the same as a substitution variable.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
SQL> alter system flush shared_pool;

System altered.

SQL> select * from dual where dummy = '&dummy';
Enter value for dummy: SUBSTITUTION_VARIABLE1
old   1: select * from dual where dummy = '&dummy'
new   1: select * from dual where dummy = 'SUBSTITUTION_VARIABLE1'

no rows selected

SQL> select * from dual where dummy = '&dummy';
Enter value for dummy: SUBSTITUTION_VARIABLE2
old   1: select * from dual where dummy = '&dummy'
new   1: select * from dual where dummy = 'SUBSTITUTION_VARIABLE2'

no rows selected

SQL>


column sql_text format a60
select sql_text,
       executions
from   v$sqlarea
where  instr(sql_text, 'select * from dual where dummy') > 0
and    instr(sql_text, 'sql_text') = 0
order by sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
select * from dual where dummy = 'SUBSTITUTION_VARIABLE1'             1
select * from dual where dummy = 'SUBSTITUTION_VARIABLE2'             1

2 rows selected.

SQL>

select * from dual where dummy = '&1';

SQL> @dummy MyValue
3

Bind Variables

The following example illustrates the affect of bind variable usage on the shared pool. It follows the same format as the previous examples. This clearly demonstrates that the same SQL statement was executed twice.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
alter system flush shared_pool;

variable dummy varchar2(30);

exec :dummy := 'BIND_VARIABLE1';
select * from dual where dummy = :dummy;

exec :dummy := 'BIND_VARIABLE2';
select * from dual where dummy = :dummy;


column sql_text format a60
select sql_text,
       executions
from   v$sqlarea
where  instr(sql_text, 'select * from dual where dummy') > 0
and    instr(sql_text, 'sql_text') = 0
order by sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
select * from dual where dummy = :dummy                               2

1 row selected.

SQL>
4

Performance Issues

The following example measures the amount of CPU used by a session for hard and soft parses when using literals. The shared pool is flushed and a new session is started. Dynamic SQL is used to mimic an application sending 10 statements to the database server. Notice that the value of the loop index is concatinated into the string, rather than using a bind variable. The CPU usage is retrieved from the view by querying the " " statistic. This statistic represents the total CPU time used for parsing (hard and soft) in 10s of milliseconds. The statements present in the shared pool are also displayed. The results show that 630 milliseconds of CPU time were used on parsing during the session. In addition, the shared pool contains 10 similar statements using literals. The following example is a repeat of the previous example, this time using bind variables. Notice that the clause is used to supply the loop index, rather than concatenating it into the string. The results show that 400 milliseconds of CPU time were used on parsing during the session, less than two thirds the amount used in the previous example. As expected, there is only a single statement in the shared pool. These simple examples clearly show how replacing literals with bind variables can save both memory and CPU, making OLTP applications faster and more scalable. If you are using third-party applications that don't use bind variables you may want to consider setting the CURSOR_SHARING parameter, but this should not be considered a replacement for bind variables. The CURSOR_SHARING parameter is less efficient and can potentially reduce performance compared to proper use of bind variables.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
conn sys/password as sysdba

alter system flush shared_pool;


conn sys/password as sysdba

declare
  l_dummy  dual.dummy%type;
begin
  for i in 1 .. 10 loop
    begin
      execute immediate 'select dummy from dual where dummy = ''' || to_char(i) || ''''
      into l_dummy;
    exception
      when no_data_found then
        null;
    end;
  end loop;
end;
/

PL/SQL procedure successfully completed.

SQL>


select sn.name, ms.value
from   v$mystat ms, v$statname sn
where  ms.statistic# = sn.statistic#
and    sn.name       = 'parse time cpu';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                           63

1 row selected.

SQL>


column sql_text format a60
select sql_text,
       executions
from   v$sqlarea
where  instr(sql_text, 'select dummy from dual where dummy') > 0
and    instr(sql_text, 'sql_text') = 0
and    instr(sql_text, 'declare') = 0
order by sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
select dummy from dual where dummy = '1'                              1
select dummy from dual where dummy = '10'                             1
select dummy from dual where dummy = '2'                              1
select dummy from dual where dummy = '3'                              1
select dummy from dual where dummy = '4'                              1
select dummy from dual where dummy = '5'                              1
select dummy from dual where dummy = '6'                              1
select dummy from dual where dummy = '7'                              1
select dummy from dual where dummy = '8'                              1
select dummy from dual where dummy = '9'                              1

10 rows selected.

SQL>

conn sys/password as sysdba

alter system flush shared_pool;


conn sys/password as sysdba

declare
  l_dummy  dual.dummy%type;
begin
  for i in 1 .. 10 loop
    begin
      execute immediate 'select dummy from dual where dummy = to_char(:dummy)'
      into l_dummy using i;
    exception
      when no_data_found then
        null;
    end;
  end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> 


select sn.name, ms.value
from   v$mystat ms, v$statname sn
where  ms.statistic# = sn.statistic#
and    sn.name       = 'parse time cpu';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                           40

1 row selected.

SQL> 


column sql_text format a60
select sql_text,
       executions
from   v$sqlarea
where  instr(sql_text, 'select dummy from dual where dummy') > 0
and    instr(sql_text, 'sql_text') = 0
and    instr(sql_text, 'declare') = 0
order by sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
select dummy from dual where dummy = to_char(:dummy)                 10

1 row selected.

SQL>
5

SQL Injection

Concatenating strings together with user input to form an SQL statement is a classic way to allow an SQL injection attack against your system. If you use bind variables for user input, the statement can't be attacked with an SQL injection attack. The statement is fixed. Only the bind variable value changes. If for some reason you can't use a bind variable, you must sanitise the user input. This can be done using the package, described here . For more information see: - V$SQL - V$MYSTAT - When to Set CURSOR_SHARING to a Nondefault Value - Bind Variables : For Performance and Protection Against SQL Injection - DBMS_ASSERT - Sanitize User Input to Help Prevent SQL Injection - CURSOR_SHARING : Automatically Convert Literals to Bind Variables in SQL Statements Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!