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 […]
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 […]
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 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 […]
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 […]
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 […]
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. Who are the authors? Several colleagues of mine at Trivadis (Mirko Hotzy, Konrad Häfeli, Daniel Steiger, Sven Vetter, Peter Welker), Andrea Held, […]
The STATISTICS_LEVEL parameter controls a bunch of features. In addition to the STATISTICS_LEVEL, also the V$STATISTICS_LEVEL view provides a list of the ones it controls. SQL> SELECT statistics_name, description, activation_level 2 FROM v$statistics_level 3 ORDER BY 3 DESC, 1; STATISTICS_NAME DESCRIPTION ACTIVATION_LEVEL ————————————– ———————————————————— —————- Active Session History Monitors active session activity using MMNL TYPICAL […]
At page 189 of TOP I wrote the following piece of text: In summary, with the initialization parameter optimizer_secure_view_merging set to TRUE, the query optimizer checks whether view merging could lead to security issues. If this is the case, no view merging will be performed, and performance could be suboptimal as a result. For this […]
A reader of this blog, Paresh, asked me how I was able to find out the logic behind ITL waits without having access to Oracle code. My reply was: I wrote a test case that reproduce ITL waits and a piece of code that monitors them. Since other readers might be interested, here is the […]