C H A P T E R  9

PL/SQL from SQL

by Adrian Billington

Functions are an integral part of any well-designed PL/SQL application. They are an embodiment of programming best practices, such as code modularization, reuse, and the encapsulation of business or application logic. When used as simple building-blocks for larger programs, they can be an elegant and simple way to extend functionality while reducing code-complexity at minimal cost.

Conversely, when PL/SQL functions are heavily used, particularly in SQL statements, there can be a range of associated costs, most notably for performance. Depending on the nature of the function, simply calling PL/SQL from SQL and/or excessive I/O can degrade performance of even the most trivial of queries.

Given this, I will describe a variety of costs associated with calling PL/SQL functions from SQL and, more importantly, demonstrate a range of techniques that you can use to reduce their impact. By the end of this chapter, you will have a greater awareness of the costs of using PL/SQL functions in your applications. You will therefore be better placed to make good design decisions for your new or re-factored applications and improve your performance tuning efforts on critical parts of your system.

The Cost of Using PL/SQL Functions in SQL

The SQL and PL/SQL languages are seamlessly integrated. Since Oracle Database 9i, the two languages have even shared the same parser. We use this inter-operability to our advantage in most of the code that we write, such as SQL cursors in PL/SQL programs or PL/SQL functions that are called from SQL. We often do this with little concern for what Oracle Database has to do under the covers to run our programs.

Despite this integration, there is a runtime cost to combining them, in particular when calling PL/SQL from SQL (the focus of this chapter, of course). I’m going to spend the next few pages exploring some of these costs. What is interesting is that Oracle has been working hard to reduce the impact of combining SQL and PL/SQL code over several versions, so the issues I describe are not necessarily in their final state.

My investigation of the costs of using PL/SQL functions in SQL can be broken down into three main areas:

• Performance
• Predictability (which is related to performance)
• Side effects

Context-Switching

Context-switching is an almost-irreducible cost of computing that occurs in CPUs, operating systems, and software. The mechanics differ depending on where it is occurring, but it is often a CPU-intensive operation that designers attempt to tune as much as is possible.

When we consider “context switching” in relation to Oracle Database, we specifically refer to the exchange of processing control between the SQL and PL/SQL engines (without necessarily understanding what occurs in such a process). These two engines are separate and distinct but we use them interchangeably. This means that when we call SQL from PL/SQL or vice versa, the calling context needs to store its process state and hand over control and data to its counterpart engine (which may or may not be picking up from an earlier switch). This switching cycle is computationally intensive and can typically be repeated so many times that its effects on response times can become quite noticeable.

For many, the first encounter with context-switching in Oracle Database would have been the introduction of the BULK COLLECT and FORALL PL/SQL features in Oracle 8i, designed specifically to reduce context-switching. These performance features made it possible to bind arrays of data between the SQL and PL/SQL engines in a single context-switch, dramatically reducing the number of context-switches that were generated by the row-by-row PL/SQL techniques that were prevalent at the time. For several major releases, almost all of the performance benefits of these language features could be attributed to the reduction in context-switching they facilitated. (In more recent releases, some further benefits such as redo optimization for bulk INSERT operations can also be realized when using PL/SQL array processing.)

All of which is good news for PL/SQL programmers, of course, but the effects of excessive context-switching are still encountered elsewhere. In particular, context-switching is a performance penalty to pay for calling PL/SQL functions from SQL.

Measuring Context-Switching

As good as Oracle Database’s instrumentation is (and it is truly excellent), context-switching is not something that can be directly measured; it’s performed way down in the depths of the kernel and there is no simple statistic or counter available to quantify it. But given that context-switching is a fact of calling PL/SQL from SQL (and vice versa, though I shall cease to remind readers of this), its impact can be inferred with a simple set of measurements and supplied utilities.

Listing 9-1 shows a simple comparison between two SQL statements, one of which calls a PL/SQL function for every row. The function does nothing but return the input parameter and the timings are compared using the SQL*Plus timer. I’ve used an array size of 500 and Autotrace to suppress the output.

The baseline SQL statement generated 1 million rows in less than 2 seconds. As you can see from the Autotrace output, a SQL statement of this form is light on resources other than CPU (and network transfer and memory at larger rowcounts). Adding a PL/SQL function call to the SQL makes quite a difference to the elapsed time, as the Listing 9-2 demonstrates.

I’ve omitted the Autotrace output as it is almost identical to that of the baseline SQL, but you can see that adding a call to the function has added almost 4 seconds to the runtime of this simplest of queries.

As stated earlier, it is not possible to directly measure context-switching as such, but you can use tools such as SQL trace, the PL/SQL Hierarchical Profiler (an Oracle Database 11g feature), or the V$SESS_TIME_MODEL view to measure the time spent in the PL/SQL engine overall. Listing 9-3 shows the summary results of a Hierarchical Profiler session for the previous SQL.

This profile clearly shows that the PL/SQL function was called 1 million times and this accounted for a little over 1 second of elapsed time. What is also interesting is the fact that the PL/SQL VM (the PL/SQL engine) spent 2 seconds of processing time outside of the function. Some of this time can be attributed to context-switching. Overall, the PL/SQL engine accounted for over 3 seconds of elapsed time. (Note that the DBMS_OUTPUT calls are a consequence of the serveroutput setting in SQL*Plus and are no more than background noise in this instance.)

These figures are validated by an extended SQL trace of the query, as the TKProf output in Listing 9-4 demonstrates.

I’ve removed the Rows (max) and Rows (avg) statistics to shrink the output, but you can see from the rowsource statistics that it took approximately 3.6 seconds to project the 1 million rows for the resultset (this includes a small amount of network transfer time that was shown in the associated waits summary), yet it took just 1.7 seconds to generate the rows in the first place.

These examples are as simple as possible to demonstrate the cost of calling of PL/SQL functions from SQL. Despite their trivial timings, you can clearly see that the PL/SQL function adds a significant overhead to the response time (over 300%). Of course, PL/SQL functions that have much longer elapsed times (especially those functions that include SQL) will cause you far greater performance issues than context-switching problem. For PL/SQL-only functions (i.e. no SQL in the function body), however, context-switching can be a major part of the PL/SQL VM’s elapsed time.

Context-switching can also have a performance impact on queries that have plenty of other work to do, such as accessing and joining sets of data. In Listing 9-5, I have a lightweight PL/SQL function and I’ve used this to format part of a sales report. As before, I’ve used Autotrace and SQL*Plus timing to record the runtime statistics.

