The trains are a nightmare, IT’s a Miracle and how little can you know…: Tanel Põder in Utrecht!


Networking and evaluations
Networking and evaluations

Today was the first day of the second Miracle Masterclass this year: Tanel Põder has come to The Netherlands, saw a room filled with a lot of people (he called it the best seminar even before he started, just because of the amount of attendants) and made me feel I have an awful lot to explore yet. However, that is in fact a good thing and it made me get through the “trains nightmare” today with a smile: I had trouble in the morning since I left home far too late, had to cycle as fast as I could and jumped completely exhausted with sour legs into an already late train packed with people, so I had to stand. Good start of the day. And on the way back tonight all trains around Utrecht had trouble because of some “logistic problems”. Eventually I was not unlucky: only a half hour delay, although standing again (great thing a First class ticket to Utrecht, I could really use it 😦 …NOT!). But hey, I said I went through it with a smile. Because this first seminar day was already worth the money. The more new things you hear may make you feel exhausted and even a little frustrated, but at the same time satisfied because the net pay-off is awesome, right?

Cary Millsap told me (and others) Tanel is a very clever guy who knows extremely much about Oracle internals. So I checked out his blog and I figured I should be able to get a lot out of this course so I subscribed, even though my customers have limited Unix/Linux Oracle installations. To my own amazement I was able to really understand most of it (I guess my Unix/Linux skills are not so bad). It only goes so fast. As I told Anjo (Kolk, from Miracle) at the end of the day: when I was not paying attention for a few moments (dreaming about white sandy beaches or my well going 400 metres hurdles training of yesterday evening, which may very well have contributed to some problems of staying focused all time ;-)), I looked at a screen showing the results of yet another demo and wondered: “What the heck caused that result?” Many, many demo’s and Tanel switching screens faster than my split times during the 400 metres hurdles training! And I was running very well…

What I like in particular is the drive Tanel has to create scripts that need as little privileges and change as possible. A lot of sites just have a strong policy that you cannot create packages, types, get access to X$-views etc. I am working in a place like that at this moment where I am facing the issue that they are going to revoke Unix access from everyone not in the official IT department. And I am hired by the application team as a performance troubleshooter. It is in such places that you need tools and scripts that work with the minimum amount of effort and do not need to go through days and weeks of change approvals. I was amazed by the way Tanel emulates ‘loops’ within a SQL statement, taking advantage of the fact that V$-views are not Read consistent. Check out his blog, you will find a lot of valuable scripts and tools there. And as a last comment before I hit the bed: today I really saw why most Oracle guys like Unix/Linux so much. Windows has a number of drawbacks when it comes to troubleshooting problems (and then there is that paradox: in a Operating system known as  having frequent and strange problems, would you not like to have the best tools to troubleshoot them?) 

Audience from behind

Audience from behind

03-04-2009, update to the post:  day two ended as well. No train problems today and the advantage of that is that I can use the travel time to write an update yesterday’s post.  

It was a great day again. Lots of interesting stuff, luckily also a lot of recognition for me now, otherwise I would have been exhausted again ;-). The first day we talked about the different layers in the stack and the Oracle kernel layers. We dealt with the way to approach a problem and what to do if the usual ways do not show any data (V$SESSION_WAIT, SQL*Trace, ASH). We talked about latches and mutexes (a kind of cheaper latch available in the newer versions). We have seen the usage of ps, top/prstat, pmap, ipcs, pstack, strace (all to show Oracle process activity from an OS point of view) and Tanel’s snapper script. The word “script” might have been the top contributor when it comes to words spoken these two days. There was hardly a single thing where Tanel could not say the magic words: “I hate to do things by hand, so I have a script for that”.

 The second day was mainly about execution plans, row sources, application instrumentation and tracing beyond the SID level (at the module or client_identifier level, if your connection pool applications call some instrumentation functions to set the client identifier, the module, action and client_info). Oracle has efficient ways to set these kinds of information along with the SQL statement in one network round trip by using OCI calls. In two applications I support I am really in need with more information about the actual user issuing the database calls or using the session. In both applications (Siebel and FileNet) this is not instrumented out of the box and I need to see if we can code it in somewhere. It would be so helpful if these packaged applications would at least provide this minimum amount of instrumentation. 

Tanel in discussion

Tanel in discussion

 Also interesting was the topic on the life cycle of a cursor and why SQL cannot always be shared, even though the SQL text is similar. Since this topic touched the SQL Hash value and SQL_ID I raised the question if you can generate the SQL_ID or hash value when you know the SQL statement. In other words: is the function that generates the hash value and the SQL_ID available to us? Tanel and Anjo discussed some things about this, but we came to the conclusion that this hashing algorithm (and SQL_ID algorithm) is not publicly available. Anyone knows whether it probably is? I would love it, since we use a great tool written by one smart guy at our customer to display performance characteristics of Siebel applications (parsing Siebel log files) and the SQL stored in there I would love to match with SQL I see in V$SQL…but to search on the SQL_TEXT is both slow and cumbersome (especially with the long Siebel queries which are a lot of the time very similar, but slightly different).

The seminar was a big success: a very technical one presented by indeed (Cary was right) very smart guy. I like Oracle internals and if you like it too, go and see Tanel Põder’s seminar and read his blog. You have to have a solid background in Oracle otherwise it might be very frustrating. And some Unix/Linux knowledge is important!

Thanks Tanel, for two great days (and thanks Anjo and Annette as well for giving the opportunity)! See you next time!

Some more pictures below:

4 thoughts on “The trains are a nightmare, IT’s a Miracle and how little can you know…: Tanel Põder in Utrecht!

  1. Pingback: Done with my seminar in Netherlands - here’s some feedback and couple beta new versions of my scripts | Tanel Poder's blog: Core IT for Geeks and Pros

  2. “I would love to match with SQL I see in V$SQL…but to search on the SQL_TEXT is both slow and cumbersome”

    You should include a comment just after SELECT with a relatively unique value. It could be a sequence, boring and obtuse but unique, it could be package_name::proc_name::lineno, which is more human readable and useful.

    Then when you see the sql somewhere, like a log file or v$sql, you know exactly which one it is and can go find it.

    • Hi Mark,

      Sure, I am aware of this technique, but it will not help me with the SQL generated by an application like Siebel, there is no comment to generate along with it. It is indeed a technique I use quite some time when I want to find my (executed in SQL*Plus/TOAD or whatever frontend that lets me control my statement) SQL statement quickly… But since we use Siebel and have a tool that parses the Siebel logfiles, all I have is the exact Siebel SQL statements and would like to easily and especially quickly match that one with statements in Oracle and I would love to be able to generate the hash value or SQL ID like Oracle does. So find my SQL without a slow access on V$SQL.

  3. Pingback: Advertisement: Be an Early Bird « So Many Oracle Manuals, So Little Time

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s