Ixora Training Seminars

Ixora occasionally offers training courses on Oracle internals and advanced performance tuning.

Course Outline

Day 1

Data Storage
This unit makes extensive use of undocumented Oracle facilities to examine the internal data structures of each type of data block. You will learn how to minimize data access costs and avoid fragmentation problems by controlling the ways in which Oracle manages its data and free space within data blocks and segments.
Access Paths
There are about 12 distinct ways in which Oracle can access data from a single table. These are the primary building blocks of all Oracle’s query execution plans. This unit examines the performance characteristics of each single table access path and presents the physical data modelling strategies available to optimize the access to each table in your database.
Join Mechanisms
This unit makes use of some of Oracle’s many undocumented trace facilities to demonstrate exactly how Oracle uses sort area memory and temporary segment space for sorts and hash joins. This information is used to contrast the performance characteristics of sort-merge joins and hash joins with nested loops and index joins.
Day 2

Query Optimization
Oracle uses a 3-stage approach to cost-based query optimization. It first transforms the query to ensure that no data access paths will be overlooked. It then considers a variety of execution plans and estimates the cost of each one. Last, it evaluates a set of possible improvements to the cheapest execution plan. This unit explains each of these stages in detail, and develops a recommended approach for your SQL statement tuning.
Buffer Cache
There is more to tuning the Oracle buffer cache than just increasing its size. There is also scope for tuning the retention of hot buffers, the caching of large reads, the pace of incremental checkpointing, the database writer bandwidth and other factors. Many databases also suffer from buffer busy waits, contention for the hash chain latches and other advanced performance problems. This unit uses dumps and X$ tables to reveal the internal data structures and algorithms that Oracle uses to manage the buffer cache. It then builds on this foundation to explain how to tune all aspects of buffer cache performance.
I/O
Database I/O can be buffered, direct, raw, synchronous or asynchronous. Storage can be striped, mirrored or parity protected. There are also decisions to be made about the size of data blocks and data files, as well as the selection of storage technology. This unit explains the differences between the various options and identifies the principles to follow and the pitfalls to avoid.
Day 3

Redo
Redo generation and logging have a significant impact on the performance of most Oracle systems. This unit explains the internal data structures and mechanisms involved, and presents techniques that you can use to minimize redo generation and optimize logging and commit processing.
Locking
Lock waits are another major cause of poor performance in Oracle based systems. This unit looks at the data structures in both the SGA and in data blocks that are used to implement Oracle’s row level locking and most other types of locks. The focus is mainly on transaction locks and table locks, but the other enqueue locking problems that you are likely to encounter are also covered.
Shared Pool
In this unit you’ll learn how Oracle uses and manages memory in the shared pool, and also in the large pool and java pool. Shared pool tuning is fairly simple, but nevertheless vital to library cache performance.
Library Cache
You’ll need to be alert for this one, because there is considerable complexity in the library cache and many features will seem quite strange at first. Working from librarycache dumps and the X$ tables, this unit explains how cursors and database objects are represented, related and managed in the library cache. This is important for understanding the steps and costs involved in soft and hard parses and other library cache operations. You’ll not only learn how to optimize parsing, but also SQL and PL/SQL execution.
Latching
This unit will only be presented if time permits. Specific information about key latches will have been covered in the preceding units. This unit explains in more detail how Oracle latching works, how latch contention affects Oracle performance, and how to diagnose and tune serious latch contention problems.
Oracle 9.2 will be used for all demonstrations and examples throughout the course, although differences to earlier versions will be noted where applicable.

Prerequisites
There are no formal prerequisites for this training. However, it is recommended that students should have at least 2 years of experience with Oracle as a DBA or developer, and should have a strong knowledge of basic Oracle database administration and development concepts - at least to the level of OCP certification, or equivalent.

Format
The seminars are presentation based, with demonstrations. There is some opportunity for questions, but no practice sessions. Needless to say, the pace is fast, and much of the information presented is not available elsewhere, therefore all attendees receive a seminar workbook containing all the slides and the instructor's notes. The seminar workbooks form an invaluable resource for subsequent reference and further study.

Scheduled Seminars

