 |
|
|
|
|
|
|
|
|
|
|
|
|
|
Last month we looked at how to identify and optimize SQL statements that need tuning. This month, I’d like to discuss how we can do the same thing for PL/SQL routines.
We all know that PL/SQL code consumes CPU and places some additional load on the database server. However, prior to 10g it was virtually impossible to determine how much overhead was contributed by PL/SQL. I don’t know about you, but I have a “what I don’t know can’t hurt me” type of attitude and so I rarely worried about the contribution of PL/SQL to database performance.
However, things changed in 10g. Oracle added the v$sys_time_modelview, which showed the amount of time spent executing PL/SQL code, and added the PLSQL_EXEC_TIMEcolumn to V$SQLso you can see how much time a SQL statement spent executing PL/SQL code.
In this article, I'll discuss how to find PL/SQL packages that might be causing performance problems, how to identify the problems within a PL/SQL program and look at ways of making PL/SQL run faster.
Finding PL/SQL tuning opportunities
Figure 1 shows us querying v$sys_time_modelto determine what percentage of time PL/SQL is spending on the database. You can download this query here. In this case, PL/SQL contributed to almost 9% of total DB time.
Figure 1 Determining how much time PLSQL is expending on your database
We can use Spotlight to find the “top” PL/SQL in almost the same way as we use it to find top SQL. In this case, we filter for those statements that included some PL/SQL time, and sort them by the amount of PL/SQL time concerned. Figure 2 provides an example.
Figure 2 Finding "top" PLSQL
In most databases, a lot of the top PLSQL found will be Oracle’s own PL/SQL packages. A lot of Oracle maintenance routines are written in PL/SQL and some of them – such as the OEM collection routines - are fairly resource intensive and/or run very frequently. You may want to use further filters to avoid seeing these such as the “Parsing user” filter, which you can set to the account which executes the PL/SQL that concerns you. You might also want to adjust the columns displayed by Spotlight to show the PL/SQL time column. The column organizer can be found in the upper right above the results grid (see Figure 3) and allows you to select which columns to display and in what order.
Figure 3 Using the Spotlight column organizer to display PL/SQL time
Figure 4 shows the results for one of my databases. Amongst the many Oracle internal PL/SQL routines is a SQL statement that involves very significant PL/SQL time. The statement is a SELECT, but invokes a PL/SQL function (plsql_tuning_examples.isprime1)in the where clause. It’s easy to see that this PL/SQL routines accounts for the majority of this SQL statements execution time.
Figure 4 Top PL/SQL statements
The purpose of the isprime1 function is to return “1” if the number passed is a prime number and “0” otherwise. In honor of my math teacher’s birthday, we’re giving away free iPhones to customers whose customer number is a prime (a number divisible only by itself and 1).
Tuning PLSQL with the TOAD profiler
The prime number function might require a bit of tuning so let’s get into it. For normal SQL statements our next step would be to obtain an execution plan, but in this case we need to try an alternative approach. The best tool for tuning PLSQL is the PLSQL profiler, which can use from within TOAD.
To enable PL/SQL profiling in TOAD, make sure that you’ve installed server side objects (Database>Administer>Server Side Objects Wizard) and then click the profiler icon (  ) on the Toad toolbar). Then execute the PLSQL routine from the schema browser. Figure 5 shows us executing the stored function that we identified as a possible issue back in Figure 4.
Figure 5 Executing the PLSQL routine in TOAD
The profiler output identifies the amount of times and number of executions of each package involved in the PL/SQL execution and breaks down these times to individual lines of code. For instance, in Figure 6 we can see clearly that line 30 accounts for about three quarters of PLSQL execution time.
Figure 6 Toad PLSQL Profiler output
Optimizing PL/SQL loops
In line 30, we used the MODfunction to find the remainder when dividing the customer number by another number. If the remainder is 0, then the number can be divided by another number and is therefore not prime – no IPhone for that customer! The complete function is shown in Figure 7.
Figure 7 The original (inefficient) prime number routine
This implementation includes a significant inefficiency. We loop through all the numbers that are lower than the provided customer_id, even after we have found a number that divides evenly. However, the first time we find a divisor we know the number is not prime so we really don’t need to continue to loop any further. The Unnecessary loop iterations and MOD calls are adding unnecessarily to execution overhead. We can avoid these unnecessary iterations simply by EXITing once we find a divisor.
Figure 8 Modified (efficient) prime number routine
Figure 8 shows the modified routine. Now, we stop processing as soon as we find a divisor. This radically reduces the overhead of the routine. We can see from the profiler output that the new routine completes in about 1/10th of the time of the original routine (Figure 9)
Figure 9 Improvement gained by optimizing our LOOPs
Optimizing loops is one of the most important code optimization techniques. Always EXIT a loop when no further processing is required and also make sure that any statement within the loop belongs in the loop. For instance, in the following PLSQL code the highlighted statements executed once for every iteration of the inner loop—even though the value of counter1 does not change. This means it must be executed 200 times more often than necessary.
FOR counter1 IN 1 .. 2000
LOOP
FOR counter2 IN 1 .. 2000
LOOP
modcounter1 := MOD (counter1, 10);
modcounter2 := MOD (counter2, 10);
sqrt1 := SQRT (counter1);
sqrt2 := SQRT (counter2);
IF modcounter1 = 0
THEN
IF modcounter2 = 0 THEN
sum1:=sum1+sqrt1+sqrt2;
END IF;
END IF;
END LOOP;
END LOOP;
Optimizing IF and CASE statements
Another PL/SQL code optimization technique is to examine the order of IF or CASE statements. Consider this PL/SQL fragment:
v_discount :=
CASE
WHEN p_quantity > 10
THEN 17
WHEN p_quantity = 9
THEN 16
WHEN p_quantity = 8
THEN 15
WHEN p_quantity = 7
THEN 14
WHEN p_quantity = 6
THEN 13
WHEN p_quantity = 5
THEN 12
WHEN p_quantity = 4
THEN 11
WHEN p_quantity = 3
THEN 10
WHEN p_quantity = 2
THEN 8
WHEN p_quantity = 1
THEN 5
END;
The code is correct, but we know that 90% of the time, customers only buy one item. That means that most of the time we evaluate 9 unnecessary WHEN clauses. Better to rewrite this statement as follows:
v_discount :=
CASE
WHEN p_quantity = 1
THEN 5
WHEN p_quantity = 2
THEN 8
WHEN p_quantity = 3
THEN 10
WHEN p_quantity = 4
THEN 11
WHEN p_quantity = 5
THEN 12
WHEN p_quantity = 6
THEN 13
WHEN p_quantity = 7
THEN 14
WHEN p_quantity = 8
THEN 15
WHEN p_quantity = 9
THEN 16
WHEN p_quantity > 10
THEN 17
END;
This simple change reduces the overhead of calculating the discount by about 10%.
Using bulk Collect
When retrieving rows from an SQL statement, one can either loop through the rows using a FOR statement, or bulk fetch them into a collection. The latter is much more efficient than the former if there are lots of rows to be retrieved. Figure 10 shows code that uses both techniques.
Figure 10 Using bulk collect
The performance advantages of bulk collect are remarkable. If there’s a large number of rows involved, the time taken to retrieve the rows can be reduced by 10 or even 100 times. For instance, in the above example, a response time reduction of over 98% was achieved (see Figure 11)
Figure 11 Peformance gains achieved by exploiting the bulk collect feature.
Other PL/SQL optimization opportunities
Here’s a few other things you can do to speed up your PL/SQL code:
- If you are passing a collection as a parameter to a subroutine, try the NOCOPY clause; this prevents PL/SQL from creating a new copy of the collection. Instead, the sub-routine operates directly on the original collection (it’s passed “by reference”)
- Recursion often seems like a clever way to solve various programming problems, but almost always has a negative performance impact. Use iteration rather than recursion whenever possible.
- 11g includes some features than can really accelerate PL/SQL performance. These include better native compilation (see the plsql_code_type parameter), the PL/SQL function cache and the unit inlining capability. These can be best exploited if you make some minor changes to your PL/SQL code – see the “what’s new in PL/SQL” section of the PL/SQL manual at technet.oracle.com.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|