Striving for Optimal Performance

An execution plan describes the operations carried out by the SQL engine to execute a SQL statement. Every time you have to analyze a performance problem related to a SQL statement, or simply question the decisions taken by the query optimizer, you must know the execution plan. Whenever you deal with an execution plan, you carry out three basic actions: you obtain it, you interpret it, and you judge its efficiency.

I have always found it surprising how little documentation there is about how to interpret execution plans, especially since there seem to be so many people who are unable to correctly read them. I addressed this problem not only in Chapter 6 of my book, Troubleshooting Oracle Performance, but also by presenting this very same topic at several conferences and user-group meeting (e.g. UKOUG, Orcan and SIOUG). The last time I presented it, it was few days ago at Oracle OpenWorld in San Francisco. By the way, big thanks to everyone who attended my presentation! It was really good to see a packed room. That said, I’m truly sorry that Oracle has not been able to provide us with a decent room. I was embarrassed for them…

The aim of this short post is to point out that I just uploaded the slides that I presented at different events here. Since the slides themselves contain few explanations and, therefore, are not very useful without additional information, I also added comments whenever necessary (e.g. where there is an execution plan). I hope you find that document useful.

The company I work for, Trivadis, organizes a series of 1-day seminars covering the most important features of Oracle Database 11g Release 2. The following dates and locations are currently planed (some more might be added later on):

• 2009-12-10 Zurich (CH)
• 2010-01-19 Frankfurt (DE)
• 2010-01-20 Munich (DE)
• 2010-02-02 Stuttgart (DE)
• 2010-02-04 Vienna (AT)

My involvement with these seminars is twofold. First, along with several colleagues of mine I’m currently testing the new features and writing slides not only about how they (should) work, but also about the problems we experience during our tests. Second, I’ll be presenting, along with another colleague (every seminar has two speakers), at the seminars in Zurich, Frankfurt and Munich.

Addition information is available here.

In the next months I’ll be speaking at several conferences and user group meetings. I’m looking forward to meet some of you at one of these events.

The abstracts of the presentations are the following (in Switzerland and Germany I’ll give the presentation in German; hence the last abstract is in German. The short summary is that I’ll present the Oracle Database 11g Release 2 feature called “Edition-Based Redefinition”):

• Interpreting Execution Plans: An execution plan describes the operations carried out by the SQL engine to execute an SQL statement. Every time you have to analyze a performance problem related to an SQL statement or simply question the decisions taken by the query optimizer, you must know the execution plan. Whenever you deal with an execution plan, you carry out three basic actions: you obtain it, you interpret it, and you judge its efficiency. The aim of this session is to describe in detail how you should perform the second of these three actions—in other words, how to read execution plans.
• Parallel Processing: When you submit a SQL statement to a database engine, by default it is executed serially by a single server process. Therefore, even if the server running the database engine has several CPUs, your SQL statement runs on a single CPU. This means that the amount of resources used for the execution of a SQL statement is restricted by the amount of processing a single CPU can do. The purpose of parallel processing is to distribute the execution of a single SQL statement over several CPUs.
  This session describes not only how parallel processing works, but also when to use it. In other words, how it can be used to improve performance. In addition, it also describes how to configure the database engine to efficiently support it.
• Edition-Based Redefinition: Das Upgraden von kritischen Applikationen kann sehr schwierig sein. Eines der Hauptprobleme besteht darin, dass aus Verfügbarkeitsgründen regelmässige und lange Downtimes nicht eingeplant werden können. Deshalb ist es wünschenswert online Verfahren für solche Upgrades zu implementieren. Dies bedingt nicht nur eine Applikation die dafür vorgesehen ist, sondern auch dass jede benutzte Software, wie z.B. der Datenbank Server, online Upgrades zulässt. Oracle hat dieses Problem seit Jahren erkannt. Leider wurde dies bis und mit Database 11g Release 1 nicht umgesetzt. Ab Oracle Database 11g Release 2 hat sich diese Situation sehr stark geändert. Mit „Edition-Based Redefinition“ bietet Oracle eine echte Unterstützung um online Upgrades zu implementieren. In diesem Vortrag wird diese neue Funktionalität im Detail präsentiert.

In May I’ll be speaking at two events and present a two-day class.

The abstracts of the presentations and the class are the following:

• Execution Plans: An execution plan describes the operations carried out by the SQL engine to execute a SQL statement. Every time you have to analyze a performance problem related to a SQL statement, or simply question the decisions taken by the query optimizer, you must know the execution plan. Whenever you deal with an execution plan, you carry out three basic actions: you obtain it, you interpret it, and you judge its efficiency. The aim of this session is to describe in detail how you should perform these three actions.
• Interpreting Execution Plans: An execution plan describes the operations carried out by the SQL engine to execute a SQL statement. Every time you have to analyze a performance problem related to a SQL statement, or simply question the decisions taken by the query optimizer, you must know the execution plan. Whenever you deal with an execution plan, you carry out three basic actions: you obtain it, you interpret it, and you judge its efficiency. The aim of this session is to describe in detail how you should perform the second of these three actions. In other words, how to read execution plans.
• SQL Plan Baselines: A SQL plan baseline is an object associated with a SQL statement that is designed to influence the query optimizer while it generates execution plans. As of Oracle Database 11g, SQL plan baselines substitute stored outlines. Actually, they can be considered an enhanced version of stored outlines. The aim of this session is to explain what SQL plan baselines are, how to create and manage them, and when to use them.
• Oracle Data Storage Internals: During this class, you will learn how Oracle stores data and indexes in blocks and segments, manages free space at the segment and the block level, and implements transactions. With this information you will be able, during the physical design, to minimize the access costs, to imcrease the throughput, and to avoid/minimize the fragmentation. The internal structure of the most important block types will be described in detail. The detailed content of this calss is the following:
  • Description of key identifiers (RDBA, ROWID, SCN, XID and UBA)
  • How to inspect blocks (formatted block dumps, BBED and raw blocks dumps)
  • Common block structures
  • Segment space management (extent map, high water mark, freelist, freelist groups, automatic segment space management, waits)
  • Transaction layer (block cleanout, interested transaction list, waits and deadlocks)
  • Undo block management (transaction table, undo chain)
  • Data storage (table and row directory, row migration/chaining, row format, compression, reorganizations)
  • Index Storage (B*Tree and bitmap concepts, structure, management, compression, reorganizations)
  • Corruptions (block checksum, block checking, find and resolve corruptions)

I will present the class Oracle Data Storage Internals in German. Later this year further dates will be planned in both German and English. If you are interested, do not hesitate to send me a message through the contact form.

In March I’ll be speaking at the following two events.

The abstracts of the presentations are the following:

• Bloom Filters: A bloom filter is a data structure used to support membership queries. Simply put, a bloom filter is used to test whether an element is a member of a given set or not. Since Oracle Database 10g Release 2, bloom filters are used in various situations. Unfortunately, no information about their usage is available in Oracle documentation.
  The aim of this presentation is to explain not only what bloom filters are, but also, and foremost, to describe how the database engine makes use of them. Specifically, it explains how the database engine uses bloom filters to reduce data communication between slave processes in parallel joins and to implement join-filter pruning.
• SQL Plan Baselines: A SQL plan baseline is an object associated with a SQL statement that is designed to influence the query optimizer while it generates execution plans. As of Oracle Database 11g, SQL plan baselines substitute stored outlines. Actually, they can be considered an enhanced version of stored outlines.
  The aim of this presentation is to explain what SQL plan baselines are, how to create and manage them, and when to use them.