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 12.2.

Then I created a table with several numerical columns (the name of each column shows how many distinct values it contains), loaded 150 million rows into it (the size of the segment is 20 GB), and gathered the object statistics.

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 are the following:

  • OPTIMIZER_ADAPTIVE_FEATURES (12.1 only): if set to TRUE (default), performance feedback is enabled. Otherwise, it is disabled.
  • OPTIMIZER_ADAPTIVE_STATISTICS (12.1 with patch 22652097, and 12.2): if set to TRUE, performance feedback is enabled. Otherwise, which is the default, it is disabled.
  • PARALLEL_DEGREE_POLICY (12.1 and 12.2): it has to be set to ADAPTIVE (default is MANUAL) to enable performance feedback.

So far, so good.

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 on how to load it through SQL*Loader to a file. The typical steps to carry out to move the data from the source to the destination database (the best thing is to use a destination database with exactly the same version as the source database) are the following:

On the source database…

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.

Happy downloading as well as happy reading!

Adaptive Query Optimization: Backport of 12.2 Configuration in

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” (from now on, PSU). The second was installed on top of “Patch 24448103 – Database Proactive Patch” ( PBP).

From a functional point of view, both patches provides the 12.2 configuration in 12.1 as I describe it in this post. In fact, the behavior of setting the (un)documented initialization parameters as well as all the default values are the same. I was able to find only two differences between 12.2 and a patched 12.1.

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, I extended it from one day to two days.

Initialization Parameters Set at the PDB Level Are Not Always Honored

Before describing the issue that lead to this post, let’s shortly review how the handling of initialization parameters works in a multitenant environment.

Oracle 12cR2: Database parameters

For those in a desperate need to learn all 4841 database parameter variations of the…

Approximate Aggregate Transformation (AAT)

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.

Trace Files Split in Multiple Parts

Last January, in the following tweet, I pointed out that the documentation vaguely mentions that a trace file may be split into several files.