Advanced SQL Tuning Tips and Techniques with Dan Hotka Version 2
Currently, no providers in North America have scheduled a date for this course. We can schedule a class at one of our regional offices or on-site at your location.
- Vendor:
- Oracle Corporation
- Course:
- D58760_1497054
- Start Date:
- Unscheduled
- Length:
- 2 Days
- Location:
- Expired Class Date
- Tuition:
- $1,600
No Dates
Scheduled
Course Description
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 problemsThis 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 usedPLEASE 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.
Prerequisites
- SQL tuning working knowledge of the SQL language
- SQL Explain Plans is helpful and will be reviewed
Syllabus
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
