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

Share with others

Can't Read? Click to Generate New Code

Enter the verification code shown above into the text box below