Oracle Database 10g SQL Tuning Study Guide

1-4188-1103-3 © 2006 Publish date: September 15, 2005

Buy Now: $249.99

Course ID: oracle-10g-sql-tuning-sg

Learn how to build efficient SQL statements and to tune your applications by choosing the Oracle Database 10g SQL Tuning study guide from CBT Planet's collection of Oracle computer training books. When you combine efficient SQL statements, database server tuning, and PL/SQL application tuning the end result is a highly efficient environment.

If you are a senior application developer, database administrators, or a developers who builds, debugs, and tunes complex SQL statements, you will benefit by learning the material presented in the Oracle Database 10g SQL Tuning courseware.

You will learn about the various tuning issues, executing SQL statements, execution plans, Cost Based Optimizer (CBO), Optimizer operations and statistics, performance monitoring and performing proactive maintenance, ADDM, SQL tuning advisor, SQL access advisor, Optimizer hints, histograms, plan stability, private outlines, exploiting and managing cursor sharing, tkprof and autotrace, managing schema objects and more by using the Oracle Database 10g SQL Tuning courseware.

This format is a self-study format that allows you to learn these complex tasks over a schedule of your own design. You can take as long as you need to learn the material and then use the book as a reference guide whenever you need to tune your databases.

Course Contents

Considering various tuning issues

• Exploring the differences between applications
• Tuning challenges
• Understanding the impact of systems infrastructures
• Working with management and advisory frameworks

Executing SQL statements

• The phases of execution
• Various modes of SQL execution

Viewing execution plans

• Discussing execution plans
• Viewing execution plans
• Interpreting the plan

Understanding Cost Based Optimizer (CBO)

• Exploring optimization goals
• Components of CBO

Optimizer operations

• Working with table access operations
• Understanding join operations
• Understanding index operations

Optimizer statistics

• Understanding Optimizer statistics
• Working with automatic database statistics collection
• Gathering statistics manually
• Working with DBMS_stats() package
• Understanding the EM interface
• Collecting and managing system statistics
• How to manage system statistics

Performance monitoring and performing proactive maintenance

• Exploring management and advisory frameworks
• Real-time monitors
• How to manage automatic workload repositories
• Understanding Automatic Database Diagnostic Monitor
• Metrics and alert thresholds

Using ADDM and the SQL tuning advisor

• Setting up application tuning
• Application monitoring and tuning
• The SQL tuning advisor

The SQL tuning advisor

• Understanding SQL tuning advisor tasks
• Managing SQL profiles
• Managing SQL tuning sets

Working with the SQL access advisor

• Working with SQL access advisor
• How to perform a quick tune task
• Understanding index operation database parameters

Working with Optimizer hints

• Understanding hints
• Hint explanations

Histograms

• The reason histograms are needed
• The internal structure of histograms
• Managing histograms

Understanding plan stability

• Understanding plan stability
• Stored outlines preparation
• Creating and using stored outlines
• Storing data dictionaries
• Management of stored outlines

Editing private outlines

• Understanding private outlines
• How to edit private outlines
• Using private outlines

Exploiting and managing cursor sharing

• Understanding cursor sharing
• Bind variables and cursor peeking
• Working with the "cursor_sharing" parameter

Working with tkprof and autotrace

• Working with tkprof
• Working with autotrace

Schema objects management

• Understanding chained and migrated rows
• How to validate database objects