Today I faced a performance problem caused by a bad cardinality estimation involving a CDB view in a 12.1.0.2 multitenant environment. While solving the problem I did a number of observations that I try to summarize in this blog post. First of all, when checking the execution plan of a query already running for more […]
How Well a Query Optimizer Handles Subqueries?
At the beginning of December, at the UKOUG Tech17 conference in Birmingham (GB), I presented a comparison of the query optimizers of MySQL 8.0.3 and PostgreSQL 10.1. One of the things I talked about is their ability to handle subqueries. I summarized my findings with the following sentence: Simple sub-queries that are not correctly optimized […]
Purging Unused Triples Is a Never-ending Operation
This is a short post to point out a problem I recently discovered and for which I couldn’t find information online. Hence, in case you hit it, thanks to this short post I hope that you’ll save some time. The documentation describes a procedure you can use to get rid of dangling triples stored in […]
SQL_ADAPTIVE_PLAN_RESOLVED Is Broken
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 […]
The APPROX_MEDIAN Function – A Test Case
The aim of this post is not to explain how the APPROX_MEDIAN function works (you find basic information in the documentation) but to show you the results of a test case I ran to assess how well it works. Here’s what I did… I started in the Oracle Database Public Cloud an instance of version […]
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 […]
Offline Analysis of ASH Data with ASHDUMP
From time to time, it happens to me to carry out offline analyses of ASH data. For that, I mean to analyze the ASH data without having access to the database instance that generated it. For that purpose, Oracle Database provides the possibility to dump the content of the ASH buffer as well as information […]
TVD$XTAT 4.0 Beta 11
This is just a short note to point out that I finally uploaded a new version of TVD$XTAT. To download it click here. Not only I introduced some new features, but I also fixed several bugs. The detailed change log since Beta 10 is the following: Added support for bind sets using array processing Added […]
Scripts to Download Oracle Database 12c Release 2 Documentation
In the past (here, here and here) I already shared with you the scripts I use to download the Oracle Database documentation. The aim of this short post is to reference the script I just wrote for the latest version: 12c Release 2. download122.sh Happy downloading as well as happy reading! Update 2018-02-27: new version […]