|
ORA05DEV-8i-02-SG
Oracle 8i SQL Statement Tuning (3 Days)
Description
This course is tailored for developers to write efficient, well tuned SQL statements. An in-depth discussion of the Oracle optimizer is considered including cost based optimization, optimization modes and altering behavior of the optimizer. Various utilities which assist in SQL statement tuning are also presented including EXPLAIN PLAN, TKPROF and AUTOTRACE. Special techniques such as histograms and specialty indexes are also considered.
Audience
Oracle developers
Mandatory Prerequisites
• Introduction To Oracle 8i SQL & SQL*Plus
• Introduction To Oracle 8i PL/SQL Language
• Oracle 8i Advanced SQL & SQL*Plus
• Oracle 8i Architecture For Developers
Suggested Next Courses
• Oracle Developer Form Builder
Objectives
• Examine the execution plan of the Oracle optimizer
• Alter the mode and goals of the optimizer
• Collect statistics for database objects
• Interpret and influence SQL statement execution plans
• Exploit Oracle 8i plan stability techniques
Course Outline
UNDERSTANDING THE TUNING ISSUES • OLTP vs. Data warehouses • Database tuning • Infrastructure & network issues
SQL STATEMENT EXECUTION • Parse phase • Execution phase • Fetch phase • Dedicated server processes • Multi-threaded server processes • Parallel query processes
EXPLAIN PLAN UTILITY • Generating the execution plan • Viewing the execution plan • Interpreting the execution plan
COST BASED OPTIMIZER (CBO) • Optimization methods • Collecting statistics • ANALYZE • DBMS_STATS() • Chained rows
COLLECTING STATISTICS • Statistics Collection Methods • Monitoring Object Modifications • Using the DBMS_STATS() Package • Using the ANALYZE Command • CREATE INDEX...COMPUTE STATISTICS
OPTIMIZER OPERATIONS • Table scan • Join operations • Hash operations
INDEXES & THE EXECUTION PLAN • B-tree indexes • Bitmap indexes • Function-based indexes
OPTIMIZER HINTS
USING TKPROF & AUTOTRACE
HISTOGRAMS • Creating Histograms • Data Dictionary Storage
USING PLAN STABILITY • Preparing for Stored Outlines • Creating & Using Stored Outlines • Data Dictionary Storage • Managing Stored Outlines
EDITING PRIVATE OUTLINES • Preparing for Private Outlines • Creating Private Outlines • Editing Private Outlines • Utilizing Private Outlines
COLLECTING SYSTEM STATISTICS • Managing System Statistics • Developing a Tuning Strategy
EXPLOITING & MANAGING CURSOR SHARING • Bind Variables & Cursor Peeking • Using the CURSOR_SHARING Parameter
UNDERSTANDING THE RULE BASED OPTIMIZER • About the RBO • Activating the RBO Explicitly • How the RBO Works • The Access Paths • RBO Join Execution Plan Selection
TUNING WITH THE RULE BASED OPTIMIZER • Correctly Building Indexes • Understanding When Indexes Will Be Ignored • Rewriting Queries to Avoid Index Suppression • Rewriting Queries to Force Index Suppression
|
|
|
|
