Oracle Training : Oracle8i Application Tuning

Oracle8i Application Tuning

Oracle Training Overview

This course will provide you with the skills necessary to tune Oracle applications. Particular attention is paid to tuning SQL statements, PL/SQL programs and 3GL programs that access Oracle data.

Oracle Training Prerequisites

Programming experience with Oracle databases. Oracle SQL, SQL*Plus and PL/SQL programming experience. This knowledge can be obtained in Introduction to Oracle for Developers class.

Oracle Training Course duration

3 Days

Oracle Training Objectives

After successfully completing this course, you will be able to:

Improve the performance of Oracle applications
Use the EXPLAIN PLAN command to externalize access path choices made by the optimizer
Tune SQL statements to reduce statement cost and elapsed time
Create indexes on appropriate columns
Use the trace and TKPROF utilities
Implement partitioning (V8)
Take advantage of parallel processing features
Tune 3GL programs (Pro*C)
Use HINTs to influence optimizer choices
Describe the various access paths used by Oracle
Understand how PL/SQL procedures can be used to improve performance
Improve the performance of database triggers

Oracle Training Course outline

Introduction to SQL Tuning

What Can Be Tuned?
What This Course Covers
Tuning SQL: What We Need to Know
Tuning Programs: What We Need to Know

Oracle Architecture Overview

Introduction to the Oracle Architecture
The SGA
Data Block Buffer Cache
Redo Log Buffer
Background Processes

SQL Statement Processing

SQL Statement Processing Overview
Parse
Parse Steps
Execute
Fetch
Shared SQL
Stored Procedures
Shared Pool Memory Size Requirements
Viewing Pool Size
Excessive ReParsing
Query Re-Parse
Is the Shared Pool Too Big?
Monitoring Shared SQL
Monitoring Shared SQL Script -1
Monitoring Shared SQL Script -2
Monitoring Shared SQL Script - 3
Bind Variables - 1
Bind Variables - 2

Indexes

Index Usage
B-Tree Indexes
B-Tree Illustration
B-Tree Effect on Query Performance
Creating B-Tree Indexes
Run ANALYZE or DBMS_STATS
Columns Updated by Analyze
Choosing Columns to Index
Determine Selectivity
Add an Index?
Utility Output Continued
Add Another Index?
Utilities Output for Dept_no
Multi-Column Indexes - 1
Multi-Column Indexes - 2
Which Column First?
Avoiding Table Access with Multi-Column Indexes
Another Example of Eliminating Table Access
Eliminate Redundant Indexes
Indexes vs. Full Table Scans
Indexes vs. Full Table Scans Factors
Forcing Full Table Scans
Eliminating Index Access
Indexes and Parallel Operations
Parallel Hint
Parellelize
What to Avoid when using an Index
Column Functions
Column Type Conversion
Additional Indexing Guidelines
When to Create
Where to Create
Use REBUILD
Use NOLOGGING
Introduction to Bitmap Indexes
Bitmap Indexes Example
Bitmap Benefits
Multiple Indexes - Merging
Nulls
When to Use Bitmap Indexes
When Not to Use Bitmap Indexes
Restrictions
Bitmap and the Data Dictionary
Bitmap INIT.ORA PARAMETERS
Index-Organized Tables
Reverse Key Indexes
Function-Based Indexes

Optimization

Optimization Overview
Rule-Based Optimizer
Cost-Based Optimizer
Enabling Cost-Based Optimization
Check the Optimizer Setting
Check Statistics Creation
ALTER SESSION
Specifying the Goal: FIRST_ROWS or ALL_ROWS
Generating Statistics
Analyzing with ESTIMATE
Syntax of ANALYZE
Statistics Updated by ANALYZE
Using the ANALYZE_SCHEMA Proc
Creating Histograms
Access Paths
Full Table Scans
Index Scans
Table Access by ROWID
Oracle7 ROWID Format
Oracle8 ROWID Format
More Table Access by ROWID
Optimizer Calculations for Equal Comparisons
Optimizer Calculations for Range Comparisons
Range Optimization Involving Bind Variables

