Uncategorized

Here’s the latest version of my Oracle session level performance Snapper tool:

• http://blog.tanelpoder.com/files/scripts/snapper.sql

I’m going to add more stuff to Snapper in coming days, but thought to release something for testing already :)

Looking back at the comments audit trail on wordpress I see that I’ve got nearly two months of comments waiting for answers – and some of those answers need serious thought that I haven’t had time to supply yet. But I’ve got a (fairly) free day on Thursday so I’ll see what I can do to bring the comments up to date.

I’ve just had a great day at the Trivadis CBO days – Maria Colgan (the optimizer lady) on the 20th anniversary of the CBO, then Joze Senegacnik on transformations, me on Strategies for Statistics in 11g, ending with Randolf Geist on Parallel Execution (including a reference to one of my older blog items which I now think could well be wrong – so I may have to spend Thursday reviewing it and looking at his analysis instead of working through the comments).

Today’s task – chairing the Unconference from 9:00 to 15:00 (though the first slot hasn’t been booked by anyone and, at this stage, I don’t think it will be).

Here’s the current agenda:

Timetable for Tuesday:

• 9:00, Hall 8b – Are you Syre you need Exadata (presenting)
• 9:55, Media Suite – Global Resource Management, by Joel Goodman (chairing)
• 11:15, Hall 1 – SAN Performance problem, What performance problem, by Chris Dunscombe (audience)
• 12:10, Hall 1 Foyer – Living with Engineered Systems (roundtable)
• 13:15, Exhibition Hall Balcony – Autumn and the AWR (presenting)
• 13:55, Hall 8b - How the Query Optimizer Learns from its Mistakes, by Chris Antognini (chairing)
• 14:30, Exhibition Hall Balcony – Optimizer Round Table (with Maria Colgan)
• 16:00 – UKOUG Council Stand in the Exhibition Hall
• 17:15, Hall 9 – Brining Oracle Database Identity Management back to the Drawing Board by Gregory Guillou (chairing)
• 18:30, Evening Social Events (and I’ll need them).

If one is seeing  ‘enq: TX – row lock contention’ there could be a lot of reasons. One distinguishing factor is the lock mode. If the lock mode is exclusive (mode 6) then it’s most likely a classic row lock where two sessions are trying to modify the same row. On the other hand if the lock mode is share (mode 4)  it’s typically going to be

• inserting a unique key when  someone else has already inserted that key but not committed
• Inserting a foreign when then parent value has been inserted but not committed or deleted and not commited (not to be confused with locks due to un-indexed foreign key which cause a “enq: TM – contention”  wait not a TX wait)
• bitmap index chunk contention

Now how to tell which of these is happening? Well here is a query on ASH (I’ve commented out some of the useful fields to limit the output)  and a results cheat sheet:

Here is a short video

D3 Show Reel from Mike Bostock on Vimeo.

Data-driven Documents from London Web Standards on Vimeo.

We’ve all done that common administrative task of:

- find the HWM in a datafile

- resize the datafile down to that mark.

But sometimes, you might get what appears to be a problem:

Here’s a tablespace I created a while back…

SQL> create tablespace DEMO
  2  datafile ‘C:\ORACLE\ORADATA\DB112\DATAFILE\DEMO.DBF’ size 100m
  3  extent management local uniform size 1m;

Tablespace created.

After a while I wanted to reclaim that 100 megabytes back, so I looked at the high water mark in DBA_EXTENTS

SQL> select max(block_id+blocks)*8192/1024/1024 high_mb
  2  from dba_extents
  3  where tablespace_name = ‘DEMO’;

   HIGH_MB
———-
         2

So, if the high water mark is 2meg, all I need so now is resize the file….

A substantial part of the people I encounter present using OSX on a Macbook. I am not sure how much of these people use Apple’s Keynote for presenting, but I like Keynote very much for various reasons, like a cleaner interface. This blogpost is about some handy tips and tricks I learned using a few years of presenting around the world. If you don’t use OSX, this blogpost is probably not for you.

Recently, I was creating an IOT from an existing table via, and wanted to achieve it without logging.

You cannot do this in two steps (ie, create table, followed by insert-append) because it will still be logged (see the addenda at the end of this post)

So you need to do it with a CTAS, for example:

create table MY_IOT
(…)
tablespace MY_DATA
organization index
nologging
as select * from ANOTHER_IOT order by ANOTHER_COL;

In my case, I opted for the “order by ANOTHER_COL” because this yielded an execution plan of INDEX FULL SCAN (not FFS) on ANOTHER_IOT, with no sort operation.  This sounded like a good thing to do, because this table was around a terabyte in size and I’d rather not be sorting that