tanelpoder's picture

New online seminars – Advanced Oracle Troubleshooting v2.0 Deep Dives

As I mentioned in a previous post, I won’t be doing much flying anymore and so am changing all my seminar offering to online seminars.

So, I’ve changed and re-arranged my seminar content into self-contained 4-hour deep dives and thanks to the online nature (no travel needed), people can choose which days they want to attend. If you’re interested in latch contention only, you can attend the Latch Contention deep dive for example etc. Or you can still attend all the deep dives. The cool thing is that these deep dive sessions take only half a day, too (and are priced accordingly). That way you don’t have to skip work for the whole day (or week) and still can get some of your daily work done too. Hopefully it makes your life a bit easier when getting approval to attend the sessions.

As the main feedback from my seminars has been that “there’s too much to learn” within the short 2-3 days I used to do my seminars in, I have arranged the material so that there will be more time to go deep into the subject area. Also, I have planned plenty of time for questions & answers (1 hour out of the 4 hours is planned Q&A sessions and attendees can also ask questions any time during the lecture & demos).

It looks like I will only offer my Advanced Oracle Troubleshooting v2.0 class online this year. I will probably schedule my Advanced SQL Tuning deep dives in January/February 2011 and the Advanced Troubleshooting class again in March/April and so on (until I go public with my other business, when I won’t have time for full length training anymore).

You can check the current schedule and pricing out here:

Here’s a brief outline of individual half-day Deep Dives I offer:

  1. AOT deep dive 1: Systematic approach for Advanced Oracle Troubleshooting
  2. AOT deep dive 2: Troubleshooting physical IO and buffer cache issues
  3. AOT deep dive 3: Troubleshooting commit, redo, undo and transaction issues
  4. AOT deep dive 4: Troubleshooting Oracle SGA/PGA/UGA and OS memory issues
  5. AOT deep dive 5: Troubleshooting shared pool and library cache issues
  6. AOT deep dive 6: Troubleshooting enqueue lock waits and deadlocks
  7. AOT deep dive 7: Troubleshooting latch contention
  8. AOT deep dive 8: Troubleshooting Mutex and “cursor: pin” contention
  9. AOT deep dive 9: Troubleshooting complex hangs and spins
  10. AOT deep dive 10: Troubleshooting crashes, bugs and ORA-600/ORA-7445 errors

So, sign up now, seats are limited ;-)


tanelpoder's picture

Oracle Closed World presentation links

Thanks to everybody who attended my OCW hacking session!

Sorry to guys who attended via webinar – I’ll do the session again in a few weeks, with audio from end to end hopefully! And I will get someone to assist me with monitoring the transmission quality and attendee questions etc.

Note that this stuff is mostly for hacking and fun – don’t use the undocumented stuff in production!

The links are below:

Download scripts & Tools:

Rlwrap links:

Diagnostic events:

I haven’t written any articles on X$TRACE yet, but you can find some stuff from one of my very old presentations:

Or just type:


Where 10704 is the KST event number, 4 is the level and ALL means all Oracle PIDs (Oracle PIDs, not OSPID’s).


tanelpoder's picture

Hot stuff! Oracle Closed World Secret presentation webcast!

After hours of careful planning (as you’ll see from the images) I decided to webcast my tomorrow’s Oracle Closed World hacking session…

This session isn’t necessarily going to be useful, but it definitely should be fun !!!

You can register here:


tanelpoder's picture

Which number takes more space in an Oracle row?

So, which number takes more bytes inside an Oracle row?

A: 123

B:  1000000000000000000000000000000000000

And the correct answer is … (drumroll) … A! The “big” number 1000000000000000000000000000000000000 actually takes less space than the “small” 123!

Let’s verify this:

SQL> select vsize(123) A, vsize(1000000000000000000000000000000000000) B from dual;

         A          B
---------- ----------
         3          2

WTF? Why does such a small number 123 take more space than  1000000000000000000000000000000000000 ?

Well, the answer lies in how Oracle stores numbers. Oracle NUMBER datatype doesn’t store numbers in their platform-native integer format. Oracle uses it’s own format which stores numbers in scientific notation, in exponent-mantissa form. More details about this here.

You can use the DUMP sql function to see the actual binary value of the number data stored:

select dump(123) from dual;