Explain & TKPROF

Displaying Execution Plan Steps
Create the PLAN_TABLE
PLAN_TABLE Columns
The EXPLAIN PLAN Command
Explaining a Simple Query
Reading Execution Plans
Alternative Displays
SQL*Plus Autotrace
SQL*Plus Autotrace Example
Tracing SQL Statements
Enabling the Trace Facility
Execute TKPROF
TKPROF Syntax
TKPROF Sample Trace
TKPROF Report Notes
Library Cache
TKPROF Explain Output

Using Hints

Objectives
Using Hints
Hint Examples: Full scan
Hint Examples: Alias
Hint Examples: RBO
Hints: CBO
Hint Examples: Join Order
Hints: Spelling Counts
Syntax Notes
Optimization Approach and Goal Hints
Access Method Hints
Syntax of the INDEX Hint
Join Hints
When Hints will be ignored

Tuning SQL

Finding the Bad Queries
Adding Indexes
Consider Bitmap Indexes
Indexes vs. Full Table Scans
Forcing Full Table Scans
Avoiding Table Access with Multi-Column Indexes
Multiple Single Column Indexes
AND-EQUAL Operation
ORDER BY on Indexed Columns
EXISTS over DISTINCT
UNION ALL over UNION
Conditions To Avoid

Join and Subquery Access Paths

Join Operations
Nested Loops Join
Sort Merge Join
Hash Join
Join Hints
Additional Filters
Subquery Conversion

PL/SQL Tuning

Using PL/SQL to Improve Performance
PL/SQL Performance Improvements Illustration
PL/SQL Common Routines
PL/SQL in Memory
Explicit vs. Implicit Cursors
Defining Explicit Cursors
Using Implicit Cursors
Triggers
Efficient Trigger Coding
When Not to Use a Trigger
Procedures and Functions
Packages
SQL *PLUS Example
PL/SQL Example
PL/SQL Performance Problem
PL/SQL Performance Example
The Solution - Bulk Binding
Bulk Binding Keywords

Tuning 3GL Programs

Using Host Arrays
Host Array Declare Statement
Array Rules and Restrictions
SELECT INTO Statement
DECLARE CURSOR Statement
FETCH Results Notes
INSERTing Data
Updating Data
Deleting Data
Processing NULLs
Set Array Element Processing
Specifying Predicate Information
Processing Fetched Data
Communication Information
Embedding PL/SQL Programs
Embedding PL/SQL Programs Code
Final Tuning Tips

Oracle8i Optimizer Enhancements

Plan Stability Concepts
Implementing Plan Stability
DBMS_STATS Concepts
Using DBMS_STATS
Automated Statistics Gathering
Index Statistics
Types

Tuning Views

Mergeable Views
Hints & Mergeable Views
Non-Mergeable Views
Hints and Non-Mergeable Views

Summary of Tuning Tips

Conditions to Avoid
Use Hints
Using Array Processing
Using Array Processing Illustration
Oracle Tool Support for Array Processing
SQL*Plus Array Support
Use Sequences
Use Sequences Illustration
Assigning Specific Rollback Segments
Using SET TRANSACTION
SQL Performance Tips
Avoid Deadlocks
Deadlocks: Sequences
Pinning Objects in Memory
Pinning Objects Types

Hardware/Software Requirements

Hardware Requirements

Minimal Processor: Pentium 166 or Pentium 200
Recommended Processor: Pentium 233 or Pentium 266
RAM: 128 MB (256 MB recommended)

Oracle Corporation also recommends increasing virtual memory to 200 MB. (Modify it in the Performance tab of System Properties in the Control Panel.)

Available Disk Space (FAT or HTFS): 3 GB
Video: 256 color
A connection to the Internet is desirable (but not absolutely necessary) to show students various Oracle-related sites.

Software Requirements

Windows NT 4.0 or Windows 2000
Oracle8i 8.1.6 or above
Microsoft Internet Explorer 5 or Netscape Navigator 4.5
Adobe Acrobat Reader or Acrobat 4.0 or later (for the course presentation)*
Student Files
Text Editor*