Wednesday Jun 24, 2015

What you need to know about SQL Plan Management and Auto Capture

SQL Plan Management (SPM) is an Oracle database feature that allows you to establish a set of SQL execution plans that will be used even if the database is subject to changes that would otherwise cause execution plan changes to occur. For example, you might have an end-of-day batch run that operates in a business context where there are extreme peaks and troughs in daily volume, or perhaps you are upgrading a database and want to be sure that plans are carried over (at least initially). You do not have to fix execution plans in stone with SPM, you can use plan evolution to automate the process of finding improved plans, improving performance in a controlled way and at your own pace. If you’re not familiar with SPM, a very good place to start is to take a look at Maria Colgan’s four-part blog post on the subject. It gives you all the tools you need to get started.

If you are using SPM in Oracle Database 11gR2 or 12c, or if you are considering whether you should use it, then this blog post is for you. I decided to publish this post because I recently encountered a couple of environments that ran into, let’s say, “difficulties” with SPM when capturing SQL plan baselines automatically and continuously over a very long period of time (more than a year in fact). I’d like to give you a few pointers to avoid running into the same problems and why automatic SQL baseline capture was never intended to be used in that way.

[Read More]

Tuesday May 26, 2015

Space Management and Oracle Direct Path Load

Most of you will be familiar with the concept of direct path load and how it’s an efficient way to load large volumes of data into an Oracle database as well as being a great technique to use when moving and transforming data inside the database. It’s easy to use in conjunction with parallel execution too, so you can scale out and use multiple CPUs and database servers if you need to process more data in less time.

Probably less well known is how the Oracle database manages space during direct path load operations. This is understandable because the Oracle database uses a variety of approaches and it has not always been very obvious which one it has chosen. The good news is that Oracle Database 12c from version onwards makes this information visible in the SQL execution plan and it is also possible to understand what’s happening inside earlier releases with a bit of help from this post and some scripts I’ve linked to at the end.

[Read More]

Monday Dec 19, 2011

Why was the RULE hint ignored?

Recently I got a call from a customer that had just upgraded to Oracle
Database and was panicking because they thought the Rule Based
Optimizer (RBO) had been removed from Oracle Database 11g. The reason
they thought this was because when they tried to add the RULE hint to a
SQL statement they still got a Cost Based Optimizer (CBO) plan.

[Read More]

Thursday Oct 20, 2011

Optimizer Technical Papers

We recently got a comment about our paper on adaptive cursor sharing and cardinality feedback, in which the author wished for more technical papers from the optimizer team. We've actually published papers on a few different topics over the years, so we wanted to summarize and post links to them here. Unfortunately they are not all available online for free, but where possible, we've posted links to the full text.

Cost-Based Query Transformation in Oracle
Rafi Ahmed, Allison W. Lee, Andrew Witkowski, Dinesh Das, Hong Su, Mohamed Zait, Thierry Cruanes: Cost-Based Query Transformation in Oracle. VLDB 2006: 1026-1036
This paper talks about many of the query transformations in Oracle 11g which we have discussed here before, including join elimination, view merging, subquery unnesting, join predicate pushdown, join factorization, plus some others that we have not covered here before.  It also describes the cost-based framework itself, some of the challenges of transforming queries in a cost-based manner, and presents performance results for some experiments on a query workload from the Oracle E-Business Suite.

Optimizer Plan Change Management (full text)
Mohamed Ziauddin, Dinesh Das, Hong Su, Yali Zhu, Khaled Yagoub: Optimizer plan change management: improved stability and performance in Oracle 11g. PVLDB 1(2): 1346-1355 (2008)
This describes SQL plan management, which we discussed on here in a four-part SPM series.  The paper gives a bit more detail about the internals of SPM, plus analyzes the behavior of SPM on a large query workload from Oracle E-Business Suite.

Closing the Query Processing Loop in Oracle 11g
(full text)
Allison W. Lee, Mohamed Zait: Closing the query processing loop in Oracle 11g. PVLDB 1(2): 1368-1378 (2008)
This paper discusses adaptive cursor sharing and cardinality feedback, plus the general concept of using feedback from execution to improve plan selection.  We've covered both of these topics on here before, but the paper also includes an analysis of the performance impact of the two features on a query workload from Oracle E-Business Suite.

Efficient and scalable statistics gathering for large databases in Oracle 11g
Sunil Chakkappen, Thierry Cruanes, Benot Dageville, Linan Jiang, Uri Shaft, Hong Su, Mohamed Zait: Efficient and scalable statistics gathering for large databases in Oracle 11g. SIGMOD Conference 2008: 1053-1064
This paper discusses two major improvements to statistics gathering in 11g -- approximate NDV and incremental statistics gathering.  We've discussed both of these topics here before, but the paper has some additional details of the algorithms, plus results of experiments on a few different data sets.

