Search

Top 60 Oracle Blogs

Recent comments

July 2009

Oracle Terminated Connection Timeout

I have recently come across situations on two different PeopleSoft sites where ad-hoc queries continue to run on the Oracle database server long after the Application Server process, which is the Oracle session client, has terminated. Often, queries perform poorly because they are poorly coded, but that is another story. To help guard against this situation Oracle has mechanism called Terminated Connection Timeout (also known as Dead Connection Detection (DCD) when it was introduced in Net8).

Merge counter

A package to capture and report separate INSERT and UPDATE rowcounts from a MERGE statement. February 2004

my week in venn diagram form ...

(from thisisindexed.com)My take on this one has nothing to do with Bernie and everything to do with recent events on the big project. If I could add a small red 'x' in the middle of 'Doom' with an arrow and a 'You are Here' message, it would be perfect ...

How to detect when a cursor was closed from SQL trace output?

After Randolf’s comment on my last post about identifying cursor SQL text from sql trace file I think one thing needs elaboration.
I mentioned earlier in this post that this cursor dumping technique works “as long as the cursor of interest is still open”.
So how do you know whether this cursor of interest is still open or has been closed and that slot reused by some other statement instead? You would not want to get misled to wrong SQL statement…

How to detect when a cursor was closed from SQL trace output?

After Randolf’s comment on my last post about identifying cursor SQL text from sql trace file I think one thing needs elaboration.
I mentioned earlier in this post that this cursor dumping technique works “as long as the cursor of interest is still open”.
So how do you know whether this cursor of interest is still open or has been closed and that slot reused by some other statement instead? You would not want to get misled to wrong SQL statement…

Identify the SQL statement causing those WAIT #X lines in a (top-truncated) sql tracefile

Have you experienced such situation before?

  1. A performance issue happens in production – let say some batch job has ran way over time
  2. You enable SQL trace on the problem session (while the problem is already ongoing)
  3. In tracefile you see lots of waits (or execs or fetches) caused by cursor X
  4. You grep for “PARSING IN CURSOR #X” above the waits in the tracefile but don’t find the corresponding parsing entry nor SQL text there (this is a “top-truncated” tracefile)
  5. You really want to know which SQL corresponds to all those WAIT #X/FETCH #X lines

An example output would be here:

Identify the SQL statement causing those WAIT #X lines in a (top-truncated) sql tracefile

Have you experienced such situation before?

  1. A performance issue happens in production – let say some batch job has ran way over time
  2. You enable SQL trace on the problem session (while the problem is already ongoing)
  3. In tracefile you see lots of waits (or execs or fetches) caused by cursor X
  4. You grep for “PARSING IN CURSOR #X” above the waits in the tracefile but don’t find the corresponding parsing entry nor SQL text there (this is a “top-truncated” tracefile)
  5. You really want to know which SQL corresponds to all those WAIT #X/FETCH #X lines

An example output would be here:

SIOUG 2009 conference

I'll be presenting at the SIOUG 2009 conference that will take place in Portoroz, from 20th to 23rd September 2009.

The title of the presentation will be "CBO fundamentals: Understanding the different modes of System Statistics" and will be based on the series already available here on my blog.

So if you're interested in the details of the cost calculations performed by the recent releases of the Cost Based Optimizer and would like to here about this in a different style, this presentation might be something for you.

If you happen to be at the conference I'm looking forward to meeting you there.

Thanks to OakTable fellow Joze Senegacnik for inviting me to the conference.

Fundamentals of Software Performance Quick Reference Card

I just posted "Fundamentals of Software Performance Quick Reference Card" at the Method R company website:

This two-page quick reference card written by Cary Millsap sums up computer software performance the Method R way. The first page lists definitions of the terms you need to know: efficiency, knee, load, response time, and so on. The second page lists ten principles that are vital to your ability to think clearly about software performance. This document contains meaningful insight in a format that's compact enough to hang on your wall.

It's free, and there's no sign-up required. I hope you will enjoy it.