Oracle SQL tuning tools

Lesson 3

Objective

Basic SQL tuning tools
Explain the tool set for SQL tuning.

One of the primary problems with tuning SQL is that we cannot easily see how Oracle is accessing the data tables and indexes. Fortunately, Oracle has provided a host of utilities that can help us see what is happening within the database as the SQL is being executed.

These utilities include:

Utilities
The EXPLAIN PLAN utility (utlxplan.sql)	This utility allows the DBA to see the actual path to the data.
The TKPROF utility	TKPROF allows the DBA to trace through the actual execution of an SQL statement.
The Oracle SQL ANALYZE utility	This is a graphic tool that aids in the debugging of Oracle SQL. We will be using screens from this tool extensively in this course.
The SQL*Plus utility	SQL*Plus provides various commands (set timing on) that allow the DBA to get execution times for SQL statements.

Click the link below to see how the SQL Analyzer utility functions.
Sql Analyze Utility
In later lessons, we will be going deeper into SQL analyzer to show how it can be used to tune SQL queries.

In the next lesson we will look at how SQL is executed within the Oracle engine.