Avoiding common pitfalls

It is commonly considered that performance tuning is a secondary task or a post-production task, but performance tuning should start with the database design phase. Unfortunately, performance tuning is often not considered until there is actually a problem to be fixed.

The following are a few of the commonly misconfigured areas that should be carefully addressed.

Database connection management

A poor connection management may lead to scalability issues. The applications connect and disconnect very frequently for each database interaction. This is considered bad because establishing a database connection is a resource-expensive task as it involves allocating resources at OS level, negotiating a database connection with Oracle Listener, as well as connecting and allocating memory to database session.

Cursors and the shared pool

Reusing SQL statements is a key to efficient cursor management. Bind variables should be used whenever possible to avoid hard parsing of SQL statements...