SkillBuilders Welcome  |  Log in
All Oracle Classes

Oracle Database Performance Tuning Workshop
4 Day Onsite or 28 Hour Online Hands-On Training

This hands-on course covers techniques for diagnosing and fixing performance issues within the instance and the database. These include Standard Edition facilities, also facilities available only with Enterprise Edition licences and with EE options. Some aspects of SQL tuning are discussed, but generally speaking SQL tuning is dealt with elsewhere in the curriculum. As with all SkillBuilders courses, this class (if conducted at your company facilities) is highly customizable to your specific training requirements.

The course is run against the current release of 12c. Much of the content is applicable to earlier releases, and will be "back-ported" as necessary.

Hands-on workshops constitute approximately 25% of the class.

Oracle Certified Master
John Watson
Average Student Rating:
  • Database and instance architecture review
    • Physical structures
    • Memory structures
    • Sessions and Oracle Net
  • The SQL execution cycle
    •  Parse-bind-execute-fetch
    • Use of memory and IO
  • The tuning methodology
    • Identify problems
    • Develop possible solutions
    • Test their effectiveness
  • The DB Time model
    • Working time, waiting time, IO time
    • Determine which part to attack
  • Identifying bottlenecks
    • Is one operation slowing down everything else?
    • Remove the bottleneck - find the next
  • Wait events
    • Session and instancewaits
    • Foreground and background wait events
    •  Idle wait events
  • Dynamic performance views
    •  Views populated from the instance
    •  Views populated from the controlfile
  • Statspack reports, AWR reports, and ASH reports
    •  Instance activity reports
    •  SQL reports
  • PGA and SGA memory structures
    •  Tuning session memory
    •  Tuning instancve memory structures
  • Datafile management
    •  Evaluate different storage media
    •  Implement best practices for file management
  • The redo log
    •  The log buffer
    •  The online redo log
    •  Archive management
  • Metrics and alerts
    •  Configure the server managed alert system
    •  Set up metric thresholds
  • Object and system statistics
    •  Gather statistics with DBMS_STATS
    •  Create extended statistics
    •  Use dynamic sampling
  • Tracing at various levels and in various ways
    •  The SQL trace facility at he session, service, and isntance levels
    •  The DBMS_MONITOR package
  • Use of indexes
    •  B-tree indexes 
    •  Bitmap indexes
    •  Variations: function based, reverse key, and bitmap join indexes
  • Table structures
    •  Heap tables
    •  IOTs
    •  Hash and index clustered tables
  • Table and index partitioning
    •  Range, hash, and list partitioning for tables
    •  Local and global index partitioning
    •  Partial indexes
  • Reading execution plans
    •  Capture plans
    •  Identify the critical decisions
  • Bind variables
    •  Cursor sharing
    •  Minimize parsing workload
  • Influencing the optimizer
    •  Using hints
    •  Instance parameters
  • Adaptive SQL execution features
    •  Statistics (cardinality) feedback
    •  Adaptive execution plans
  • SQL profiles and SQL directives
    •  Profiles and the Tuning Pack
    •  Automatic generation of Directives
  • Performance instability
    •  Causes of instability
    •  SQL baselining
  •  Using parallel execution
    •  Manual techniques
    •  Automatic parallel policy
  • PErformance tuning advisors
    •  The SQL tuning Advisor
    •  The Segment Advisor
  • Real Application Testing
    •  Database Replay
    •  The SQL PErformasnce Analyzer
  • Common performance issues
SkillBuilders Sample Video