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


The second edition of Cary Millsap‘s MASTERING ORACLE TRACE DATA (MOTD) is finally available. You can order it through I had the pleasure not only to review MOTD while Cary was working on it, but also to write a foreword that summarizes what I think about the book. So, if you are asking yourself whether you should buy MOTD, here is my opinion/foreword…

In late 1999, through an Oracle Support note, I (more...)

Wrong Results Involving INDEX FULL SCAN (MIN/MAX) in

One of my customers that recently upgraded to 12c hit a bug that I think is good to be aware of. Note that as the title of this post states, the problem only occur in At least, I wasn’t able to reproduce it in any other version.

To reproduce it you simply need a composite partitioned table with a non-partitioned or global-partitioned index. In other words, if all your indexes are (more...)

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

Oracle Database In-Memory Demos

In the last 14 months I delivered a dozen of presentations covering the In-Memory Column Store. During many of them, I spent most of the time showing the audience several demos. The aim of this post is to share with you the scripts and a recording (MP4) of those demos.

Warning about Demos

The recordings show the results of running the scripts on an Exadata system. The performance figures are intended only to explain and (more...)

Also Extensions Created Because of SQL Plan Directives Can Invalidate Packages

The fact that an extension explicitly created by a user through DBMS_STATS can invalidate objects like packages is not new in 12c. It has been like that since the introduction of extensions in 11g. In my opinion, since such an invalidation takes place only when a developer or DBA triggers it, I do not consider it a major problem.

What is new in 12c is that a SQL plan directive can instruct DBMS_STATS to create (more...)