March 8, 2021
Hot Topics:

Sample Chapter: Efficient PL/SQL

  • By Beck, Kallman, Katz, Knox, and McDonald
  • Send Email »
  • More Articles »
SQL> create or replace
  2  procedure binding is
  3    c number;
  4    p number;
  5    x number;
  6    x1 number;
  7  begin
  8    c :=dbms_sql.open_cursor;
  9    dbms_sql.parse(c,
 10      'select pid from people '||
 11      'where pid =:b1',dbms_sql.native);

Notice the subtle difference here. The parse call is performed only once and is no longer within the loop.

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

Procedure created.

When we execute and trace the execution of this new version, the trace file shows even better performance.

select pid
  people where pid =:b1

call     count      cpu  elapsed   disk   query  current   rows
-------  ------  ------  -------  -----  ------  -------  -----
Parse    1         0.00     0.00      0       0        0      0
Execute  10000     0.65     0.39      0       0        0      0
Fetch    10000     0.27     0.26      0   20000        0   9999
-------  ------  ------  -------  -----  ------  -------  -----
total    20001     0.92     0.66      0   20000        0   9999

We only have a single parse call now. By simply reducing the amount of parsing, performance has been improved from an initial 30 seconds to less than 1 second. Many developers simply refuse to believe me when I tell them that Oracle can run 10,000 SQL queries in less than one second.

The examples just shown are carefully crafted to use the dbms_sql package to process SQL. One of the best things about PL/SQL is that it's easy to adopt the best practices of minimal parsing and bind variables. Let's recode the example using conventional static SQL.

SQL> create or replace
  2  procedure EASY_AS_THAT is
  3    x1 number;
  4  begin
  5    for i in 1 ..10000 loop
  6      select pid into x1
  7      from people
  8      where pid =i;
  9    end loop;
 10  end;
 11  /

Procedure created.

When we execute this, the trace file looks identical to the (optimal) results of the previous example. PL/SQL automatically uses bind variables for any PL/SQL variables, and minimizes parsing for static SQL. Oracle expects you to program using bind variables, and consequently PL/SQL makes it very easy for us to use them. As we said at the start, this is a strong argument for using PL/SQL in your applications. It naturally lends itself to building high-performance, scalable Oracle applications.

If the only language you use for working with Oracle is PL/SQL, you will probably be unaware that this is not generally the case with other 3GL languages. You will often have a lot more work to do to ensure appropriate usage of bind variables. Let's pick up again on the point made earlier in the chapter about Java, and the use of the JDBC Statement and PreparedStatement. In JDBC, the easiest way to process a SQL statement is to use a statement object. It is quite common to see code such as the following, which does not use bind variables:

Statement stmt =conn.createStatement();
for (int i =0;i <10000;i++){
Efficient PL/SQL
  ResultSet rs =stmt.executeQuery("select pid from people
                                    where pid ="+i );

As we proved, this code will not scale. It is much more effective to put in a little more coding effort and use PreparedStatement and bind variables, as follows:

  PreparedStatement ps;
  for (int i =0;i <10000;i++){
    pstmt =conn.prepareStatement("select pid from people
                                   where pid =?");
    ResultSet rs =pstmt.executeQuery();

A few more lines of code are required to avoid the parsing cost. However, even this is not the whole story. The code achieves a result equivalent to the example that performs an unnecessary amount of soft parsing. Each and every time, we open pstmt, execute our SQL, and then close it again. To eliminate this, for each new SQL statement, we should parse only once and execute as many times as necessary. In Java, we do this using the singleton pattern, as follows:

static PreparedStatement pstmt;
  if (pstmt ==null){
    pstmt =conn.prepareStatement("select pid from people
                                  where pid =?");
  for (int i =0;i <10000;i++){

Doing it the right way in JDBC takes quite a bit of thought and significantly more code.

Page 10 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