Oracle SQL Performance Tuning Tips & Techniques using TOAD

4 Half-Day Training Course for Developers

Instructor-led training at your desk! Yes, the future is here today. Morning lectures allow you to receive valuable training while having the rest of the day to perform your regular job tasks. This course is complete with the same instructor-led training lectures, same lab exercises, and same training experience, as you would receive with classroom training. The course guide also includes applicable related white papers.

This course is designed for the Oracle professional with novice or no SQL tuning skills.

The lectures will be 10am till 1:00pm Central Time Zone USA, Monday thru Thursday. Morning lectures allow the attendees to attend this valuable training as well as perform their daily job tasks. The lecture slides and voice will be via the web. A dial-in phone number will be provided for those who need it. The course is completely interactive. Students can ask questions at any time. Labs are generally at the end of the lecture and the instructor will be available for Q & A and lab assistance after the lecture. Lab solutions will always be reviewed. The course guide will be provided electronically and students can print a copy if they desire a hard copy. The course guide contains all the lecture slides and instructor notes, the labs, lab solutions, and relevant white papers.

The focus of this tuning course is 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.

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. Dan goes into considerable detail, with 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 this course is tuning SQL via coding style. The instructor finds that tuning in this fashion maintains the performance of the SQL when migrating to other Oracle environments (upgrades to newer releases).

Lectures and topics are enhanced with live illustrations and hands-on exercises.

Topics covered includes:

  • 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
  • Oracle Trace Facility – collecting SQL and interpreting using TKProf
  • Profiling PL/SQL, PL/SQL Coding Tips

Lectures and topics are enhanced with live illustrations and hands-on exercises

Attendees Receive:

  • Study guide with presentations and relevant white papers
  • Zip file full of tuning and problem discovery scripts
  • Opportunity to ask the tough Oracle questions
  • A hands-on opportunity to learn more about Oracle

Course Outline:

Day 1: SQL Statement Tuning

  • Oracle RDBMS Architecture overview
  • Understanding/Reading/Interpreting Explain Plans
  • Understanding the Rule-based Optimizer
  • Understanding the Cost-based Optimizer
  • Working with Hints

Day 2: Index Review

  • Index Review/Tips & Techniques

Day 3: Helping the CBO

  • A close look at sub-query coding techniques
  • Plan Stability
  • Histograms
  • SQL Coding Tips

Day 4: Oracle Tuning Tools

  • Tuning Tool Review: SQL Tracing/Tkprof
  • Profiling and tuning PL/SQL
  • PL/SQL Coding Tips

The Training Room

The class uses Cisco WebEx software.

  • Access to an Oracle10g or Oracle11g database instance. This class uses a variety of table objects. 100mb disk space will be the maximum room required. Lab setup scripts supplied. About any version of TOAD or SQL Developer but the latest version is preferred. Download instructions and temporary license keys are provided.

For more information about scheduling one of these courses, please call us at: (800) 887-4513 , or e-mail us at: info@perftuning.com