Develop the skills to effectively interact with an Oracle 11g Database using Oracle SQL.

Write faster, more efficient code by optimizing SQL statements and the Oracle architecture.

Course Description:

This course will introduce the developer to techniques used to provide minimize response times in and Oracle database. 

Who Needs to Attend:

Application Developers and Database Administrators who need a fundamental understanding of tuning the Oracle architecture and SQL statements.

Prerequisites:

Oracle 10g SQL or equivalent experience  

Course Content:

SUBQUERIES

• Where Clause Subqueries

• From Clause Subqueries

• Correlated Subqueries

• Scalar Subqueries

• Exists Subqueries

• Tree-Structured queries

OVERVIEW OF THE TUNING ENVIRONMENT

• Performance Tuning Duties

• Steps For Tuning

• Tuning Methodology

• The Tuning Team

• Tuning Tools 

TUNE DATABASE OPERATIONS

• SQL Parsing And Execution

• Shared SQL Statements

• The SQL Area

• Automatic Segment Space Management

• Statement Transformation

• Using SQL Syntax 

TUNING THE LOGICAL STRUCTURE

• Tuning Overview

• Tablespace Considerations

• Table Considerations

• Free Space Management

• Chaining And Migrated Data

• Choosing The Right Index

• Index Usage Monitoring

• Materialized Views

SQL TUNING TOOLS

• Optimizer Overview

• Execution Plans

• Rule-Based Optimization (Desupported)

• Cost-Based Optimization

• Table And Index Statistics

• DBMS_STATS

• Execution Plans

• Autotrace

• SQL_TRACE And TKPROF

• Using Hints

• Stored Outlines And Plan Stability 

APPLICATION MEMORY USE

• Memory Tuning Goals

• Tuning The Buffer Cache

• Memory Subcaches

• Partitioned Tables And Indexes 

PL/SQL PACKAGES FOR THE TUNER

• DBMS_SESSION

• DBMS_TRACE

• DBMS_STATS 

PERFORMANCE TUNING CHECKLIST

• Data Model Review

• Object Management

• Architectural Issues

• Shared Pool

• Buffer Cache

• File I/O

• Redo Log Buffer And Checkpoints

• Sort Activity 

TEMPORARY TABLES

• Purpose

• Creation

• Use 

BEST PRACTICES

• Code Development

• Style

• Data Structures

• Flow Control

• Exceptions

• SQL Within PL/SQL

• Program Structure

• Packages 

DBMS_PROFILER

• Setup

• Profiling Code

• Additional Statistics

• PL/SQL Specific Issues

Course Labs: 

1. Subqueries
2. SQL Statement Processing
3. Logical Structures
4. Statistics And Execution Plans
5. SQL_TRACE, TKPROF, Stored Outlines And Hints
6. Application Memory Use