Back in the (really) old days, systemstate dumps had to be used for diagnosing hangs and finding blockers of hung databases. Basically you just identified which resource your waiting sessions were waiting for and then scanned through the rest of the system state dump to see which session held that particular resource (note that over time the instrumentation and systemstate dumps have evolved to resolve and dump the blocker information right at the waiting session section in the dump).
When you troubleshoot an Oracle (performance) problem you usually want to find out what are the problem sessions doing (wrong) and then why are they doing it.
The “what” question is usually answered using some wait interface based method – like ASH or SQL*Trace, which both add plenty of extra details to the plain wait event data.
My normal session troubleshooting data collection sequence consists of only three steps:
Ok, it took only a year or so, but I’ve fixed most of the broken links (to my scripts etc) in my blog :-)
Please let me know if you hit any more broken links from now on….
Ok, I’ve wanted to write this blog entry for a long time – and now it’s time!
Most of my blog readers (thank you!) are performance-minded computer enthusiasts, who care about efficiency and optimization. You’ve been tuning SQL execution plans, instance and OS configuration so that your sessions would achieve the same results with less work and also with less waiting!
There was a question in the twitter-sphere about whether using sequences (sequence.NEXTVAL) in your select query’s projection list would somehow disable smart scans happening?
Ok fellow internals geeks, tomorrow’s going to be another 1-hour hacking session (which will probably take 2 hours) with me. It’s about how Oracle parameters work and all the different types of them too. See the registration link for more info:
Date: 12 april 2012
I am going to run my updated Advanced Oracle Troubleshooting online training again, in May and June! :)
I just noticed that (finally) in 11.2 this syntax is supported:
SQL> CREATE DATABASE LINK demo_x2 2 CONNECT TO tanel IDENTIFIED BY password 3 USING 'exadb03:1521/DEMO'; Database link created.
This just makes life a bit easier as there’s no need to use the long TNS format entry (or a tnsnames.ora/LDAP alias). It might work in 11.1 too (haven’t tested) but it didn’t work on 10.2.0.4 …
In case you didn’t know, the sqlplus supports such an easy connect method since 10g:
In case you didn’t know, Jonathan Lewis’es new Oracle Core: Essential Internals for DBAs and Developers book is out (for a few weeks already).
I was the technical reviewer for that book and I can say it’s awesome! It will likely be the best Oracle internals book out there for the coming 10 years, just like Steve Adams’es Oracle Internal Services book was in the last decade :)