Typ=2 Len=3: 194,2,24

SQL> select dump(1000000000000000000000000000000000000) from dual;

Typ=2 Len=2: Typ=2 Len=2: 211,2

So, although the number 1000000000000000000000000000000000000 is bigger than 123, when stored in base-10 exponent form, it really carries much less information in it than 123 (1 x 10^36 vs 123 x 10^0). Oracle doesn’t need many bits for keeping the precision of this large value as it happens to be a power of 10.

See what happens when I store a number only slightly bigger or smaller than the original large number, now the stored number requires much more storage for keeping the required precision:

SQL> select dump(1000000000000000000000000000000000000+1) from dual;

Typ=2 Len=20: 211,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2

SQL> select dump(1000000000000000000000000000000000000-1) from dual;

Typ=2 Len=19: 210,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100


marco's picture

XMLDB Whitepapers and Tooling about Design, Performance and Selectivity

From time to time the main Oracle XML DB page gets updated with new whitepapers, tooling or Oracle By Example/ Hands-on Lab examples. “Lately” some cool and interesting new whitepapers and updated tooling content were created on this main Oracle XML DB page. The following items and content are really worth reading. Small issue, though, is that you need a bit more than basic understanding to put all this “lessons learned from the last one, two years” into context, but its worth it and otherwise a small reprise on the Oracle XML DB Developers Guide is always useful. A bit like re-reading the Oracle Concepts Manual.

The “Ease of Use Tools” ( tool set) for handling XMLType Object Relational storage has been updated and is now applicable on Oracle 10.x and 11.x. No specific to be installed versioned tool set needed anymore. This prepacked tool set on PL/SQL packages is installable on both versions. The zip file also contains a whitepaper that describes some of the (performance) lessons learned while using XMLType Object Relational storage.

tanelpoder's picture

Dropping and creating tables in read only tablespaces?!

You probably already know that it’s possible to drop tables in Oracle read only tablespaces… (You did know that already, right? ;-) Here’s a little example:

SQL> create tablespace ronly datafile '/u03/oradata/LIN112/ronly.01.dbf' size 10m;

Tablespace created.

SQL> create table test tablespace ronly as select * from all_users;

Table created.

SQL> alter tablespace ronly READ ONLY;

Tablespace altered.

SQL> drop table test;

Table dropped.

I just dropped a table from a read only tablespace! Well, perhaps it’s because that instead of dropping the table was put into recyclebin instead (which is a data dictionary update)? Let’s check which segments remain in the RONLY tablespace:

SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name = 'RONLY';

------- ------------------------------- ------------------

Indeed, it seems that the table segment wasn’t actually dropped. Well, let’s purge the recycle bin to try to actually drop the table segment:

tanelpoder's picture

The full power of Oracle’s diagnostic events, part 2: ORADEBUG DOC and 11g improvements

I haven’t written any blog entries for a while, so here’s a very sweet treat for low-level Oracle troubleshooters and internals geeks out there :)

Over a year ago I wrote that Oracle 11g has a completely new low-level kernel diagnostics & tracing infrastructure built in to it. I wanted to write a longer article about it with comprehensive examples and use cases, but by now I realize I won’t ever have time for this, so I’ll just point you to the right direction :)

Basically, since 11g, you can use SQL_Trace, kernel undocumented traces, various dumps and other actions at much better granularity than before.

For example, you can enable SQL_Trace for a specific SQL_ID only:

SQL> alter session set events 'sql_trace[SQL: 32cqz71gd8wy3{pgadep: exactdepth 0} {callstack: fname opiexe}

Session altered.

Actually I have done more in above example, I have also said that trace only when the PGA depth (the dep= in tracefile) is zero. This means that trace only top-level calls, issued directly by the client application and not recursively by some PL/SQL or by dictionary cache layer. Additionally I have added a check whether we are currently servicing opiexe function (whether the current call stack contains opiexe as a (grand)parent function) – this allows to trace & dump only in specific cases of interest!

tanelpoder's picture

Oracle memory troubleshooting article

Randolf Geist has written a good article about systematic troubleshooting of a PL/SQL memory allocation & CPU utilization problem – and he has used some of my tools too!


tanelpoder's picture

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 |


tanelpoder's picture

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?

To prevent automated spam submissions leave this field empty.
Syndicate content