Enhanced Subquery Optimizations in Oracle, VLDB 2009 (full text)
Srikanth Bellamkonda, Rafi Ahmed, Andrew Witkowski, Angela Amor, Mohamed Zait, Chun Chieh Lin: Enhanced Subquery Optimizations in Oracle. PVLDB 2(2): 1366-1377 (2009)
This paper talks about several of the newer subquery optimizations in Oracle.  We talked about basic subquery unnesting in two posts here a while ago, and alluded to some of the other subquery transformations we perform -- this paper talks about all of those.  It also talks about a new form of join elimination which we introduced in 11gR2.  Nearly all of the topics in this paper are NOT covered here on the blog yet, but we hope to fix that soon!

For future reference, we will be adding a new section on the right side of the page called "Optimizer Technical Papers" with links to all of these papers.

Tuesday May 24, 2011

Cardinality Feedback

Cardinality feedback was introduced in Oracle Database 11gR2. The purpose of this feature is to automatically improve plans for queries that are executed repeatedly, for which the optimizer does not estimate cardinalities in the plan properly. The optimizer may misestimate cardinalities for a variety of reasons, such as missing or inaccurate statistics, or complex predicates. Whatever the reason for the misestimate, cardinality feedback may be able to help.[Read More]

Monday May 09, 2011

Explain the Explain Plan white paper finally published!

New Oracle Optimizer white paper, Explain the Explain Plan, published on OTN.

[Read More]

Monday Mar 28, 2011

What happened to the Optimizer white papers on OTN?

We have gotten a lot of questions recently about what happened to the Optimizer white papers that use to be on OTN. The white papers are still there but the URL has changed slightly, which is causing the problem. Here are new URL's for the most recent Optimizer white papers:

SQL Plan Management in Oracle Database 11g

Upgrading from Oracle Database 9i to 10g: What to expect from the Optimizer

Upgrading from Oracle Database 10g to 11g: What to expect from the Optimizer

Monday Aug 16, 2010

Dynamic sampling and its impact on the Optimizer

Dynamic sampling (DS) was introduced in Oracle Database 9i Release 2 to improve the optimizer's ability to generate good execution plans. The most common misconception is that DS can be used as a substitute for optimizer statistics. The goal of DS is to augment the optimizer statistics; it is used when regular statistics are not sufficient to get good quality cardinality estimates.

So how and when will DS be use?

[Read More]

Monday Aug 02, 2010

We have moved!

You might have been wondering why things had gone so quiet on the Optimizer development team's blog Optimizer Magic over the last few months. Well the blog has moved to All of the old articles have moved too and we plan to be a lot more active at our new home, with at least one new post every month.

Monday Dec 21, 2009

Upgrading from 9i to 11g and the implicit migration from RBO

Now that Oracle Database 11g Release 2 is out, more and more folks are considering upgrading to 11g. However, if you are currently on Oracle 9i then you will have to tackle both the upgrade to a new release and the migration from the Rule-Based Optimizer (RBO) to the Cost-based Optimizer (CBO). The RBO was de-supported in Oracle Database 10g, so in Oracle Database 11g you must use the CBO. Thanks to SQL Plan Management (SPM), originally discussed in our January post, you can handle the upgrade and the migration with ease. By loading the original RBO plans into SPM you can ensure the Optimizer won't change the execution plans during an upgrade and the implicit migration from RBO to CBO. In fact, there are two possible approaches you can take.[Read More]

Thursday Jun 26, 2008

Why are some of the tables in my query missing from the plan?

In 10gR2, we introduced a new transformation, table elimination (alternately called "join elimination"), which removes redundant tables from a query. A table is redundant if its columns are only referenced to in join predicates, and it is guaranteed that those joins neither filter nor expand the resulting rows. There are several cases where Oracle will eliminate a redundant table. We will discuss each case in turn.[Read More]

Wednesday Feb 27, 2008

What to expect from the Optimizer when upgrading from Oracle Database 9i to 10g

One of the most daunting activities a DBA can undertake is upgrading the database to a new version. Having to comprehend all of the new features and to deal with potential plan changes can be overwhelming. In order to help DBA's upgrade from Oracle Database 9i to 10g a new whitepaper called "Upgrading from Oracle Database 9i to 10g: What to expect from the Optimizer" has recently been posted on Oracle Technology Network (OTN). This paper aims to explain in detail what to expect from the CBO when you upgrade from Oracle database 9i to 10g and describes what steps you should take before and after the upgrade to minimize any potential SQL regressions. This is a must read for any DBA planning on upgrading from 9i to 10g in the near future!

Tuesday Dec 11, 2007

Outerjoins in Oracle

Since release 6, Oracle has supported a restricted form of left outerjoin, which uses Oracle-specific syntax. In 9i, Oracle introduced support for ANSI SQL 92/99 syntax for inner joins and various types of outerjoin. The Oracle syntax for left outerjoin and that of ANSI SQL 92/99 are not equivalent, as the latter is more expressive.

There appears to be some confusion about equivalence between ANSI outer join and Oracle outer join syntax. The following examples explain the equivalences and in-equivalences of these two syntaxes.

[Read More]

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.


« July 2015