Oracle SQL Tuning

Oracle SQL tuning basically boils down to discovering the quickest way to perform a function in an Oracle database. In fact, studies have shown that by far poor SQL coding is the predominate reason why Oracle databases experience difficulties. Thus when tuning the database, the Oracle optimizer will first try to figure out how the execution of each individual SQL statement impacts the overall response time of the system.

In today's fast paced and busy environment it can be rather difficult to track every SQL statement a user executes. Especially when you consider that as a database grows and its functionality expands, the execution of one simple SQL statement can span multiple Oracle instances or processes. Fortunately, there are various tools that the database administrator (DBA) can use to accomplish the tedious task of tracking and ultimately optimizing poorly coded SQL statements.

Built in Utility

Oracle 10g has a built in utility that allows the DBA to capture detailed SQL statistics from any user. The tool is called DBMS_MONITOR and can be used to generate SQL trace reports. This tool can be rather useful for database applications that are loaded onto a shared server.

The DBA can use the DBMS_MONITOR tool to specify the criteria for the report he or she wants to return results for. Some of the statistics the tool is capable of tracking are session ID, module name, and action name. DBAs can simply turn this feature on or off as they see fit.