Oracle SQL Tuning: Diagnostics and Tuning the Schema

Audience

Application Developers Database Administrators and Designer/Developers. Learners should be familiar with the basic principles of SQL and PL/SQL or have completed courses 61110 61111 61112 and 60113.

Objective

• Identify the need and areas for tuning the performance of SQL statements.
• Identify tuning steps.
• Identify the tools that help you test an diagnose performance problems with a SQL statement.
• Use efficient data access methods that are available for the optimizer.
• Create the appropriate database objects before starting to tune SQL statements.

Topics Include

Unit 1: Managing Performance

• Identify the causes of SQL performance problems.
• Identify the main system areas that can be addressed with the tuning process.
• Sequence the steps involved in the tuning methodology.
• Identify the guidelines to be followed when applying the tuning methodology.

Unit 2: Analyzing SQL Performance

• Match the identifiers in the EXPLAIN PLAN command with their descriptions.
• Match the columns in the EXPLAIN PLAN output table with their descriptions.
• Identify the combination of operation and option used given the interpretation of the EXPLAIN PLAN output.
• Display the execution plan worked out by the optimizer for a given query by using the EXPLAIN PLAN command.
• Set the SQL Trace initialization parameters in the parameter file.
• Switch on SQL Trace.
• Match the columns in the TKPROF output with their descriptions.
• Match the TKPROF timing output columns with their descriptions.
• Identify the additional components of the TKPROF output.
• Sequence the steps to be performed for using SQLTrace.
• Turn on autotrace option in the specified format.
• Match the various performance analysis tools available tools available for identifying expensive statements with their functions.

Unit 3: Tuning the Schema

• Identify the levels of B*-tree index structure.
• Identify the features of a B*-tree index scan.
• Identify the columns on which a B*-tree index should ideally be created.
• Create a B*-tree index by using SQL commands.
• Identify the features of bitmapped indexes.
• Identify the advantages of bitmapped indexes.
• Create a bitmapped index by using the CREATE INDEX command.
• Identify the characteristics of the different cluster types.
• Create a hash cluster by using the CREATE CLUSTER command.
• Identify the guidelines for using a hash cluster.
• Create an index cluster by using the CREATE CLUSTER command.
• Identify the advantages of partition views.
• Sequence the steps to create a partition view.

Duration

8

Minimum Requirements

The CDROM version of this course requires:

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
• At least a double speed CDROM drive.
• An MPC compliant sound card with attached speakers or headphones is recommended (Currently only the CDROM version supports audio).

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM and 22MB available hard disk space or file server space.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).

Media