Striving for Optimal Performance (Christian Antognini)

ITL Deadlocks (script)

Chris Antognini — Wed, 01 May 2013 06:07:18 GMT

A reader of this blog, VijayS, asked me to share the script I use to demo ITL deadlocks that I mentioned in this comment. Since other readers might be interested, here is the script. SET TERMOUT ON FEEDBACK ON VERIFY OFF SCAN ON ECHO ON @connect SELECT...(read more)

Premature Optimization

Chris Antognini — Thu, 14 Mar 2013 23:30:37 GMT

Premature optimization, (probably) because of Donald Knuth’s famous line “premature optimization is the root of all evil,” (see Structured Programming with go to Statements) is, at the very least, a controversial topic. The misconception based on that...(read more)

Ad: CBO Days 2012 – Final Agenda

Chris Antognini — Wed, 28 Nov 2012 10:58:12 GMT

The 11th and 12th of December and, therefore, the CBO Days in Zurich are approaching. Given the high quality of the speakers that accepted my invitation and the number of people that decided to attend the event, in my opinion it will be a blast! By the...(read more)

Does the Parse Time Increase Linearly with the Number Of Child Cursors?

Chris Antognini — Wed, 24 Oct 2012 00:42:39 GMT

In the last post I discussed a test case generating lot of child cursors. Today I wanted to show you, for the very same test case, that in 11.2 the parse time might increases linearly with the number of child cursors per parent cursor. This is the expected...(read more)

How Many Children Can a Parent Cursor Have? 1,000,000?

Chris Antognini — Fri, 19 Oct 2012 12:15:43 GMT

The patch set 11.2.0.3 includes a fix for bug# 10187168 which, in reality, is an enhancement request. Its purpose is to artificially limit the number of child cursors that a parent cursor can have. The concept is quite easy: when a parent cursor reaches...(read more)

Ad: CBO Days 2012

Chris Antognini — Thu, 27 Sep 2012 11:51:30 GMT

The company I work for, Trivadis, is very pleased to organize, the 11th and 12th of December, 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...(read more)

Event 10046 – Full List of Levels

Chris Antognini — Fri, 10 Aug 2012 08:49:45 GMT

Extended SQL trace (a.k.a. debugging event 10046 at a level higher than 1) is one of the key features provided by Oracle to troubleshoot applications using Oracle Database. For many years the available levels were always the same (4, 8 and 12). In fact...(read more)

Ad: Mastering Oracle Trace Data

Chris Antognini — Mon, 11 Jun 2012 09:12:13 GMT

This is just a short post to point out that the company I work for, Trivadis, is organizing 3 classes with Cary Millsap. The topic, as the title suggests, is Cary’s 1-day class entitled “Mastering Oracle Trace Data”. The following dates and locations...(read more)

Display System Activity without Enterprise Manager

Chris Antognini — Wed, 06 Jun 2012 12:35:32 GMT

I regularly use the system-level activity chart available in Enterprise Manager. In my opinion it is a simple and effective way to know how much a specific database is loaded at a specific time. This is for example an interesting way for observing how...(read more)

COMMIT_WAIT and COMMIT_LOGGING

Chris Antognini — Thu, 05 Apr 2012 06:15:18 GMT

Recently I used the COMMIT_WAIT and COMMIT_LOGGING parameters for solving (or, better, working around) a problem I faced while optimizing a specific task for one of my customers. Since it was the first time I used them in a production system, I thought...(read more)

Analysing Row Lock Contention with LogMiner

Chris Antognini — Mon, 12 Mar 2012 06:53:00 GMT

Recently I had to analyse a row lock contention problem that can be illustrated by the following test case: A session (let’s call it #1) creates a table and inserts a row into it (note that “n” is the primary key of the table): SQL> CREATE TABLE...(read more)

Index Scan with Filter Predicate Based on a Subquery

Chris Antognini — Mon, 06 Feb 2012 21:36:58 GMT

Most execution plans can be interpreted by following few basic rules (in TOP, Chapter 6, I provide such a list of rules). Nevertheless, there are some special cases. One of them is when an index scan, in addition to the access predicate, has a filter...(read more)

Ad: The Oracle Query Optimizer 2-Day Seminar

Chris Antognini — Sat, 17 Dec 2011 23:42:45 GMT

The 31st of January and 1st of February 2012 I will present a 2-day seminar about the Oracle query optimizer in Ballerup (DK). The event is organized by Miracle A/S. The content, which is based on the chapters 2, 4, 5, 6, 9 and 10 of my book, is the following...(read more)

Challenges and Chances of the 11g Query Optimizer

Chris Antognini — Mon, 12 Dec 2011 09:59:52 GMT

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

Ad: Trivadis TechEvent 2011

Chris Antognini — Thu, 01 Dec 2011 15:44:02 GMT

All Trivadis employees meet twice a year near Zurich for a unique conference called TechEvent. The TechEvents, with up to 15 parallel sessions, are among the largest IT conferences in Switzerland. During two days, employees can attend sessions about a...(read more)