Create Account - Sign In
Browse - New Book - My Books - Sell - Groups - $19 ISBNs - Upload / Convert - Help - follow us!   


You can expect to experience a degradation in the performance of a

pipelined function implementation when using wide rows or rows with

many columns (pertinent to the denormalized multirecord example de-

scribed above). For example, I tested a 50,000-row serial pipelined bulk

load against row-by-row inserts using multiple columns of 10 bytes

each. In Oracle9i Database, the row-based solution became faster than

the pipelined solution at just 50 columns. Fortunately, this increases to

somewhere between 100 and 150 columns in all major versions of Ora-

cle Database 10g and Oracle Database 11g.

A Final Word on Pipelined Functions In this discussion of pipelined functions, I've shown several scenarios where such func- tions (serial or parallel) can help you improve the performance of your data loads and extracts. As a tuning tool, some of these techniques should prove to be useful. However, I do not recommend that you convert your entire code base to pipelined functions! They are a specific tool that is likely to apply to only a subset of your data-processing tasks. If you need to implement complex transformations that are too unwieldy when repre- sented in SQL (typically as analytic functions, CASE expressions, subqueries, or even the frightening MODEL clause), then encapsulating them in pipelined functions, as I've shown in this section, may provide substantial performance benefits.

Specialized Optimization Techniques You should always proactively use FORALL and BULK COLLECT for all non-trivial multirow SQL operations (that is, those involving more than a few dozen rows). You should always look for opportunities to cache data. And for many data-processing tasks, you should strongly consider the use of pipelined functions. In other words, some techniques are so broadly effective that they should be used at every possible oppor- tunity. Other performance optimization techniques, however, really will only help you in rel- atively specialized circumstances. For example: the recommendation to use the PLS_INTEGER datatype instead of INTEGER is likely to do you little good unless you are running a program with a very large number of integer operations. And that's what I cover in this section: performance-related features of PL/SQL that can make a noticeable difference, but only in more specialized circumstances. Gener- ally, I suggest that you not worry too much about applying each and every one of these proactively. Instead, focus on building readable, maintainable code, and then if you identify bottlenecks in specific programs, see if any of these techniques might offer some relief.

866 | Chapter 21:Optimizing PL/SQL Performance

two page view?


Share "Oracle PL SQL Programming 5Ed [2009, O'Reilly]":

Download for all devices (0 B)