Lesson 3	Basic SQL tuning tools
Objective	Explain the tool set for SQL tuning.

Oracle SQL Tuning Tools

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.

Read the paragraphs below to understand how the SQL Analyzer utility functions.

SQL Analyze Utility

With the Oracle Enterprise Manager open, click the Oracle SQL Analyzer button.
Read the opening screen, and click OK.
Now we are ready to begin. Select the bottommost database under the Databases folder.
Select Top SQL from the mon1 database.
The TopSQL Options box will allow you to control the number of disk reads as well as apply various filters. Click OK.
Choose our database to view the init.ora parameters for SQL values. Select select/*+rule*/ a.db_name.
Click the SQL Text tab to view the full SQL text.
Place your cursor anywhere in the SQL text field and click to highlight the text.
In an actual Oracle environment, you would press Ctrl+C to copy this text and Ctrl+N to move to a new screen. For the purposes of this simulation, press Enter to move to a new SQL screen.
For the purposes of this simulation, place your cursor in the SQL statement window and hit Enter to paste your SQL text. You would normally accomplish this by pressing Ctrl+V.
Typically, you would press F9 to see the rule-based explain plan, but for the purposes of this simulation press Enter.

Oracle SQL Analyze

Oracle SQL Analyze can be used to identify and correct problem SQL statements. SQL Analyze allows you to sort SQL statements by various performance metrics, and acquire detailed tuning information for any SQL statements. Wizards are included which guide the user through a SQL optimization methodology or add hints to existing SQL statements.

Oracle SQL Expert

Oracle Expert automates the database tuning process. Oracle Expert provides a methodology that is used to collect, evaluate, verify, and implement database tuning changes. Tuning areas covered by Oracle Expert include instance parameter tuning, database structure and placement tuning, index tuning, and SQL statement reuse evaluation.

Oracle Index Tuning Wizard

The Index Tuning Wizard provides an easy solution to the Index Tuning problem. It automatically identifies tables which would benefit from index changes, presents its findings for verification, and allows you to implement the index tuning recommendations.

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.

When to Use the EXPLAIN PLAN Statement

Use the EXPLAIN PLAN statement to determine the execution plan Oracle Database follows to execute a specified SQL statement. This statement inserts a row describing each step of the execution plan into a specified table. You can also issue the EXPLAIN PLAN statement as part of the SQL trace facility. This statement also determines the cost of executing the statement. If any domain indexes are defined on the table, then user-defined CPU and I/O costs will also be inserted. The definition of a sample output table PLAN_TABLE is available in a SQL script on your distribution media. Your output table must have the same column names and datatypes as this table. The common name of this script is UTLXPLAN.SQL. The exact name and location depend on your operating system. Oracle Database provides information on cached cursors through several dynamic performance views:

For information on the work areas used by SQL cursors, query V$SQL_WORKAREA.
For information on the execution plan for a cached cursor, query V$SQL_PLAN.
For execution statistics at each step or operation of an execution plan of cached cursors (for example, number of produced rows, number of blocks read), query V$SQL_PLAN_STATISTICS.
For a selective precomputed join of the preceding three views, query V$SQL_PLAN_STATISTICS_ALL.
Execution statistics at each step or operation of an execution plan of cached cursors are displayed in V$SQL_PLAN_MONITOR if the statement execution is monitored. You can force monitoring using the MONITOR hint.