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…

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:

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: