trivadis.com - Daten sind immer im Spiel

Audience

Target audiences of this event are performance analysts and engineers, database administrators who want to improve their skills in managing performance. Participants are expected to have a working knowledge of the Oracle database engine.

Content

Seminar outline: Day 1

 

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 following sessions 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 dis-cussed.

 

• Oracle OSD layer
• Unix/Windows private and shared memory allocation for Oracle
• Dynamic SGA and realfree PGA 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.

 

• Granule, heap and frame memory management
• Process startup, session creation
• State objects, task cleanup
• Latching, locking, enqueues
• 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 instru-mentation framework
• Diagnostic event internals
• Oradebug diagnostics and when to not use it
• Oracle 11g diagnosability framework

 

 

Seminar Outline: Day 2

 

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 an 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