Tuning contention is appealing from a DBA perspective, since you don’t have to change the application code or schema. But it’s often more effective to reduce the demand on the database by making the application work smarter. In general, this involves tuning SQL and PL/SQL.  In this successor to my Tuning Contention series, I'll look at how best to tune the application using the Toad DBA suite. 

Optimizing Memory In previous articles, we’ve looked at ways of reducing the demand placed on the database by application SQL, and we’ve looked at ways to reduce contention between the demands of concurrently executing sessions.  If we’ve done a good job with this, then we hopefully have minimized the amount of logical IO demand that the application creates for the database. This logical IO consists of requests for information contained within the database files as well as for temporary result sets required to resolve joins and subqueries, to sort results or to create hash structures for joins and similar operations.  Reducing this logical IO is one of the primary aims of SQL tuning – but just as important is to prevent as much as possible from turning into physical IO.

Tracing SQL Statement Execution In this article, I'll discuss why tracing session execution is one of the most powerful ways of tuning an application.   Oracle’s SQL tracing facility allows you to capture all of the activity from a session or groups of sessions and identify tuning opportunities or bottlenecks.  Naturally, we’ve added facilities to the TOAD DBA suite to make this even more useful..

Tuning PL/SQL code with the TOAD DBA suite In this article, I'll discuss how to find PL/SQL packages that might be causing performance problems, how to identify the problems within a PL/SQL program and look at ways of making PL/SQL run faster.

Make Oracle work smarter, not harder Getting started with SQL tuning involves mastering two fundamentals: finding SQL that warrants tuning, and working out how to make those SQLs work more efficiently. Spotlight and SQL Optimizer – within the Toad DBA Suite for Oracle – make these two steps extremely easy even if you’re new to SQL tuning. Tuning SQL reduces the demands that the application makes on the database. Every downstream activity – contention, memory efficiency and IO load – will be improved as a result. For this reason, tuning SQL is an essential pre-requisite for an efficient and scalable database server.