Posts in category Bug

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

Named Notation and SEM_MATCH Table Function

This is a short post about a strange behavior I discovered today… The fact is that it is not really possible to use the named notation with the SEM_MATCH table function. In fact, even though the parameter’s names can be specified, the order of the parameters overrides the specification done with the named notation!?! Here […]

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

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 _cursor_obsolete_threshold (default value is 100) child cursors the parent cursor is […]

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

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

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

Workload System Statistics Bug in 11.2

Since the introduction of 11.2 I receive on a regular basis questions related to “strange” workload system statistics. The last email on that topic was sent to me yesterday. So, instead to send, again, a private reply, I decided to write this short post. What’s wrong with 11.2 and workload system statistics? Let’s have a […]

DBMS_AUTO_SQLTUNE: ORA-01748 and Documentation Bugs

As of 11.2.0.2 a new package, DBMS_AUTO_SQLTUNE, is available for accessing and configuring Automatic SQL Tuning. The package provides three features: Execution of the Automatic SQL Tuning task (EXECUTE_AUTO_TUNING_TASK) Generation of a report showing the output generated by the Automatic SQL Tuning tasks (REPORT_AUTO_TUNING_TASK) Configuration of the Automatic SQL Tuning parameters (SET_AUTO_TUNING_TASK_PARAMETER) In this post […]

Exadata Storage Server and the Query Optimizer – Part 4

When I started writing the series of posts about Exadata Storage Server and the query optimizer, I didn’t expect to write more than three posts (part 1, part 2, part 3). Of course, things change. Hence, here is part 4 to cover a couple of things that I learned in the last couple of months. […]

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