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?)
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.
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.
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!