Systematic Oracle SQL Optimization in Real Life

Key facts & details

Speakers: Cary Millsap, Tanel Poder, Kerry Osborne and Jonathan Lewis!

18. - 19. November 2010


This conference is already over! Stay tuned for more events in the future!


Thursday 18th November 2010

Friday 19th November 2010

Attending, asking questions and organizational details

System Requirements

Presentation abstracts

Thinking Clearly about Performance

By Cary Millsap

Creating high-performance as an attribute of complex software is extremely difficult business for developers, technology administrators, architects, system analysts, and project managers. However, by understanding some fundamental principles, performance problem solving and prevention can be made far simpler and more reliable. This session describes those principles, linking them together in a coherent journey covering the goals, the terms, the tools, and the decisions that you need to maximize your application’s chance of having a long, productive, high-performance life.

The paper behind this session was honored with the Oracle Development Tools User Group “Editor’s Choice” 2010 award and is featured in ACM Queue and Communications of the ACM magazines.

Understanding and Profiling Oracle Execution Plans

By Tanel Poder

Even though SQL statements and their compiled execution plans are just declarations of what work needs to be done, when Oracle gets to execution of a SQL statement, all processing is done procedurally. Every row-source, join method and access path is internally just a loop, continuing as long as there is data flowing from its child rowsources.

This session gives you in-depth understanding of how Oracle SQL plan execution physically works, a step beyond just interpreting the logical view of execution plan trees. This should give you good foundation for troubleshooting complex SQL performance issues and understanding things like in which cases one join method is better than another.

After covering the internals, we will look into tools and techniques for profiling SQL execution plans, for finding out in which part of the execution plan most of the response time is spent and where optimizer has wrongly estimated the amount of rows to process.

Writing Your SQL to Help the Optimizer

By Jonathan Lewis

It is easy to write SQL that has to run inefficiently, and it is easy to write SQL that results in very unstable performance. In this presentation I'll take an example of each type of problem and explain why sometimes you have to do a little extra work and introduce some extra complexity in the front-end code to allow the database to respond efficiently and consistently.

From this presentation you will learn to recognise SQL that is likely to run inefficiently, or vary dramatically in performance from day to day, or even minute to minute, and learn strategies for handling such code with a minimum of effort. You will also learn some of the benefits of frequency histograms and function-based indexes.

Controlling Execution Plans (without Touching the Code)

By Kerry Osborne

One of the most frustrating problems we face as DBAs is not being able to fix bad code. Our inability to change the code occurs for many reasons. In some cases we are dealing with packaged applications where the code is just not available. In other cases, the politics of an organization can dictate lengthy delays in making changes to code. And finally, Oracle sometimes just can't make up its mind about how best to deal with a specific SQL statement. Regardless of the reasons, Oracle specialists often find themselves in the unenviable position of being asked to make things better without touching the code.

Fortunately, Oracle provides many options for doing just that. Among the most effective approaches are techniques that focus on modifying execution plans of individual statements. Oracle has provided various mechanisms for accomplishing this over the years including Outlines, SQL Profiles, and SQL Baselines. These techniques provide laser-like specificity, by limiting their effect to a single statement. While these constructs are extremely powerful, they are not well documented and therefore not particularly well understood. Also, they suffer from some "quirkiness". For example, despite what the documentation implies regarding Outlines locking execution plans, there are situations where creating an Outline on an existing statement will actually cause the plan to change. This "quirk" is not limited to the older Outline construct. It has been carried forward to the newer SQL Profiles and SQL Baselines as well.

This presentation provides a general overview of the basic methods available as well as the underlying hint-based mechanism that they rely on. It also gives a historical perspective as to how we got where we are. Examples will be provided with specific scripts for making use of them, modifying them, and reporting on them. The presentation addresses where these techniques are appropriate and where they are not, and finally, offers some ideas on how to pitch the use of these features to others within your organization.

About the speakers

Cary Millsap

Cary Millsap is the founder and president of Method R Corporation, a small business that builds and optimizes software all over the world. Cary specializes in software optimization. He is the author of Optimizing Oracle Performance (O’Reilly 2003), for which he and Method R colleague Jeff Holt were granted the Oracle Magazine Author of the Year award. He has presented at hundreds of conferences and courses worldwide, and he is also published in Communications of the ACM. Cary spent the 1990s as a consultant and leader of consulting teams within Oracle Corporation. In 1999, Cary resigned as vice president of Oracle’s System Performance Group to start a career as a business owner.

Cary’s blog:

Method-R website:

Jonathan Lewis

Jonathan Lewis is a well-known figure in the Oracle world with more than 22 years experience using the software. He has published two books, contributed to three others, 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. He specialises in short-term assignments, typically of a design, review, or trouble-shooting nature and has an O1 visa for working in the USA.

Jonathan’s blog:

JL Computer Consultancy website:

Tanel Poder

Tanel Poder is an experienced consultant, speaker and entrepreneur with deep expertise in Oracle database internals, advanced performance tuning and end-to-end troubleshooting. He specializes in solving complex problems spanning multiple infrastructure layers such as UNIX, Oracle Database, networks, storage and recently the whole Oracle Exadata hardware-software stack. He is a co-founder of the E2SN start-up which is sill in stealth mode but will be heard of soon :-)

Tanel is a frequent speaker at conferences worldwide, he is one of the first Oracle Certified Masters in the world, passing the OCM DBA exam in 2002; he is also an Oracle ACE Director and a proud member of the OakTable Network.

Tanel’s blog:

E2SN website:

Kerry Osborne

Kerry Osborne began working with Oracle (version 2) in 1982. He has worked as both a developer and a DBA. For the past several years he has been focused on understanding Oracle internals and solving performance problems. He is an OakTable Network member and is the author of an upcoming Apress book on Exadata. Kerry is a frequent speaker at Oracle conferences. He is also a co-founder of Enkitec, an Oracle focused consulting company headquartered in Dallas, Texas.

Kerry’s blog:

Enkitec website: