ONLINE CLASSES

Oracle10g SQL Tuning for Developers and DBAs:
21-Hour Online Class and Hands-On Workshop.

You will also learn how to identify existing high-load, poor performing SQL statements and tune them (reactive SQL tuning).

 

This hands-on course will provide you with the skills necessary to create scalable, high performance SQL-based Oracle applications (proactive SQL tuning). Particular attention is paid to making the best use of Oracle architecture, bench-marking various SQL formulations and identifying the best schema object (e.g. heap table, IOT, etc.) for a given situation.

 

Oracle10g introduces "automatic tuning" features such as the Automatic Workload Repository, Automatic Database Diagnostic Monitor and SQL Tuning Advisor. These features are covered in this class.

 

The course also includes a lesson on tuning PL/SQL programs. This includes using bulk processing and profiling to identify high-load statements.

 

Working examples are included throughout the course book. Students have access to electronic versions of all sample scripts and lab solutions. Approximately 50% of class time is spent on hands-on exercises.

 

As with all of our courses, this class is highly customizable to your specific training requirements. Hands-on workshops constitute approximately 50% of the class.


Also see our updated Oracle 11g SQL Tuning course.

Share |

Cost: $1,185

Upcoming Class Schedule

Dates Times (ET) Location Availability
No Events Scheduled at this time

Enrollment includes:

  • eg 300+ page coursebook (hardcopy)
  • Headset
  • Workshop solutions and sample code
  • Review recordings of your class sessions online, any time*what is this?
  • Certificate of Completion what is this?
  • 100% Satisfaction Guarantee what is this?
  • Bring a colleague and receive a $25 Amazon.com gift certificate what is this?
  • Introduction to Oracle SQL Tuning          
    • What Can Be Tuned?          
    • What You Need to Know to Tune Oracle SQL
    • Workshop: Setup
  • SQL Statement Processing       
    • Oracle10g Architecture
    • The SGA
    • SQL Statement Processing Overview            
    • Soft Parse
    • Hard Parse: Optimization
    • Row Source Generation
    • Execute Step
    • Fetch Step
    • Workshop
  • Viewing The Execution Plan
    • The EXPLAIN PLAN Command        
    • PLAN_TABLE Columns       
    • Reading Execution Plans
    • Retrieving the Historical  Plan: V$SQL_AREA
    • Using SQL*Plus Autotrace   
    • Workshop
  • Using SQL Trace and TKPROF
    • Trace Concepts
    • Trace Steps
    • Trace-Related Initialization Parameters
    • Enable Trace
    • Find the Trace File
    • Using the TKPROF Utility
    • Trace Analysis Tips
    • Workshop
  • The Importance of Bind Variables
    • Shared Pool Reuse
    • Bind Variables
    • Querying V$SQL
    • PL/SQL Bind Variables
    • Cursor Sharing
    • Workshop
  • Getting to Know Cost Based Optimization
    • Optimization Concepts
    • Rule-Based Optimization (Deprecated in 10g)
    • Enabling CBO
    • Checking the Optimizer Setting
    • Collecting Object Statistics
    • Tweaking CBO
    • Collecting System Statistics
    • Creating Histograms
    • Dynamic Sampling
    • Optimizer Calculations for Equal Comparisons           
    • Optimizer Calculations for Range Comparisons
    • Bind Variable Peeking and Range Optimization Involving Bind Variables           
    • Workshop
  • Learning the Access Paths
    • Full Scans
    • Multi-Block Reads
    • Forcing Full Table Scans     
    • High-Water Mark
    • Large Deletes and the HWM
    • When is a Full Scan Bad?
    • Index-Based Scans
    • Unique, Range, Full and Fast-Full Index Scans
    • Index Skip Scan
    • Workshop
  • Using Hints
    • What are Hints?
    • When to Use Hints
    • Hint Examples
    • Some Syntax Notes
    • Workshop
  • Schema (Data Storage) Options
    • Heap Tables
    • Index Organized Tables       
    • Index Clustered Tables        
    • Hash Clustered Tables        
    • Sorted Hash Clusters (10g)
    • Materialized Views
    • Temporary Tables
    • Nested Tables        
    • Object Tables
    • Workshop
  • Indexes, Part I – All About B-Tree Indexes
    • Index Concepts
    • B-Tree Index Concepts
    • B-Tree Effect on Query Performance 
    • Creating B-Tree Indexes      
    • Update Statistics with DBMS_STATS           
    • Impact of Clustering Factor
    • Choosing Columns to Index 
    • Calculating Selectivity
    • Effect of Indexes on ORDER BY Columns
    • When to Avoid Adding an Index
    • Multi-Column Indexes
    • Which Column First?
    • Oracle9i Skip Scan
    • Avoiding Table Access with Multi-Column Indexes
    • Multiple Single Column Indexes        
    • When and Where to Create Indexes
    • What to Avoid when using an Index  
    • Finding Unused Indexes with MONITORING
    • Additional Indexing Guidelines
    • Workshop
  • Indexes, Part II - Introduction to Other Index Types
    • Bitmap Indexes
    • Reverse Key Indexes
    • Function-Based Indexes
    • Bitmap Join Indexes
    • Workshop
  • Using Oracle10g Advisors to Tune SQL
    • Introduction to Automatic Workload Repository Concepts
    • Using ADDM to find Problems
    • Using SQL Tuning Advisor to Get Recommendations
    • Exploring STA Recommendations
    • Using SQL Profiles
    • Workshop
  • Impact of Reformulating SQL Statements
    • EXISTS versus DISTINCT
    • NOT IN versus NOT EXISTS
    • IN versus EXISTS
    • UNION ALL versus UNION
    • Oracle Analytic Functions
    • Workshop
  • Join and Subquery Access Paths
    • Join Operations
    • Nested Loops Join
    • Sort Merge Join
    • Hash Join
    • Join Hints
    • Additional Filters     
    • Subquery Conversion
    • Workshop
  • Concurrency Control
    • Introduction to Locks
    • Row Lock Architecture
    • Basic Locking Rules
    • DDL and DML locks
    • Locking Issues: Blocking
    • Locking Issues: Deadlocks
    • Detecting Waits Due to Blocking
    • Workshop
  • Parallel Query
    • When Parallel Query Helps
    • Parallel Query Examples
    • Parallel DML
    • Conditions To Avoid
    • Workshop
  • Optimizer Plan Stability 
    • Plan Stability Concepts       
    • Implementing Plan Stability  
    • Exporting/Importing Outlines
    • Managing Outlines
    • Workshop
  • Tuning Views    
    • Mergeable Views
    • Hints & Mergeable Views     
    • Non-Mergeable Views          
    • Hints and Non-Mergeable Views
    • Workshop
  • PL/SQL Tuning 
    • Introduction to DBMS_TRACE
    • Introduction to DBMS_PROFILER
    • Using PL/SQL to Improve Performance         
    • 10g PL/SQL Performance Improvements       
    • Bulk Processing
    • Efficient Lookups
    • Coding Pipelined Table Functions
    • Static SQL versus Dynamic SQL
    • Using RefCursors versus Arrays to Return Data
    • Explicit versus Implicit Cursors
    • Pipelined Functions
    • Workshop

Solid experience with Oracle SQL.  PL/SQL programming is required to understand the lesson on PL/SQL tuning. This knowledge can be obtained via the following SkillBuilders’ courses:

"Today is their last day of class, and they've told me that this has been one of the best classes on SQL that they’ve ever attended. I mentioned that to our quality assurance manager, who has been wanting to send three his testers to an advanced SQL course. He’s planning to contact you soon."
— Sarah Lawrence, TeleCommunications Systems Inc.

Read reviews of all SkillBuilders classes.

4.6 out of 5

Student Rating