Oracle Training : Oracle9i SQL Tuning for Developers & DBAs

This course will provide you with the skills necessary to tune Oracle SQL statements. An introduction to application tuning (e.g. avoiding blocking), PL/SQL tuning and Oracle Programmer tuning is also provided. Particular attention is paid to understanding Oracle architecture, cost-based optimization and to testing various SQL formulations with EXPLAIN and trace to find the best performing option. This course has been completely updated for Oracle9i Release 2. Working examples are included throughout the course book

Oracle Training Prerequisites

Programming experience with Oracle databases. This includes a strong Oracle SQL background as well as PL/SQL programming experience. This knowledge can be obtained via the following classes:

• Introduction to Oracle for Developers
• Advanced SQL Queries for Oracle9i Databases.

Oracle Training Course duration

3 days

Oracle Training Objectives

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

Successfully improve the performance of applications that depend heavily on access to Oracle by testing various SQL possibilities and choosing the most efficient option
Understand the purpose of the Oracle memory structures
Understand Oracle query architecture including parsing, cursors, the importance of bind variables, optimizer statistics and more
Query many "V$" dynamic performance views to aid your understanding of Oracle and a particular situation
Rewrite queries with the goal of finding a less expensive query
Use HINTs to influence optimizer choices
Choose which index type best fits for a given scenario, create, collect statistics and determine if the index helped your situation
Understand the Oracle optimization modes, and choose the best for your situation
Describe the various access paths used by Oracle
Use the EXPLAIN PLAN and V$SQL_PLAN to externalize access path choices made by the optimizer
Use the trace and TKPROF utilities to test potential solutions to find the least expensive and best performing solution
Create histograms to educate the optimizer about skewed data
Take advantage of parallel processing features
Use DBMS_TRACE and DBMS_PROFILER to trace and tune PL/SQL programs
Improve the performance of database triggers
Implementing array processing to improve the performance of #GL programs that access Oracle data

Oracle Training Course outline

Introduction to SQL Tuning
- What Can Be Tuned?
- What This Course Covers
- What We Need to Know
Architecture Overview
- Oracle Architecture
- The SGA
- Data Block Buffer Cache
- Sub-Caches
- Keep, Recycle Caches
- Redo Log Buffer
- Background Processes
- Alternative Oracle Architectures
SQL Statement Processing
- SQL Statement Processing
- Parse: Soft Parse
- Parse: Hard Parse
- Execute
- Fetch
- Shared SQL
- Use Bind Variables
- Excessive Re-Parsing
- CURSOR_SHARING
- Shared Pool Retention
- Shared Pool Memory Size Requirements
- Viewing Pool Size
- Is the Shared Pool Too Big?
- Monitoring Shared SQL Script
Indexes: Part I
- Index Usage
- B-Tree Illustration
- B-Tree Effect on Query Performance
- Creating an Index…
- Statistics Collection
- Choosing Columns to Index
- Selectivity
- Multi-Column Indexes
- Which Column First?
- Eliminating Table Access
- Monitoring Index Usage
- Eliminate Redundant Indexes
- Indexes and Parallel Operations
- Parallel Hint
- Parallelize
- Additional Indexing Guidelines
- When to Create
- Where to Create
- Using NOLOGGING
Indexes: Part II
- 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
EXPLAIN & Trace
- EXPLAIN Concepts
- Create PLAN_TABLE
- PLAN_TABLE Columns
- EXPLAIN Syntax
- Executing EXPLAIN
- Reading Execution Plans
- Alternative Displays
- V$SQL_PLAN
- Monitoring Plan Changes
- AUTOTRACE
- Trace and TKPROF
Optimization
- Optimization Concepts
- Rule-Based Optimizer
- Cost-Based Optimizer
- Enable CBO
- Check the Optimizer Setting
- Check Statistics Creation
- Statistics Collection and Management
- Using DBMS_STATS
- Statistics Updated
- Histograms
- Access Paths
- Full Table Scans
- Index Scans
- Table Access by ROWID
- Oracle ROWID Format
- Optimizer Calculations for Equal Comparisons
- Optimizer Calculations for Range Comparisons
- Range Optimization Involving Bind Variables
Using Hints
- Using Hints
- Hint Examples: Full scan
- Hint Examples: Aliases
- Hint Examples: RBO
- Hints: CBO
- Hint Examples: Join Order
- Hints: Spelling Counts
- Syntax Notes
- When Hints will be ignored
- Optimization Approach and Goal Hints
- Access Method Hints
- Syntax of the INDEX Hint
- Join Hints
Tuning SQL
- What Can We Do?
- What to Avoid when using an Index
- Column Functions
- Column Type Conversion
- EXISTS over DISTINCT
- NOT EXISTS vs. NOT IN
- UNION ALL over UNION
- Conditions To Avoid
- SQL Performance Tips
- SQL Tuning Tools Summary
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 Common Routines
- PL/SQL in Memory
- Procedures and Functions
- Triggers
- Efficient Trigger Coding
- When Not to Use a Trigger
- PL/SQL Performance Problem
- PL/SQL Performance Example
- The Solution - Bulk Binding
- Bulk Binding Keywords
- BULK COLLECT Example
- DBMS_TRACE
- DBMS_TRACE Subprograms
- Controlling Trace File Contents
- DBMS_TRACE Example
- DBMS_PROFILER
- DBMS_PROFILER Subprograms
- DBMS_PROFILER Example
- Native PL/SQL Execution
- Summary
Tuning GL 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
Tuning Views
- Mergeable Views
- Hints & Mergeable Views
- Non-Mergeable Views
- Hints and Non-Mergeable Views
Final Thoughts
- Use Sequences
- Use Sequences Illustration
- Avoid Deadlocks
- Deadlocks: Cause and Fix
- Deadlocks: Sequences
- Pinning Objects in Memory
- Pinning Objects Types
- Summary

Hardware/Software Requirements

Hardware Requirements

Minimal Processor: Pentium 166 or Pentium 200
Recommended Processor: Pentium 233 or Pentium 266
RAM: Minimum 128 MB (256 MB recommended)
Oracle Corporation also recommends increasing virtual memory to: MB.
- Minimum 200 MB, Maximum 400 MB
- Modify it in the Performance tab of System Properties in the Control Panel
Available Disk Space:
- FAT 4.5 GB for HOME Directory, 140MB for System Drive
- NTFS 2.75 GB for HOME Directory, 140MB for System Drive
- Temp Space - 400 MB
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 (SP5 or Higher) or Windows 2000 (SP 1 or higher)
Oracle9i Enterprise Edition. Oracle8i can be used, but some workshops will be omitted from the class.
A new install is strongly recommended if the existing Oracle install is on a freely accessible machine, such as classroom machine.
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*

	About us Contact us Careers at Wintrac Our Clients Why Wintrac	Register for a free training CD-ROM drawing Refer a client or instructor and earn $$$	Wintrac Inc. 16523 SW McGwire Ct. Beaverton OR 97007
© Wintrac, Inc. All rights reserved. Site Map \| Terms of Use \| Privacy Policy