Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

CDB Views and Query Optimizer Cardinality Estimations

18 January 2018 Written by Christian Antognini

Today I faced a performance problem caused by a bad cardinality estimation involving a CDB view in a 12.1.0.2 multitenant environment. While solving the problem I did a number of observations that I try to summarize in this blog post. First of all, when checking the execution plan of a query already running for more […]

Read More 2 Comments

SPD State Does Not Change If Adaptive Statistics Are Disabled

28 November 2017 Written by Christian Antognini

The aim of this post is to point out an issue (bug?) that I recently discovered. But, before talking about it, I need to go through a rather long introduction about the state of SQL plan directive (SPD). As of version 12.1.0.2, an SPD has two state information. You can see both of them through […]

Read More 4 Comments

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

9 March 2016 Written by Christian Antognini

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 […]

Read More Leave a Comment

Also Extensions Created Because of SQL Plan Directives Can Invalidate Packages

17 December 2015 Written by Christian Antognini

UPDATE 2015-12-18: As metioned by Bryn Llewellyn in this tweet, the invalidation I describe below is caused by bug 19450314. A patch for it is available here for several releases (e.g. it is available for 12.1.0.1.0, 12.1.0.2.1 or 12.1.0.2.13, but not for 12.1.0.2.0). I successfully tested it on 12.1.0.1.0. UPDATE 2016-11-08: The patch related to […]

Read More 6 Comments

Restrict the query optimizer through OPTIMIZER_FEATURES_ENABLE and Top-Frequency/Hybrid...

29 January 2015 Written by Christian Antognini

To make upgrades easier, I regularly see people considering disabling query optimizer features by setting the OPTIMIZER_FEATURES_ENABLE initialization parameter to a non-default value. My general opinion about this “habit” is summarized in TOP with the following two sentences: Changing the default value of the OPTIMIZER_FEATURES_ENABLE initialization parameter is only a short-term workaround. Sooner or later […]

Read More 2 Comments

Extension Bypassed Because of Missing Histogram

5 February 2014 Written by Christian Antognini

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 […]

Read More 7 Comments

Challenges and Chances of the 11g Query Optimizer

12 December 2011 Written by Christian Antognini

Challenges and Chances of the 11g Query Optimizer is the name of a presentation I gave at several events (e.g. Trivadis Performance Days, Oracle OpenWorld, DOAG Konferenz, UKOUG Conference) throughout 2011. Its abstract is the following: With every new release, the query optimizer is enhanced. Oracle Database 11g Release 1 and Release 2 are no […]

Read More 2 Comments

Does CREATE INDEX Gather Global Statistics?

17 December 2009 Written by Christian Antognini

You can add the COMPUTE STATISTICS clause to the CREATE INDEX statement. It instructs the SQL statement to gather and store index statistics in the data dictionary, while creating the index. This is useful because the overhead associated with the gathering of statistics while executing this SQL statement is negligible. In Oracle9i, the gathering of […]

Read More 4 Comments

Granularity 'APPROX_GLOBAL AND PARTITION'

3 October 2008 Written by Christian Antognini

The patchset 11.1.0.7 introduces in the package DBMS_STATS a new value for the parameter GRANULARITY. The description provided by the development team in $ORACLE_HOME/rdbms/admin/dbmsstat.sql is the following: ‘APPROX_GLOBAL AND PARTITION’ – This option is similar to ‘GLOBAL AND PARTITION’. But the global statistics are aggregated from partition level statistics. It will aggregate all statistics except […]

Read More 4 Comments