Oracle SQL Performance Tuning Tips & Techniques

 

 

 

 

 

 

Course name: Oracle SQL Performance Tuning Tips & Techniques

Date: 14th & 15th May 2018 (2 Days)

Venue: Professional Training Solutions, Building 6500 E, Cork Airport Business Park

Training provider: Professional.ie (Dan Hotka, US Expert, see bio below)

Member fee: €500 (or  €1000 for this course plus the 3 day Advanced Oracle PL/SQL tips & Techniques, 16th to 19th April 2018)

Non-member fee: €800

 

To register your interest or to book a place on this course please email Annette Coburn at skillnet@itcork.ie

 

Description:

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.  This course includes Oracle10, Oracle11, and Oracle12 SQL tuning topics.  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 discussion includes 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.

Prerequisites:

A working knowledge of SQL. A knowledge of SQL Explain Plans is helpful but not necessary.

Topics covered:

  • Oracle10, Oracle11, and Oracle12! • 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 • 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 • 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 Developer, and TOAD

 

Day 1:

– Oracle RDBMS Architecture overview – Understanding/Reading/Interpreting Explain Plans – Understanding the Cost-based Optimizer – Understanding Parallel Explain Plans – Working with Hints – Index Review/Tips & Techniques

Day 2:

– A close look at sub-query coding techniques – SQL Coding Tips – Where Clause and controlling Cardinality – A close look at CBO statistics – Hint Review/Tips & Techniques – Tuning Tool Review: SQL Tracing/Tkprof – Profiling and tuning PL/SQL – PL/SQL Coding Tips

 

About Dan Hotka:

Dan Hotka is a Training Specialist and an Oracle ACE Director who has over 35 years in the computer industry, over 29 years of experience with Oracle products. His experience with the Oracle RDBMS dates back to the Oracle V4.0 days. Dan enjoys sharing his knowledge of the Oracle RDBMS. Dan is well-published with 12 Oracle books and well over 200 printed articles. He is frequently published in Oracle trade journals, regularly blogs, and speaks at Oracle conferences and user groups around the world. Dan Hotka – Author/Instructor/Oracle ACE Director

 

To register your interest or to book a place on this course please email Annette Coburn at skillnet@itcork.ie