Top 60 Oracle Blogs

Recent comments


Oracle Latch Contention Troubleshooting

I wrote a latch contention troubleshooting article for IOUG Select journal last year (it was published earlier this year). I have uploaded this to tech.E2SN too, I recommend you to read it if you want to become systematic about latch contention troubleshooting:

I’m working on getting the commenting & feedback work at tech.E2SN site too, but for now you can comment here at this blog entry…


Session Snapper v3.11 – bugfix update – now ASH report works properly on Oracle 10.1 too

This is an updated version of Snapper, which works ok on Oracle 10.1 now as well (9i support is coming some time in the future :)

Thanks to Jamey Johnston for sending me the fix info (and saving me some time that way :)

So if you have some problems with Snapper on Oracle 10.1, please make sure you have the latest version v3.11, which you can get from here:

The output below is from Snapper 3.11 on Oracle, the ASH columns in the bottom part of the output are displayed correctly now:

Oracle Session Snapper v3.10

Hi all, long time no see!  =8-)

Now as I’m done with the awesome Hotsos Symposium (and the training day which I delivered) and have got some rest, I’ll start publishing some of the cool things I’ve been working on over the past half a year or so.

The first is Oracle Session Snapper version 3!

There are some major improvements in Snapper 3, like ASH style session activity sampling!

When you troubleshoot a session’s performance (or instance performance) then the main things you want to know first are very very simple:

  1. Which SQL statements are being executed
  2. What are they doing, are they working on CPU or waiting.
  3. If waiting, then for what

Often this is enough for troubleshooting what’s wrong. For example, if a session is waiting for a lock, then wait interface will show you that. If a single SQL statement is taking 99% of total response time, the V$SESSION (ASH style) samples will point out the problem SQL and so on. Simple stuff.

However there are cases where you need to go beyond wait interface and use V$SESSTAT (and other) counters and even take a “screwdriver” and open Oracle up from outside by stack tracing :-)

When I wrote the first version of Snapper for my own use some 4-5 years ago I wrote it mainly having the “beyond wait interface” part in mind. So I focused on V$SESSTAT and various other counters and left the basic troubleshooting to other tools. I used to manually sample V$SESSION/V$SESSION_WAIT a few times in a row to get a rough overview of what a session was doing or some other special-purpose scripts.

However after Snapper got more popular and I started getting some feedback about it I saw the need for covering more with Snapper, not just the “beyond wait interface” part, but also the “wait interface” and “which SQL” part too.

xplan: dbms_metadata.get_ddl for tables referenced by the plan

As a minor but useful new feature, xplan is now able to integrate into its report the DDL of tables (and indexes) referenced by the plan, calling dbms_metadata.get_ddl transparently.

This is mostly useful to get more details about referenced tables' constraints and partitions definition - to complement their CBO-related statistics that xplan reports about.

This feature can be activated by specifing dbms_metadata=y or dbms_metadata=all (check xplan.sql header of xplan.sql for more informations).

We spoke about xplan in general here.

it didn't work ...

I did manage to publish a link to this blog post, 'Releasing early is not always good? Heresy!' at about 3:00 am on Jan 1st. The plan was that I'd close out 2009 by getting the last few posts related to Agile design off of my mind so I could start 2010 ready to return my focus to measurement. Fail.I woke up late that morning, drank my coffee and thought about problems in the design and