Coskan’s Approach to Oracle

April 30, 2009

What I learned from AOT by Tanel Poder

Filed under: Basics, Blogroll, Diary, Performance — coskan @ 8:28 pm

Guys who personally knows me, knows also that I am a very big fan of Tanel Poder and his work. I have been following him, since he started blogging in 2007 and after reading all his stuff,  at UKOUG 2008 Conference I found chance to have his masterclass about Advanced Oracle Troubleshooting. When I saw how he uses the things he wrote on his blog, all my approach to troubleshooting oracle had changed, but something was still missing. Blog and masterclass session wasn’t enough for me, I wanted more and more everytime I read a post from him. God must have heard my wishes and sent him to Edinburgh for his Advance Oracle Troubleshooting seminar and gave me the chance to fill the missing part.

After nearly 8 Oracle trainings ( 3 of them were celebrity seminars), I can personally say, If you love Oracle, if you love performance tuning and troubleshooting, if you look for a method to troubleshoot, if you want to learn something systematic and also practical   this is the best one you can attend to find what you are looking for. What makes this training unique is not just his methodology and knowledge, it is also his instructing skills. I don’t really enjoy sessions of  the instructors who are strict about their agenda and who don’t allow questions before they finish the topic but Tanel was always open to questions and I think he is aware that off-topic questions can lead to cover an upcoming topic too, so he doesn’t say, “I will see it next chapters” (which might not be covered because of the time limit. ) he answers them all. I asked as many questions as I can and none of them were remained without an answer not just by talk also by proof. Maybe he couldn’t finish all the chapters totally, but he nearly covered all in the middle of other chapters, by allowing questions. To get more benefit from this training, I strongly recommend you to read all the posts on his blog and try them before you come. Believe me you will focus more and benefit as much as I did. One more confession is that this one was the only training I never lost concentration 🙂

After all this voluntarily marketing, let me tell you tiny bits about what I learned from this young (it was a big shock to hear his IT experience is nearly 18 years at my age I think thats why Jonathan Lewis said that he saw his youth on him 🙂 ), %100 geek and genius evangelist.

*If the session state is WAITING it means waiting, otherwise no matter what is written on state column of v$session it is on the CPU as simple as this. (sw script)

*On ideal world, if the session is running, session stats (snapper script) will show you some increasing stats

* For short intervals of snapping with snapper scripts DBTime and DB CPU might show non realistic values ideal is 5 and over I think, not less

*Oracle does not immediately put a line to an event trace, it does it when the call finishes.

* on solaris kill -STOP SPID suspends the process without killing it and you can resume it again by PRUN. check manual page for you operation system.

*using wireshark , snoop (solaris) tcpdump (linux) to check network related issues

*Oracle might lie about a process but OS wont. What OS says on process stack is always the most reliable one.

*sed.sql is the simple but life and time saver script to check the P1 P2 P3 value definitions of a wait event.

*procmon is a life saver tool for dodgy errors with “unknown error” message on Microsoft OS.

* on a dump trace file you can ignore function calls at the top of stack they are related with dumping process

* If you see sqlid sqltext but no plan for the running sql. dont get shocked there can be an ongoing hard parse.

* vmstat on unix/solaris sr which is scan rate column needs to be 0 for healthy monitoring

*on linux swappiness is an important memory setting which isnt referenced in online documents or metalink officially.

* good practice if you can take the dump of the process if you need to kill it. for finding the problem

*there is an hang analyze analyzer on EM. Never took my attention.

* v$wait_chain is similar with hang analyze dump but available after 11G

* Finding latch related problems are not a big issue any more after latchprof and latchprofx. Wont tell anything here everything is on his blog.

* root block is the next block after the block header. useful info for latch buffer cache chain issues. (might be leaf block on very tiny tables)

* quest spotlight has session _spin_count autotuner in it . (see Tanels comment below. )

*To drink more by Working less , you should be practical and script the things you use most in addition to his scripts

*there is a tool called trcsess which can be used to bring tracefiles together.

* he also say it is pointless to check statspack for something still going on.
I personally used statspack/awr only 3 times and one was at a job interview. Some stupid guy before doing rman online backup to 7/24 system, tried to test me what is wrong with that statspack report. Was totally crap.
To be honest Purely or initially statspack oriented problem solving is something like gues from your .ss type problem solving. Thanks Tanel for this term 🙂 And his perfsheet tool makes statspack more useful to check what has happened last week type problems on a graphical interface. Having your own ASH if you don’t have Diagnostic Pack is far more better than getting lost on system wide reports.

* If you cant sleep on the plane, sleep enough before the flight and fly during office hours and work during the flight. systematic approach from a man who flys nearly 2 times a week.

* if you are googling an about oracle internal functions put .ch or .ru  domains for some better results (yes you need google translate to understand the page)

*BEST ONE I asked him how do you learn all these interesting stuff. He gave me a perfect clue. Sign up for metalink headlines mailing list. Bugs docs they are all there after they are created. Perfect resource.

There are two things, I don’t agree with him, about his approach. First is , I want to see the sql first about a session I am troubleshooting. If I know the system this will give me a basic idea, it can also lead wrong way but still SQL is something worth to see before starting other steps.
Write one script run on all versions idea is another thing I am not very keen to follow. I prefer to have 3 different script home to benefit from new columns added to tables. The only thing I need to do is call the right home in a bat file before I connect.

These were just my tiny highlights, from a big training. If you need more just attend his class, you wont regret.

I heard that he will come to London for seminar possibly after UKOUG 2009. If I were you, I won’t miss it.

Thank you very much Tanel for sharing your knowledge with us. You are the rising sun over Oracle Performance World.

One special thanks is coming for Thomas C. Presslie from PISEC who was the organizator of this event. Venue, environment and all training package and his hospitality  was wonderfull. I wish I could come for the upcoming ones 😦

Blog at