Oracle Database 10g SQL Tuning

Day 1

• Lesson 1 : Oracle Database Architecture: Overview
• Lesson 2 : Following a Tuning Methodology
• Lesson 3 : Designing and Developing for Performance
• Lesson 4 : Introduction to the Optimizer
• Lesson 5 : Optimizer Operations

Day 2

• Lesson 6 : Execution Plans
• Lesson 7 : Gathering Statistics
• Lesson 8 : Application Tracing
• Lesson 9 : Identifying High-Load SQL
• Lesson 10 : Automatic SQL Tuning

Day 3

• Lesson 11 : Index Usage
• Lesson 12 : Using Different Indexes
• Lesson 13 : Optimizer Hints
• Lesson 14 : Materialized Views

Lesson 1 : Oracle Database Architecture: Overview

• Describe the Oracle Database architecture and components
• Make qualified decisions about your tuning actions

Lesson 2 : Following a Tuning Methodology

• Determine performance problems
• Manage performance
• Describe tuning methodologies
• Identify goals for tuning
• Describe automatic SQL tuning features
• List manual SQL tuning steps

Lesson 3 : Designing and Developing for Performance

• describe the basic steps involved in designing and developing for performance

Lesson 4 : Introduction to the Optimizer

• Describe the functions of the Oracle optimizer
• Identify the factors influencing the optimizer
• Set the optimizer approach at the instance and session level

Lesson 5 : Optimizer Operations

• Describe different access paths
• Optimize sort performance
• Describe different join techniques
• Explain join optimization
• Find optimal join execution plans

Lesson 6 : Execution Plans

• Use the EXPLAIN PLAN command to show how a statement is processed
• Use the DBMS_XPLAN package
• Use the Automatic Workload Repository
• Query the V$SQL_PLAN performance view
• Use the SQL*Plus AUTOTRACE setting to show SQL statement execution plans and statistics

Lesson 7 : Gathering Statistics

• Identify table, index, and column statistics
• Describe the Automatic Statistics Gathering mechanism
• Use the DBMS_STATS package to collect statistics manually
• Identify predicate selectivity calculations

Lesson 8 : Application Tracing

• Configure the SQL Trace facility to collect session statistics
• Enable SQL Trace and locate your trace files
• Format trace files using the TKPROF utility
• Interpret the output of the TKPROF command

Lesson 9 : Identifying High-Load SQL

• ADDM
• Top SQL
• Dynamic performance views
• Statspack

Lesson 10 : Automatic SQL Tuning

• Describe automatic SQL tuning
• Describe the Automatic Workload Repository
• Use Automatic Database Diagnostic Monitor
• View the cursor cache
• Perform automatic SQL tuning
• Use the SQL Tuning Advisor
• Use the SQL Access Advisor

Lesson 11 : Index Usage

• Identify index types
• Identify basic access methods
• Monitor index usage

Lesson 12 : Using Different Indexes

• Use composite indexes
• Use bitmap indexes
• Use bitmap join indexes
• Identify bitmap index operations
• Create function-based indexes
• Use index-organized tables

Lesson 13 : Optimizer Hints

• Optimizer mode
• Query transformation
• Access path
• Join orders
• Join methods

Lesson 14 : Materialized Views

• Identify the characteristics and benefits of materialized views
• Use materialized views to enable query rewrites
• Verify the properties of materialized views
• Perform refreshes on materialized views