|
|
 |
Blogs
Toad and Database Commentaries |
Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies. Have some views of your own to share? Post your comments! Note: Comments are restricted to registered Toad World users.
 Do you have a topic that you'd like discussed? We'd love to hear from you. Send us your idea for a blog topic.
|
|
|
|
|
|
Location: Blogs
Steven Feuerstein's Blog
|
|
| StevenFeuersteinTW |
Wednesday, July 22, 2009 7:50 AM |
I spend way too much of my life on airplanes zipping around to different places, doing presentations and trainings on PL/SQL. In the process, I meet many PL/SQL developers – and I continue to be surprised and disappointed at how many developers do not know about or at least do not seem to take advantage of very powerful and useful features of PL/SQL. So I have to decided to list below all those elements of PL/SQL that I consider to be "must know." In other words, if you are not familiar with the following features, and not utilizing them, then your PL/SQL programs likely leave lots to be desired from the standpoints of efficiency and maintainability.
In the table below, you will find on the left the PL/SQL keywords that identify the feature and on the right a brief description and link to the Oracle documentation on this feature. Of course, you can also download my trainings at PL/SQL Obsession and learn about these features – oh, and don't forget to buy my books. J
|
Collections
|
Associative arrays, nested tables, varrays: if these terms mean little to you, then you are missing out on some of the most important functionality in PL/SQL. The collection is PL/SQL's version of an array, and they can help you in so many different ways.
|
|
FORALL
|
Bulk processing of DML statements. This is the most important new feature in PL/SQL since Oracle8i was released. Anytime you execute an insert, update, delete or merge inside a loop of some sort (multi-row DML), you should convert this looping operation to a FORALL statement. You will see an amazing improvement in performance...and you need to use collections to leverage FORALL.
Don't forget to also check out these nuances of FORALL: SAVE EXCEPTIONS, INDICES OF, VALUES OF.
|
|
BULK COLLECT
|
Bulk retrieval of data. Second only to FORALL in importance, use BULK COLLECT with both implicit and explicit cursors to drastically speed up the performance of data retrieval. Don't forget to use the LIMIT clause to manage the amount of PGA memory consumed by your program. As with FORALL, you need to use collections to leverage BULK COLLECT.
|
|
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
|
Answers the question "On what line number was the most recent exception raised?" Available in Oracle Database 10g Release 2 and above, this function should be called whenever you trap and log an error.
|
|
DBMS_UTILITY.FORMAT_CALL_STACK
|
Answers the question "How did I get here?" In other words, it shows you the execution call stack (A called B called C). Very useful information when logging errors or tracing application execution.
|
|
DBMS_UTILITY.FORMAT_ERROR_STACK
|
Returns the current Oracle error message (sometimes as a single string, and sometimes as a stack of messages, if the exception has been re-raised one or more times). Oracle recommends you use this instead of SQLERRM, which may truncate your error message.
|
|
PRAGMA AUTONOMOUS_TRANSACTION;
|
This pragma defines your procedure or function as an autonomous transaction, which means that any DML operations performed within this subprogram are saved or rolled back without affecting any other uncommitted changes in your session. Very handy when logging errors or tracing, also useful in a variety of other circumstances.
|
|
AUTHID CURRENT_USER
|
When you include this clause in the header of a program unit, then when the program is run, the privileges of the current user or invoker of the program are used to resolve references to table and view names. This is called invoker rights and is in contrast to definer rights, the default (or AUTHID DEFINER), in which the privileges of the owner of the program determine at compile time how references to database objects are resolved.
Invoker rights comes in very handy when you need the same code to work against different tables, depending on who is calling the program.
|
|
SUBTYPE
|
The SUBTYPE keyword allows you to define your own, application-specific datatypes. It is a trivial feature to learn and use, but a very powerful one from the standpoint of writing maintainable code.
Use packaged-based subtypes to hide the actual datatype definition, such as VARCHAR2(500). That way, if you ever need to change the datatype (for example, to make the maximum size of the string larger), you can change it one place and all the variables that are declared using the subtype will use this new definition after recompilation.
|
|
RESULT_CACHE
|
New to Oracle Database 11g, you can specify that a function is a result cache. This means that Oracle will cache the inputs and return values of the function in a new SGA cache, and share this data across all sessions in the instance. It can provide a significant boost in query performance for any tables that are queried more frequently than they are updated.
Sure, you are not using 11g yet, but you will. And you can get ready to take advantage of the result cache by hiding your queries inside functions now.
|
|
DBMS_ERRLOG
|
Introduced in Oracle Database 10g Release 2, this package and the associated LOG ERRORS clause, allows you to suppress the raising of exceptions in for DML statements, instead writing error information to a log table. This feature is very handy when performing large numbers of DML operations (ie, batch loads) from within PL/SQL and you need to continue past any exceptions raised. You can also use this within a FORALL statement.
|
|
|
| Permalink |
Trackback |
Comments (2)
|
By dclamage on
Thursday, July 23, 2009 12:55 PM |
| I would add pipelining, which reduces the PGA memory footprint that bulk collect and collections in general incur. Pipelining allows you to avoid instantiating a result set in memory inside a function, that's going to be returned to perhaps yet another calling function. When a function returns a collection, a full copy of the collection is made (albeit temporarily). For large collections, this can lead to poor performance. Pipelining allows you to return the collection's elements in bursts, and can be parallellized. |
|
|
By StevenFeuersteinTW on
Thursday, July 23, 2009 1:01 PM |
Thanks for the comment, Dan. It is good to hear from you. Pipelining is a very cool technique, and it is built on the more general table function capability. To demonstrate how much I agree with you that this is an important tuning technique, the fifth edition of my OPP text (coming out with the Oracle announcement of 11.2 this fall) features about twenty pages just on how to take full advantage of PTFs (written by Adrian Billington, who has tons more experience with this feature).
Remember, though, that pipelined table functions can only used when the function is being called from within a SQL statement.
Regards, SF |
|
|
|
|
|
|
|
|
|
|