Oracle8i Application Tuning

Course Length: 3 days

This class will teach Oracle developers and DBAs how to performance tune applications for an Oracle8i 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 Oracle8i. 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 Oracle8i Server Architecture for Developers

Responsibilities of a Developer
What is an Oracle Server?
Oracle Versioning
Server Architectural Overview
Operating System Environment
Oracle Instance Components
Multiple DB Cache Buffer Pools
Caching Tables
Logical Storage Structures
Tablespaces
Logical Structure Design
Database, Control, and Redo Log Files
Oracle8i JVM
Aurora and MTS
Oracle's Enterprise Java Engine
Oracle8.0- The Object-Relational Release
Oracle8 Size Ceilings
Oracle8i The Internet Release

Efficient Memory Utilization for Applications

The Database Buffer Cache
Buffer Cache Hit Ratios
Multiple DB Cache Buffer Pools
The Buffer Pool Parameter
Defining Storage for Buffer Pools
Calculating Buffer Pool Sizes
Caching Tables
Memory Pool Areas
The Shared Pool Area
Tuning the Dictionary Cache
Program Global Areas
User Global Area
The Parse Phase
Execution Plan Phase
Fetch Phase
Processing DML Statements
Library Cache
Sizing the Shared Pool Area
Evaluating Library Cache Utilization
Pinning Large Objects
Evaluating Statements in the Library Cache
Sharing Cursors
The Large Pooled Area

Tuning SQL Statements

SQL Tuning
Cost-based Optimizer Enhancements
The Rule-Based Optimizer
Rule vs Cost-Based Optimizer
Setting the Optimizer Mode
SQL Hints
Generating Statistics
The ANALYZE Command
DBMS_STATS
Copying Statistics to Different Databases
Monitoring Tables
Stored Outlines

Explain Plan and Autotrace

Explain Plan Utility
Explain Plan Table
PLAN_TABLE Column Definitions
Explain Plan - Syntax
Explain Plan Displaying Output
Explain Plan Operation Definitions
Autotrace - Configuration

ROLE

Customizing the Report Example

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
Disabling SQL Trace
Running TKPROF

Tuning Joins

Join Types
Simple
Multi Table
Outer
Self
Cartesian
Correlated sub query
Nested Loops
Sort Merge
Hash

Hints

Hint Syntax

Managing Large Tables

Oracle8 Size Ceilings
Managing Large Tables with Oracle7
Oracle8 Data Storage
What is Partitioning?
Range-Partitioning
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
Restrictions on Partition Extended Name
Multi-Column Partition Keys
Inserting into a Multi-Column Partition Key
Modifying Partition Key Values
Setting Physical Attributes for Partitions
Hash Partitioning
Defining Tablespaces for Hash Partitions
Composite Partitioning
Creating a Composite-partitioned Table
Subpartitioning
Getting Information on Composite Objects
Index-Organized Tables
Restrictions on Index-Organized Tables
Temporary Tables
Managing Large Indexes
Different Types of Indexes
GLOBAL Prefixed Indexes
Create a Global Prefixed Index
Local Prefixed Indexes
Create a Local Prefixed Index
Local Non-Prefixed Indexes
Composite-Partitioned Indexes
Reverse Key Indexes
Improvements to Bitmap Indexes
Function-Based Indexes
Index Rebuilds 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
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
Additional Considerations
The DBMS_TRACE Package
Managing the Volume of Tracing Information
The DBMS_DEBUG Package
Steps to Setup a Debug Session
DBMS_PROFILER
Profiler Tables
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
More on the 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
Cursor Attributes
Assigning Nulls to Bind Variables
Issues When Executing Subprograms
O8i Data Warehousing Features
What is a Materialized View?
Creating a Materialized View
Dimensions
Hierarchies
Normalized Dimensions
Managing Dimensions
Alter Dimension Syntax
Building Dimensions and Hierarchies
Query Rewrites
Materialized View Options
REFRESH Options
CREATE MATERIALIZED VIEW
Materialized View Log
New ROLLUP Operators
Analytical Functions
Top-N Analysis
Object Relational Concepts
Object-Relational Databases
New Object-Relational Features
New Oracle8i Object-Relational Features
New Oracle8i Data Types
Abstract Data Types
Data Dictionary Views
Review: CREATE TYPE Command
Object Tables
Type Methods
The Constructor Method
Working with User Defined Datatypes
Modifying Data in Object Tables
Working with Object Tables
Object Identifiers
Defining a REF
Updating REF Values
INSERTING REF Values
Dangling REFS
The DEREF Operator
ROWIDs
DBMS_ROWID Package
Different Types of Rowids
Collections
Create a Type Specification
The TABLE Operator
LOBS