Speed vs Maintainability vs Flexibility

One of my main remits at my current client is performance tuning and mainly at the application SQL and PL/SQL level (where most gains are to be normally made).

The primary tools for this are EXPLAIN PLAN, extended SQL TRACE, DBMS_PROFILER and RUNSTATS.

Whilst there will be some missing indexes and some benefits in rewriting queries and routines to work with better set operations, it has quickly become obvious that the biggest benefits will almost certainly come from taking all the lookups that have been packaged into nice, reusable function calls and bringing them back in line in the queries with appropriate set operations.

There are lots of function lookups in the SQL. And these, in themselves, are well written and make lots of sense. But the trouble is that hundreds and thousands of little row-by-row lookups can kill your performance.

 It’s also worth noting that a simple explain plan or autotrace won’t reveal the cost of this whereas a sql trace of a session and a dbms_profiler run against the procedure will give a much better picture of where all the problems are.

But, even so, it’s not as if these lookups are wrong as such. It’s just that in these circumstances speed has become more of a priority over maintainability and reuse.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: