http://nyoug.org/event/nyoug-fall-2014-training-event-high-performance-plsql/?instance_id=258
I think, it is as important as ever to follow best practices for server-side development. There are lots of options that could get the job done "now", but very few that would survive for months/years. Also, there is a constant pressure from various "alternative" solutions to move the business logic somewhere else (front-end/ middle-tier/other database technologies/you name it). But my strong believe that by properly utilizing available Oracle technologies you can achieve much better results without compromising data security and manageability.
I've put together more than a decade of my experience to a full-day seminar that can really help you. Here is an outline:
1. Know your Environment – This section introduces the available tools and features related to
PL/SQL performance tuning including: data dictionary views, logging, tracing, profiling etc. It
also discusses the proper ways to instrument code deal with and exception handling.
2. User-Defined PL/SQL Functions in the SQL Context – For most developers, knowledge of
PL/SQL starts from writing user-defined functions. As a result, even if this code is functionally
correct, the program units are fired significantly more often than needed, impact CBO
decisions, and cause execution plan degradation. This section addresses these issues and
includes a number of examples of how PL/SQL can extend basic SQL functionality.
3. SQL Sets Within PL/SQL – The goal of PL/SQL is to efficiently communicate with SQL sets
using cursors. This key feature of database cursors is often missed by assuming that they are
nothing more than pointers to SQL sets. The most efficient way of working with SQL sets is to
access data using set-based operators. This section reviews the results of two case studies
focused on comparing different access approaches. The first one looks for the most efficient
ways of moving significant volumes of data between remote locations, while the second tests
the impact of MULTISET operations on resource utilization.
4. Triggers are NOT Evil - Both table triggers and INSTEAD-OF triggers are examined from a
global system optimization point of view that includes not only the aspect of functional
correctness, but also the tradeoffs between multiple goals. For example, depending upon the
available hardware, developers can select either CPU-intense or I/O-intense solutions. This
section also covers some of the most common performance problems related to different kinds
of DML triggers and the proper ways of resolving them.
5. Keeping the Cache - One of the most important performance tuning strategies is to ensure
that you are not doing exactly the same thing multiple times. However, often you cannot avoid
this repetition. In those cases, the best option is to minimize the impact by utilizing existing
caching techniques, either manual or built-in. Currently in Oracle, there are different caching
mechanisms, each with its own strengths, drawbacks, and side-effects. This section introduces
all of them and details the selection criteria for matching the appropriate mechanism to specific
situations.3
6. WHEN OTHERS THEN… – The topic of PL/SQL performance tuning is too large to cover
in a 6-hour seminar. However, some tips and tricks can be very useful to attendees. This last
section describes some one-off cases that overlap multiple areas. It also includes an extended
question & answer period where attendees will get a chance to share their experiences and ask
questions.
