Search

Top 60 Oracle Blogs

Recent comments

May 2008

Querying the current tracefile name, using SQL – with tracefile_identifier

_Update: In Oracle 11g upwards you can use V$DIAG_INFO for getting your own session’s trace file name too (diag.sql script). But on earlier versions or if you want to see the current tracefile name of another, you still need to use the V$PROCESS approach below. Note that V$PROCESS in 11g (or 11.2?) has a column TRACEFILE that shows the entire path+name of a process tracefile (however there seems to be a bug in 12.

Querying the current tracefile name, using SQL – with tracefile_identifier

_Update: In Oracle 11g upwards you can use V$DIAG_INFO for getting your own session’s trace file name too (diag.sql script). But on earlier versions or if you want to see the current tracefile name of another, you still need to use the V$PROCESS approach below. Note that V$PROCESS in 11g (or 11.2?) has a column TRACEFILE that shows the entire path+name of a process tracefile (however there seems to be a bug in 12.

Oracle Troubleshooting with Snapper – detecting who’s causing excessive redo generation

Update: As this post was written many years ago, you should check out newer Snapper articles/videos:

My friend asked today a question that how to identify why his Oracle 9.2 database has suddenly started generating loads more redo than usual.

So obviously I recommended him Snapper as first thing, it’s perfect for ad-hoc analysis like that! ( I know I sound biased but if you haven’t used Snapper yet, then now is the time! :)

So, I asked him to run Snapper on all sessions of the instance with 10 second interval and find the session with highest “redo size” delta figure.

Oracle Troubleshooting with Snapper – detecting who’s causing excessive redo generation

Update: As this post was written many years ago, you should check out newer Snapper articles/videos:

My friend asked today a question that how to identify why his Oracle 9.2 database has suddenly started generating loads more redo than usual.

So obviously I recommended him Snapper as first thing, it’s perfect for ad-hoc analysis like that! ( I know I sound biased but if you haven’t used Snapper yet, then now is the time! :)

So, I asked him to run Snapper on all sessions of the instance with 10 second interval and find the session with highest “redo size” delta figure.

Performance and Scalalability Improvements in Oracle 10g and 11g

I have uploaded the slides of my “Performance and Scalalability Improvements in Oracle 10g and 11g” presentation here.

Performance and Scalalability Improvements in Oracle 10g and 11g

I have uploaded the slides of my “Performance and Scalalability Improvements in Oracle 10g and 11g” presentation here.

Using autonomous transactions for sleeping

There was a question in a recent Oracle-L thread about various uses of autonomous transactions.

Autonomous transactions can be very useful for a PL/SQL application logging, but sometimes they are also abused to cope with bad application design (like avoiding mutating table errors in triggers etc).

I’m not going to start on that topic here though, but instead presenting another case where autonomous transactions have helped me to work around a problem. It’s more a hack than a real solution though, but may be useful for someone else too.

Using autonomous transactions for sleeping

There was a question in a recent Oracle-L thread about various uses of autonomous transactions.

Autonomous transactions can be very useful for a PL/SQL application logging, but sometimes they are also abused to cope with bad application design (like avoiding mutating table errors in triggers etc).

I’m not going to start on that topic here though, but instead presenting another case where autonomous transactions have helped me to work around a problem. It’s more a hack than a real solution though, but may be useful for someone else too.

Pl/sql counter

A simple package to start and maintain a session-based counter. Useful for debugging and instrumentation. May 2008

Pl/sql counter

A simple package to start and maintain a session-based counter. Useful for debugging and instrumentation. May 2008