OVERVIEW
The course starts out with a complete overview of the Oracle architecture so students can get an understanding how their SQL and applications can take advantage of the computing environment. This course goes in-depth on understanding and controlling the explain plan (how Oracle retrieves data and in what order). The discussions include the differences of the various Explain Plan steps such as Merge-Join and Nested-Loop, and when is it best to use each. There are detailed SQL examples, on how the optimizers (both rule-based and cost-based but mostly cost-based) make their decisions. Students will work with a variety of SQL statements, reviewing explain plans and making changes to make these SQL statements perform better. Lectures include index design, using hints and coding style to control the explain plans, and how to use useful tools such as index monitoring, SQL Trace, and the PL/SQL profiler. This course takes a close look at indexes: how Oracle selects them, why they are sometimes not used, and how to tell if indexes are being used/not being used.
The focus of this tuning course is for developers and to illustrate coding techniques that insure a consistent response time between instances and releases of the Oracle database. This course works closely with performance tuning of actual SQL statements.
DURATION: 2 days
PRE-REQUISITES:
- 1 to 3 years application’s development using Oracle SQL
- Working knowledge of Oracle RDBMS
- Working with applications that join millions of rows of data
- Familiar with SQL*Plus and at least one other Oracle related tool such as TOAD
Topics:
- Oracle Architecture from a SQL Performance point-of-view
- Understanding SQL Tuning Statement Topics
- Reading Explain Plans/Understanding Explain Plans (main focus)
- 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
- Oracle Trace Facility – collecting SQL and interpreting using TKProf
- Profiling PL/SQL, PL/SQL Coding Tips
Attendees Receive:
- Study guide with presentations and relevant white papers
- Diskette full of tuning and problem discovery scripts
- Opportunity to ask the tough Oracle questions
- A hands-on opportunity to learn more about Oracle, SQL*Plus, and TOAD
Course Outline:
Day 1: Explain Plan Review
- Oracle RDBMS Architecture overview
- Understanding/Reading/Interpreting Explain Plans
- Understanding the Rule-based Optimizer
- Understanding the Cost-based Optimizer
- Working with Hints
- Lab: Working with Explain Plans, running Statistics, and working with Optimizer Goal/Join Order hints
Day 1: Explain (continued) Index Review
- Lab: Working with Explain plans
- Index Review/Tips & Techniques
- Lab: Improving SQL performance utilizing a variety of Indexes, using Index hints
Day 2: SQL Coding Style Review
- A close look at sub-query coding techniques
- SQL Coding Tips
- Lab: Recoding SQL to utilize coding tips, using Sub Query hints
Day 2: Tuning Tool Review
- SQL Tracing/&Tkprof
- Profiling and tuning PL/SQL
- PL/SQL Coding Tips
- Group Exercise on using PL/SQL Profiler