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

Offline Analysis of ASH Data with ASHDUMP

6 July 2017 Written by Christian Antognini

From time to time, it happens to me to carry out offline analyses of ASH data. For that, I mean to analyze the ASH data without having access to the database instance that generated it. For that purpose, Oracle Database provides the possibility to dump the content of the ASH buffer as well as information […]

Read More 1 Comment

Statement-level PARALLEL Hint

17 September 2016 Written by Christian Antognini

UPDATE 2017-08-23: because of the confusing meaning of the term “force” (the meaning that most people give to it isn’t the one of the optimizer traces), I edited the text to make it more clear. Given the number of changes I completely removed the old text. From version 11.2 onward, the PARALLEL hint supports two […]

Read More Leave a Comment

When CURSOR_SHARING=FORCE, Does Literal Replacement Always Take Place?

21 June 2016 Written by Christian Antognini

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

Read More 2 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

Auto SGA Management Impacts the Default Value of DB_FILE_MULTIBLOCK_READ_COUNT

10 October 2015 Written by Christian Antognini

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

Read More 6 Comments

Foreign Keys and Library Cache Locks

3 September 2015 Written by Christian Antognini

In this post I would like to describe a behavior of Oracle Database that, at least for me, isn’t obvious at all. Actually, it’s something that I can’t explain why it works in that way. Let’s start by setting the scene by describing the schema I’m using for the following tests. As you can see […]

Read More 8 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

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

6 October 2013 Written by Christian Antognini

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 thing I would like to add is that the “exadata mode” is available as of 11.2.0.4 or when […]

Read More 4 Comments

The Broken Statistics: "parse count (total)" and "session cursor cache h...

14 August 2013 Written by Christian Antognini

The values provided by the “parse count (total)” and “session cursor cache hits” statistics are subject to several bugs. And, what’s worse, for years Oracle didn’t care to fix it. This is my impression, at least. Then, when few weeks ago I read in the Oracle Support note 13837105.8 (Bug 13837105 – statistics “parse count […]

Read More 8 Comments
Older Entries