If you run TKPROF without arguments, you get a complete list of its arguments with a short description for each of them (here the output generated by version 18.1.0): $ tkprof Usage: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] table=schema.tablename Use ‘schema.tablename’ with ‘explain=’ option. explain=user/password Connect […]
CDB Views and Query Optimizer Cardinality Estimations
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 […]
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 […]
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 scripts I just wrote for the latest version: 12c Release 2. download122.cmd download122.sh Happy downloading as well as happy reading! Update 2018-02-27: new […]
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 […]