PL/SQL Loops and the Performance Problem they May Cause
PL/SQL is an excellent language for Oracle. Integrated with the database, highly useful extensions to SQL, extremely powerful when exploiting Oracle Objects, and in spite of all this, it is still way easy to learn. But like any procedural programming language, one can get lazy with it, tending to accept the first solution arrived at. Looping constructs in particular seem to be used as crutches rather than necessary components of a solution. Bad looping causing performance issues is a problem that liters the PL/SQL landscape. But it is easy to spot and fix.
In short the problem is this: a PL/SQL routine contains a select statement whose rows returned are used to drive other SQL code inside a loop. Cursor-for-loops are common. Inside the loop we will see SQL statements which do not use indexes based on the data elements in the driving row set and as a result do way more work than necessary. Here is a contrived but clear example:
for r1 in ( select * from dept where dept_name like ‘SALES%’ ) loop update emp set salary = salary * 1.1 ,last_update_date = sysdate where deptno = r1.deptno; end loop;
Many of you will recognize this SQL as a variation of the “lets give everyone in a sales department a raise of 10%”. Let us goof on it a little bit. Suppose there are three sales departments. Further suppose there is no index on DEPTNO of the EMP table. You should see right away that this means we will scan the EMP table three times when we run this code. If you don’t see why three passes of the EMP table will happen, then please go back and look at the code again. Three SALES departments means three rows in the for loop means three executed update statements each of which must access the EMP table looking for employees in the right department. No index on EMP.DEPTNO means a real scan, not an index lookup. We look at every row in the EMP table three times even though the most we can update each is once. Many we won’t update at all. What a waste of time.
Stating our example another way, if we are looping through departments, then we are exposing DEPTNO to the DML inside the loop. The SQL statements in the loop should be taking advantage of this. If not, then the loop is hurting us performance wise, not helping us. This leads to useless overhead being done each time through the loop, which is bad for performance.
It takes only a little imagination to recognize that this could become a problem in situations where there are lots of rows coming out of the loop, or lots of rows in the EMP table. Fortunately we have at least three ways to fix this problem. The one to use will depend upon the specifics your code.
If you have any data sense at all you likely have thought, “that is stupid; DEPTNO on the EMP table is a foreign key, it should have been indexed”. Of course you are right, as we all should know by now that that when it comes to Oracle, every Primary Key, Unique Key, and Foreign Key should be supported by an index, no exceptions. Well, there may be exceptions but I have never met one for this rule that could be justified. Creating an index on DEPTNO is the first and most obvious way to fix this problem. Adding an index on EMP.DEPTNO would mean that each loop would execute an update statement that was driven by the loop data. Thus although we would still be doing three updates because in our scenario we said there were three Sales departments and thus three rows in our loop, each update, rather than a table scan, would be an indexed lookup that looked at only those rows it was interested in.
Certainly that solves the potential performance problem presented. But adding an index, in my experience is not the best approach to fixing this problem most of the time. You should consider two questions before adding an index to your database, to solve a problem like this.
First, whenever an index is added to a data system, you run the risk of negatively affecting something else in the system. Databases these days are no longer little pockets of information; they are large integrated data services and their pieces do not operate independently of each other even though we often treat component pieces that way. A new index can and will change query plans for many things that access the table getting the new index. Thus you can for example hose your online application by accident at the expense of making some batch component go faster. This is not far fetched. I in fact did this last week, which is part of the reason for this article. If you are going to add an index, at the least, have some idea of what accesses the table you are enhancing, and be available for the next day incase you need to back it out. Also, make sure to pass the new index by your DBA. Remember the BUDDY rule; “never go swimming in hot water without a friend”.
Second, if your goal of adding the index is to change the query plan of the DML in the loop in order by make the DML run faster then, what makes you think the query plan you are trying to change is actually a bad one? Recent releases of Oracle’s Cost Based Optimizer are in fact quite good. Maybe not perfect, but if statistics for tables and indexes have been collected properly, then the likelihood is that Oracle is generating good plans. The query plan for the update statement in our example may be a good one too. Oracle tuned the update in our example, thinking it was only going to execute the update once (what else can it think?). It is our loop that is the actual problem because it forces Oracle to execute SQL multiple times but the Cost Based Optimizer can’t consider that fact in its plan generation. If the update in our example were indeed only executing once, then the query plan for the update may be a great plan and an index would only be going backwards performance wise. Nope, a better solution most of the time is to get rid of the loop altogether.
In reality, you should never see a simple example like we have shown above. What you will see are much more sophisticated pieces of code. Loop structures that contain either large SQL statements with many joins, or many SQL statements, or more than likely, both. What we need to do is get rid of the loop altogether. In this way, we take advantage of the Optimizer’s plan crafting ability without affecting anything but the code we want to affect.
Why not recode the update to look like this:
update emp set salary = salary * 1.1 ,last_update_date = sysdate where deptno in ( select deptno from dept where dept_name like ‘SALES%’ );
This update statement will do the same thing as the original code, but in a sense embeds the loop inside the update statement by encompassing the DEPT select in its where clause. The fact that there is no index on DEPTNO is now less important because we are no longer walking the data manually as the loop forced us to do. The Oracle optimizer can consider the nature of the sub-query in its query plan. If there are enough EMP rows in our SALES departments, then a full table scan may indeed be the best plan for this update. In situations where the embedded SQL has lots of joins and sub-queries or is otherwise highly complex (as is what we often see inside PL/SQL loops), then re-coding the PL/SQL to remove its reliance on the loop structure often yields the results we desire. Of course you will have to do some testing to demonstrate that you have not changed the meaning of the process, but this testing is much easier than the regression testing you might be faced with when you add an index. Hmm… I wonder how many people actually consider regression testing when all they do is add an index?
We said there were at least three solutions to this situation. The third is a variation of the one we just looked at. The likelihood is that your loop does more than one thing. That is, the loop contains many SQL statements, not just one. In this situation you don’t want to be adding what might be a large sub-query to every piece of SQL in the loop. That would require the materialization of the same data across multiple statements which itself can have a performance cost. So, it can be beneficial to back up in time a little and use an older technique that works pretty well; the working table.
Create table work1 (deptno number); Insert into work1 select deptno from dept where dept_name like ‘SALES%’; update emp set salary = salary * 1.1 ,last_update_date = sysdate where deptno in (select deptno from work1);
Use of a worktable preserves our driving rows and thus lets us do the loop query only once yet use its results in the code controlled by the loop everywhere we need it. Many people don’t like using worktables because they seem “dirty” somehow. I know the feeling. But I got over it. Worktables are very effective mechanisms when employed for the right reasons.
To recap, the problem we are dealing with is a loop structure that drives one of more SQL statements but the SQL inside the loop does not use the data the loop exposes, as part of an efficient path in its query plan. The solutions presented above are to either a) do something with indexing to alter the query plan of each SQL statement in the loop and hope it will be faster (less preferred solution), or b) to eliminate the loop altogether by somehow adding the loop’s query to the other SQL directly, or indirectly via a work table (more preferred).
So what kind of improvement can one expect? Naturally that depends upon how bad your performance problem is. The fact that you are looking at a problem in the first place means it is likely pretty bad. You should expect to achieve orders of magnitude improvement. This means you should be expecting something between 10 and 100 times better performance when you are done. Jobs that take 10 hours to run should take 1 hour or better yet, 10 minutes. The reason you should expect this is because, by removing the loop structure, you should be eliminating massive amounts of overhead work done every time through the loop, that never contributed to the final result. Even if the query plan of each DML does not change after removing reliance on the loop structure, you should still see this improvement because you will only be executing each SQL statement once, not N-times (based on the number of rows of the loop). Your goal was never to change query plans, it was to stop doing useless overhead. A query plan change with this method is only a side-affect to achieving your performance goal of removing needless overhead work.
Remember what to look for: DML contained in a loop that does not take advantage in its query plan, of the data values exposed by loop. If you see this, get cracking as you may be looking at some serious gravy.
About the Author: Kevin Meade is a 22-year veteran of Relational Databases in general and Oracle in particular. He is employed under many hats: Architect, Designer, Modeler, and Programmer and so is currently a contractor using the title of “Oracle Specialist” in the Finance and Insurance Industries. Married with two girls, he likes to swim in his heated pool with his family, is addicted to Strategy computer games, keeps a Saltwater Reef in his home, and brews his own dark beers (yum).