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” (12. (more...)

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 23rd of January, 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.

The seminar in January is a Live Virtual Class organized by (more...)

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.

  • Initialization parameters exist at both the CDB level and the PDB level. This is a critical feature because it wouldn’t be acceptable to use the same set of initialization parameters for all PDB (of course, except if you have only one). For example, the initialization parameters used by the query optimizer primarily (more...)

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.

Version 12.1.0. (more...)

What’s the Difference between Row Migration and Row Chaining?

In Oracle Database migrated and chained rows are often confused. In my opinion, this is for two main reasons. First, they share some characteristics, so it’s easy to confuse them. Second, Oracle, in its documentation and in the implementation of its software, has never been very consistent in distinguishing them. So, it’s essential to understand the differences between the two.

When rows are inserted into a block, the database engine reserves some free space for (more...)

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.

As a follow-up, few days later Jonathan Lewis published a post entitled Trace file size.

Until recently, I didn’t bother to investigate how that feature works. But, (more...)

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 Edition, include adaptive join methods (the ability to switch the join method from a nested
    loops to a (more...)

SQL Trace in Oracle Database Exadata Express Cloud Service

Today I started having a look to the Oracle Database Exadata Express Cloud Service announced last week at Oracle OpenWorld. Note that since the amount of resources provided (in summary, 1 OCPU, 20 or 50 GB of database storage) is very limited, in general, in my opinion that service will only be useful for functional tests. In fact, if it wasn’t because that is the very first 12.2 release ( (more...)

Statement-level PARALLEL Hint

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:

  • PARALLEL(DEFAULT) forces the default DOP. Note that the (more...)

When CURSOR_SHARING=FORCE, Does Literal Replacement Always Take Place?

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 (more...)