Tanel Poder Agenda PDF Print E-mail

Seminar outline - Advanced Oracle Troubleshooting for DBAs and Performance Engineers by Tanel Poder:

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 pre fetching
Asynchronous IO, unbuffered direct, concurrent and raw IO
IO troubleshooting 

Session 8 - Lifecycle of a transaction

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

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