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 […]
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. SET TERMOUT ON FEEDBACK ON VERIFY OFF SCAN ON ECHO ON @connect SELECT * FROM v$version WHERE rownum = 1; REM […]
Does the Parse Time Increase Linearly with the Number Of Child Cursors?
In the last post I discussed a test case generating lot of child cursors. Today I wanted to show you, for the very same test case, that in 11.2 the parse time might increases linearly with the number of child cursors per parent cursor. This is the expected behavior. In fact, to check whether an […]
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 […]
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). […]
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 […]
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: A session (let’s call it #1) creates a table and inserts a row into it (note that “n” is the primary key of the table): SQL> CREATE TABLE t (n NUMBER PRIMARY KEY); SQL> VARIABLE n […]
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 […]