What you see below is a common problem. Someone sends you (or posts to a forum) a wide execution plan, which is unreadable because of wrapped lines. For example, this one below: -------------------------------------------------------------------------------- ------------------- | Id  | Operation                   | Name                    | E-Rows |  OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------- ------------------- |   0 | SELECT STATEMENT            |                         |        |       | |          | |   1 |  SORT AGGREGATE             |                         |      1 |       | |          | |*  2 |   HASH JOIN                 |                         |     13 |  1102K| 1102K|  355K (0)| |*  3 |    HASH JOIN                |                         |     13 |   988K| 988K|  367K (0)| |*  4 |     HASH JOIN               |                         |     13 |   921K| ...

Tanel Poder
Administration, Oracle, Productivity, Tools, Unix/Linux

This is the second part of the joint blog "project" with James Morle, called "The Wait Interface Is Useless (Sometimes)". We already did a joint presentation on this topic at UKOUG and more conferences will follow :) Read the first part by James here for intro. So, where do we go when Oracle's wait interface doesn't help us? We will show multiple techniques over time, but here's where I normally continue when wait interface is "useless". I use V$SESSTAT. Oh, were you expecting something more "advanced" instead of boring old V$SESSTAT's performance counters which has been available in Oracle for ages? ;-) Well, there is ...

Tanel Poder
Internals, Oracle, Performance, Troubleshooting

I started blogging over 2.5 years ago, so now it's (finally) time to use a blog template which sucks less than my original one! I spent quite a lot of time searching for the perfect template and oh, didn't find any. All templates had some shortcomings and majority of the more advanced ones were geared towards people publishing news about their pets (or socks for that matter :) I had been a (secret) admirer or Greg Rahn's blog template for a while and he agreed to share his config with me, so that saved me a few days setting up and tweaking ...

Tanel Poder
Cool stuff, Oracle

Some time ago I wrote a post about how COUNT(*) and COUNT(column) are semantically different things (link). Such queries may return different results if the column counted has NULLs in it. And the difference comes from that NULL is not a value, it's rather a state which says "value unknown" or "no value entered". So, you better understand how NULLs interact with your SQL constructs if you call yourself a DBA or a database developer ;-) Here's another example about how misunderstanding NULLs may cause your application to return different results than what was intended. I will create a little table with TWO ...

Tanel Poder
Design, Oracle, SQL

Cary Millsap's recent post prompted me to write down some of the related thoughts in my head. Here are few of my mantras for systematic troubleshooting and performance tuning, which have materialized in my head over the years of work: Picking the right starting point to troubleshooting and performance tuning is the most important decision in that process. Pick the wrong starting point and you end up going in circles. The scope of your performance data needs to match the scope of your problem, otherwise you end up going in circles. If you don't measure what matters, you may end up fixing what doesn't matter. If ...

Tanel Poder
Design, Oracle, Performance, Productivity, Troubleshooting

There's another interesting thread going on in Oracle-L, about understanding logical IOs and drilling down into their reasons. Of course sometimes (or rather usually) the excessive logical IOs come from a bad execution plan (when a nested loop loops over lots of datablocks again and again or a wrong index is used for driving a query etc), but sometimes the excessive LIOs are caused by some internal issues, like space management etc. A convenient tool I use for reporting logical IO reasons is (again) my Snapper! It has  an option "b" for reporting Buffer get reasons or as I use below ...

Tanel Poder
Cool stuff, Internals, Oracle, Performance, Troubleshooting

In Oracle-L mailing list a question was asked about under which conditions can the explain plan report a wrong execution plan (not the one which was actually used when a problem happened). I replied this, but thought to show an example test case of this problem too: 1) The optimizer statistics the EXPLAIN PLAN ends up using are different from the statistics the other session ended up using 2) Explain plan does not use bind variable peeking thus will not optimize for current bind variable values 3) Explain plan treats all bind variables as VARCHAR2, thus you ma have implicit datatype conversion happening during the plan execution, ...

Tanel Poder
Administration, Oracle, Performance, SQL, Troubleshooting

I received a question about migrated rows recently. It was about how to detect migrated rows in a 200TB data warehouse, with huge tables - as the ANALYZE TABLE xyz LIST CHAINED ROWS INTO command can not be automatically parallelized at table level (as DBMS_STATS can be, but oh, DBMS_STATS doesn't gather the migrated/chained row info). Therefore the analyze command would pretty much run forever before returning (and committing) the chained row info in the output table. Also as there are regular maintenance jobs running on these tables (I suspect partition maintentance for example), then it wouldn't be nice to keep ...

Tanel Poder
Administration, Internals, Oracle, Performance, Troubleshooting

Sometimes when an attendee describes me some totally weird problem during a seminar, I am immediately able to answer something like "Hey this looks like a bug related to this Oracle configuration and can be influenced by xyz". And then people ask me "How the hell do you know all this stuff?" Well, I haven't been bitten by all of these bugs myself, but I have been doing something for many years, almost every day... reading my email! Oh, and additionally I have configured Metalink to send me daily updates about new/updated notes, forum articles and... bug descriptions! The last part is very important. ...

Tanel Poder
Administration, Cool stuff, Internals, Oracle, Performance, Productivity, Troubleshooting

If you don't know what SystemTap is - it's the Linux world's attempt to build Solaris DTrace style safe dynamic instrumentation into Linux kernel. I'm not going into religious discussions which one is better here, I have used both SystemTap and DTrace successfully for diagnosing low level issues inside OS kernel, so both are good enough for me :) The problem with SystemTap though has been that it's not production quality, it's rather been a technology preview. But with RHEL 5.4 part of it has changed, Redhat says following in this article: SystemTap is no longer a technology preview, and now has production support. ...

Tanel Poder
Cool stuff, DTrace, Internals, Oracle, Performance, Troubleshooting