Oracle Database 10g: SQL Tuning

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

course content details

	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