Oracle9i Application Tuning

Course Length: 3 days

This class will teach Oracle developers and DBAs how to performance tune applications for an Oracle9i system. Good design techniques, programming techniques and performance tuning practices will be emphasized throughout the course. Students will learn how Oracle processes SQL statements and makes decisions on how to execute SQL statements. Students will see how to performance tune SQL statements through numerous examples and labs. The last day will focus on customizing an Oracle session for OLTP and DSS applications and how to optimize PL/SQL applications.

Audience: Application developers and database administrators

Prerequisites: Introduction to Oracle9i. Previous working experience with Oracle is recommended. A solid understanding of SQL and PL/SQL is required. A base understanding of the operating system used for class (UNIX or NT) is needed as well.

Introduction to Performance Tuning

What Needs Tuning?
Who is Responsible for Tuning
Monitoring a System
Understanding Different Types of Applications
Developing a Tuning Methodology
Areas of Focus
Oracle Performance Tuning Tools
Primary V$ Views Used in Tuning by DBAs
Putting Together a Tuning Game Plan

The Oracle9i Server Architecture for Developers

Responsibilities of a Developer
What is an Oracle Server?
Server Architectural Overview
Review of Logical Storage Structures
Database Storage
The Standard Database Block Size
Different Block Sizes
Looking Inside an Oracle Data Block
Sizing the Database Block
Setting Physical Storage Characteristics
Chaining and Migration
The High-Water Mark
Setting PCTFREE and PCTUSED
How PCTUSED and PCTFREE are Used
Guidelines for PCTFREE and PCTUSED
Automatic-Segment Space Management
Table Statistics Generated with ANALYZE
Index Statistics Generated with ANALYZE
Determining the Number of Chained Rows
Finding Chained and Migrated Rows
Table Storage Statistics
Environmental Impacts on Design
Tablespaces
Separating Information
Logical Structural Design
Database, Control, and Redo Log Files
System Change Numbers
Oracle8i JVM
Aurora and MTS
Oracle's Enterprise Java Engine
Oracle8.0 - The Object-Relational Release
Oracle8 Size Ceilings
Oracle8i - The Internet Release
Oracle Architectures

Efficient Memory Utilization for Applications

The Database Buffer Cache
Buffer Cache Hit Ratios
Multiple DB Cache Buffer Pools (Oracle8i style)
The Buffer Pool Parameter
Defining Storage for Buffer Pools
Multiple Block Sizes
Generating Statistics
Caching Tables
Memory Pool Areas
The Shared Pool Area
Sizing the Shared Pool Area
Library Cache
Library Cache Utilization
Evaluating Statements in the Library Cache
Pinning Large Objects
Tuning the Dictionary Cache
The Large Pool Area
Putting it All Together
Program Global Areas
User Global Area
Shared SQL Areas
The Parse Phase
Execution Plan Phase
Fetch Phase
Processing Cursors
Processing DML Statements
Sharing Cursors

Introduction to Tuning SQL Statements

SQL Tuning
Oracle Optimizers
The Rule-Based Optimizer
The Cost-Based Optimizer
Rule-Based Versus Cost-Based Optimization
Setting the Optimizer Mode
SQL Hints
Generating Statistics
The ANALYZE Command
DBMS_STATS
DBMS_STATS Routines
Copying Statistics to Different Databases
Monitoring Tables
Computing Statistics (Review)
Additional Cost-Based Optimizer Considerations
Stored Outlines
Creating Stored Outlines
OUTLN_PKG Package

Explain Plan and Autotrace

Explain Plan Utility
The PLAN_TABLE Columns
Resource Columns in the PLAN_TABLE
Explain Plan - Syntax
Explain Plan Displaying Output
Explain Plan Operation Definitions
Autotrace - Configuration
AUTOTRACE - GRANTING PLUSTRACE ROLE
AUTOTRACE - Turning on Autotrace
AUTOTRACE - Customizing the Report

SQL Trace and TKPROF

SQL Trace Utility
Steps for Utilizing the SQL Trace Utility
SQL Trace INIT.ORA Parameter Definitions
V$PARAMETER
Enabling SQL Trace
Enabling SQL Trace - At Session Level
Enabling SQL Trace - Instance Level
Enabling SQL Trace - DBMS_SESSION
Enabling SQL Trace - DBMS_SYSTEM
Disabling SQL Trace
Running TKPROF
TKPROF Parameter Definitions
TKPROF SORT Parameter Options
TKPROF - Interpreting the Results

Tuning Joins and Subqueries

Join Operations and Types
Simple Join
Multi-Table Join
Outer Join
Self Join
Cartesian Join
Correlated Subquery Join
Join Optimization Methods
Nested Loops Join
Hash Join
Sort Merge Join
A Star Schema
A Snowflake Schema
Data Warehouse Schema Objects
Optimizing Star Joins
Execution Plan of Star Query
Bitmap Join Indexes
Multiple Bitmap Join Indexes - How They Work
Multi-Table Joins
ORDERED Hint
Tuning Subqueries
Correlated Subqueries Using EXISTS and NOT IN
Additional Guidelines for Subqueries