Note that I’ve trimmed the Autotrace output to include just the I/O statistics. The report completed in approximately 7 seconds. I’ve compared this to a SQL-only implementation in Listing 9-6.

By removing the function in favor of a SQL expression, the runtime of the report reduced to less than 1.5 seconds. You can see, therefore, that even when accessing and joining multiple rowsources, the presence of a PL/SQL function can still impact the overall response time of the query.

Other PL/SQL Function Types

The context-switching penalty applies to any PL/SQL source that is invoked from SQL (including user-defined aggregate functions, type methods, or table functions). A good example is the STRAGG function that Tom Kyte wrote many years back to demonstrate user-defined aggregate functions (then a new feature of Oracle 9i). Until Oracle Database 11g, there was no built-in equivalent to STRAGG, so this utility function became extremely popular as a solution to string-aggregation problems on Oracle forums.

The release of the LISTAGG built-in function provides a significant performance boost over STRAGG by simply removing the need for the SQL engine to perform context-switching. I’ve demonstrated this in Listing 9-7 with a simple example of aggregating the range of IDs for each customer in the supplied SH.CUSTOMERS table.

I’ve suppressed the Autotrace output, but neither method incurs any physical I/O and the logical I/O is identical. Yet the user-defined aggregate function takes over six times longer than the built-in equivalent.

In summary, I’ve demonstrated that simply calling PL/SQL functions from SQL can be expensive, regardless of the nature of the function itself. Later, I’ll demonstrate various methods you can apply to reduce or remove this cost altogether from your SQL queries.

Executions

It is often said that the fastest way to do something is not to do it at all. It’s also commonly said that even when something is nanoseconds-fast, if you execute it enough times, its elapsed time will start to mount up. This is what commonly happens with PL/SQL functions that are executed from SQL. It follows that the more times a PL/SQL function is executed from a SQL statement, the greater the time and resources that are spent in both the function itself and the associated context-switching.

When a PL/SQL function is included in a SQL statement, how many times should that function be executed? There are a number of factors to be considered when attempting to answer this question; even assuming that you have some knowledge of the function’s usage in the query, you can’t be certain.

SQL is a non-procedural language, yet trying to predict the number of times that a PL/SQL function is executed by a SQL query is a decidedly procedural way of thinking. You have no way of controlling the innermost workings of the SQL engine and therefore no absolute control over how many times a PL/SQL function is called. (I say absolute because there are techniques you can use to reduce a function’s executions, but you can’t reliably predict the number of calls that you ultimately reduce it to.) If you don’t attempt to control the number of executions, then it follows that you won’t have any influence over the contribution that your function makes to the response time of your SQL statement.

Query Rewrite and the CBO

There are also the effects of the Cost Based Optimizer (CBO) to consider. With every release of Oracle Database, the CBO becomes more sophisticated (read complex); in particular, it finds more ways to optimize queries through transformation techniques. This means that the query block you call your PL/SQL function from might not exist in the final version of the query that the CBO generates.

This can easily be demonstrated with a small example of the CBO’s view-merging transformation. Listing 9-8 contains a mock report that aggregates product sales by customer and calendar year. The PL/SQL function in this example maintains a counter to trace the number of executions. It is called from an in-line view of the CUSTOMERS table in an attempt to pre-compute and also limit the number of function calls.

Having executed the report, I can retrieve the latest counter to see how many times the function was executed, as demonstrated in Listing 9-9. Note that you can refer to the source code on the Apress web site for implementation details of the COUNTER package and its use in the FORMAT_CUSTOMER_NAME function.

The PL/SQL function has been executed over 918,000 times, although my intention was that the in-line view would limit the function executions to just 55,500 (i.e. once per row in the CUSTOMERS table). This hasn’t worked, however, because the CBO has simply merged the inline view with the outer query block, and as a result, my PL/SQL function has been applied to a much larger rowsource than I intended. The execution plan in Listing 9-10 shows that the inline view has been factored out (i.e. merged) and the projection information clearly shows that the function calls have been applied at the very last step in the plan.

View-merging, subquery unnesting, and other complex query transformations are not the only CBO-related issues that can affect the number of executions of your PL/SQL function. The CBO is also free to ignore the order that your tables or predicates are listed (although with the latter there is a notable exception, specific to PL/SQL functions, and I’ll be describing it later). The ordering of joins and predicates can have a dramatic effect on the cardinality of each step of an execution plan; this, in turn, will probably cause your PL/SQL function to be executed more, or less perhaps, than you expect.

Internal Optimizations

In addition to the numerous CBO optimizations, Oracle Database also has a range of internal optimizations that are applied at runtime, and trying to second-guess the behavior of these can often be an exercise in futility. Not only will they probably change with database version, they can also be dependent on a wide range of factors, some of which you can derive and others that are hidden from you.

Oracle Database’s optimization for DETERMINISTIC functions is one such example. Because a deterministic function will, by definition, always return the same result for a particular input value, Oracle Database can avoid re-executing the PL/SQL function if it encounters the same inputs. The efficiency of this optimization is, however, dependant on other factors, such as data type, data length, cardinality (i.e. the number of distinct values), the database version, and even environmental settings such as array fetch size. For a good discussion of how these factors can result in a wildly varying reduction in function executions, see Dom Brooks’ “Determining Deterministic” series of articles on http://orastory.wordpress.com.

Similar behavior is seen when PL/SQL function calls are encapsulated in scalar-subqueries (a commonly-used technique to reduce the impact of functions in SQL and one that I demonstrate later in this chapter). While the scalar subquery cache appears to be a more effective optimization than that for deterministic functions, it is still dependant on some of the same factors (such as data type, length, and order) in addition to the size of the internal cache (which varies by database version). See Cost-Based Oracle Fundamentals by Jonathan Lewis (Apress, 2005) for a more in-depth description of the internal hash table that supports the scalar subquery cache. The fact that this hash table is quite small means that collisions (i.e. cache “misses”) are almost inevitable on all but the more trivial set of input values. Because you can’t predict when these collisions will occur, you should accept that you can usually derive some benefit by using the scalar subquery cache, but not of a precisely quantifiable amount.

Suboptimal Data Access

What happens when a PL/SQL function contains a SQL statement? An extremely common design pattern that can be found in most large database applications is that of SQL lookups wrapped in PL/SQL functions. In general, the principles behind such “getter” functions are sound (i.e. the encapsulation of common lookups, reusable data access methods, etc.), but the performance implications of such design decisions can be devastating, especially when the PL/SQL functions are called from SQL statements.

