12cR1

Chris Antognini's picture

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:

Chris Antognini's picture

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:

randolf.geist's picture

Adaptive Cursor Sharing Fail

Here is another example (besides the fact that Adaptive Cursor Sharing only gets evaluated during a PARSE call (still valid in 12c) and supports a maximum of 14 bind variables) I've recently come across at a client site where the default implementation of Adaptive Cursor Sharing fails to create a more suitable execution plan for different bind variable values.Broken down to a bare minimum the query was sometimes executed using non-existing values for a particular bind variable, but other times these values were existing and very popular. There were two suitable candidate indexes and one of them appeared to the optimizer more attractive in case of the "non-existing" value case.

randolf.geist's picture

Nested Loop Join Physical I/O Optimizations

Having done my mini-series on Nested Loop join logical I/O optimizations a while ago I unfortunately never managed to publish anything regarding the Nested Loop join physical I/O optimizations, which are certainly much more relevant to real-life performance.Therefore the main purpose of this blog post is to point you to Nikolay Savvinov's (whose blog I can recommend in general) great mini-series covering various aspects of these optimizations:Part 1Part 2Part 3SummaryOne point that - at least to me - isn't entirely clear when reading Nikolay's series is which specific plan shape he refers to, in particul

randolf.geist's picture

DBMS_STATS - Gather statistics on tables with many columns - 12c update

This is just a short 12c update on my post about gathering statistics on tables with many columns from some time ago.I'm currently investigating the "Incremental Statistics" feature in 12.1.0.2 for a client, which probably will be worth one or more other posts, but since we're still in the process of evaluating and installing various patches it's too early to write about that.As part of the investigation I've noticed a significant change in behaviour in 12.1.0.2 compared to previous versions when it comes to gathering statistics on tables with many columns, hence this post here.The key message of the original post was that DBMS_STATS needs potentially several passes when gathering statistics on tables with many columns, which meant a significant increase in overall work and resource consumption, exaggerated by the fact that tables with that many colu

randolf.geist's picture

Comparing Columns Containing NULL Values

Prompted by a (not really that) recent discussion on the OTN forum I've decided to publish this note.Sometimes you have the task of comparing column values and handling the NULL value cases correctly makes this rather cumbersome for columns that are allowed to be NULL.The "official" SQL way of comparing two column values and to find out whether they are equal or not - under the assumption that having NULL in both columns should be treated as equal (a point that can be argued) would read in SQL something like the following for the "unequal" case:


column1 != column2 or (column1 is null and column2 is not null) or (column1 is not null and column2 is null)

and

randolf.geist's picture

New Version Of XPLAN_ASH Utility

A new version 4.23 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

This version comes only with minor changes, see the change log below.

Here are the notes from the change log:

- Finally corrected the very old and wrong description of "wait times" in the script comments, where it was talking about "in-flight" wait events but that is not correct. ASH performs a "fix-up" of the last 255 samples or so and updates them with the time waited, so these wait events are not "in-flight"

- Removed some of the clean up code added in 4.22 to the beginning of the script, because it doesn't really help much but spooled script output always contained these error messages about non-existent column definitions being cleared

Chris Antognini's picture

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 always take place?

The short answer is no.

I’m aware of three cases where it doesn’t take place. The first two cases are summarized by the following note that I published in the second edition of Troubleshooting Oracle Performance (page 434).

randolf.geist's picture

Combining Features - Wrong Results With Scalar Subquery Caching

Quite often you can get into trouble with Oracle when you start combining different features.In this case of one my clients it is the combination of user-defined PL/SQL functions that can raise exceptions (think of currency conversion and a non-existent currency code gets passed into the function), DML error logging and attempting to improve performance by wrapping the PL/SQL function call into a scalar subquery to benefit from the built-in scalar subquery caching feature of the SQL runtime engine.As long as the scalar subquery didn't get used everything worked as expected, but after adding the scalar subquery after some while it became obvious that wrong results occurred - in that particular case here it meant rows that should have been rejected and written to the error logging table due to the exception raised in the user-defined PL/SQL function suddenly showed up in the target table, and what was even more worrying - they included a co

Chris Antognini's picture

Also Extensions Created Because of SQL Plan Directives Can Invalidate Packages – Take Two

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 examples!) based on the extension_invalidate_pkg_remote.sql script, what can happen when the table on which the extension is created is stored in a remote database.

Let’s start by creating the required objects. Notice that all objects are local but, because the package body references the table through a database link that points to the very same schema owning all other objects, the database engine considers it a remote table.

To prevent automated spam submissions leave this field empty.
Syndicate content