Search

Top 60 Oracle Blogs

Recent comments

June 2008

Advanced Oracle Troubleshooting Guide, Part 5: Sampling V$ stuff with WaitProf. Really fast. Using SQL!

I bet you thought I’ll be writing about direct SGA access?! ;)

Nope!

Direct SGA access has excellent troubleshooting potential (as long as you know the shared memory data structures), but it has one major drawback – very few companies have such tools already in place in their production systems.

I have occasionally been called in to solve an urgent performance problem, happening right now and it needs solving immediately! And did I mention, these are critical production systems. Where you can’t just install binary executables freshly downloaded off internet. In fact you would want to diagnose the issue with minimal impact and changes required to those production environments (and that leaves sql tracing out the first round troubleshooting tools for me as well!)

Advanced Oracle Troubleshooting Guide, Part 5: Sampling V$ stuff with WaitProf. Really fast. Using SQL!

I bet you thought I’ll be writing about direct SGA access?! ;)

Nope!

Direct SGA access has excellent troubleshooting potential (as long as you know the shared memory data structures), but it has one major drawback – very few companies have such tools already in place in their production systems.

I have occasionally been called in to solve an urgent performance problem, happening right now and it needs solving immediately! And did I mention, these are critical production systems. Where you can’t just install binary executables freshly downloaded off internet. In fact you would want to diagnose the issue with minimal impact and changes required to those production environments (and that leaves sql tracing out the first round troubleshooting tools for me as well!)

Performance Tools Quick Reference Guide

There’s a nice Metalink Note 438452.1 about various less known Oracle performance tuning utilities.
If you haven’t heard about things like StackX, LTOM, HangFG, SQLTXPLAIN, OS_Watcher or OPDG then it’s time to check this note out! :)

Performance Tools Quick Reference Guide

There’s a nice Metalink Note 438452.1 about various less known Oracle performance tuning utilities.
If you haven’t heard about things like StackX, LTOM, HangFG, SQLTXPLAIN, OS_Watcher or OPDG then it’s time to check this note out! :)

Advanced Oracle Troubleshooting Guide, Part 4: Diagnosing a long parsing issue

There was a recent thread in Oracle Forums about a session getting stuck somewhere when a specific SQL was issued. The SQL executed did not return at all unless ORDERED hint was used. Even the EXPLAIN PLAN command (which only parses the statement, doesn’t execute it) did never return.

Classic tracing + tkprof techniques didn’t show much (just some recursive queries consuming insignificant amounts of time).

The proven V$SESSION_WAIT sampling technique didn’t reveal anything as it showed the session being constantly on CPU (the wait state = ‘WAITED KNOWN TIME’ which means session is on CPU) and SEQ# didn’t increase (which means that wait state did not change over time).

Advanced Oracle Troubleshooting Guide, Part 4: Diagnosing a long parsing issue

There was a recent thread in Oracle Forums about a session getting stuck somewhere when a specific SQL was issued. The SQL executed did not return at all unless ORDERED hint was used. Even the EXPLAIN PLAN command (which only parses the statement, doesn’t execute it) did never return.

Classic tracing + tkprof techniques didn’t show much (just some recursive queries consuming insignificant amounts of time).

The proven V$SESSION_WAIT sampling technique didn’t reveal anything as it showed the session being constantly on CPU (the wait state = ‘WAITED KNOWN TIME’ which means session is on CPU) and SEQ# didn’t increase (which means that wait state did not change over time).

Snapper shortcut

I have a (very) small script called sn.sql which I use as a wrapper around snapper (maybe I should’ve called it Snapper Wrapper but it’s too long name for the purpose :)
The idea is to have to type less when running Snapper with default options (take 1 snapshot, output to screen and display Session tats,Wait events and Time model stats).
Whenever there’s a performance issue with a session I first quickly run @sn , for example:

Snapper shortcut

I have a (very) small script called sn.sql which I use as a wrapper around snapper (maybe I should’ve called it Snapper Wrapper but it’s too long name for the purpose :)
The idea is to have to type less when running Snapper with default options (take 1 snapshot, output to screen and display Session tats,Wait events and Time model stats).
Whenever there’s a performance issue with a session I first quickly run @sn , for example: