11gR1

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.

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 and the release of 11g nothing changed.

With 11g, as I described in this post, new levels (16 and 32) were introduced.

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 a specific load is processed (see this post for an example).
Unfortunately it also happens that this possibility is not available. The main reasons I faced in the past are the following:

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 to write this post not only to shortly explain the purpose of the these two parameters, but also to show a case where it is sensible to use them.

The purpose of the two parameters is the following:

COMMIT_WAIT

Recently I had to analyse a row lock contention problem that can be illustrated by the following test case:

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 predicate applying a subquery.

The following execution plan, taken from Enterprise Manager 11.2, is an example (click on the image to increase its size):

Notes:

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 exception to the rule. Specifically, they introduce key improvements in the following areas: indexing, optimization techniques, object statistics and plan stability. The aim of this presentation is to review the new features from a practical point of view as well as to point out challenges related to them. In other words, to let you know what you can expect from the query optimizer when you upgrade to Oracle Database 11g.

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 that Oracle Portal uses a pool of connections and that for each HTTP call it can use several database sessions, statically enable SQL trace for specific sessions was not an option.

In 2003 I published a paper entitled Debugging PL/SQL and Java Stored Procedures with JPDA. Its aim was to describe how to debug PL/SQL and Java code deployed into the database with JDeveloper 9i. Two weeks ago a reader of my blog, Pradip Kumar Pathy, contacted me because he tried, without success, to do something similar with JDeveloper 11g, WebLogic 11g and Oracle Database 11g. Unfortunately I was not able to help him. The reason is quite simple, since 2004 I’m an Eclipse user…

Few days later Pradip contacted me again to let me know that, at last, he succeeded. Here you find his notes…

1. Grant the required privileges

GRANT DEBUG CONNECT SESSION to &&schema_name;
GRANT DEBUG ANY PROCEDURE TO &&schema_name;

The book Der Oracle DBA (Hanser, 2011), which was written in German, is at last available!

I say “at last” because the authors worked on this project for not less than two years.