Hints

Hints
Hint Syntax
RULE Hint
CHOOSE Hint
FIRST_ROWS Hint
ALL_ROWS Hint
FULL Hint
ROWID Hint
CLUSTER Hint
HASH Hint
HASH_AJ
HASH_SJ Hint
INDEX Hint
INDEX_ASC Hint
INDEX_COMBINE Hint
INDEX_JOIN Hint
INDEX_DESC Hint
INDEX_FFS Hint
NO_INDEX Hint
MERGE_AJ Hint
MERGE_SJ Hint
AND_EQUAL Hint
USE_CONCAT Hint
NO_EXPAND Hint
REWRITE/NOREWRITE Hint

Managing Large Tables

Oracle Size Ceilings
Managing Large Tables with Oracle7
Oracle Data Storage
What is Partitioning?
Range-Partitioning
Partitioning Features
Advantages of Partitioning
Creating a Range-Partitioned Table
The Partition Key
Partition Syntax
Accessing a Partitioned Table
Data Dictionary Views
Using MAXVALUE
Partitioning Rules to Follow
Partition Extended Table Name Syntax
Restrictions on Partition Extended Name
Inserting into a Multi-Column Partition Key
Multi-Column Partition Keys
Setting Physical Attributes for Partitions
Hash Partitioning
Defining Tablespaces for Hash Partitions
Composite Partitioning
Creating a Composite-Partitioned Table
Subpartitioning
Getting Information About Composite Objects
List Partitioning
Index-Organized Tables
Global Temporary Tables

Managing Large Indexes

Indexes on Partitioned Tables
GLOBAL Prefixed Indexes
Create a Global Prefixed Index
Local Prefixed Indexes
Create a Local Prefixed Index
Tblspces4.sql Script
Local Non-Prefixed Indexes
Composite-Partitioned Indexes
Reverse Key Indexes
Improvements to Bitmap Indexes
Function-Based Indexes
Index Rebuilds ONLINE
Rebuilding Indexes Online
Additional Index Features

Session Tuning

DB_FILE_MULTIBLOCK_READ_COUNT
Managing Sort Operations
Sort Operations
Tuning Sorts
The SORT_AREA_SIZE Parameter
Allocating Sort Memory Space
The SORT_AREA_RETAINED_SIZE
Memory versus Disk Sorts
Temporary Tablespaces
Tuning Parallel Query
Parallel DML
Parallel DML Restrictions

Tuning Application Code

Working with Object Types and Collections
Bulk Binds
Bulk Collect Into Clause
Use the RETURNING Clause
NOCOPY Hint
Memory and PL/SQL
Pinning PL/SQL Programs
KEEP Procedure
Relative Sizes of Code to Pin
Serially Reusable Packages
Optimizing Integer Operations
Avoid NOT NULL Constraints
Rephrase Conditional Control Statements
Additional Considerations
The DBMS_TRACE Package and Constants
Managing the Volume of Tracing Information
The DBMS_DEBUG Package
Steps to Setup a Debug Session
Setting Up a Debug Session
DBMS_PROFILER
Profiler Tables
Table PLSQL_Profiler_DATA
Java Stored Procedures
Java and PL/SQL
Java Stored Procedures
Java Database Programs
Running a Java Stored Procedure

Dynamic SQL

Executing Dynamic SQL Statements in PL/SQL
Advantages of Dynamic SQL & DBMS_SQL
DBMS_SQL Package
Advantages of Native Dynamic SQL in PL/SQL
Syntax: EXECUTE IMMEDIATE Statement
Bind Variables
Defining Modes for Bind Variables
Executing DML, DCL, and DDL Statements
Executing Dynamic Multi-row Queries
Using Schema Objects with Dynamic SQL
Enhancements to Bulk Operations
Bulk FETCH
Bulk FORALL with EXECUTE IMMEDIATE
Cursor Attributes
Assigning Nulls to Bind Variables
Issues When Executing Subprograms

Data Warehousing Features

What is a Materialized View?
Creating a Materialized View
Dimensions
Create Dimension Syntax
Hierarchies
Create a DIMENSION with Hierarchies
Attributes
Normalized Dimensions
Managing Dimensions
Alter Dimension Syntax
Considerations for Building Dimensions and Hierarchies
Query Rewrites
Enabling Query Rewrites
Creating the Materialized View
Materialized View Options
REFRESH Options
CREATE MATERIALIZED VIEW
Materialized View Log
Create Materialized View Log Syntax
Materialized View Log Setup
Create Materialized View Logs
Create Final Materialized View Log
Constraints on Views
Creating a Constrained View
ROLLUP Operators
Analytical Functions
Top-N Analysis