Search

OakieTags

Who's online

There are currently 0 users and 37 guests online.

Recent comments

Affiliations

11gR1

Extension Bypassed Because of Missing Histogram

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…

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

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 (total)” and “session cursor cache hits” miscounted) that the bug introduced in 11.2.0.3 was fixed, I hoped that others bugs in this area were fixed as well.

Unfortunately, it’s not the case. What a disappointment!

Even though several posts were already wrote about this topic (e.g. here and here; check the comments as well…), I thought it could be useful to summarize what the current status is.

ITL Deadlocks (script)

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.

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

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

Display System Activity without Enterprise Manager

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:

COMMIT_WAIT and COMMIT_LOGGING

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

Analysing Row Lock Contention with LogMiner

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

Index Scan with Filter Predicate Based on a Subquery

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):
Execution Plan
Notes:

Challenges and Chances of the 11g Query Optimizer

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.

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 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.