When a SQL statement is wrapped in a PL/SQL function and called from SQL queries, the cost of context-switching is automatically doubled—it now occurs both inside and outside the function. Far more critical, however, is the fact that by wrapping SQL lookups in functions, the optimization choices that the CBO would otherwise have had if the lookup were in the main body of the calling SQL statement are effectively disabled. The impact that this can have on your queries’ elapsed times can be severe (it’s fair to say that the cost of context-switching pales into insignificance in these scenarios).

Consider the example in Listing 9-11. I have a PL/SQL function that returns an exchange rate between two currencies for a given date. Note that I’ve kept this deliberately simple for the purposes of the example.

In the report of sales by product and calendar year shown in Listing 9-12, I’ve used this function to calculate revenue in GBP as well as USD. Note that I’ve used Autotrace to suppress the resultset and enabled the SQL*Plus timer.

The elapsed time of this query is roughly 66 seconds—a poor response time for an aggregate query over less than 1 million rows. The Autotrace output for this statement is provided in Listing 9-13.

As highlighted, that’s over 1.8 million consistent gets for the report (and over 918,000 recursive calls relating to the execution of the SQL statement embedded in the GET_RATE function). In Listing 9-14, I’ve converted this query into a “straight SQL” alternative by joining directly to the RATES table and calculating the currency conversion in an expression.

The performance difference is staggering! By removing the PL/SQL function and joining directly to the RATES table, the response time has dropped from 66 seconds to just 1 second.

The Autotrace statistics for the SQL-only report are shown in Listing 9-15.

The Autotrace output highlights a striking difference in the logical I/O required to satisfy the two SQL statements. The PL/SQL function incurs a penalty of over 1.8 million consistent gets. The TKProf report in Listing 9-16 clearly shows the cost of encapsulating the rates lookup in a function.

The TKProf report shows that the SQL inside the PL/SQL function is treated as a separate query; in this case it has accounted for all of the additional logical I/O that the sales query generated and almost half of the additional runtime (if you’re wondering where the remaining runtime was spent for this example, a Hierarchical Profiler report showed that almost 59 seconds were spent in the PL/SQL engine overall. Some 47 seconds of this PL/SQL time was accounted for by the rates-lookup SQL itself—double that reported by the SQL trace above—meaning that approximately 23 seconds was PL/SQL overhead). The base query itself has incurred very little logical I/O and is in fact comparable to the SQL-only implementation.

This separation highlights an important point. If the embedded SQL is treated as a query in its own right, the CBO cannot optimize it in the context of the outer query. The CBO is completely ignorant of what the function actually does, so the RATES table does not even feature in the CBO’s decision-making. The results, as you can see, can be catastrophic.

You can see, therefore, that it makes good sense (from a performance perspective) to convert PL/SQL lookup functions to SQL. If performance is critical and such lookup functions are heavily used in your application, you are encouraged to remove them wherever possible. There are alternative encapsulation methods that I’ll demonstrate later in this chapter that can help you with this.

However, I recognize that it’s not always possible to “sweep” a legacy application to convert its SQL (it might be embedded in many different application tiers and technologies, or the regression testing overhead might be too prohibitive). In such cases, there are methods you can use to reduce the costs of the PL/SQL functions and again I’ll show some of these later.

Optimizer Difficulties

Many of the examples so far have shown PL/SQL functions called from the SELECT block (projection) of SQL statements but it is equally common to see user-written functions being used in predicates. This can give rise to a range of issues. Firstly, as I demonstrated earlier, a PL/SQL function used in a predicate will be executed an unpredictable number of times. Secondly, a PL/SQL function (or indeed any function) applied to an indexed column will prevent the use of the index. Thirdly, the CBO knows very little about PL/SQL functions as it usually has no statistics with which to work. This can lead to suboptimal execution plans. I will concentrate on the third issue here, as I described the first issue earlier and the second is well understood by the Oracle DBA/developer community.

PL/SQL Functions and Statistics

Why does the CBO have difficulties with PL/SQL functions? The answer is statistics. You know that the CBO relies on good-quality statistics to enable it to make sensible optimization decisions. When it comes to PL/SQL functions, however, the CBO almost always has no statistics to work with and has to resort to default heuristics instead. For example, when encountering a predicate of the form WHERE plsql_function(expression-involving-some-column) = value, the CBO defaults the predicate’s selectivity to just 1% (in the absence of statistics for the function, it has no alternative).

In Listing 9-17, I demonstrate this heuristic using a small table of 1,000 rows, a PL/SQL function, and a simple query with CBO tracing enabled (10053 event).

In the absence of statistics for the PL/SQL function, the CBO has defaulted to a 1% selectivity to determine the cardinality of the predicate. You can see this reflected in the cardinalities (Rows) displayed in the execution plan above. An excerpt from the 10053 trace file in Listing 9-18 also demonstrates the CBO’s calculations.

I’ve highlighted some important information in this trace file. It states that there are no default statistics or statistics types defined for either cost or selectivity of the PL/SQL function. This shows you, therefore, that it is possible to provide statistics on PL/SQL functions; I’ll demonstrate how to do this later.

Predicate Ordering

When statistics for PL/SQL functions are missing, other potential issues can arise as a result of the order in which the predicates are coded. For example, if you write a WHERE clause that contains two PL/SQL function predicates, in the absence of statistics the functions will be executed simply in the order in which they appear in the SQL.

I’ve demonstrated this in Listing 9-19, where I have a SQL statement that filters on two PL/SQL functions, SLOW_FUNCTION and QUICK_FUNCTION. As their names suggest, one function is more costly than the other, so I’ve executed the SQL statement twice with the predicates in different orders, using Autotrace and a SQL*Plus timer to compare the results.

I’ve removed the Autotrace statistics but kept the Explain Plan sections to show the order in which the PL/SQL functions were executed for each SQL statement. You can see that this matches the order in which they were coded and that this made quite a difference to the relative performance of the two statements. This is the point. The CBO has no idea that SLOW_FUNCTION has a lower selectivity or that it takes longer to execute than QUICK_FUNCTION. In the absence of supplied statistics, how can it? It makes no sense for the CBO to re-order the predicates, because it has no basis for doing so.

The good news is that there are several methods for generating or setting statistics for PL/SQL functions to mitigate this issue. These are described in the section entitled “Assisting the CBO.”

The Read-Consistency Trap

