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, since I wrote my first paper about it in May 2000...

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 are planned: September 11, 2012 – Münich (DE) September...

Recently I was involved in a project where I had to trace the database calls of an application based on Oracle Portal 10.1.4. The basic requirements were the following: Tracing takes place in the production environment Tracing has to be enable for a single user only Instrumentation code cannot be added...

The release 11.2.0.2 not only provides a lot of documented new features, but also provides undocumented ones. Today, I would like to spend few words about the undocumented changes introduced in the TKPROF output. In this area it is interesting to point out that after the introduction of wait events in...

While writing a post about the TKPROF new features in 11.2.0.2 I noticed that I didn’t write one about an important change introduced in 11.2.0.1. So, before finishing the other one, let’s have a look to what changed in 11.2.0.1. One problem with TKPROF up to 11gR1 is that when the AGGREGATE parameter...

During an evolution the database engine compares the performance of two execution plans. The aim is to find out which one provides the better performance. For that purpose it has to run the SQL statement on which the SQL plan baseline is based and compare some execution statistics. The following output...

The aim of this post is to describe a strange (buggy) situation that I observed recently. But before doing that, I shortly summarize what a parent cursor and a child cursor are as well as when they can be shared. By the way, I borrowed this description from the pages 20/21 of my book. Hence, [...]

As of Oracle Database 11g the DBMS_SQLTUNE package provides the SELECT_SQL_TRACE function. Its purpose is to load the content of a SQL trace file into a SQL tuning set. But, as it often happens, a feature can be (mis)used for another purpose. The aim of this post is to show how to take advantage of ...

Last week I had to analyze a strange performance problem. Since the cause/solution was somehow surprising, at least for me, I thought to share it with you. Let me start by quickly describing the setup and what was done to reproduce the problem: Database version: Oracle Database 11g Enterprise Edition...

Yesterday, I received the following question from a TVD$XTAT user: XCTEND lines are reported as “COMMIT/ROLLBACK (synthetic)”. Using Goolge and Metalink I can’t find any other resources describing “COMMIT/ROLLBACK (synthetic)”. This term seems not be widely used, although...

This is just a short note to point out that I just uploaded under the section Downloadable Files of TOP a new version of TVD$XTAT. Not only I introduced some new features, but I also fixed a couple of major bugs related to memory consumption and poor performance… The detailed change log since Beta...

As of 11g the package dbms_monitor provides an important new feature. The aim of this post is to describe not only what this feature is, but also why it is important. To illustrate how the new feature works, two things are necessary. First, a small table: SQL> SELECT * FROM t; [...]

In a previous post I already pointed out that as of 11.1.0.7 new information is provided in the SQL trace files. One of them is the value “plh” in the PARSE, EXEC and FETCH lines. This new value provides the execution plan hash value. While adding this information to the output generated...

This is just a short note to point out that I just uploaded under the section Downloadable Files of TOP a new version of TVD$XTAT. The change log since Beta 7 is the following: Improved generation of synthetic cursors (particularly for RPC and XCTEND) Added support for 11.1.0.7 formatting of XCTEND...

Trivadis Extended Tracefile Analysis Tool (TVD$XTAT) is a command-line tool. Like TKPROF, its main purpose is to take a raw SQL trace file as input and generate a formatted file as output. Why Is TKPROF Not Enough? In late 1999, I had my first encounter with extended SQL trace, through MetaLink note...