RESULT_CACHE Hint in 12.1.0.2

Every new Oracle Database patch introduces not only documented features, but also undocumented ones. In this short post I would like to provide information about an enhancement of the RESULT_CACHE hint.

According the documentation, the RESULT_CACHE hint has no parameter. But, as of 12.1.0.2, at least the SNAPSHOT parameter is supported. Its purpose is to specify how much time (in seconds) after the creation the result cache entry has to (more...)

Troubleshooting Oracle Performance, 2nd Edition – Downloadable Files

This is just a short post to point out that the downloadable files are available here. You can download either a ZIP or a TAR file containing all files, or single files.

Since every file has its own URL, you can also directly executed them in SQL*Plus. For example:

SQL> @http://antognini.ch/downloads/top2/chapter02/selectivity.sql

I’m sorry for the delay!

Differences between the First and the Second Edition of Troubleshooting Oracle Performance

Several people asked me how much I changed in the second edition. The short answer is… A LOT.

The main goals set for the revision of the book were the following:

  • Add content about Oracle Database 11g Release 2 and Oracle Database 12c Release 1.
  • Remove content about Oracle9i and Oracle Database 10g Release 1.
  • Add content that was “missing” in the first edition (for example, features like hierarchical profiler, active session history, AWR and (more...)

Troubleshooting Oracle Performance, 2nd Edition

Few days ago the second edition of Troubleshooting Oracle Performance was finally published. Today, I updated the pages on this site that provide information about the book. It goes without saying that the new pages cover the second edition. Hence, from here you can access the description, the table of contents, the forewords written by Jonathan Lewis and Cary Millsap, as well as a page with some errata. Yes, as for the first edition, (more...)

Upcoming Speaking Engagements

After about four months without any speaking engagements, over the next three months I’ll have the opportunity to speak at several events. I’m looking forward to meet you at one of them!

April 3, I’ll be in Baden-Dättwil to speak about “ASH Analytics” at an event organized by SOUG. Since this is a new presentation covering some stuff that I wrote for the second edition of TOP, I’m looking forward to see how it works.

(more...)

CBO Days 2014

CBO Days 2014
The company I work for, Trivadis, is very pleased to organize, on the 10th and 11th of June, an outstanding seminar with top guest speakers in Zurich. This year’s focus will be on the Oracle query optimizer, also known as a cost-based optimizer (CBO).

The query optimizer is not only one of the most complex pieces of software that constitutes the Oracle kernel; it is also one of the most unappreciated. Why? To make the (more...)

Extension Bypassed Because of Missing Histogram

Today, while tuning a fairly complex query experiencing wrong cardinality estimates, I noticed something I was not aware of. Hence, I thought to write this short post to illustrate how to reproduce the problem I experienced…

  • Create the test table (notice the correlation between the data of the two columns):
SQL> CREATE TABLE t
  2  AS
  3  SELECT mod(rownum,50) AS n1, mod(rownum,50) AS n2
  4  FROM dual
  5  CONNECT BY level < = 1000;

Adaptive Plans in Active Session History

Yesterday, during the talk “The Query Optimizer in Oracle Database 12c – What’s New?” at OakTable World UK 2013, an attendee asked me an interesting question that can be summarized as follows: does ASH show the switch of execution plan that takes place for an adaptive plan?

Since (more...)

Oracle ACE Director

Oracle ACE Program

Few days ago Oracle accepted my Oracle ACE Director nomination! In this short post I would like to thank the people that actively supported the nomination.

Special thanks go to Yury Velikanov. When during the last Oracle OpenWorld in San Francisco he discovered I was not part of the Oracle (more...)

System Statistics Gathered in Exadata Mode – When Are They Relevant?

The aim of this post isn’t to explain what the “exadata mode” is. Hence, if you don’t know what it is, before continuing reading have a look to this post published on Kerry Osborne’s blog. The only think I would like to add is that the “exadata mode” is available (more...)