Oracle Database 10g: SQL Tuning

Total time
Logo Oracle University

Need more information? Get more details on the site of the provider.

Starting dates and places

There are no known starting dates for this product.

Description

This course is designed to give the experienced SQL Developer or DBA a firm foundation in SQL tuning techniques. The participant learns the necessary knowledge and skills to effectively tune SQL in the Oracle Database 10g Release 2. They learn about tuning methodology as well proactive tuning and reactive tuning methods. Students are introduced to the benefits of the new automatic tuning mechanisms available in Oracle Database 10g. On completion of the course they are able to compare and contrast the steps involved to tune manually as in prior releases as well as use the automatic SQL tuning features provided in the current release. Students gain a thorough conceptual understanding of the Or…

Read the complete description

Frequently asked questions

There are no frequently asked questions yet. If you have any more questions or need help, contact our customer service.

Didn't find what you were looking for? See also: Oracle 10G, PL/SQL, Programming (general), Oracle, and IT Security.

This course is designed to give the experienced SQL Developer or DBA a firm foundation in SQL tuning techniques. The participant learns the necessary knowledge and skills to effectively tune SQL in the Oracle Database 10g Release 2. They learn about tuning methodology as well proactive tuning and reactive tuning methods. Students are introduced to the benefits of the new automatic tuning mechanisms available in Oracle Database 10g. On completion of the course they are able to compare and contrast the steps involved to tune manually as in prior releases as well as use the automatic SQL tuning features provided in the current release. Students gain a thorough conceptual understanding of the Oracle Optimizer, and reinforce instructor-led learning with structured hands-on practices. The course uses a series of challenge-level workshops, allowing students to "play, discover, and learn" at their own level and pace. The students learn to use the Oracle diagnostic tools and facilities: Automatic SQL Tuning components, EXPLAIN, SQL Trace and TKPROF, SQL*Plus AUTOTRACE. Students also learn to influence the behavior of the Optimizer by changing the physical schema and modifying SQL statement syntax.

This course counts towards the Hands-on course requirement for the Oracle Database 10g Administrator Certification. Only instructor-led inclass or instructor-led online formats of this course will meet the Certification Hands-on Requirement. Self Study CD-Rom and Knowledge Center courses DO NOT meet the Hands-on Requirement.

A Live Virtual Class (LVC) is exclusively for registered students; unregistered individuals may not view an LVC at any time. Registered students must view the class from the country listed in the registration form. Unauthorized recording, copying, or transmission of LVC content may not be made.


Audience
  • Business Intelligence Developer
  • Forms Developer
  • Reports Developer
  • PL/SQL Developer
  • Technical Consultant
  • Java Developer

Course Topics Database Architecture overview
  • Overview of Database architecture
  • Listing the SQL Statement Processing Steps
  • Identifying Means to Minimize Parsing
  • Stating the Use of Bind Variables
Following a Tuning Methodology
  • Describing the Causes of Performance Problems
  • Identifying Performance Problems
  • Using a Tuning Methodology
Designing Applications for performance
  • Oracle Methodology
  • Understanding Scalability
  • System Architecture
  • Application Design Principles
  • Deploying New Applications
Introducing the optimizer
  • Describe the functions of the Oracle optimizer
  • Identify the factors that the optimizer considers when it selects an execution plan
  • Set the optimizer approach at the instance and session level
  • Use dynamic sampling
Optimizer Operations
  • Execution plans
  • Types of Joins
Displaying Execution plans
  • Using the EXPLAIN PLAN Command
  • Interpreting EXPLAIN Output
  • Interpreting AUTOTRACE Statistics
Gathering Statistics
  • Using the DBMS_STATS Package
  • Identifying Table, Column, and Index Statistics
  • Building Histograms
Application Tracing
  • Statspack
  • End to End tracing
  • Invoking the SQL Trace Facility
  • Setting Up Appropriate Initialization Parameters
  • Formatting Trace Files with TKPROF
  • Interpreting the Output of the TKPROF Command
Identifying High Load SQL
  • Use different methods to identify high-load SQL
  • ADDM
  • Top SQL
  • Dynamic Performance views
  • Statspack
Automatic SQL Tuning
  • Query Optimizer Modes
  • Types of Tuning Analysis
  • SQL Tuning Advisor
  • SQL Tuning Sets
  • Top SQL
Introduction to Indexes
  • Identifying Row Access Methods
  • Creating B-Tree Indexes
  • Understanding B-Tree Index Access and Index Merging
Advanced Indexes
  • Using Bitmapped Indexes
  • Using Function-Based Key Indexes
Optimizer Hints and Plan Stability
  • Using Hints
  • Purpose and Benefits of Optimizer Plan Stability
Materialized Views and Temporary Tables
  • Using the CREATE MATERIALIZED VIEW Syntax
  • Utilizing Query Rewrites

Course Objectives
  • Describe the basic steps in processing SQL statements
  • Describe the causes of performance problems
  • Understand where SQL tuning fits in an overall tuning methodology
  • Describe Automatic SQL Tuning
  • Use the diagnostic tools to gather information about SQL statement processing
  • Understand Optimizer behavior
  • Influence the optimizer behavior
  • Influence the physical data model so as to avoid performance problems

There are no reviews yet.

Share your review

Do you have experience with this course? Submit your review and help other people make the right choice. As a thank you for your effort we will donate $1.- to Stichting Edukans.

There are no frequently asked questions yet. If you have any more questions or need help, contact our customer service.