If you regularly read my blog then very likely you also follow other, more popular OakTable Network members that blog like Jonathan Lewis or Richard Foote for example and therefore probably already know that the OakTable Network website has been re-launched with a modern interface and a lot of interesting functions, in particular a blog aggregator for all blogs of the OakTable Network members.
You might want to give it a try at www.oaktable.net.
Here’s a little trick question. Check out the execution plan below.
What the hell, shouldn’t the INDEX/TABLE access be the other way around?!
Also, how come it’s TABLE ACCESS FULL (and not by INDEX ROWID) in there?
This question is with a little gotcha, but can you come up with a query which produced such plan? ;-)
---------------------------------------------- | Id | Operation | Name | E-Rows | ---------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | INDEX RANGE SCAN | PK_EMP | 1 | |* 2 | TABLE ACCESS FULL| EMP | 1 | ----------------------------------------------
Did you know that it’s possible to use EXPLAIN PLAN FOR CREATE INDEX ON table(col1,col2,col3) syntax for explaining what exactly would be done when an index is created?
That’s useful for example for seeing the Oracle’s estimated index size without having to actually create the index.
You can also use EXPLAIN PLAN FOR ALTER INDEX i REBUILD to see whether this operation would use a FULL TABLE SCAN or a FAST FULL INDEX SCAN (offline index rebuilds of valid indexes can use this method).
Anyway, you can experiment with this yourself, but here’s a little quiz (with a little gotcha :)
What kind of index creation statement would create such an execution plan?
Some time ago I wrote that since Oracle 10.2, some of the buffer cache can physically reside within shared pool granules.
I just noticed this in an 11.2 instance:
Some time ago I wrote an article about the 10g+ SQL_ID being just a hash value of the SQL statement text. It’s just like the “old” SQL_HASH_VALUE, only twice longer (8 last bytes instead of 4 last bytes of the MD5 hash value of SQL text).
Slavik Markovich has written a nice python script for calculating SQL_IDs and SQL hash values from SQL text using that approach.
Slavik’s article is available here:
I wrote a latch contention troubleshooting article for IOUG Select journal last year (it was published earlier this year). I have uploaded this to tech.E2SN too, I recommend you to read it if you want to become systematic about latch contention troubleshooting:
I’m working on getting the commenting & feedback work at tech.E2SN site too, but for now you can comment here at this blog entry…
If you are in Singapore and have 24th Feb afternoon available then you can register and join a free Oracle performance troubleshooting seminar I’m doing in Singapore Management University’s (SMU) campus.
The seminar will be about:
The date is Wednesday, 24th Feb
The seminar time is from 15:30-19:00 (don’t be late)
Registration and more details are here:
The LatchProf and LatchProfX scripts allow you to be more systematic with latch contention troubleshooting and tuning. No more guesswork is needed as these scripts give you exact session IDs and in this version also SQLIDs of the troublemaking applications.
You can download the new versions here:
Example output (with SQLID info) is below:
I have written the first article to the troubleshooting section of my new website tech.E2SN.com:
It’s about a very valuable Oracle troubleshooting tool -> ERRORSTACK trace.
I cover 4 frequently asked questions there:
You can read it here:
By the way, if you like my new website, feel free to link to it !!! ;-)
Just to let you know where I’ll be in the near future :)
I have rearranged and adjusted the material so it flows smoother, has even more practical tools and scripts and describes some internals even deeper ;-)
These dates are close, last chance to register ;-) Note that after these I won’t be doing an Advanced Oracle Troubleshooting class in US for a while…
I will deliver 2 presentations at the Hotsos Symposium 2010
Also, I will be the Training Day speaker after the conference (I feel quite honored about this btw ;)
Note that I will show some very cool (and of course, useful) stuff at the Training Day! Some things are gonna be so cool that I almost want to sit in the audience myself! One of the things is called MOATS – the Mother Of All Tuning Scripts for example ;-)