Search

Top 60 Oracle Blogs

Recent comments

June 2008

Advanced Oracle Troubleshooting Guide, Part 6: Understanding Oracle execution plans with os_explain

Get ready for some more adventures in Oracle process stack!

Before proceeding though, please read this post about safety of different stack sampling approaches.

I have had few non-trivial Oracle troubleshooting cases, related to query hangs and bad performance, where I’ve wanted to know where exactly in execution plan the current execution is.

Remember, Oracle is just another program executing instructions clustered in functions on your server, so stack sampling can help out here as well.

So, I was looking into the following stack trace taken from an Oracle 10.1 database on Solaris SPARC, running a SQL with this execution plan.

Debugger dangers

Whenever I deliver training or conference presentations on advanced troubleshooting topics, I usually spend some time demonstrating how to get and interpret Oracle server process stack traces.

As I’ve mentioned before, stack traces are the ultimate indicators showing where in Oracle kernel (or whatever application) code the execution currently is (or where it was when a crash occurred). This is the reason Oracle Support asks for stack traces whenever there’s a crash or non-trivial hang involved, that’s why Oracle database dumps errorstacks when ORA-600’s and other exceptions occur.

There are multiple ways for getting stack traces for Oracle, but not all ways are equal. Some give you more contextual info, some less, but what I’m blogging about today is that some ways are less safe than others.

Debugger dangers

Whenever I deliver training or conference presentations on advanced troubleshooting topics, I usually spend some time demonstrating how to get and interpret Oracle server process stack traces.

As I’ve mentioned before, stack traces are the ultimate indicators showing where in Oracle kernel (or whatever application) code the execution currently is (or where it was when a crash occurred). This is the reason Oracle Support asks for stack traces whenever there’s a crash or non-trivial hang involved, that’s why Oracle database dumps errorstacks when ORA-600’s and other exceptions occur.

There are multiple ways for getting stack traces for Oracle, but not all ways are equal. Some give you more contextual info, some less, but what I’m blogging about today is that some ways are less safe than others.

Short note on KGX Mutexes

I received a question on what’s the point of the use of Mutexes for Oracle cursors in library cache. For short intro, I’m pasting one of my fairly recent answers in Oracle forums about Oracle mutexes here:
In Oracle, latches and mutexes are different things and managed using different modules. KSL* modules for latches and KGX* for mutexes.
General mutex operatins require less CPU instructions than latch operations (as they aren’t as sophisticated as latches and don’t maintain get/miss counts as latches do).

Short note on KGX Mutexes

I received a question on what’s the point of the use of Mutexes for Oracle cursors in library cache. For short intro, I’m pasting one of my fairly recent answers in Oracle forums about Oracle mutexes here:
In Oracle, latches and mutexes are different things and managed using different modules. KSL* modules for latches and KGX* for mutexes.
General mutex operatins require less CPU instructions than latch operations (as they aren’t as sophisticated as latches and don’t maintain get/miss counts as latches do).

So, there’s two posts, guess I’m on the blogging bandwagon…

As the subject says, there’s my first two real posts, so, I guess I’m blogging.  I won’t guarantee how active I’ll be here, or how much of what I write will be Oracle, as opposed to other stuff, but, for what it’s worth, here I am.

11g is more deadlock sensitive than 10g?

I ran into a situation over the weekend, where an application and schema, which were stable under 10.2.0.3, started hitting ORA-00060 deadlocks in 11.1.0.6, in spite of the fact that no application code changes had occurred.  It seems that 11g was more sensitive to deadlocks in this situation than 10gR2 was.

Bit of a stumper….

Well, I finally decided I have something noteworthy to blog about.  This was a bit of a stumper, that we ran into the other day….I did finally get to the bottom of it, and I thought it worth a mention, here.

We have a three node RAC running 10.2.0.3 on DL-585s.

Generating lots of rows using connect by – safely!

Every now and then I need to generate a large number of rows into some table.

Not that I have any personal urge to do so, but you know, this is needed for some test cases and so on ;-)

It’s quite well known that it’s possible to generate lots of rows using CONNECT BY LEVEL <= #rows syntax.

However as the CONNECT BY LEVEL syntax is recursive by nature, it will consume more memory the more rows you query in one shot ( I think it was Mark Bobak who once posted this observation to Oracle-L, but I didn’t manage to find that thread anymore ).

So, here’s a test case:

Generating lots of rows using connect by – safely!

Every now and then I need to generate a large number of rows into some table.

Not that I have any personal urge to do so, but you know, this is needed for some test cases and so on ;-)

It’s quite well known that it’s possible to generate lots of rows using CONNECT BY LEVEL <= #rows syntax.

However as the CONNECT BY LEVEL syntax is recursive by nature, it will consume more memory the more rows you query in one shot ( I think it was Mark Bobak who once posted this observation to Oracle-L, but I didn’t manage to find that thread anymore ).

So, here’s a test case: