In the past I gave a number of 1-day seminars about the new performance features available in Oracle Database 12c Release 1. On the 22nd of February, for the first time, I’ll give an updated version of that seminar with content about both Release 1 and Release 2. Note that because there is more content, I extended it from one day to two days.
There are situations where approximate results are superior than exact results. Typically, this is the case when two conditions are met. First, when the time and/or resources needed to produce exact results are much higher than for approximate results. Second, when approximate results are good enough. Approximate results are for example superior in case of exploratory queries or when results are displayed in a visual manner that doesn’t convey small differences.
The aim of this post is to summarize the knowledge about the 12.1 and 12.2 adaptive query optimizer configuration that, as far as I know, is spread over a number of (too many) different sources.
First of all, let’s shortly review which adaptive query optimization features exist:
From version 11.2 onward, the PARALLEL hint supports two syntaxes: object-level and statement-level. The object-level syntax, which is the only one available up to version 11.1, overrides the DOP associated to a tables. The statement-level syntax can not only override the PARALLEL_DEGREE_POLICY initialization parameter at the SQL statement level, but also force the utilization of parallel processing.
The statement-level PARALLEL hint supports the following values:
The concept of cursor sharing is simple. If an application executes SQL statements containing literals and if cursor sharing is enabled (i.e. CURSOR_SHARING=FORCE), the database engine automatically replaces the literals with bind variables. Thanks to these replacements, hard parses might be turned into soft parses for the SQL statements that differ only in the literals.
The question raised by the title of this post is: in case cursor sharing is enabled, does literal replacement always take place?
The short answer is no.
I’m aware of three cases where it doesn’t take place. The first two cases are summarized by the following note that I published in the second edition of Troubleshooting Oracle Performance (page 434).
One of my customers that recently upgraded to 12c hit a bug (22913528) that I think is good to be aware of. Note that as the title of this post states, the problem only occur in 18.104.22.168. At least, I wasn’t able to reproduce it in any other version.
To reproduce it you simply need a composite partitioned table with a non-partitioned or global-partitioned index. In other words, if all your indexes are local, you shouldn’t be impacted by the bug.
The SQL statements I use to prepare the schema to reproduce it are the following:
SQL plan directives are a new concept introduced in version 12.1. Their purpose is to help the query optimizer cope with misestimates. To do so, they store in the data dictionary information about the predicates that cause misestimates. Simply put, the purpose of SQL plan directives is to instruct the database engine to either use dynamic sampling or automatically create extended statistics (specifically, column groups).
Since the database engine automatically maintains (e.g. creates and purges) SQL plan directives, in some situations it is necessary to copy the SQL plan directives created in one database to another one. This can be done with the help of the DBMS_SPD package.
Here are the key steps for such a copy:
To make upgrades easier, I regularly see people considering disabling query optimizer features by setting the OPTIMIZER_FEATURES_ENABLE initialization parameter to a non-default value. My general opinion about this “habit” is summarized in TOP with the following two sentences:
Changing the default value of the OPTIMIZER_FEATURES_ENABLE initialization parameter is only a short-term workaround. Sooner or later the application should be adapted (optimized) for the new database version.
The aim of the STATISTICS COLLECTOR row source operation, which is used in adaptive plans, is to buffer all data produced by its child operation until it is known whether the inflection point is crossed. It goes without saying that buffering requires memory and, therefore, Oracle Database has to limit the amount of memory that can be allocated for that purpose. As a result, in some situations no adaptive plans can be used because according to the query optimizer estimations too much memory is required.
A special case of the previous limitation is when the row source operation under the STATISTICS COLLECTOR operation produces a LOB. In fact, when a LOB is detected, independently of the amount of data that is expected to be produced, the adaptive plan is bypassed by the query optimizer.
Let’s have a look to an example:
The company I work for, Trivadis, is very pleased to organize, on the 10th and 11th of June, 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: