Oracle SQL Tuning

Course Length - 4 half-days

This course is designed to give the SQL Developer or DBA a solid understanding of the SQL tuning methods and techniques available in Oracle while learning how to write efficient SQL statements. The students will learn about developing a tuning methodology as well as what proactive and reactive tuning methods are available. They are also introduced to the benefits of the automatic tuning features available in Oracle.

Hands-on exercises are used to reinforce the student’s knowledge throughout the course. Students will diagnose and tune SQL statements and discuss their results.

Who Should Attend

SQL Developers and Database Administrators (DBA)

Outline

Using a Tuning Methodology

• Listing and Describing the Causes of SQL Performance Problems
• Identifying SQL Performance Problems
• Prioritizing Potential SQL Performance Problems
• Developing a Tuning Methodology

Database Architecture and Processing SQL

• Database Architecture related to Processing SQL
• Memory Structures
• Background Processes
• Server Process
• Client Processes
• Database Files
• Database Objects
• Blocks
• Listing the SQL Statement Processing Steps
• Parsing
• Syntax Check
• Semantic Check
• Hard Parse vs. Soft Parse
• Identifying Means to Minimize Hard Parsing
• SQL Coding Standards
• Identifying Identical Statements
• The CURSOR_SHARING Parameter
• EXACT, SIMILAR, FORCE Values
• Using Bind Variables
• Sizing the Shared Pool Memory Structure
• Pinning SQL in the Shared Pool
• Monitoring SQL Usage and Parsing
• Data Dictionary Views
• V$SQL, V$SQLAREA, V$SQLTEXT
• V$SYSSTAT, V$SESSTAT

The Optimizer

• Describe the functions of the Oracle optimizer
• Rule-Based (Desupported in 10g) Optimizer
• Cost-Based Optimizer
• The Cost-Based Optimizer’s Processing Steps
• Set the optimizer approach at the instance and session level
• The OPTIMIZER_MODE Parameter
• ALL_ROWS vs. FIRST_ROWS
• Performance Statistics
• Gathering Statistics
• The DBMS_STATS Package
• Important Programs
• The ANALYZE Command
• Monitoring Statistics
• The ALL_TABLES and ALL_INDEXES Views
• Important Columns
• Dynamic sampling
• Influencing the Optimizer
• Hints and Hints Usage
• Trace Event 10053
• The Optimizer’s Decision Path

Indexes

• Index Types
• Unique and Non-Unique B-Tree
• Function-Based
• Bitmap
• Bitmap Join
• Reverse Key
• Cluster
• Local Partition
• Global Partition
• Pros and Cons of each index type
• Identifying Row Access Methods
• Creating B-Tree Indexes
• Understanding B-Tree Index Access and Index Merging
• Index Skip Scanning
• Inefficient Indexes and Monitoring
• How DML affects Indexes
• Negating Index Usage
• Monitoring for Unused Indexes
• ALTER INDEX …MONITORING USAGE;
• The V$OBJECT_USAGE View
• Index Do’s
• Index Don’ts

Access and Join Methods

• Index Access Paths
• FULL Table Access Path
• Join Methods
• Nested Loop
• Sort-Merge
• Hash
• Pros and Cons of Each Join Method

Hints

• Hints Usage
• Hint Types
• Optimizer
• Access Path
• Join
• Miscellaneous

Block Size Considerations for SQL Performance

• Goal: Minimize Blocks Read
• Standard and Non-Standard Block Sizes
• Setting up Non-Standard Block Size Usage
• Associating Tables with Different Block Sizes

SQL Tuning Tools and Utilities

• EXPLAIN PLAN
• Using EXPLAIN PLAN
• The PLAN_TABLE Table
• The UTLXPLAN.SQL Script
• Viewing EXPLAIN PLAN Output
• Scripts
• Using the DBMS_XPLAN Package
• The V$SQL_PLAN View
• Interpreting EXPLAIN Output
• What to Look For
• Using AUTOTRACE
• Interpreting AUTOTRACE Output
• What to Look For

• The SQL Trace Facility
• Setting Up the Appropriate Initialization Parameters
• Formatting Trace Files with TKPROF
• Interpreting the Output of the TKPROF Command
• The TRCSESS Utility

The SQL Tuning Advisor

• SQL Tuning Advisor Usage
• The Automatic Workload Repository
• SQL Tuning Sets
• Advisor Steps
• Create a Task
• Execute a Task
• Report Task Results
• Advisor Views
• DBA_ADVISOR_*
• The DBMS_SQLTUNE Package
• Usage
• Using Enterprise Manager