CBO Days 2012

Meet international Oracle performance specialists

Jonathan Lewis

Jonathan Lewis is a well-known figure in the Oracle world with more than 24 years experience using the software. He has published three books about Oracle - the most recent being "Oracle Core" published by Apress Nov 2011 - and contributed to three others. He runs a couple of websites and contributes fairly regularly to newsgroups, forums, and User Group magazines and events around the world. Jonathan has been self-employed for most of his time in the IT industry.

For the last eighteen years he has specialised in short-term assignments, typically of a design, review, or trouble-shooting nature - often spending no more than two or three days on a client site to address problems . He runs seminars about using Oracle all over the world and has visited more than 50 different countries (and more than a dozen US states) to talk about, or trouble-shoot, Oracle systems.

 

The Evolution of Histograms
To produce good execution plans the optimizer needs good statistics, but when there are any types of skew in the data simple statistics may not be enough. Data skew results in cases where you may need histograms to help the optimizer. Unfortunately histograms can be expensive to create and, if your data patterns are unlucky, may cause as many problems as they solve.

Historically Oracle gave us frequency histograms and height-balanced histograms, so we start this presentation by looking at the different benefits they offer, the costs involved, and the special cases where better strategies are needed. We then move on to looking at the enhancements to histograms in the latest generation of database technology, and show how they do better than the existing mechanism, both in creation time and in usefulness.

 

New strategies for Statistics in 11g with Jonathan Lewis
Oracle 11g offers two key benefits in the area of stats collection - the first is in the statistics you can collect, the second is in the speed with which you can collect them. We start the presentation by taking a look at how you can collect statistics more efficiently and more accurately by invoking the "approximate NDV" mechanism, including a visual representation of why this new method works why it can be trusted to be efficient and accurate. There are, inevitably, limitations on how useful the new technique is, and we examine some of the ways it could be improved moving on, as we do so, to take a brief look at the "extended statistics" especially "column group" statistics, and how these can be used to help the optimizer and the process of stats collection.

Randolf Geist

Randolf Geist has been working with Oracle software for 15 years now, and since 2000, as freelance database consultant. He focuses primarily on performance-related issues, and in particular in helping people to understand and unleash the power of the Oracle cost based optimizer (CBO). He blogs about CBO related issues and also regularly contributes to the official OTN database-related forums. Randolf
is a regular speaker at all major user conferences around the world, and also acts as an instructor for the Oracle University as part of their "Celebrity Seminar" program, where he gives seminars on "Advanced Oracle Performance Troubleshooting" and "Mastering Parallel Execution". Randolf is a proud member of the Oak Table Network and the Oracle ACE Director program.

 

Understanding Parallel Execution

Oracle Enterprise Edition includes the powerful parallel execution feature that allows spreading the processing of a single SQL statement across multiple threads. The feature is fully integrated into the SQL execution runtime engine and automatically distributes the work across the so called parallel slaves. However, like any other feature, it doesn't necessarily provide the expected performance improvement out of the box in every case, hence it is important to understand the building blocks and their implications.

In this session we'll look at the specifics of parallel execution plans, in particular:

  • General considerations
  • DOP (degree of parallelism) considerations
  • The implications of the producer / consumer model
  • Parallel distribution methods
  • Executions plans consisting of multiple DFOs (data flow operations)
  • Data distribution skew

Jože Senegačnik

Jože Senegacnik has more than 24 years of experience in working with Oracle products. He began in 1988 with Oracle Database version 4. He specializes in performance tuning and problems connected with the Cost Based Optimizer (CBO). Joze is an internationally recognized speaker, and a member of the highly respected OakTable Network (http://oaktable.net). He is a regular speaker at user-group conferences. He was awarded Oracle ACE Director membership for his long record of positive contributions to the Oracle community. In private life he likes flying around with his Mooney and specializes in cooking and baking.

Query Transformations
One of the first operations performed by the CBO during the SQL statement optimization phase is query transformation. The goal of query transformation is to generate semantically equivalent form of SQL statement which produces the same results, but differ in performance. In the presentation we will discuss the most common transformations like sub-query un-nesting, predicate move around, and join predicate push-down into view, simple and complex view merging and others.

Mohamed Zait

Dr. Mohamed Zait received his M.S. in Computer Science from the University of Pierre and Marie Curie in 1990 (Paris, France) and a Ph.D. in Computer and Information Systems from the University of Pierre and Marie Curie in 1994. He did his thesis work at INRIA (France) on query optimization in parallel database systems. In 1994, he joined the data mining group, at the IBM Almaden Research Center (San Jose, California), then moved to Oracle in 1996 to join the Datawarehouse and Language Technologies. He is currently the manager of the query optimizer group at Oracle responsible for all research and development in query optimization.

He authored several journal and international conference papers in both data mining and database areas. His domains of research include query optimization, memory management, manageability, diagnosability, parallelism, data mining.

Maria Colgan

Maria Colgan is a senior principal product manager at Oracle Corporation and has been with the company since version 7.3 was released in 1996. Maria's core responsibilities are the Oracle Optimizer, statistics, and SQL Tuning. Based on Maria's extensive experience in Oracle's Server Technology Performance Group - where she conducted competitive customer benchmarks and tuning sessions - Maria creates and lectures on the Oracle Optimizer and the statistics that feed it. She is also a contributing author to the Oracle Optimizer blog http://blogs.oracle.com/optimizer.

 

The History of the Oracle Optimizer
Twenty years ago the face of the Oracle Optimizer changed forever. Gone were the 20 simple rules, that once determined what execution plan would be chosen. With the launch of Oracle 7, in 1992, we ushered in the age of the Cost Based Optimizer (CBO). Several other concepts came too, including the notion of having to gather and maintain statistics and the possibility of plan changes when something in the environment changes. This new era struck fear in the hearts of many Oracle users and a lot of people resisted the change. In this session we will look back over the last 20 years of evolution that have gone into the Oracle Optimizer and the statistics that feed it. We will show how each change, although scary has improved the execution plans chosen by the Optimizer and taken us one step closer to the Holy Grail, of always finding the optimal execution plan for every SQL statement.

Christian Antognini

Since 1995, Christian Antognini has focused on understanding how the Oracle database engine works. His main interests include logical and physical database design, the integration of databases with Java applications, the query optimizer and basically everything else related to application performance management and optimization. He is currently working as a senior principal consultant and trainer at Trivadis in Zürich, Switzerland.

If Christian is not helping one of his customers get the most out of Oracle, he is somewhere lecturing on application performance management or new Oracle Database features for developers. In addition to classes and seminars organized by Trivadis, he regularly presents at conferences and user-group meetings. He is a proud member of the OakTable Network.

Christian is the author of the book Troubleshooting Oracle Performance (Apress, 2008) and the co-author of Der Oracle DBA (Hanser, 2011).

How the Query Optimizer Learns from ITs Mistakes
The aim of the query optimizer is not only to deliver the SQL engine execution plans that describe how to process data but also, and more importantly, to deliver efficient execution plans. Even though this central component of Oracle Database is enhanced with every new release, there are always cases where it makes mistakes. It is therefore critical that the query optimizer learns from these mistakes automatically. This is exactly what was introduced, starting with 11g, with feedback-based optimization. The aim of this presentation is to review and demonstrate the available features in this area.