Sample Chapter: Efficient PL/SQL

SQL> select *from custlog;

 ELAPSED_CENTISECONDS
---------------------
                10785
                10878
                11172
                11116
                11184
                11450
                11347
                11701
                11655
                11897
                11726
                12055
                11962
                12028
                12373
                11859
                11995
                11905
                12547
                11977

20 rows selected.

We averaged approximately 115 seconds for each of the jobs and the number of rows created in the customers table is 200,000. Given that these tests were performed on a single CPU laptop, these results are comparable with the 55-60 seconds per 100,000 rows results we obtained earlier.

Achieving Efficiency

For our programs to be efficient, they must fall within our elapsed time requirements and not cause critical damage to any other part of the system or its resources. We also must be able to demonstrate that this efficiency can be maintained under most or all anticipated conditions.

For the remainder of this chapter, we present some basic guidelines to achieving efficient PL/SQL along with code to demonstrate how this efficiency is achieved. We're not even going to try to cover everything here. After all, this whole book is basically about writing and using PL/SQL in an efficient manner. At this point, we're going to focus on three simple, high-level guidelines for achieving efficiency.

• Minimize the amount of work the database has to do when parsing the SQL you want to execute. In a sense, this is not a PL/SQL issue because it is by no means limited to executing SQL from PL/SQL. It applies to execution of SQL from any language (Java, Visual Basic, and so on). However, it is possibly the number one cause of nonscalability in Oracle applications, so it must be discussed.
• Understand PL/SQL's features and functionality and know how to correctly exploit them so you never have to reinvent the wheel.
• Never use PL/SQL to do the job of SQL.

Page 6 of 19



This article was originally published on February 5, 2004