Here’s the latest version of my Oracle session level performance Snapper tool:
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:
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
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:
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;
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’;
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
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