Oracle SQL Statement Tuning

Course Number:  577-JK
Recommended Duration:  2 days

Intended Audience
Experienced users of Oracle and SQL needing to tune Oracle applications.

Course Overview
Students tune SQL statements using DBMS_STATS (ANALYSE in older releases), AUTOTRACE, EXPLAIN, and TKPROF.  There are six machine exercises.

Course appropriate for Oracle 10g.  Course covers Oracle9i and Oracle8i.  It is also useful for shops using Oracle8 or Oracle 7. (earlier course versions available)

Objectives
Participants will improve SQL so that they may more efficiently satisfy user needs.  The latest releases of Oracle products will be discussed.  Hands-on exercises will allow participants to: practice complex SQL statements, learn how to use catalog tables, properly use indexes, be able to detect and improve many common performance problems.

Prerequisites
Previous Oracle experience or education is essential to understanding the advanced topics presented in this class.  Participants should be familiar with both SQL and SQL*Plus.

Topics

• Oracle RDBMS concepts and data storage

• Advanced SQL usage

• Complex SELECT statements (subqueries, joins, and outer joins)

• System catalog tables

• Indexing, proper use of indexes

• Optimizing SQL

• Hints

• SQL use of indexes

• EXPLAIN

• Trace

• Trace 10046 and Wait Events

• TKPROF

• AUTOTRACE

• PL/SQL