Tanel Poder’s blog: Core IT for geeks and pros

Advanced Oracle Troubleshooting Seminar

Advanced Oracle Troubleshooting for DBAs and Performance Engineers

by Tanel Poder (http://www.tanelpoder.com)

If you like the stuff in my blog or my conference sessions, you will sure like my seminar :)

I’m happy to announce the dates of my first public Advanced Oracle Troubleshooting seminars:

Vienna, Austria 03.-04. November 2008
Düsseldorf, Germany 06.-07. November 2008
Munich, Germany 10.-11. November 2008
Zurich, Switzerland 13.-14. November 2008
The above four seminars are organized by Trivadis
Vilnius, Lithuania 18.-19. November 2008
Riga, Latvia 20.-21. November 2008 (organized by Mebius IT)
Tallinn, Estonia 24.-25. November 2008 (organized by Oracle)
New York 3.-4. February 2009
Singapore 13.-14. April 2009
Sydney 16.-17. April 2009
Melbourne 20.-21. April 2009
Auckland 23.-24. April 2009
The above four seminars are organized by Oracle
Scotland (exact location TBD) 27.-28. April 2009

If you are interested in my seminar in your city/company, please drop me an email to tanel@tanelpoder.com and I’ll see what I can do (you can also contact your local user group directly as that’s probably what I will do when planning next seminars).

Below is the class description which I deliver in two formats:

1. 2-day public seminar with lots of demos and hands on exercises for students who have their laptops with Oracle installed

2. 2+n day corporate internal seminar, followed with post-class mentoring and hands-on troubleshooting of these hard to solve problems your Oracle database systems experience. This approach helps to facilitate further knowledge transfer, help the students to get most out of the seminar and immediately apply the skills learnt for solving real problems.

I am available for engagements on short notice. I will deliver this as public seminar in few European and US cities in Q4 2008. I will post the details once the dates and locations are set.

For enquiries, please contact me at tanel@tanelpoder.com

Course outline in PDF format.

Seminar Title: Advanced Oracle Troubleshooting for DBAs and Performance Engineers

Seminar Description:

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 misbehaviour.

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 two days are full of intensive learning, reading dumps, stack traces, network packet captures and Oracle SGA directly. You’ll be using debuggers and custom tools provided to you for real-time and post-mortem diagnosis.

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


  • 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

Prerequisites: Good understanding of general Oracle database architecture, OS commands

Duration: 2 days (4 x 1.5h sessions per day)

Keywords: Oracle troubleshooting, performance tuning, internals

Target Audience: Database Administrators, Performance Engineers


  • Systematic approach to troubleshooting and tuning
  • End to end lifecycle of a database request
  • Oracle, OS and hardware interaction
  • Oracle kernel core services
  • Oracle Wait Interface and kernel instrumentation framework
  • Lifecycle of a cursor
  • SQL execution row source operators and data flow
  • Logical and physical IO internals
  • Lifecycle of a transaction


Tanel Poder is a researcher and database engineer, focusing mainly on designing Oracle Database based solutions for high performance, scalability and availability. He gets often involved in advanced end-to-end system troubleshooting, which has taken him to work for clients in Asia, US and Europe.

He hosts an Oracle performance tuning site at http://www.tanelpoder.com and has published useful Oracle troubleshooting tools like PerfSheet, Sesspack and Session Snapper.

In addition to delivering training and seminars, Tanel is a frequent speaker at major conferences such as OracleWorld, UKOUG, Hotsos Symposium, IOUG-A Live and many regional ones.

Tanel is also one of the first Oracle Certified Masters in the world and an OakTable Network member.

Seminar outline:

Day 1 (4 x 1.5 hour sessions)

Session 1 – End to end lifecycle of a database request

This session gives an overview of the core hardware layers and software code paths a database request has to pass through. For every relevant layer a commonly available diagnosis technique or tracing tool is proposed.

  • End to end call execution flow
  • OCI, Oracle Net (SQL*Net) and direct wire protocol client libraries
  • TCP, IPC and network services for Oracle
  • Upper Oracle kernel: TNS protocol and Two-Task Common layer
  • Lower Oracle kernel: From OPI to OSD

The sessions following will give you the skill and knowledge of using the right tool for the right problem at the right time.

Session 2 – Oracle, OS and hardware interaction

This session concentrates on what happens at OS and hardware level when Oracle needs to use operating system services or OS needs to interact with Oracle. Both Unix derivatives and Windows server family internals are discussed.

  • Oracle OSD layer
  • Unix/Windows private and shared memory allocation for Oracle
  • Dynamic SGA, automatic PGA, MEMORY_TARGET internals and gotchas
  • OS scheduling challenges for Oracle
  • IO services and system call interface
  • Context switching and TLB misses

Session 3 – Oracle kernel core services

