You can use the V$SQL.IS_RESOLVED_ADAPTIVE_PLAN column to know whether the execution plan associated to a child cursor is adaptive or not. Specifically, to know whether the query optimizer selected either an adaptive join method or an adaptive star transformation (notice that it is not set when the hybrid hash distribution is involved). The column takes […]
SPD State Does Not Change If Adaptive Statistics Are Disabled
The aim of this post is to point out an issue (bug?) that I recently discovered. But, before talking about it, I need to go through a rather long introduction about the state of SQL plan directive (SPD). As of version 12.1.0.2, an SPD has two state information. You can see both of them through […]
Activating and Deactivating Performance Feedback
Performance feedback is one of the adaptive query optimizer features introduced in Oracle Database 12c. The aim of this short post isn’t to explain how it works, but to simply warn you about a buggy behavior in its configuration. The parameters that control performance feedback in 12c are the following: OPTIMIZER_ADAPTIVE_FEATURES (12.1 only): if set […]
Adaptive Query Optimization: Backport of 12.2 Configuration in 12.1.0.2
I finally managed to install and test patch 22652097 (PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES). Actually, I installed and tested two patches. The first was installed on top of “Oracle Database Patch 23054246 – Database Patch Set Update 12.1.0.2.160719” (from now on, 12.1.0.2.160719 PSU). The second was installed on top of […]
Ad – Oracle Database 12c Release 1 and 2: New Performance Features
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, […]
Adaptive Query Optimization Configuration: Parameters, Preferences and Fix Controls
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: Adaptive plans, which are only available in Enterprise […]
Also Extensions Created Because of SQL Plan Directives Can Invalidate Packages – ...
In a previous post I described a situation where the creation of an extension, independently of whether it’s carried out manually by a person or automatically by the database engine, can lead to the invalidation of PL/SQL objects. In this second post on that subject, I describe, with the help of an example (I love […]
Also Extensions Created Because of SQL Plan Directives Can Invalidate Packages
UPDATE 2015-12-18: As metioned by Bryn Llewellyn in this tweet, the invalidation I describe below is caused by bug 19450314. A patch for it is available here for several releases (e.g. it is available for 12.1.0.1.0, 12.1.0.2.1 or 12.1.0.2.13, but not for 12.1.0.2.0). I successfully tested it on 12.1.0.1.0. UPDATE 2016-11-08: The patch related to […]
Bugs Related to SQL Plan Directives Pack and Unpack
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 […]
STATISTICS COLLECTOR Row Source Operation and LOBs
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 […]