Introduction to Tuning
Two important objectives in tuning any
application or database system are to reduce the response time
for its end users and to minimize the consumption of resources
for the system. Tuning of SQL statements is a major factor in
determining the system performance of the database.
SQL tuning is performed by the execution of
the following steps:
-
Identify the top SQL statements
that
utilize a major portion
of the application workload and system resources.
-
Analyze the execution plans from
the query optimizer.
-
Improve the execution plan of
poorly performing SQL statements.
The above steps have to be repeated until
performance results are satisfactory for the system.These
steps will be examined
in more detail in the next
sections.
SQL Tuning Features
Top SQL statements or high-load SQL
statements are those statements that are poorly performing and
resource draining, which in turn slows
the entire database and any associated
applications. To identify high-load SQL statements, Oracle
Database 10g has
provided a handful of
features, some of which are entirely new to this release. These
features are Automatic Workload Repository (AWR), Automatic
Database Diagnostic Monitor (ADDM), v$sql view, SQL
Trace, and Custom Workload.
