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, using bind variables allow reuse of statements as the text of the statement remains the same. Only the value of the bind variable 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
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 bind variable. The CPU usage is retrieved from the
V$MYSTATview 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. SQL> SELECT sn.name, ms.value 2 FROM v$mystat ms, v$statname sn 3 WHERE ms.statistic# = sn.statistic# 4 AND sn.name = 'parse time cpu'; 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> SQL> SELECT sn.name, ms.value 2 FROM v$mystat ms, v$statname sn 3 WHERE ms.statistic# = sn.statistic# 4 AND sn.name = 'parse time cpu'; 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.
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 can potentially reduce performance compared to proper use of bind variables.
No comments:
Post a Comment