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

Ad: The Method R Guide to MASTERING ORACLE TRACE DATA

The second edition of Cary Millsap‘s MASTERING ORACLE TRACE DATA (MOTD) is finally available. You can order it through amazon.com. 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 12.1.0.2

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

Auto SGA Management Impacts the Default Value of DB_FILE_MULTIBLOCK_READ_COUNT

The database engine determines the maximum disk I/O size used during multiblock reads (for example, full table scans or index fast full scans) by multiplying the values of the db_block_size and db_file_multiblock_read_count initialization parameters. The db_file_multiblock_read_count initialization parameter can be set explicitly, or, as of version 10.2, it’s also possible to instruct the database engine to automatically configure it. For the latter, simply don’t set it.

About the value which is automatically determined by (more...)

Online Training Ad: Oracle Database 12c – New Performance Features

On 10 December 2015 I’ll give an online training entitled Oracle Database 12c – New Performance Features. This short post provides key information about it.

Description

With every new release of Oracle Database, a number of features aimed at improving performance are introduced. It goes without saying that 12.1 is no exception to the rule. Notably, it introduces key improvements in three areas.

  • The query optimizer has been enhanced not only by the introduction (more...)