Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Auto SGA Management Impacts the Default Value of DB_FILE_MULTIBLOCK_READ_COUNT

10 October 2015 Written by Christian Antognini

The database engine determines the maximum disk I/O size used during multiblock reads (for example, full table scans or index fast full scans) by multiplying the values of the db_block_size and db_file_multiblock_read_count initialization parameters. The db_file_multiblock_read_count initialization parameter can be set explicitly, or, as of version 10.2, it’s also possible to instruct the database engine […]

Read More 6 Comments

Foreign Keys and Library Cache Locks

3 September 2015 Written by Christian Antognini

In this post I would like to describe a behavior of Oracle Database that, at least for me, isn’t obvious at all. Actually, it’s something that I can’t explain why it works in that way. Let’s start by setting the scene by describing the schema I’m using for the following tests. As you can see […]

Read More 8 Comments

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

14 August 2013 Written by Christian Antognini

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

Read More 8 Comments

ITL Deadlocks (script)

1 May 2013 Written by Christian Antognini

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

Read More 7 Comments

Display System Activity without Enterprise Manager

6 June 2012 Written by Christian Antognini

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

Read More 16 Comments

COMMIT_WAIT and COMMIT_LOGGING

5 April 2012 Written by Christian Antognini

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

Read More 19 Comments

Analysing Row Lock Contention with LogMiner

12 March 2012 Written by Christian Antognini

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

Read More 12 Comments

Index Scan with Filter Predicate Based on a Subquery

6 February 2012 Written by Christian Antognini

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

Read More 4 Comments

SQL Trace and Oracle Portal

29 November 2011 Written by Christian Antognini

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

Read More 2 Comments

optimizer_secure_view_merging and VPD

11 September 2011 Written by Christian Antognini

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

Read More 12 Comments
Older Entries