Advanced Oracle SQL Performance Tuning – ORA720
The focus of this three day Oracle SQL Performance 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.
Advanced Oracle SQL Performance Tuning – ORA720 – 3 days Request a Class Date
The focus of this Oracle SQL Performance 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.
Course ID: ORA720 Duration: 3 days
Audience: Oracle SQL Developers,Oracle DBAs
This course covers a number of advanced Oracle SQL topics such as Index clustering factor, Oracle 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. 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.
Advanced Oracle SQL Performance Tuning General Topics
- Oracle architecture from a SQL performance point-of-view
- Review of reading explaining plans/understanding explains plans
- Review of CBO hints
- A close look at Indexes Oracle trace facility/stats pack/wait events
- Tracing wait events back to the problem SQL
- Oracle AWR and new tools such as SQL T
- Profiling PL/SQL, PL/SQL coding tips
- SGA topics
- Buffer cache options and sizing
- Library cache utilization/internals included cursor-sharing topics
- Oracle 11g result cache
- Oracle 10g/11g tablespace options that affect indexes
- Oracle data black internals and fragmentation reviewed
- Finding various problems using V$ dictionary tables with scripts
Advanced Oracle SQL Performance Tuning Training Topics
- Oracle RDBMS architecture overview
- Review reading and interpreting Explain Plans
- Understanding the Rule-based optimizer
- Understanding the Cost-based optimizer
Module 2: Working with Indexes and Hints
- Review CBO Hints – displaying internal hints
- Oracle considers Advanced index review / tips and techniques
Module 3: Helping the CBO
- A close look at sub-query coding techniques
- Plan stability Histograms SQL Coding Tips
- Review SGA memory structures:
- Buffer Cache
- Result Cache
- Library Cache
- SQL trace file analysis
- Using Toad (new features) TKProf and SQL Developer
- Using SQL TXPlan (new free Oracle SQL analysis tool)
Module 5: Additional Advanced Material
- A close look at other useful Oracle Traces
- 10053 CBO Trace
- 10030 and 10031 Sort Traces
- 10104 Hash Join Traces
- Oracle Internals: How Oracle writes
- Various space management issues
- Monitoring sorting
- Finding problems SQL using V$ information
- Using automated workload repository
- Review the SQL Tuning Advisor
