Yesterday, during the talk “The Query Optimizer in Oracle Database 12c – What’s New?” at OakTable World UK 2013, an attendee asked me an interesting question that can be summarized as follows: does ASH show the switch of execution plan that takes place for an adaptive plan?
Since I didn’t know the answer, here are some tests I did to find out how it works.
First of all, it’s important to recognize that the plan hash value related to an adaptive plan changes depending on which subplan is activated. Let’s take as example the following query (notice that the execution plan is adaptive and that the hash value of the default plan is 1837274416):
The aim of this post isn’t to explain what the “exadata mode” is. Hence, if you don’t know what it is, before continuing reading have a look to this post published on Kerry Osborne’s blog. The only thing I would like to add is that the “exadata mode” is available as of 126.96.36.199 or when a patch implementing the enhancement associated to bug 10248538 is installed.
The key information I would like to share with you is that, in some situations, gathering system statistics in “exadata mode” is pointless. Let me explain why… But, before doing so, it’s important to review how the query optimizer computes the cost of full scans.
The key formula used by the query optimizer to compute the I/O cost of a full scan is the following:
io_cost = ceil ( blocks / mbrc * mreadtim / sreadtim ) + 1
The 11th and 12th of December and, therefore, the CBO Days in Zurich are approaching. Given the high quality of the speakers that accepted my invitation and the number of people that decided to attend the event, in my opinion it will be a blast! By the way, if you are interested in attending the event, do not wait any longer. In fact, from the 80 available seats, 75 are taken.
Here is a preview of the agenda:
Tuesday 11th December
The company I work for, Trivadis, is very pleased to organize, the 11th and 12th of December, an outstanding seminar with top guest speakers in Zurich. This year’s focus will be on the Oracle query optimizer, also known as a cost-based optimizer (CBO).
The query optimizer is not only one of the most complex pieces of software that constitutes the Oracle kernel; it is also one of the most unappreciated. Why? To make the best and most efficient use of the query optimizer, you definitely need to understand how it works. This is exactly what we are aiming for at the CBO Days.
Enjoy the two days with:
- Jonathan Lewis, JL Computer Consultancy (GB)
- Jože Senegačnik, DbProf (SI)
- Maria Colgan, Optimizer product manager, Oracle Corporation (USA)
- Mohamed Zait, Optimizer chief developer, Oracle Corporation (USA)
- Randolf Geist, Freelance (DE)
- Christian Antognini, Trivadis (CH)
Most execution plans can be interpreted by following few basic rules (in TOP, Chapter 6, I provide such a list of rules). Nevertheless, there are some special cases. One of them is when an index scan, in addition to the access predicate, has a filter predicate applying a subquery.
Challenges and Chances of the 11g Query Optimizer is the name of a presentation I gave at several events (e.g. Trivadis Performance Days, Oracle OpenWorld, DOAG Konferenz, UKOUG Conference) throughout 2011. Its abstract is the following:
With every new release, the query optimizer is enhanced. Oracle Database 11g Release 1 and Release 2 are no exception to the rule. Specifically, they introduce key improvements in the following areas: indexing, optimization techniques, object statistics and plan stability. The aim of this presentation is to review the new features from a practical point of view as well as to point out challenges related to them. In other words, to let you know what you can expect from the query optimizer when you upgrade to Oracle Database 11g.
At page 189 of TOP I wrote the following piece of text:
In summary, with the initialization parameter optimizer_secure_view_merging set to TRUE, the query optimizer checks whether view merging could lead to security issues. If this is the case, no view merging will be performed, and performance could be suboptimal as a result. For this reason, if you are not using views for security purposes, it is better to set this initialization parameter to FALSE.
What I didn’t consider when I wrote it, it is the implication of predicate move-around related to Virtual Private Database (VPD). In fact, as described in the documentation, that parameter controls view merging as well as predicate move-around.
At page 383 of my book I wrote the following sentence (BTW, the same information is also provided by Table 9-3 at page 381):
With B-tree indexes, IS NULL conditions can be applied only through composite B-tree indexes when several SQL conditions are applied and at least one of them is not based on IS NULL or an inequality.
The text continues by showing the following examples (notice that in both cases the IS NULL predicate is applied through an access predicate):