Recent site activity


Advanced Oracle Troubleshooting v2.1

Seminar Overview

This seminar is concentrated entirely on Oracle troubleshooting – understanding what exactly is Oracle doing right now or what was it doing when the problem occurred. You will gain the skill to systematically work out the reasons for crashes, hangs, bad performance or other misbehavior.

The seminar takes a holistic approach for end-to-end troubleshooting. It will explain the full lifecycle of a database request, from database client libraries and network to Oracle database kernel and underlying OS. For each layer a troubleshooting technique is provided, along with advice on using the right tool for the right problem at the right time.

The seminar days are full of intensive learning, reading dumps, stack traces and querying advanced V$/X$ views. You’ll also be using OS-level tools and custom scripts provided to you for real-time and post-mortem diagnosis.

The emphasis is put on practical troubleshooting, the safety comes first and most techniques are designed to require no change to database schemas or instance parameters.

Seminar Duration and Details

  • Seminar duration: 10 x 4 hour deep dives (spread into two parts, across 2 weeks, usually a month apart)
  • Venue: Online, virtual seminars at your desk (no travel!)
  • Audience: Senior DBAs, Performance Engineers
  • Skill level: Advanced to Expert
  • Speaker: Tanel Poder
  • Price: 
    • 3250 USD
Check the next seminar dates and sign up here:


  • Understand internals of core Oracle kernel components and their interaction with OS
  • Be proficient in systematic end-to-end troubleshooting, using the right tool for right problem
  • Have the ability to proactively eliminate potential database performance issues


  • This seminar is not a beginner database tuning course
  • This seminar is not an SQL tuning or CBO course (and it is not a SQL developer class)

You will learn the following...

  • Systematic approach for advanced Oracle troubleshooting and performance tuning
  • Oracle, OS interaction and using OS-level tools for troubleshooting Oracle from "outside"
  • Using the full power of Oracle’s built in instrumentation for troubleshooting
  • Troubleshooting crashes, ORA-600’s and complex hangs
  • Troubleshooting latch and mutex contention
  • Troubleshooting enqueue lock contention and deadlocks
  • Troubleshooting Oracle private, shared and OS memory problems
  • Troubleshooting Oracle cursor management and library cache problems
  • Troubleshooting buffer cache, logical and physical I/O problems
  • Troubleshooting undo errors, excessive redo and transaction related problems

Seminar content technical details

Week 1

AOT deep dive 1: Systematic approach for Advanced Oracle Troubleshooting
  • How to pick the correct starting point for troubleshooting and how to drill down systematically
  • Right tool for the right problem: understanding the advantages and shortcomings of different performance troubleshooting tools
  • Getting the most out of Oracle's instrumentation
  • Oracle Wait Interface internals
  • Using wait events, dynamic performance counters and metrics for troubleshooting
  • Using Active Session History and Time Model metrics
  • Troubleshooting high CPU usage
AOT deep dive 2: Troubleshooting physical IO and buffer cache issues
  • Troubleshooting IO problems, at Oracle database and OS level
  • Troubleshooting excessive IO
  • Troubleshooting slow IO
  • Using IO wait histograms
  • Measuring OS-level and hardware IO performance
  • Understanding iostat output
  • Troubleshooting buffer busy waits
  • Troubleshooting DBWR and free buffer waits
  • Configuring the OS IO layer optimally for Oracle
AOT deep dive 3: Troubleshooting commit, redo, undo and transaction issues
  • Troubleshooting unexpected TX and TM enqueue waits
  • Troubleshooting long running transactions
  • Troubleshooting undo generation and ORA-1555 errors
  • Troubleshooting excessive redolog generation and "log buffer space" waits
  • Troubleshooting LGWR, "log file sync" waits and commit performance

AOT deep dive 4: Troubleshooting Oracle SGA/PGA/UGA and OS memory issues
  • PGA/UGA memory usage troubleshooting and monitoring
  • Finding which cursors and operations consume the most of the private memory
  • Troubleshooting ORA-4030 errors
  • Troubleshooting SGA_TARGET, MEMORY_TARGET and SGA resizing related problems
  • Troubleshooting OS memory shortage and excessive paging
Note: The shared pool specific issues are covered in AOT deep dive 5

AOT deep dive 5: Troubleshooting shared pool and library cache memory allocation issues
  • Shared pool and SGA memory allocation internals
  • Troubleshooting shared pool memory usage and excessive flushing
  • Understanding and troubleshooting ORA-4031 errors
  • Troubleshooting library cache cursor sharing issues
  • Troubleshooting cursor leaks
  • Optimal cursor management strategy for high performance applications

Week 2

AOT deep dive 6: Troubleshooting enqueue lock waits and deadlocks
  • Understanding commonly used enqueue types and their meaning
  • Mapping enqueue wait events and the exact item waited for using lock identifiers
  • Reading enqueue deadlock / ORA-60 traces
  • Making Oracle dump more information about deadlocks
  • Tracing enqueue locking activity
  • We will look into multiple enqueue waits which can cause headache, such as:
    • enq: TX - contention
    • enq: TM - contention
    • enq: KO - fast object checkpoint
    • enq: RO - fast object reuse
    • enq: CF - contention
    • enq: HW - contention
    • enq: HV - contention
    • enq: TS - contention
    • enq: TT - contention
    • enq: TO - contention
    • enq: SQ - contention
Note that the TX and TM lock waits are covered in more detail in AOT deep-dive 3

AOT deep dive 7: Troubleshooting latch contention
  • Oracle latching implementation and internals
  • Troubleshooting the classic latch contention waits such as:
    • latch: shared pool
    • latch: library cache / latch: library cache pin
    • latch: cache buffers chains
    • latch: cache buffers lru chain
    • latch: object queue header operation
    • latch: row cache objects
    • latch: undo global data
  • Identifying the problematic child latch from generic "latch free" wait events
  • Advanced latch contention troubleshooting using V$LATCHHOLDER and X$KSUPRLAT
  • Identifying the session and exact reason causing the latch to be busy
  • Systematically identifying the hot block causing cache buffer chains latch contention

AOT deep dive 8: Troubleshooting Library cache, Mutex and "cursor: pin" contention
  • Library cache and cursor lookup internals
  • Troubleshooting library cache lock/pin contention
  • Library cache hash buckets and their protection by mutexes in Oracle 11g
  • Oracle mutex implementation and internals
  • Cursor pinning using mutexes
  • Identifying mutex holder from Oracle Wait interface data
  • Reducing mutex contention by better cursor management
  • Common mutex-related bugs and issues
  • Advanced mutex contention troubleshooting using V$MUTEX_SLEEP_HISTORY

AOT deep dive 9: Troubleshooting complex hangs and spins
  • Logging on to a hung instance when even SYSDBA can't log on
  • Getting hanganalyze dumps and understanding hanganalyze output
  • Using V$WAIT_CHAINS for identifying blockers in 11g
  • Understanding state objects and reading systemstate dumps
  • Finding why a process is hung doing nothing even when the wait interface says the session isn't waiting
  • Finding why a process is burning CPU when Oracle reports it's doing nothing

AOT deep dive 10: Troubleshooting crashes, bugs and ORA-600/ORA-7445 errors
  • Reading ORA-600/ORA-7445 dumps
  • Reading ERRORSTACK dumps
  • Getting Oracle to dump extra information on error messages
  • Reading Oracle kernel stack traces
  • Proactive OS configuration for gathering Oracle crash information
  • Reading crash reasons from OS corefiles

Next public seminar dates

Check the next seminar dates and sign up here!