Technology at a glance!

Today I had a full production stop on a system under my command.

A simple delete statement was blocked. I normally fire up TOAD when this occures.
But I found (read: google) a more simple way to do this in plain sql:

SELECT s1.username || '@' || s1.machine ||
' ( SID=' || s1.SID || ' )  is blocking ' ||
s2.username || '@' || s2.machine ||
' ( SID=' || s2.SID || ' ) ' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.SID = l1.SID
AND s2.SID = l2.SID
AND l1.BLOCK = 1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2

It produces a nice result:

BLOCKING_STATUS

user@host ( SID=24 )  is blocking user@host( SID=31 )
SYSTEM@host( SID=42 )  is blocking user@host( SID=68 )
SYSTEM@host( SID=42 )  is blocking user@host( SID=24 )
SYSTEM@host( SID=42 )  is blocking user@host( SID=45 )
user@host( SID=45 )  is blocking user@host( SID=43 )
user@host( SID=68 )  is blocking user@host( SID=44 )
It seems like our DBA was generating a HUGE index in daily production hours.

Somthing he usually knife other pure souls for.

Shame on you Mr. DBA, shame on you.
Never the less : Today I knifed him, and what a feeling!

Benjamin Sølberg Oracle

As you might know, having a column with NULL values degrades your chances on a well performing oracle database when using the “IS NULL” in the WHERE clause of your SQL. This is because “IS NULL” isn’t a value but actually just “undefined”, and thereby you can’t create a database index on that value. 

But no more!

The solution is to use FBI or Function Based Indexes. This can be archived using the NVL function in both the WHERE clause and in the index creation. 

See more here:

http://www.dba-oracle.com/oracle_tips_null_idx.htm

Benjamin Sølberg Oracle