Oracle Database 11g: SQL Tuning Workshop

This lesson provides an overview of the Oracle Database server architecture. You learn about physical and logical structures and about the various components.

Lessons:

• List the major architectural components of the Oracle Database server

• Correlate logical and physical storage structures

• Explain memory structures

• Lab:

• Describe background processes

• Looking at some instance and database components directly on your machine

This lesson describes the nature of the tuning process and the different components of an Oracle database that may require tuning.

Lessons:

• Describe what attributes of a SQL statement can make it perform poorly

• Lab:

• List the Oracle tools that can be used to tune SQL

• Rewriting queries for better performance

• List the tuning tasks

• Rewriting applications for better performance

This lesson describes in detail how SQL statements are processed within Oracle Database 11g. It also details the Oracle SQL Optimizer and ways of controlling its behaviour.

Lessons:

• Describe the execution steps of a SQL statement

• Control the behaviour of the optimizer

• Discuss the need for an optimizer

• Lab:

• Explain the various phases of optimization

• This practice covers exploring a trace file to understand the optimizer’s decisions

This lesson describes how Optimizer execution plans by examining the characteristics of specific SQL statements’ syntax.

Lessons:

• Describe most of the SQL operators

• Lab:

• List the possible access paths

• Using different access paths for better optimization

• Explain how join operations are performed

• Case 14 to case 16

• Using the result cache

This lesson introduces the star and snowflake schema models used in a data warehouse environment. Participants will learn how to implement transformation and show query plan data.

Lessons:

• Define a star schema

• Show a star query plan after transformation

• Show a star query plan without transformation

• Lab:

• Define the star transformation requirements

• This practice covers using the star transformation technique to optimize your query

This lesson introduces the star and snowflake schema models used in a data warehouse environment. Participants will learn how to implement transformation and show query plan data. Lessons:

• Define a star schema

• Show a star query plan after transformation

• Show a star query plan without transformation

• Lab:

• Define the star transformation requirements

• This practice covers using the star transformation technique to optimize your query

This lesson demonstrates how to define, gather, use and manipulate Oracle database optimizer statistics.

Lessons:

• Gather optimizer statistics

• Manipulate optimizer statistics

• Set statistic preferences

• Lab:

• Use dynamic sampling

• Using system statistics

• Gather system statistics

• Using automatic statistics gathering

This lesson describes the advantages of using bind variables and advanced cursor sharing techniques.

Lessons:

• List the benefits of using bind variables

• Use bind peeking

• Use adaptive cursor sharing

• Lab:

• Using adaptive cursor sharing and bind peeking

• Using the CURSOR_SHARING initialization parameter

This lesson explores the use of optimizer hints and how they could influence query performance.

Lessons:

• Use hints when appropriate

• Lab:

• Specify hints for

• This practice covers using various hints to influence execution plans

• Optimizer mode

• Join orders

• Query transformation

• Join methods

• Access path

• Join orders

• Join methods

This lesson demonstrates the configuration and usage of tracing facilities and utilities to generate meaningful tracing reports.

Lessons:

• Configure the SQL Trace facility to collect session statistics

• Lab:

• Use the TRCSESS utility to consolidate SQL trace files

• Creating a service

• Format trace files using the tkprof utility

• Tracing your application using services

• Interpret the output of the tkprof command

• Interpreting trace information using trcsess and tkprof

This lesson explains the use of builtin advisors as well as automatic SQL tuning for application tuning.

Lessons:

• Describe statement profiling

• Lab:

• Use SQL Tuning Advisor

• Using ADDM and SQL Tuning Advisor to tune your SQL statements

• Use SQL Access Advisor

• Using SQL Access Advisor to change your schema

• Use Automatic SQL Tuning

• Using Automatic SQL Tuning to tune your statements