Provided by: 123-CBT Computer Based Training Oracle SQL Tuning Diagnostics and Tuning Schema Click here for more information or to take this course

---

---

Training Provided by 123-CBT Computer Based Training

Course Overview

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.

Learn To:

• 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.

Content Emphasis

Skills-Based

Audience

Application Developers, Database Administrators, and Designer/Developers.

Total Learning Time

6 - 8 Hour(s)

Course Contents

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.

Training Avaliability and Delivery

This is primarily online training This is an online eLearning or CBT training program This program may be available on CD Contact 123-CBT Computer Based Training for more information

Training Presented in: English

About 123-CBT Computer Based Training - Training Provider

More Oracle SQL PL SQL Training from 123-CBT Computer Based Training

• Oracle PL SQL Database Programming
• Oracle PL SQL Procedures Functions Packages
• Oracle SQL and SQL Plus Advanced SELECT Statements
• Oracle SQL and SQL Plus SQL Plus and Reporting
• Oracle SQL PL SQL Using Procedure Builder Curriculum
• Oracle SQL Specifics Creating and Managing Database Objects
• Oracle SQL Specifics Retrieving and Formatting Data
• Oracle SQL Tuning Tuning SQL and the Optimizer