If
you've read anything about writing OLTP applications that talk to Oracle
databases, you will know that bind variables are very important.
Each
time an SQL statement is sent to the database, an exact text match is performed
to see if the statement is already present in the shared pool. If no matching
statement is found a hard parse is performed, which is a resource intensive
process. If the statement is found in the shared pool this step is not necessary
and a soft parse is performed. Concatenating variable values into an SQL statement makes the
statement unique, forcing a hard parse. By contrast, usingbind variables allow reuse of statements as the text of the
statement remains the same. Only the value of the bindvariable changes.
Why
do we care?
· Holding many similar SQL statements in the
shared pool is a waste of memory.
· Filling the shared pool with similar
statements will cause well written statements to get paged out of the shared
pool quickly, forcing them to be reparsed also.
· Parsing SQL statements is a resource intensive
process. Reducing the number of hard parses results in reduced CPU usage.
In
the sections below you will see the impact of using literals, substitution variables and bind variables in your code.
· Literals
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 WHERE clause. Finally the contents of the shared pool is displayed by
querying the V$SQL view.
SQL> ALTER SYSTEM
FLUSH SHARED_POOL;
System altered.
SQL> SELECT * FROM
dual WHERE dummy = 'LITERAL1';
no rows selected
SQL> SELECT * FROM
dual WHERE dummy = 'LITERAL2';
no rows selected
SQL> COLUMN
sql_text FORMAT A60
SQL> SELECT
sql_text,
2
executions
3
FROM v$sql
4
WHERE INSTR(sql_text, 'SELECT *
FROM dual WHERE dummy') > 0
5
AND INSTR(sql_text, 'sql_text')
= 0
6
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>
From
this we can see that both queries were parsed separately.
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.
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
SQL> SELECT
sql_text,
2
executions
3
FROM v$sql
4
WHERE INSTR(sql_text, 'SELECT *
FROM dual WHERE dummy') > 0
5
AND INSTR(sql_text, 'sql_text')
= 0
6
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>
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.
SELECT * FROM dual
WHERE dummy = '&1';
This
can be called from SQL*Plus like this.
SQL> @dummy MyValue
When
run, the placeholder '&1' will be replaced by the value 'MyValue'. This is
just the same as a substitution variable.
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.
SQL> ALTER SYSTEM
FLUSH SHARED_POOL;
System altered.
SQL> VARIABLE dummy
VARCHAR2(30);
SQL> EXEC :dummy :=
'BIND_VARIABLE1';
PL/SQL procedure
successfully completed.
SQL> SELECT * FROM
dual WHERE dummy = :dummy;
no rows selected
SQL> EXEC :dummy :=
'BIND_VARIABLE2';
PL/SQL procedure
successfully completed.
SQL> SELECT * FROM
dual WHERE dummy = :dummy;
no rows selected
SQL> COLUMN
sql_text FORMAT A60
SQL> SELECT
sql_text,
2
executions
3
FROM v$sql
4
WHERE INSTR(sql_text, 'SELECT *
FROM dual WHERE dummy') > 0
5
AND INSTR(sql_text, 'sql_text')
= 0
6
ORDER BY sql_text;
SQL_TEXT
EXECUTIONS
------------------------------------------------------------
----------
SELECT * FROM dual
WHERE dummy = :dummy 2
1 row selected.
SQL>
This
clearly demonstrates that the same SQL statement was executed twice.
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 bindvariable. The CPU usage is retrieved from the V$MYSTAT view by querying the "parse time cpu" 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.
SQL> CONN
sys/password AS SYSDBA
Connected.
SQL> ALTER SYSTEM
FLUSH SHARED_POOL;
System altered.
SQL> CONN
sys/password AS SYSDBA
Connected.
SQL> DECLARE
2
l_dummy dual.dummy%TYPE;
3
BEGIN
4
FOR i IN 1 .. 10 LOOP
5
BEGIN
6
EXECUTE IMMEDIATE 'SELECT dummy FROM dual WHERE dummy = ''' ||
TO_CHAR(i) || ''''
7
INTO l_dummy;
8
EXCEPTION
9
WHEN NO_DATA_FOUND THEN
10
NULL;
11
END;
12
END LOOP;
13 END;
14 /
PL/SQL procedure
successfully completed.
2
FROM v$mystat ms, v$statname sn
3
WHERE ms.statistic# =
sn.statistic#
NAME
VALUE
----------------------------------------------------------------
----------
parse time cpu
63
1 row selected.
SQL> COLUMN
sql_text FORMAT A60
SQL> SELECT
sql_text,
2
executions
3
FROM v$sql
4
WHERE INSTR(sql_text, 'SELECT dummy
FROM dual WHERE dummy') > 0
5
AND INSTR(sql_text, 'sql_text')
= 0
6
AND INSTR(sql_text, 'DECLARE')
= 0
7
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>
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 USING clause is used to supply the loop index,
rather than concatenating it into the string.
SQL> CONN
sys/password AS SYSDBA
Connected.
SQL> ALTER SYSTEM
FLUSH SHARED_POOL;
System altered.
SQL> CONN
sys/password AS SYSDBA
Connected.
SQL>
SQL> DECLARE
2
l_dummy dual.dummy%TYPE;
3
BEGIN
4
FOR i IN 1 .. 10 LOOP
5
BEGIN
6
EXECUTE IMMEDIATE 'SELECT dummy FROM dual WHERE dummy = TO_CHAR(:dummy)'
7
INTO l_dummy USING i;
8
EXCEPTION
9
WHEN NO_DATA_FOUND THEN
10
NULL;
11
END;
12
END LOOP;
13 END;
14 /
PL/SQL procedure
successfully completed.
SQL>
2
FROM v$mystat ms, v$statname sn
3
WHERE ms.statistic# =
sn.statistic#
NAME
VALUE
----------------------------------------------------------------
----------
parse time cpu
40
1 row selected.
SQL>
SQL> COLUMN
sql_text FORMAT A60
SQL> SELECT
sql_text,
2
executions
3
FROM v$sql
4 WHERE INSTR(sql_text, 'SELECT dummy FROM dual WHERE
dummy') > 0
5
AND INSTR(sql_text, 'sql_text')
= 0
6
AND INSTR(sql_text, 'DECLARE')
= 0
7
ORDER BY sql_text;
SQL_TEXT
EXECUTIONS
------------------------------------------------------------
----------
SELECT dummy FROM dual
WHERE dummy = TO_CHAR(:dummy)
10
1 row selected.
SQL>
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
No comments:
Post a Comment