Advanced SQL Tuning Tips and Techniques with Dan Hotka Version 2

This course will cover a variety of SQL statement tuning issues, controlling the Oracle optimizers, tips and techniques for good relational/application design to take advantage of Oracle's b-tree indexing structures, and advanced topics such as Oracle Trace, Stats Pack/AWR, and a variety of parameter settings that make a big difference to Oracle-based applications. One-forth of this workshop focuses on monitoring Oracle RDBMS and SQL via the v$ tables. This course is packed full of scripts to discover a variety of issues. This course also covers new features in Oracle10g /11g that will have an affect on your migrated applications. The focus of this tuning course is to cover advanced SQL performance tuning topics for those Oracle professionals that already have a SQL tuning background. This course is perfect for DBA’s that need the additional background to solve the more difficult problems

This course covers a number of advanced topics such as Index clustering factor, Oracle10g Tablespace options that affect index performance, various Oracle traces including the 10053 Cost-Based Optimizer trace and sizing the SORT_AREA_SIZE and HASH_AREA_SIZE using the 10053, 10033, 10034, and 10104 traces (and scripts to run them). Lectures include an in-depth look at various SGA structures such as buffer cache options and sizing, library cache and cursor sharing, and Oracle fragmentation and locking issues/data block internals. Students will also experience how to monitor their application environment, their indexes, and see how to find problem SQL in their applications using Stats Pack, SQL Trace, AWR, SQL Tuning Advisor, and the V$ dictionary tables (using a variety of supplied SQL*Plus scripts). 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

PLEASE NOTE: When you register for this event it will appear you are booking for an event on the first 2 days only. This is not an error but due to constraints within our booking system, please do proceed with your booking and you will in fact receive the access details for all 4 days.

A Live Virtual Class (LVC) is exclusively for registered students; unregistered individuals may not view an LVC at any time. Registered students must view the class from the country listed in the registration form. Unauthorized recording, copying, or transmission of LVC content may not be made.

Audience
Course Topics Day 1

• SQL Statement Tuning
• Oracle RDBMS Architecture overview
• Review Reading and Interpreting Explain Plans
• Understanding the Cost-based Optimizer

Day 2

• Advanced Index Review/Tips & Techniques
• Review CBO Hints
• Displaying internal hints Oracle considers

Day 3

• Advanced SQL Troubleshooting Tips and Techniques
• Review SGA Memory Structures
• Buffer Cache
• Library Cache - SQL Trace File Analysis
• Using TOAD (new features), TKProf, and SQL Developer
• Using SQL TXPlan (new free Oracle SQL analysis tool) - A close look at other useful Oracle Traces
• 10053 CBO Trace
• 10030 & 31 Sort Traces

Day 4

• Finding Problem SQL using v$ information
• Using Automated Workload Repository
• Review the SQL Tuning Advisor
• Review the usefulness of Database Replay
• PL/SQL Tuning Tips and the PL/SQL Profiler

Course Objectives