13 to 15 January 2003, Copenhagen, Denmark
This seminar will be presented in conjunction with Miracle A/S, Denmark. Please visit the Miracle web site for registeration details.
Other Seminars
If you'd like to be notified of any future training seminars without having to revisit this web page regularly, then please subscribe to the Ixora Newsletter.

	Course Outline Day 1 Data Storage This unit makes extensive use of undocumented Oracle facilities to examine the internal data structures of each type of data block. You will learn how to minimize data access costs and avoid fragmentation problems by controlling the ways in which Oracle manages its data and free space within data blocks and segments. Access Paths There are about 12 distinct ways in which Oracle can access data from a single table. These are the primary building blocks of all Oracle’s query execution plans. This unit examines the performance characteristics of each single table access path and presents the physical data modelling strategies available to optimize the access to each table in your database. Join Mechanisms This unit makes use of some of Oracle’s many undocumented trace facilities to demonstrate exactly how Oracle uses sort area memory and temporary segment space for sorts and hash joins. This information is used to contrast the performance characteristics of sort-merge joins and hash joins with nested loops and index joins. Day 2 Query Optimization Oracle uses a 3-stage approach to cost-based query optimization. It first transforms the query to ensure that no data access paths will be overlooked. It then considers a variety of execution plans and estimates the cost of each one. Last, it evaluates a set of possible improvements to the cheapest execution plan. This unit explains each of these stages in detail, and develops a recommended approach for your SQL statement tuning. Buffer Cache There is more to tuning the Oracle buffer cache than just increasing its size. There is also scope for tuning the retention of hot buffers, the caching of large reads, the pace of incremental checkpointing, the database writer bandwidth and other factors. Many databases also suffer from buffer busy waits, contention for the hash chain latches and other advanced performance problems. This unit uses dumps and X$ tables to reveal the internal data structures and algorithms that Oracle uses to manage the buffer cache. It then builds on this foundation to explain how to tune all aspects of buffer cache performance. I/O Database I/O can be buffered, direct, raw, synchronous or asynchronous. Storage can be striped, mirrored or parity protected. There are also decisions to be made about the size of data blocks and data files, as well as the selection of storage technology. This unit explains the differences between the various options and identifies the principles to follow and the pitfalls to avoid. Day 3 Redo Redo generation and logging have a significant impact on the performance of most Oracle systems. This unit explains the internal data structures and mechanisms involved, and presents techniques that you can use to minimize redo generation and optimize logging and commit processing. Locking Lock waits are another major cause of poor performance in Oracle based systems. This unit looks at the data structures in both the SGA and in data blocks that are used to implement Oracle’s row level locking and most other types of locks. The focus is mainly on transaction locks and table locks, but the other enqueue locking problems that you are likely to encounter are also covered. Shared Pool In this unit you’ll learn how Oracle uses and manages memory in the shared pool, and also in the large pool and java pool. Shared pool tuning is fairly simple, but nevertheless vital to library cache performance. Library Cache You’ll need to be alert for this one, because there is considerable complexity in the library cache and many features will seem quite strange at first. Working from librarycache dumps and the X$ tables, this unit explains how cursors and database objects are represented, related and managed in the library cache. This is important for understanding the steps and costs involved in soft and hard parses and other library cache operations. You’ll not only learn how to optimize parsing, but also SQL and PL/SQL execution. Latching This unit will only be presented if time permits. Specific information about key latches will have been covered in the preceding units. This unit explains in more detail how Oracle latching works, how latch contention affects Oracle performance, and how to diagnose and tune serious latch contention problems. Oracle 9.2 will be used for all demonstrations and examples throughout the course, although differences to earlier versions will be noted where applicable.
	Prerequisites There are no formal prerequisites for this training. However, it is recommended that students should have at least 2 years of experience with Oracle as a DBA or developer, and should have a strong knowledge of basic Oracle database administration and development concepts - at least to the level of OCP certification, or equivalent.
	Format The seminars are presentation based, with demonstrations. There is some opportunity for questions, but no practice sessions. Needless to say, the pace is fast, and much of the information presented is not available elsewhere, therefore all attendees receive a seminar workbook containing all the slides and the instructor's notes. The seminar workbooks form an invaluable resource for subsequent reference and further study.
	Scheduled Seminars 13 to 15 January 2003, Copenhagen, Denmark This seminar will be presented in conjunction with Miracle A/S, Denmark. Please visit the Miracle web site for registeration details. Other Seminars If you'd like to be notified of any future training seminars without having to revisit this web page regularly, then please subscribe to the Ixora Newsletter.