Search

Top 60 Oracle Blogs

Recent comments

Cool Stuff

New OakTable Network website

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.

Thanks to Kurt Van Meerbeeck, James Morle, Marco Gralike and anyone else who has helped to make this happen.

Execution plan Quiz: Shouldn’t these row sources be the other way around ;-)

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 |
----------------------------------------------

Share/Bookmark

Quiz: Explaining index creation

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?

KGH: NO ACCESS – Buffer cache inside streams pool too!

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:

Calculate SQL_ID and SQL_HASH_VALUE from SQL text

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:

http://www.slaviks-blog.com/2010/03/30/oracle-sql_id-and-hash-value/

Share/Bookmark

Oracle Latch Contention Troubleshooting

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:

http://tech.e2sn.com/oracle/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…

Share/Bookmark

A Free afternoon seminar in Singapore (24th Feb)

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:

  • Systematic Oracle Performance Troubleshooting
  • Identifying performance troublemakers
  • Understanding execution plans

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:

Share/Bookmark

New versions of LatchProf and LatchProfX for latch contention troubleshooting and tuning

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:

Oracle Troubleshooting: How to read Oracle ERRORSTACK output?!

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:

  1. Reading the current executing SQL statement text from errorstack trace
  2. Reading the current executing PL/SQL package and PL/SQL source code line number from errorstack trace
  3. Reading the current bind variable values from errostack trace
  4. Identifying how much private (UGA) memory a cursor is using

You can read it here:

By the way, if you like my new website, feel free to link to it !!! ;-)

Share/Bookmark

Future appearances, conferences and seminars

Just to let you know where I’ll be in the near future :)

Seminars

Advanced Oracle Troubleshooting 2.0

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…

Conferences

Hotsos Symposium, Dallas, TX, 8-11 March

I will deliver 2 presentations at the Hotsos Symposium 2010

  • Back to Basics: Choosing the Starting Point of Performance Tuning and Troubleshooting Wisely
  • Understanding LGWR, log file sync Waits and Commit Performance

Also, I will be the Training Day speaker after the conference (I feel quite honored about this btw ;)

  • Scripts and Tools for Oracle Troubleshooting and Advanced Performance Analysis

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 ;-)