This session covers in detail the kernel services and components most of database calls interact with – and often get in trouble with.

  • KGH Heap and shared/java/large/streams pool memory management
  • Process startup, session creation
  • State objects, task cleanup
  • Latching, locking
  • Sleeping, interprocess communication

Session 4 – Oracle Wait Interface and kernel instrumentation framework

This session concentrates on Oracle wait interface internals, shortcomings and workarounds. Also the 10g+ new end-to-end instrumentation framework is covered.

  • Oracle waits, sleeps, timeouts and uninstrumented time
  • Getting the most out of Oracle performance counters
  • Oracle Time Model statistics improvements
  • Modules, actions and client_ids in end-to-end instrumentation framework
  • Diagnostic event internals
  • Oradebug diagnostics and when to not use it

Day 2 (4 x 1.5 hour sessions)

Session 5 – Lifecycle of a cursor

Most of the application code execution in Oracle is done via cursors – this includes calling PL/SQL stored procedures. Different workloads require different cursor management strategies, getting it wrong may cause your system not scale regardless the amount of hardware thrown in. Getting it right requires understanding the library cache data structures and concurrency mechanisms, which this session will deliver.

  • Library cache lookup and data structures
  • Cursor management, parsing and execution
  • Library cache latching, locking, pins and mutexes
  • Bind variable strategies, overcoming bind peeking problems
  • Cursor caching benefits and problems
  • Diagnosing library cache bottlenecks and inefficiencies

Session 6 – SQL execution row source operators and data flow

This session gives you detailed understanding of what a server process has to go through when looking up records and generating result sets based on our SQL. This will be crucial for reading and really understanding SQL execution plans. You will also be introduced to a way for diagnosing where a statement has got stuck if the conventional diagnostics methods like tracing and V$/X$ queries fail to reveal it.

  • Reading execution plans
  • Row source operators, RSO/DFO trees
  • Filter operators
  • Join methods
  • Sorting, grouping, aggregations, analytics
  • Measuring SQL performance at execution plan line level

Session 7 – Logical and physical IO internals

This session gives you understanding of Oracle’s internal buffer cache management and key differences between many OS level IO configuration options. Also the different costs of different LIO operations are discussed along with tuning advice.

  • Cache layer – datafile and datablock structure
  • Buffer cache management, multiversion read consistency
  • The varying cost of logical IOs
  • Single block, multiblock physical IO, data block prefetching
  • Asynchronous IO, unbuffered direct, concurrent and raw IO
  • IO troubleshooting

Session 8 – Lifecycle of a transaction

I addition to covering Oracle transaction management core internals, this session wraps up the topics learnt, by studying a case of a transaction life cycle, from its initialization to post-commit cleanout.

  • Oracle row-level locking and transaction management
  • Undo segments, ITLs and write consistency
  • Undo vectors, in-memory undo
  • Cache layer – redolog structure, private redo strands
  • Commit processing, commit cleanout
  • Gotchas with LGWR and log file sync



  1. […] looks exactly like one of the many troubleshooting use cases I demonstrate in first session of my Advanced Oracle Troubleshooting class (nice embedded advertisment, huh? […]

    Pingback by Advanced Oracle Troubleshooting Guide, Part 4: Diagnosing a long parsing issue « Tanel Poder’s blog: Core IT for geeks and pros — June 3, 2008 @ 2:00 am

  2. […] augtsāk minētajā saitē ir semināra apraksts latviski, angliski tas ir atrodams šeit. Seminārs acīmredzot notiks kopējas Eiropas tūres ietvaros, kur daži pārējie semināri būs […]

    Pingback by Seminārs nopietniem ORACLE-istiem « Datubāzu resurss latviski — July 14, 2008 @ 10:16 pm

  3. […] happy to announce the dates of my first public Advanced Oracle Troubleshooting […]

    Pingback by Advanced Oracle Troubleshooting seminars - announcing dates « Tanel Poder’s blog: Core IT for geeks and pros — August 5, 2008 @ 12:29 am

  4. […] to complete it in a month or so as I’m extremely busy right now (I’ll be delivering my Advanced Oracle Troubleshooting seminar in 7 cities in November so if you want to see more of oStackProf, then sign up […]

    Pingback by Advanced Oracle Troubleshooting Guide, Part 9 - Process stack profiling from sqlplus using OStackProf « Tanel Poder’s blog: Core IT for geeks and pros — October 31, 2008 @ 9:02 am

  5. […] occurred long awaited event in Latvia – Advanced Oracle troubleshooting seminar with Tanel Poder (http://blog.tanelpoder.com/seminar/). The seminar was arranged by Affecto Latvia and Oracle University, lasted 2 days, and collected […]

    Pingback by Few notes on ‘Advanced Oracle Troubleshooting Seminar’ with Tanel Poder in Latvia. « erudans.info — December 12, 2008 @ 1:13 am

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at WordPress.com.

%d bloggers like this: