Oracle SQL Tuning: Diagnostics and Tuning the Schema

This course is the first in a two-part series on SQL Statement Tuning. This course will help experienced application developers to learn a tuning methodology they can use to maximize database performance with minimal effort. Participants will gain experience creating optimal access paths such as partitioned views B-tree indexes bitmapped indexes index clusters and hash clusters. Participants will then be able to test the results of their tuning efforts using performance analysis tools.

This is primarily online training This is an online eLearning or CBT training program Contact Serebra Learning Corporation for more information

Duration: 8 hours Training Presented in: English

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

• Oracle SQL Tuning: Tuning SQL and the Optimizer