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 😦
Coskan's Approach to Oracle 
Thanks man 🙂
I enjoyed delivering the seminar as well (and that’s again why I ran out of time in the end). If attendees are staying focused and “with me” I get additional energy out of it and try to give even more out on the topics people seem to be interested in the most.
Some context for any readers who haven’t seen mu work – you mentioned the spin_count autotuner. To avoid someone thinking that I spoke about it in a positive fashion, I mentioned it in a context that spin_count is usually misused & abused parameter. In 99.9% cases you don’t need to touch it, the root causes of your problems need to be fixed (like parsing,executing too often or too many logical IOs against same blocks due bad exec plan/physical design etc).
Regarding the 2 things you don’t agree with
1) The SQL text doesn’t need to be the FIRST thing to check in my approach. It’s all about narrowing down the things to look up the most efficient way. Running sw takes only a second or two and gives evidence about what the session is doing right now. Execution plan can give only remote clues at best about what’s the session doing/waiting on right now. But again, my approach comes from my experience – you use whatever order suits you best.
2) Regarding different versions of scripts to take advantage of new columns etc. First, if there’s a new column in a view in newer oracle version – it doesn’t automatically mean that this column is actually useful and should be displayed at all 🙂
It’s again about looking into minimum amount of RELEVANT data, not looking into all possible numbers you can see.
So, as my scripts are living ones, I frequently improve them, then in order to avoid the discrepancies between the same script in different directories I have single universal versions – which are dynamic based on the version you run on. For example both snapper and latchprofX scripts are dynamic, they show the columns/views which exist on 10g+ but the same scripts don’t show these columns in 9i. That way I can have a single script, avoid versioning problems/overhead and still have dynamic output where it matters.
Thanks once more for your feedback!
Comment by Tanel Poder — May 1, 2009 @ 12:42 pm
I think I was misunderstood. I meant I prefer to check usql immediatelly after sw so I can see the sql before going further 🙂
about the script homes yes you are right again from your point of view but at least blocking column of v$session could be good on sw for versions over 10G
Thank you for your comment Tanel 🙂
Comment by coskan — May 1, 2009 @ 12:56 pm
Yep the blocking session column could be a time-saver… I think I’ll implement it into a future version of sw.sql… although I’ll still demo the low-level approach walking various lock structures in my coming seminars 🙂
Comment by Tanel Poder — May 5, 2009 @ 12:34 pm
Hi Poder,
1. for the blocking session column,
I have a handy script to release the system to end user promptly. 🙂 my copyright 🙂
your advice, please.
set pages 80 lines 125
rem v$lock.block = 1 — blocking
rem v$lock.request > 0 — blocked
prompt Where the blocking user’s Session:
select ‘alter system kill session ”’|| s1.sid ||’, ‘|| s1.serial# ||”’;’ “Kill Command”
, to_char(s1.logon_time, ‘ddMon hh24:mi’) ||’ ‘|| s1.username ||’ => ‘|| s1.osuser ||’ ‘||
regexp_substr(s1.machine, ‘\\\\.*’) ||’ ‘|| s1.program ||’ ‘|| s1.module ||’ ‘||
s1.action ||’ ‘|| s1.client_info ||’ ‘|| s1.status ||’ ‘|| s1.seconds_in_wait “blocking user”
, substr(sqlt1.sql_text, 1, 120) “blocking SQL”
, to_char(s2.logon_time, ‘ddMon hh24:mi’) ||’ ‘|| s2.username ||’ => ‘|| s2.osuser ||’ ‘||
regexp_substr(s2.machine, ‘\\\\.*’) ||’ ‘|| s2.program ||’ ‘|| s2.module ||’ ‘||
s2.action ||’ ‘|| s2.client_info ||’ ‘|| s2.status ||’ ‘|| s2.seconds_in_wait “blocked user”
, substr(sqlt2.sql_text, 1, 120) “blocked SQL”
from v$session s1, v$session s2, v$sql sqlt1, v$sql sqlt2
where s1.sid = s2.blocking_session
and sqlt1.sql_id = s1.prev_sql_id
and sqlt2.sql_id = s2.sql_id
/
2). for SQL text, for me, it is the first thing to look into it. as it is very common that server performance issue is caused by bad SQL application, such as not use index properly.
thank you.
Comment by an oracle dba — May 19, 2009 @ 5:36 am
[…] Class . I was in Edinburgh DBA SIG when I first watched this presentation and it was just after AOT by Tanel Poder, so I couldn’t not focus that much. This time I listened better and I plan to review […]
Pingback by UKOUG DBMS SIG July 2009 « Coskan’s Approach to Oracle — July 2, 2009 @ 12:06 pm
[…] Attendee Coskan Gundogar describes the seminar in Edinburgh […]
Pingback by Advertisement: Be an Early Bird « So Many Oracle Manuals, So Little Time — September 6, 2009 @ 4:09 pm
[…] to go on this course, I read the testimonials and there’s a great piece of advice from Tanel via Coskan Gundogar: I asked him how do you learn all these interesting stuff. He gave me a perfect clue. Sign up for […]
Pingback by Metalink headlines « OraStory — September 16, 2009 @ 9:51 am
[…] remembered to mention this to anyone if people ask where to learn internals. But Dominic Brooks and Coskan Gundogar have recently written about this so I thought I should share this with my blog audience […]
Pingback by What’s a good way to learn some Oracle internals every day? | Tanel Poder's blog: IT & Mobile for Geeks and Pros — October 7, 2013 @ 6:20 am
Dear Coskan,
I appreciate the great work that you are doing. I got a lot of insight about the oracle. Where is the list of scripts that you use in demo, Please provide the location..
With regards
Abuzar Kamal
abuzar.kamal@gmail.com
Comment by Abuzar — November 3, 2013 @ 8:33 pm