February 28, 2021
Hot Topics:

Sample Chapter: Efficient PL/SQL

  • By Beck, Kallman, Katz, Knox, and McDonald
  • Send Email »
  • More Articles »

Bind Variables to the Rescue

Let's return to our example scenario from earlier in this section. We want to maximize the chances of an SQL statement being found in our log of previously processed SQL statements. In order to achieve that, we need to use bind variables. With a small change to the literals procedure, we can create a new procedure, binding, which takes advantage of bind variables.

SQL> create or replace
  2  procedure binding is
  3    c number;
  4    p number;
  5    x number;
  6    x1 number;
  7  begin
  8  for i in 1 ..10000 loop
  9    c :=dbms_sql.open_cursor;
 10    dbms_sql.parse(c,
 11      'select pid from people '||
 12      'where pid =:b1 ',dbms_sql.native);

Notice that the SQL statement that we parse never actually changes. It is only after we have parsed the statement that we assign a value to the bind variable. This is the key difference here. From the point of view of hard parsing, in this procedure we are running 10,000 identical SQL statements. We'll see shortly how this makes a large difference to how Oracle will process the statement.

 13     dbms_sql.bind_variable(c,':b1',i);
 14     x :=dbms_sql.execute(c);
 15     x1 :=dbms_sql.fetch_rows(c);
 16     dbms_sql.close_cursor(c);
 17   end loop;
 18 end;
 19 /

 Procedure created.

We then run and trace the execution as per the previous test

SQL> alter session set sql_trace =true;

Session altered.

SQL> exec binding

PL/SQL procedure successfully completed.

SQL> alter session set sql_trace =false;

Session altered.

A look at the trace file seems to show that we are still parsing (the parse count is still 10,000), but somehow the performance is dramatically improved.

select pid
  people where pid =:b1
call     count      cpu  elapsed   disk   query  current   rows
-------  ------  ------  -------  -----  ------  -------  -----
Parse     10000    0.99     1.02      0       0        0      0
Execute   10000    1.17     1.26      0       0        0      0
Fetch     10000    0.77     0.56      0   20000        0   9999
-------  ------  ------  -------  -----  ------  -------  -----
total     30000    2.93     2.85      0   20000        0   9999

So the routine is still performing lots of parses—after all, we called the dbms_sql.parse routine 10,000 times, so the figures make sense. To explain where the performance benefit was obtained, we need to look at the session-level statistics. To do so, first we create a view that makes it easy to obtain session-level statistics.

SQL> create or replace
  2  view V$MYSTATS as
  3  select s.name,m.value
  4  from v$mystat m,v$statname s
  5  where s.statistic#=m.statistic#;

View created.

SQL> grant select on V$MYSTATS to public;

Grant succeeded.

SQL> create or replace public synonym V$MYSTATS for V$MYSTATS;

Synonym created.

Now we can look at the statistics that correspond to parsing on the system.

SQL> select *from v$mystats
  2  where name like 'parse%';

NAME                                 VALUE
------------------------------  ----------
parse time cpu                         107
parse time elapsed                     137
parse count (total)                  10019
parse count (hard)                       2
parse count (failures)                   0

The key statistic is "parse count (hard)." Although in our binding procedure we asked Oracle to parse a SQL statement 10,000 times, Oracle performed this very expensive task only twice—once for the execution of the binding procedure, and once for the first SQL statement parsed. The remaining 9,999 calls to parse did not require a full parse because the SQL statement is unchanged between executions. We reused the parsing information. This is soft parsing—an explicit parse call was issued but the SQL code found in the shared pool could be reused. That is the beauty of bind variables—the likelihood of an individual SQL statement being reused is far greater. We can do even better than that—as we saw in the binding procedure, the SQL statement we parsed did not change for each iteration of the loop. So if the SQL does not change between executions, we do not need to parse it again at all, and we can take the parse call out of the loop altogether, yielding the following revised solution.

Page 9 of 19

This article was originally published on February 5, 2004

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date