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