Database Reference
In-Depth Information
Not Using PL/SQL for Data-Centric Processing
A special case, from the point raised in the previous section, is the use of PL/SQL for implementing batches that
process lots of data. The most common example is an extract-transform-load (ETL) process. When, in such a process,
the extract and load phases are executed against the very same database, from a performance point of view it is
almost insane to not process the transform phase by taking advantage of the SQL and PL/SQL engines provided by
the database engine that manages the source and target data. Unfortunately, the architecture of several mainstream
ETL tools leads exactly to such insane behavior. In other words, data is extracted from the database (and frequently
also moved to another server), the transform phase is executed, and then the resulting data is loaded back into
the very same database from which it came. For this reason, vendors like Oracle started offering tools that perform
transformations inside the database. Such tools, to differentiate them from the ETL tools, are commonly called ELT.
For best performance, I advise performing data-centric processing as closely as possible to the data.
Performing Unnecessary Commits
Commits are operations that call for serialization (the reason is simple: there is a single process (LGWR) that is
responsible for writing data to redolog files). It goes without saying that every operation that leads to serialization
inhibits scalability. And as a result, serialization is unwanted and should be minimized as much as possible. One
approach is to put several unrelated transactions together. The typical example is a batch job that loads many rows.
Instead of committing after every insert, it is much better to commit the inserted data in batches.
Steadily Opening and Closing Database Connections
Opening a database connection that in turn starts an associated dedicated process on the database server, is not
a lightweight operation. Do not underestimate the amount of time and resources required. A worst-case scenario
that I sometimes observe is a web application that opens and closes a database connection for every request that
involves a database access. Such an approach is highly suboptimal. Using a pool of connections in such a situation
is of paramount importance. By using a connection pool, you avoid the constant starting and stopping of dedicated
services processes, thus avoiding all the overhead involved.
Do You Have Performance Problems?
There is probably a good chance that sooner or later the performance of an application will be questioned. If, as
described in the previous sections, you have carefully defined the performance requirements, it should be quite
simple to determine whether the application in question is in fact experiencing performance problems. If you have
not carefully defined them, the response will largely depend on who answers the question.
Interestingly enough, in practice the most common scenarios leading to questions regarding the performance of
an application fall into very few categories. They are short-listed here:
•
Users are unsatisfied with the current performance of the application.
•
A system-monitoring tool alerts you that a component of the infrastructure is experiencing
timeouts or an unusual load.
•
A response-time monitoring tool informs you that a service level agreement is not being
fulfilled.
The difference between the second point and the third point is particularly important. For this reason, in the next
two sections I briefly describe these monitoring solutions. After that, I present some situations where optimization
appears to be necessary but in fact is not necessary at all.