Listing 9-13 clearly demonstrates the resource-cost of putting SQL lookups into PL/SQL functions (and these costs are easy to quantify). However, there is another important pitfall of using such a design, although it is much harder to quantify the potential impact that this might have on your application(s). That is, the SQL inside the PL/SQL function is not read-consistent with the main SQL statement that is calling the function.

The TKProf report from Listing 9-12 (shown in Listing 9-16) demonstrated that the SQL inside the PL/SQL function was treated as a separate statement. This means that under the READ COMMITTED isolation level, it has its own read-consistency and not that of the SQL statement that calls the function.

In Listings 9-20 through 9-23, I’ve demonstrated this read-consistency trap. I have two sessions; the first of which (Session One) executes a long-running query that includes a call to a PL/SQL lookup function. The second session (Session Two) updates one of the lookup records at a random point in time, but importantly, this is while the first session’s query is still running.

I’ll begin with the PL/SQL lookup function in Listing 9-20.

You can see that the lookup function fetches a customer name by primary key. In addition, I have created a small SLEEPER function to enable me to slow down Session One’s query and simulate a long-running SQL statement (this function simply calls DBMS_LOCK.SLEEP and returns a constant 0).

Continuing with the example, Session One issues the following query shown in Listing 9-21.

Note the time at which Session One issues this SQL. The 10-row resultset and the 1 second sleep for each row means that this query should complete in just over 10 seconds. While this is executing, Session Two updates one of the customer names that Session One is repeatedly querying, as shown in Listing 9-22.

Again, note the time that this update is committed (approximately 3 seconds into Session One’s long-running query). Listing 9-23 shows the effect of this on Session One’s report.

Approximately halfway through Session One’s resultset, the customer name for ID=2 changed, because the lookup function operates outside of the main query’s read-consistent image of data. The report itself is therefore inconsistent.

What does this mean if you use such PL/SQL functions? Clearly you are at risk of inconsistent resultsets every time you include the function in a query, particularly if your users can update the lookup table at any time. You would need to assess this risk in the context of your own application patterns, of course. Users who received an inconsistent report would naturally question what they were seeing, and even worse, question your application.

Unfortunately, the options for protecting yourself against this are less than satisfactory. Short of removing all PL/SQL lookup functions, you have a range of options, such as

• Using the SERIALIZABLE isolation level.
• Using SET TRANSACTION READ ONLY.
• Making your PL/SQL function use flashback query with a given SCN to match the start of the main query.
• Making your readers block writers with FOR UPDATE queries (which even then would only work if the lookup table was accessed directly elsewhere in your main query. At which point you’d need to question the need for your PL/SQL lookup function in the first place!)

Any of these options would solve the issue but would more likely cause you greater headaches by seriously limiting your application’s concurrency and performance.

Other Issues

I’ve described in some detail the major issues you need to be aware of when using PL/SQL functions in SQL. Needless to say, there are others and perhaps you’ve encountered a particular corner-case that I haven’t thought of. I’ll complete this section with a very brief mention of other issues that you might encounter at some stage.

Parallel Query

PL/SQL functions can be enabled to work with Oracle Database’s parallel query option (PQ). While many PL/SQL functions can be executed by PQ slaves without issue, it is sometimes possible to disable PQ by referencing a PL/SQL function that is not explicitly parallel-enabled. If you are having trouble parallelizing a SQL statement and it contains a PL/SQL function, check to see if the function is parallel-enabled. Assuming your PQ hints, session PQ settings, and PQ system parameters are valid for parallel query yet it’s simply not “taking,” re-create your PL/SQL function to include the PARALLEL_ENABLE clause and try again. Note that for a PL/SQL function to be parallel-enabled, it should not reference session state variables that a PQ-slave might not be able to access (such as package state).

NO_DATA_FOUND

I described this earlier in the commentary for Listing 9-12 but it’s worth reiterating for completeness. A NO_DATA_FOUND exception raised in a PL/SQL function called from a SQL statement will not propagate the exception! Instead, the function will return NULL.

Reducing the Cost of PL/SQL Functions

I’ve just spent several pages describing many of the difficulties, costs, and pitfalls that you might encounter when using PL/SQL functions. Yet this is a book for PL/SQL professionals, so I’m going to spend the remainder of this chapter describing a range of techniques to reduce the impact of using PL/SQL functions.

A Sense of Perspective

Before I continue, I think it is important to give a sense of perspective. Nothing in the world of Oracle development and performance is absolute and, fortunately, there are times when a basic implementation of a PL/SQL function will be more than adequate. Listings 9-24 and 9-25 demonstrate such an example. I’m going to create an isNumber checker to validate in-bound raw data and compare a PL/SQL function implementation with a SQL-only version. To do this, I’ve created an external table of seven fields (all as VARCHAR2(30)), dumped the data in the SALES table to a flat-file and modified 1 in every 10,000 AMOUNT_SOLD values to include non-numeric characters (AMOUNT_SOLD maps to FIELD_07 in the external table). Finally, I’ve created the IS_NUMBER function shown in Listing 9-24 to validate that strings are in fact of numeric format.

In Listing 9-25, I’ve used two alternative approaches to validate that the data in FIELD_07 is of numeric format. First, I’ve used the IS_NUMBER PL/SQL function (which returns 1 if the input value is a number). Second, I’ve used a SQL built-in regular expression, taken from an example in an OTN forum thread. I’ve used Autotrace to suppress the output and the SQL*Plus timer to record the timings.

You can see that the PL/SQL function implementation is much quicker (almost three times) than the regular expression. It is better still when reversing the two methods to find the small number of non-numeric records. Regular expressions are very CPU-intensive; compare the CPU times for the two SQL statements in V$SQL or trace the two queries with DBMS_UTILITY.GET_CPU_TIME to see the difference for yourself.

This example shows that the validity of using PL/SQL functions in SQL statements cannot be dismissed outright. Your requirements, your knowledge of the systems you are developing, and your testing will help you to determine whether a PL/SQL function is viable or not. And if it is viable, the techniques I will describe in the remainder of this chapter should help you to get a lot more from your implementations.

Using SQL Alternatives

It is, however, quite rare that a PL/SQL function will be quicker than a SQL-only expression or built-in function. You should consider whether it is possible or desirable to change some of your application’s critical queries (that use PL/SQL functions) to use SQL-only alternatives. If you are developing new applications, you have a good opportunity to build-in performance from the outset.

Some considerations will be required here, of course, and if any of the following are true, you might decide to favour some PL/SQL functions over a SQL implementation:

