Posts in category SQL Trace

The Importance of the In-Memory DUPLICATE Clause for a RAC System

With the INMEMORY clause you can specify 4 sub-clauses: The MEMCOMPRESS clause specifies whether and how compression is used The PRIORITY clause specifies the priority (“order”) in which the segments are loaded when the IMCS is populated The DISTRIBUTE clause specifies how data is distributed across RAC instances The DUPLICATE clause specifies whether and how […]

Event 10046 – Full List of Levels

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

Ad: Mastering Oracle Trace Data

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 13, 2012 – […]

SQL Trace and Oracle Portal

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 to the application Given […]

TKPROF New Features in 11gR2 – 11.2.0.2

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 Oracle9i, there were really […]

TKPROF New Features in 11gR2 – Release 11.2.0.1

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

Evolution of a SQL Plan Baseline Based on a DELETE Statement

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 of the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function […]

Optimizer Mode Mismatch Does Not Prevent Sharing of Child Cursor!?!?

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

Analyzing a SQL Trace File with SQL Statements

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

Inserts Experiencing an Increasing CPU Consumption

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 Release 11.1.0.6.0 (64-bit) Operating system: […]