COURSE DESCRIPTION: This is a 3-day class (6-hours per day) that includes lecture, lab and testing for up to 12 students.
Attendees will learn coding techniques that insure a consistent response time between instances and releases of the Oracle database. This course focuses on performance tuning of actual SQL statements via coding style. The instructor finds that tuning in this fashion maintains the performance of the SQL when upgrading Oracle.
The attendees will work with a variety of SQL statements and tune them by making coding changes based on their review of the explain plans, modifying indexes and using hints and coding style to control the explain plans. They will also learn how to use tools such as index monitoring, SQL Trace and the PL/SQL profiler. They will be instructed on how Oracle selects indexes, why they are sometimes not used and how to tell which indexes are being used or not.
Lectures and topics are enhanced with live illustrations and hands-on exercises.
Oracle Architecture from a SQL Performance point-of-view
Understanding SQL Tuning Statement Topics
Reading Explain Plans/Understanding Explain Plans
Controlling both the Cost-based and Rule-based Optimizers
A close look at Indexes – how they work and how they are selected
SQL Tuning via coding style
A review of how Oracle computes row cardinality
Important CBO Statistics review
Oracle Trace Facility – collecting SQL and interpreting using TKProf and TOAD’s Trace Analyzer
Quest SQL Optimizer
Profiling PL/SQL, PL/SQL Coding Tips
Introduction to Explain Plans and Hints.
Introduction to Cost-Based Optimizer.
Introduction to Tuning Tools: SQL Tracing & TKProf
Learning SQL Coding Tips.
Learning PL/SQL Coding Tips.
AUDIENCE: The focus of this course is to give SQL and PL/SQL developers the knowledge and experience so that they can tune their statements and scripts for optimal performance.
PREREQUISITES: Working knowledge of SQL.
ATTENDEES RECEIVE: o Study Guide with presentations and relevant white papers o Example files of tuning and problem discovery scripts
COURSE OUTLINE: Day 1: SQL Statement Tuning o Oracle RDBMS Architecture overview o Explain Plans explained - Understanding - Reading - Interpreting o Understanding the Cost-Based Optimizer (CBO) o Understanding Parallel Explain Plans o Working with Hints o Indexes - Review - Tips and Techniques
Day 2: Sub-Queries, Coding Tips, Profiling, Tuning o Sub-Query Coding Techniques o SQL Coding Tips o WHERE Clause and controlling Cardinality o CBO Statistics
Day 3: Tuning o Hints Tips and Techniques
o Finding Problem SQL o Tuning Tools - SQL Tracing - TKProf o Profiling and tuning PL/SQL o PL/SQL Coding Tips - Tips and Techniques