• A PL/SQL function is not heavily used.
• A PL/SQL function is too complicated to be easily expressed in SQL.
• A SQL statement is not displaying any of the issues I’ve described.
• You have simply too much SQL to change or regression-testing would be too prohibitive.

I’ll demonstrate the performance benefits of using SQL and offer some alternative techniques that you can use to refactor those critical SQL statements (or even consider as a standard for new applications).

Use SQL

In Listing 9-12, I ran a sales report that generated over 918,000 executions of a GET_RATE PL/SQL function. I demonstrated that a SQL-only implementation of the same report (i.e. a simple join to the RATES table) reduced the runtime of that query from 66 seconds to just 1 second (there was also a massive reduction in logical I/O). The SQL-only technique can also make a difference to the elapsed time of queries that use quicker PL/SQL functions, such as those that don’t contain any SQL.

SQL is an extremely flexible and rich language for processing sets of data quickly. Assuming that performance is your goal, you can use SQL such as analytic functions, subquery factoring, and a wide range of applications of the built-in functions to refactor your PL/SQL functions to SQL (visit the OTN SQL and PL/SQL forum or http://oraqa.com/ for examples of innovative uses for SQL).

Use a View

An argument in favour of retaining PL/SQL functions is that they encapsulate and centralize application logic, preventing a proliferation of the same rules throughout the code base. There are alternative encapsulation methods, such as using views, which have the added benefits of also providing the performance of SQL. In Listing 9-26, I’ve converted the sales report by calendar year and product from using the GET_RATE PL/SQL function to using a view, where the logic is encapsulated in a SQL expression.

With this view, I’ve converted the GET_RATE PL/SQL function to a scalar subquery lookup. The entire rates conversion is now projected as a single column. I’ve used a scalar subquery instead of a simple join to RATES to take advantage of an optimization with views known as column-elimination. With this optimization, the scalar subquery will only be executed when the AMOUNT_SOLD_GBP column is referenced in a query. This means that queries that don’t need the converted rate can also take advantage of this view without penalty, widening the scope for re-use. The timing and statistics for the sales report when using this view are shown in Listing 9-27.

I’ve highlighted the critical parts of the output. There is slightly more logical I/O than with a simple join to RATES, but this is minor compared with the I/O generated by the PL/SQL function. The sales report is still much quicker than the version with the PL/SQL function and the business logic is encapsulated.

Listing 9-28 demonstrates column-elimination. I’ve excluded the AMOUNT_SOLD_GBP column from the report and captured the statistics again.

This time, the query completes in half the time and the logical I/O decreases again. This is because the RATES table lookup has been eliminated from the view projection at runtime. The execution plan and projection in Listing 9-29 clearly demonstrate this.

This highlights the fact that views can be a higher-performing alternative to PL/SQL functions, without compromising the need for encapsulation and centralization. As a final consideration, you could rename the base table and allow the view to take on its name. With these actions, you will have “slotted in” the business logic columns into the SALES “table” and your DML statements will not be compromised, as Listing 9-30 shows.

To use this approach, consideration needs to be given to any existing code base, such as %ROWTYPE declarations, PL/SQL record-based DML, unqualified INSERT or SELECT lists (i.e. those without explicit column references), and so on. Fortunately, derived columns in views cannot be modified, as the final example for this section demonstrates in Listing 9-31.

Use a Virtual Column

To wrap up this section on using SQL expressions in place of PL/SQL functions, I’m going to demonstrate how to replace a PL/SQL function with a virtual column.

A feature of Oracle Database 11g, virtual columns are expressions stored as column metadata in tables (they don’t use any storage as such). They are logically similar to columns in views but are far more flexible as they can have statistics gathered for them or be indexed. Virtual columns are an excellent tool for encapsulating simple business rules and, because they are stored as metadata against tables, they are also self-documenting declarations of your data logic.

To demonstrate virtual columns as an alternative to PL/SQL functions, I’ve converted the sales report from Listing 9-6. To recap, this used the FORMAT_CUSTOMER_NAME function to format a customer name and took almost 7 seconds to execute (compared with just 1 second as a SQL-only query). To begin the conversion, I’ve added a virtual column to the CUSTOMERS table, as shown in Listing 9-32.

The GENERATED ALWAYS AS (expression) syntax is specific to virtual columns (I’ve omitted an optional VIRTUAL keyword to keep the display on a single line). The CUSTOMERS table now looks as follows in Listing 9-33.

Using the virtual column in a SQL statement is no different from any other table, view, or other column. The converted sales report is shown in Listing 9-34.

As you can see from the elapsed time of this query, by moving the customer name formatting to a virtual column, I’ve eliminated the overhead of the PL/SQL function while retaining the encapsulation of the business logic with the very data to which it applies.

Reducing Executions

Earlier in this chapter, I described the fact that you can’t reliably predict the number calls to a PL/SQL function that a SQL statement will make. Further, each execution carries a context-switching penalty in addition to the work the PL/SQL function has to do. There are, however, techniques you can use to reliably reduce the function calls and I will demonstrate some of these next.

Pre/Post-Computing with SQL Hints

I gave an example of the CBO’s cost-based query transformation in Listings 9-8 to 9-10, where my attempt to pre-compute formatted customer names in an inline view was negated by view merging. The result was 918,000 PL/SQL function executions (i.e. once per row in the larger SALES table) instead of an intended 55,500 calls (i.e. once per row in the CUSTOMERS table). For some queries, such an order of magnitude or more function executions can be disastrous.

I’ve repeated the customer sales report in Listing 9-35. This time, however, I’ve used a couple of hints to ensure that the pre-computation of the customer names in an inline view “sticks” and the CBO doesn’t merge the query blocks.

You can see that this time, I’ve used hints to instruct the CBO to leave my inline view unmerged. The aim is to ensure that my PL/SQL function only executes against the CUSTOMERS dataset and not the larger SALES set. I’ve done this by using the NO_MERGE hint, adopting the query block naming syntax introduced in Oracle Database 10g. The QB_NAME hint is used to label the inline view query block; I’m therefore able to reference the inline view from the main query block, as you can see in the NO_MERGE hint.

I can go one step further with this query, however. In this particular example, the FORMAT_CUSTOMER_NAME function only needs to be applied to the final resultset of just11,604 rows (far fewer than the 55,500 customer records). I can therefore pre-group the entire resultset using the same hints as previously, but only call the PL/SQL function at the very last stage. Listing 9-36 shows the impact of this.

This is better still, because I have managed to pre-aggregate all the data prior to calling the PL/SQL function, reducing its number of calls to 11,604 and saving more time on the report.

This technique can be applied in targeted cases where PL/SQL function calls absolutely must be kept to a minimum. For example, by adopting this technique for the earlier sales report with the GET_RATE function, I managed to reduce 918,000 function executions to just 36,000, which decreased the overall response time from 66 seconds to just 4 seconds.

A more strategic method for reducing function calls, however, is to exploit a caching solution, which I’ll now describe.

Caching to Reduce Function Calls

There are several options for caching data in Oracle Database, depending on your version. I’m going to briefly describe two caching techniques for reducing function calls that you might wish to investigate and/or apply to your application. These are:

• Scalar-subquery caching
• Cross-session PL/SQL function result cache

Scalar-Subquery Caching

This caching feature is an internal optimization designed to reduce the number of executions of SQL statements or PL/SQL functions embedded in scalar subqueries. Although the efficiency of this internal cache can’t be reliably predicted, it can be used to reduce PL/SQL function calls to some extent. In Listing 9-37, I’ve converted the sales report with the GET_RATES lookup function to exploit the scalar subquery cache.

By encapsulating the call to GET_RATE in a scalar subquery, I’ve managed to reduce the query’s elapsed time from 66 seconds to just under 3 seconds. The counter shows that the PL/SQL function calls have dropped to just 19,450 (from over 918,000), resulting in an enormous saving in time and logical I/O (over 1.8 million consistent gets were prevented by the caching).

As noted earlier, the cache’s efficiency is dependent on a range of factors, including the order of the input data. With this in mind, I know that the range of input-values to the GET_RATE function in my SALES data is quite small, so in Listing 9-38, I have attempted to reduce the function calls further still by ordering the inputs into the scalar subquery.

By ordering the inputs to the scalar subquery cache, I’ve reduced the PL/SQL function calls further to just 1,460 (and therefore reduced the logical I/O by an order of magnitude and trimmed the overall elapsed time by a small margin). This demonstrates that ordering (or clustering) of calls into the subquery cache can make a difference to its efficiency. In my case, the savings I made through the further reduction in PL/SQL function calls “paid for” the cost of pre-sorting the SALES data.

Cross-Session PL/SQL Function Result Cache

The cross-session PL/SQL function Result Cache was introduced in Oracle Database 11g. The principle behind result caching is simple. First, a PL/SQL function is flagged for caching (using the RESULT_CACHE directive). Thereafter, each time the function is invoked with new parameters, Oracle Database executes the function, adds the return value(s) to the Result Cache, and returns the result(s) to the calling context. If a call is repeated, Oracle Database retrieves the results from the cache rather than re-execute the function. Under certain circumstances, this caching behavior can result in significant performance gains. It also has the benefit of requiring very little re-factoring (just a small function change, in fact).

I’ll demonstrate the effect of the Result Cache on the GET_RATE function. First, I need to re-compile the function with the cache directive, as shown in Listing 9-39.

I’ve added two pieces of syntax specific to the Result Cache, as highlighted above. The RESULT_CACHE keyword is self-explanatory and tells Oracle Database to enable this function for the Result Cache. The RELIES_ON (rates) syntax is required only in Oracle Database 11g Release 1 and declares the fact that the function is dependent on the data in the RATES table (Oracle Database 11g Release 2 automatically recognizes such dependencies). Therefore, any transaction against RATES will cause Oracle Database to invalidate the cached results and suspend caching this function until the transaction is complete (at which point the cache will commence a new refresh cycle).

Now that I have cache-enabled this function, I can re-execute my original sales query, as demonstrated in Listing 9-40.

By caching the function results, the elapsed time of this query has dropped by 57 seconds. This isn’t quite as quick as the SQL-only implementation (that completed in 1 second) or the scalar subquery version (that completed in 2 seconds), but you might consider this to be an acceptable performance gain, given that no SQL needed to be re-written to achieve it and its behavior is predictable.

Another issue with the original query was the I/O generated by the encapsulated RATES lookup. With the Result Cache, the logical I/O has reduced to a more acceptable level.

To quantify the reduction in function executions, I traced the query with the PL/SQL Hierarchical Profiler. The profile report is provided in Listing 9-41.

You can see that the cache-enabled GET_RATES function was executed just 1,460 times (the same result as the best scalar subquery cache example). Interestingly, context-switching into the PL/SQL VM is not reduced by using the Result Cache, so Oracle Database therefore passed control to the PL/SQL engine once for each of the 918,000 rows the query needed to process. For this reason, it rarely pays to convert a PL/SQL-only function (i.e. one without any embedded SQL) to use the Result Cache. First, there is no reduction in context-switching and second, you’ll possibly find that diverting a function call to the Result Cache and acquiring the RC latch that protects it will take longer than an execution of the function itself.

To wrap up, Oracle Database also provides several performance views for the Result Cache. For example, the V$RESULT_CACHE_STATISTICS view can provide a useful overview of the cache’s efficiency. Listing 9-40 ran with an empty cache, so the statistics in Listing 9-42 are entirely attributed to this query.

These statistics demonstrate a very efficient cache. The GET_RATE function was executed just 1,460 times to load the cache (as demonstrated by the Create Count Success statistic, which also corresponds to the GET_RATE statistics in the Hierarchical Profiler report in Listing 9-41). Once cached, the results were heavily reused (the Find Count statistic shows that the cached results were used over 917,000 times). If I were to repeat the sales query at this point, the PL/SQL function would not be executed at all (assuming the cached results were not invalidated) and the response time would be further reduced, albeit by a small margin.

Deterministic Functions

As stated earlier, Oracle Database has an internal optimization for deterministic functions which can sometimes make them a useful feature for reducing PL/SQL function calls. In Listing 9-43, I’ve demonstrated the effect of declaring the GET_CUSTOMER_NAME function to be deterministic (i.e. by using the DETERMINISTIC keyword in the function specification) and running a variation on the sales-by-customer report from Listing 9-5.

You can see from the results that the number of PL/SQL function calls has reduced by a very small margin (approximately 6,000 fewer than the original query in Listing 9-5) so it hasn’t been particularly effective (this report is actually slower than the original). In fact, to benefit from the deterministic function optimization at all with this example, I needed to pre-order the input data to ensure that customer names passing into the function were clustered together. In this case, the cost of sorting the data outweighed the marginal gains from 6,000 fewer calls to FORMAT_CUSTOMER_NAME and my report ran slower as a result.

That is not to say that the optimization for deterministic functions doesn’t work. As noted earlier, there are a range of factors that affect its efficiency. It can be quite effective in some cases, particularly when your resultset contains very few distinct function inputs. For example, there are approximately 3,700 distinct customers in the SALES table, so I simplified my sales query to join SALES to CUSTOMERS, order the data in an in-line view and call the FORMAT_CUSTOMER_NAME function with no aggregation. With this simplified query, the PL/SQL function was executed just 36,000 times for 918,000 rows. This is better than the standard sales report, of course, albeit quite a restricted optimization when compared with the alternative techniques I’ve demonstrated.

Assisting the CBO

As described earlier, there are several ways in which PL/SQL functions can trip up the Cost-Based Optimizer, largely due to the lack of statistics and the reliance on defaults. There are a range of methods you can use to improve the CBO’s handling SQL with PL/SQL functions and I’ll demonstrate the following techniques:

• Function-based indexes
• Extended statistics
• Default statistics
• The Extensible Optimizer

Function-Based Indexes

Functions applied to an indexed column completely disable the index for use in an execution plan. To counter this, function-based indexes have been available since Oracle 8i and allow such expressions to be indexed. In the following listings, I’ve demonstrated the use of a function-based index on a query that uses a PL/SQL function predicate. Listing 9-44 begins with a simple PL/SQL function applied to a highly–selective indexed column.

You can see that despite having an index on the PROMO_CATEGORY column, applying the PL/SQL function to the column forces the CBO to choose a full table scan of PROMOTIONS (it has no other choice). Fortunately, I can use a function-based index to work around this (only because PROMO_FUNCTION is deterministic). In Listing 9-45, I’ve created the function-based index and included the new execution plan.

This time the CBO has chosen to use the function-based index. Not only does the index exist, but the CBO also has some statistics to work with in choosing an optimal plan.

If you wish to investigate function-based indexes, you will find metadata regarding their implementation in views such as USER_INDEXES, USER_IND_EXPRESSIONS, and USER_TAB_COLS/USER_IND_COLUMNS (the indexed expression is stored as a hidden virtual column in the table being indexed).

Extended Statistics

Statistics are one of the benefits of function-based indexes. These can be invaluable to the CBO (even if it chooses not to use the index, it can make use of the statistics in deciding join or predicate orders). However, indexing a PL/SQL function predicate might not always be possible. In such cases, Oracle Database 11g provides an alternative known as extended statistics.

Extended statistics can be created on expressions without requiring a supporting index. Such statistics are known as extensions and they have an added benefit over function-based indexes of being applicable to all PL/SQL function expressions in a query (i.e. not just those in predicates).

To demonstrate this, I have re-executed the sales report by customer with extended statistics in place. Listing 9-46 is a reminder of the query without extended statistics, together with its execution plan and function call output from Listing 9-5.

As you can see, this performs over 918,000 function calls and completes in roughly 7 seconds. In Listing 9-47, I’ve used DBMS_STATS to generate extended statistics on the PL/SQL function call and show the execution plan from the re-executed query.

With the benefit of the extended statistics for the PL/SQL function, the CBO has made an informed decision to rewrite the query and pre-group the SALES and TIMES data. This is represented by Step 3 of the execution plan. Because this pre-groups and therefore reduces the resultset, the PL/SQL function calls are reduced to below 238,000. Overall, the effect on the response time is dramatic.

Extended statistics are a good alternative to the NO_MERGE hint demonstrated earlier. Without extended statistics, the query shown in Listing 9-48 was merged into the main query block by the CBO, resulting in 918,000 function executions. Fortunately, generating the extended statistics works nicely to prevent this transformation, again with good results.

Interestingly, the CBO has generated the same execution plan for this re-arranged query, although the function executions have dropped dramatically from 234,000 to just 18,000. This is another excellent example of the function executions being unpredictable—I have the same execution plan yet more than an order of magnitude difference in function calls. Nevertheless, these listings clearly highlight how important statistics on PL/SQL functions are for the CBO and that extended statistics are an excellent mechanism for supplying them.

Default Statistics

Unlike extended statistics, default statistics are user-generated and only apply to PL/SQL functions in predicates. Furthermore, the CBO will not use default statistics to inform cost-based query transformations, but it will use them to determine predicate ordering in the event that you have more than one function call in your WHERE clause.

Default statistics are supplied using the ASSOCIATE STATISTICS SQL command and with these you can define statistics on selectivity, CPU, and I/O costs for your PL/SQL functions (thereby improving on the defaults that the CBO otherwise adopts).

In Listing 9-19, I highlighted the impact of default predicate ordering when I referenced two functions in a query (these were appropriately named QUICK_FUNCTION and SLOW_FUNCTION). Listing 9-49 demonstrates how applying default statistics can ensure that the CBO applies the function predicates in the most efficient order.

Because of the selectivity statistics associated with SLOW_FUNCTION and QUICK_FUNCTION, you can see that the CBO has chosen to re-order the predicates to good effect. I have told Oracle Database that an equality predicate against QUICK_FUNCTION will only be true 1 in 1,000 times, whereas the SLOW_FUNCTION predicate will be true 1 in 2 times. It clearly made sense to apply QUICK_FUNCTION first to reduce the rowsource as soon as possible. An excerpt from the 10053 trace file in Listing 9-50 shows the workings of the CBO for this query.

The ASSOCIATE STATISTICS command can also be used to supply cost information for PL/SQL functions and this has a similar impact to the selectivity statistics above. Listing 9-51 shows how to set default cost statistics for the GET_RATE PL/SQL function:

I calculated these cost statistics as follows (in parameter order):

• CPU cost (403416): Calculated by the DBMS_ODCI.ESTIMATE_CPU_UNITS(<ms>) function, where <ms> is the number of milliseconds it takes to execute the GET_RATE function once (as reported by the Hierarchical Profiler).
• I/O cost (2): The sum of logical and physical I/Os of one execution of GET_RATE (reported by Autotrace).
• Network cost (0): This is not yet implemented so it can be left at 0.

It is possible to set both selectivity and costs in a single ASSOCIATE STATISTICS command and it makes sense to supply both when using this strategy.

Extensible Optimizer

I’ll finish this walkthrough of supplying statistics to the CBO with a brief overview of the Extensible Optimizer. This feature (part of the Oracle Data Cartridge toolset), takes default statistics one stage further by associating selectivity and cost by means of an object type, rather than by hardcoded defaults such as those previously mentioned.

It is possible to spend many pages describing the Extensible Optimizer so I’ll just provide a flavor of the technology to show you how to build a method for estimating statistics for your PL/SQL functions that can adapt to your data patterns.

Consider Listing 9-52. I have a simple function that returns a product code in uppercase. Without statistics, the CBO will assume that any predicate using this function will have a 1% selectivity as follows.

The CBO has estimated a cardinality of just 1 row for this predicate, but I know for a fact that there are more rows for the Software/Other category as the data profile in Listing 9-53 shows.

You can see a range of selectivity in this data, so a default statistics solution wouldn’t be flexible enough to cover all categories. Therefore, you can use the Extensible Optimizer to build a dynamic statistics parser for this function predicate. As stated earlier, this is implemented using an object type, the specification for which is provided in Listing 9-54.

The Extensible Optimizer uses well-defined interface methods and Listing 9-54 uses the three that are needed to generate selectivity and cost statistics. You must use the exact method names that Oracle prescribes, as I have done.

The parameter data types and order are also prescribed by the Extensible Optimizer, although you can use parameter names of your own choosing, with one notable exception. You must include the same parameter names in your type methods that you have in the PL/SQL function(s) that will eventually be associated with the statistics type. In my case, the FORMAT_PROD_CATEGORY function that I am building this statistics type for has a single parameter named p_prod_category (so I’ve included this in the relevant methods).

The type body implements the statistics type and can include any logic you like that enables the CBO to determine the selectivity and cost of your associated PL/SQL function(s). The type body for the PROD_STATS_OT.ODCIStatsSelectivity method is provided in Listing 9-55 (the remaining methods are available from the Apress web site).

As its name suggests, the ODCIStatsSelectivity method is used to calculate the selectivity for the associated PL/SQL function predicate for a given set of values. How it does this is the interesting part. Imagine that I have a predicate of the form WHERE format_prod_category(p.prod_category) = 'SOFTWARE/OTHER'. When the CBO optimizes this predicate, it invokes the ODCIStatsSelectivity method and passes the value ‘SOFTWARE/OTHER’ on to the statistics method in the p_start and p_stop parameters. (If you have a range predicate, p_start and p_stop will contain the lower and upper bounds, respectively.) This means, therefore, that I can specifically count the number of occurrences of ‘SOFTWARE/OTHER’ in the PRODUCTS table to determine the selectivity, as above.

Once the statistics type is created, it can be associated with the PL/SQL function. Listing 9-56 demonstrates the syntax for associating the statistics type and a couple of small queries to show its dynamic nature.

You can see that by using the Extensible Optimizer, I’ve provided accurate and dynamic statistics on my PL/SQL function to the CBO (in this example, these match the data profile of the PRODUCTS table perfectly). Note that the ODCI* methods are called once during the SQL optimization phase (i.e. the “hard parse”) and bind variables are also supported (as long as bind-variable peeking is enabled). However, you should give some consideration to the time it takes to execute your statistics methods and ensure that this is more than offset by the performance improvements they provide or by the fact that you reuse the shared cursor many times over.

Tuning PL/SQL

I’ve described many techniques for improving the performance of queries that use PL/SQL functions. Unless you are going to eliminate or dramatically reduce your PL/SQL function calls, however, you should also consider tuning the functions themselves.

You have a range of tuning options at your disposal (especially with later versions of Oracle Database), such as native compilation, subprogram-inlining, new integer data types, array fetching, associative array caching, and so on. For your critical queries and/or heavily-executed PL/SQL functions, you should find that you can reduce their elapsed time by using some of the wide range of PL/SQL tuning techniques available to you.

That said, I’m only going to demonstrate a tuning technique involving array-caching here, because it’s closely related to the Result Cache option I demonstrated earlier and can have dramatic results.

User-Defined PL/SQL Session Cache

I mentioned earlier that caching lookup data in associative arrays is an alternative to the Cross-Session PL/SQL Function Result Cache when the built-in feature is not available. There are several ways to do this and in Listing 9-57, I have provided one such method. I’ve created a PL/SQL array cache for the rates data (using a private global associative array) and provided a single GET_RATES function to load and access the cached data.

I’ve omitted the package specification (it only has the GET_RATES function signature) and listed just the package body. To keep the example short, I’ve excluded the exception-handling that would be needed to cater for NO_DATA_FOUND, among others. Some other points to note about the implementation are as follows:

Lines 7-9: I’ve created a private global associative array type and variable to store the cached data.

Lines 19-20: The index of the array is a string representation of the primary key for the RATES table. I’ve assigned this key directly in the function’s declaration to save space, but for best practice, any assignments should be performed in the executable block of the PL/SQL program.

Lines 24-27: I test to see if the rate is already cached. If it is, I simply return it directly from the cache.

Lines 32-37: If the rate is not already in the cache, I fetch it from the RATES table and add it to the cache. I’ve decided to cache only what is needed to reduce the potential PGA memory footprint (as an alternative, you might prefer to pre-load small lookup tables into associative arrays on instantiation).

In Listing 9-58, I’ve repeated the sales report that I used in Listing 9-40 (when demonstrating the Result Cache) but replaced the call to the result-cached GET_RATES function with my new user-defined cache equivalent.

Like the Result Cache example, the user-defined cache has dramatically reduced the logical I/O and runtime of the query to acceptable levels. In fact, the statistics for the user-defined session cache are similar to those of the Result Cache (the cache misses are identical), although the user-cache is slightly slower overall. Listing 9-59 shows the Hierarchical Profiler report for the query and demonstrates where the additional time (over the Result Cache example) was spent.

You can see from the calls to the SQL inside the rates function that there were 1,460 cache misses, which is identical to the earlier scalar subquery cache and Result Cache examples. This accounts for a dramatic reduction in the number of lookups against the RATES table. However, remember that the Result Cache also eliminated the calls to the GET_RATES PL/SQL function itself. In this respect, the user-defined cache is less optimal as calls to the RATES_PKG.GET_RATES function are not reduced at all; in fact, the function calls account for all of the additional runtime.

By using your own PL/SQL array cache, as I have done, you are essentially tuning the function rather than reducing or eliminating its use. Nonetheless, the user-defined PL/SQL session cache is a useful tuning technique for reducing the cost of SQL embedded in PL/SQL functions.

Summary

To wrap up this chapter, I have demonstrated a range of costs that you should consider when designing PL/SQL functions into your applications and queries. In addition, I’ve demonstrated a wide range of techniques that are at your disposal to eliminate, reduce, or optimize the use of PL/SQL functions in your SQL statements.