This hands-on course will provide you with the skills necessary to create scalable, high performance SQL-based Oracle applications (proactive SQL tuning). You will also learn how to identify existing high-load, poor performing SQL statements and tune them (reactive SQL tuning).
Particular attention is paid to making the best use of Oracle architecture, bench-marking various SQL formulations and identifying the best schema object (e.g. heap table, IOT, etc.) for a given situation.
Oracle10g introduces "automatic tuning" features such as the Automatic Workload Repository, Automatic Database Diagnostic Monitor and SQL Tuning Advisor. These features are covered in this class.
The course also includes a lesson on tuning PL/SQL programs. This includes using bulk processing and profiling to identify high-load statements.
Working examples are included throughout the course book. Students have access to electronic versions of all sample scripts and lab solutions. Approximately 50% of class time is spent on hands-on exercises.
As with all of our courses, this class is highly customizable to your specific training requirements. Hands-on workshops constitute approximately 50% of the class.
Audience: Application developers and database administrators. Analysts with strong Oracle SQL skills will also benefit from this course. This course is n ot intended for end-users.
Prerequisites: Solid experience with Oracle SQL. PL/SQL programming is required to understand the lesson on PL/SQL tuning. This knowledge can be obtained via the following SkillBuilders' courses:
Introduction to Oracle for Developers - The skills taught in the introductory class are required for successful completion of this Tuning course.
Advanced SQL Queries for Oracle10g databases - The skills taught in this Advanced course are not required, but helpful. For example, prior knowledge of the Oracle Analytic functions will help you when you learn to improve the performance of a complex join by converting it into an analytic function call.
PL/SQL Programming Workshop - Required to successfully complete the PL/SQL Tuning lesson
Next Courses: Effective Data Warehousing in Oracle, Advanced SQL Queries for Oracle10g Databases, Oracle10g Database Administration.
Objectives: After successfully completing this course, you will be able to:
Successfully improve the performance of SQL-based applications
Have a solid understanding of Oracle architecture. Specifically, you will:
Understand the purpose of the Oracle primary memory structures (e.g. SGA, PGA)
Understand Oracle query processing technology including parsing (soft parse, hard parse), cursors, the importance of bind variables, optimizer statistics and more
Understand Oracle lock architecture, and code applications that do not impede scalability
Determine what type of data storage object is best for your application. Specifically, you will:
Understand the various data storage options (e.g. heap, index organized, cluster, sorted hash cluster, etc) and benchmark which provides the best performance for your situation
Choose which index type best fits for a given scenario
Use the latest tuning techniques including:
Use the EXPLAIN PLAN and V$SQL_PLAN to externalize access path choices made by the optimizer
Use SQL Trace and V$SQL_PLAN_STATISTICS to find the least expensive and best performing solution
Query many "V$" dynamic performance views to aid your understanding of Oracle and a particular situation
Describe the various access paths used by Oracle, and usually understand why the optimizer chose one access path over another
Rewrite queries with the goal of finding a less expensive query (note that to some degree this objective relies on your preexisting knowledge of Oracle SQL)
Identify queries that are blocked and find the source of the blocking lock
Use HINTs to influence optimizer choices
Create histograms to educate the optimizer about skewed data
Take advantage of parallel processing features
Know how to use the 10g-specific automatic SQL tuning features
Know when to implement parallel query and parallel DML operations
Implement the Oracle plan stability feature
Tune PL/SQL programs including (but not limited to):
Using DBMS_PROFILER and DBMS_TRACE to identify areas within the code that deserve your attention
Use bulk processing to improve performance
Avoiding inefficient lookups
Coding pipelined table functions
Topic Summary
Introduction to Oracle SQL Tuning
What Can Be Tuned?
What You Need to Know to Tune Oracle SQL
Workshop: Setup
SQL Statement Processing
Oracle10g Architecture
The SGA
SQL Statement Processing Overview
Soft Parse
Hard Parse: Optimization
Row Source Generation
Execute Step
Fetch Step
Workshop
Viewing The Execution Plan
The EXPLAIN PLAN Command
PLAN_TABLE Columns
Reading Execution Plans
Retrieving the Historical Plan: V$SQL_AREA
Using SQL*Plus Autotrace
Workshop
Using SQL Trace and TKPROF
Trace Concepts
Trace Steps
Trace-Related Initialization Parameters
Enable Trace
Find the Trace File
Using the TKPROF Utility
Trace Analysis Tips
Workshop
The Importance of Bind Variables
Shared Pool Reuse
Bind Variables
Querying V$SQL
PL/SQL Bind Variables
Cursor Sharing
Workshop
Getting to Know Cost Based Optimization
Optimization Concepts
Rule-Based Optimization (Deprecated in 10g)
Enabling CBO
Checking the Optimizer Setting
Collecting Object Statistics
Tweaking CBO
Collecting System Statistics
Creating Histograms
Dynamic Sampling
Optimizer Calculations for Equal Comparisons
Optimizer Calculations for Range Comparisons
Bind Variable Peeking and Range Optimization Involving Bind Variables
Workshop
Learning the Access Paths
Full Scans
Multi-Block Reads
Forcing Full Table Scans
High-Water Mark
Large Deletes and the HWM
When is a Full Scan Bad?
Index-Based Scans
Unique, Range, Full and Fast-Full Index Scans
Index Skip Scan
Workshop
Using Hints
What are Hints?
When to Use Hints
Hint Examples
Some Syntax Notes
Workshop
Schema (Data Storage) Options
Heap Tables
Index Organized Tables
Index Clustered Tables
Hash Clustered Tables
Sorted Hash Clusters (10g)
Materialized Views
Temporary Tables
Nested Tables
Object Tables
Workshop
Indexes, Part I - All About B-Tree Indexes
Index Concepts
B-Tree Index Concepts
B-Tree Effect on Query Performance
Creating B-Tree Indexes
Update Statistics with DBMS_STATS
Impact of Clustering Factor
Choosing Columns to Index
Calculating Selectivity
Effect of Indexes on ORDER BY Columns
When to Avoid Adding an Index
Multi-Column Indexes
Which Column First?
Oracle9i Skip Scan
Avoiding Table Access with Multi-Column Indexes
Multiple Single Column Indexes
When and Where to Create Indexes
What to Avoid when using an Index
Finding Unused Indexes with MONITORING
Additional Indexing Guidelines
Workshop
Indexes, Part II - Introduction to Other Index Types
Bitmap Indexes
Reverse Key Indexes
Function-Based Indexes
Bitmap Join Indexes
Workshop
Using Oracle10g Advisors to Tune SQL
Introduction to Automatic Workload Repository Concepts
