Sunday, June 30, 2013

Rude Awakenings

Here's a piece of esoterica. The java.util.concurrent.locks.Condition mentions that "a Condition should always be waited upon in a loop, testing the state predicate that is being waited for." This can be because of spurious wake-ups. But what are these?

"On some implementations, a thread waiting on a condition variable may be woken up even though no other thread actually signalled the condition variable. Such spurious wake-ups are a (rare) consequence of the technique required for efficient implementation on some multiprocessor systems, and are explicitly permitted by SUSv3".
Linux Programming Interface, Michael Kerrisk

(SUS is the Single Unix Specification.)

"If the state [of the task] is set to TASK_INTERRUPTIBLE, a signal wakes the process up. This is called a spurious wake up (a wake-up not caused by the occurrence of the event)"
- Linux Kernel Development, Rob Love

Even if we didn't have this rare edge case, we should still spin on the condition that put a thread into a waiting state. If we take the code in the Condition JavaDoc, make the BoundedBuffer only have a capacity of 1 and then have multiple threads adding and removing objects from it, you'll see many occasions where the thread woke up erroneously (about 1% of the time when I measured it). This is (probably) due to some other thread changing the state of the BoundedBuffer after the waiting thread was signalled to wake but before it actually got chance to run.

This is not limited to Linux. "There is no guarantee around when a thread that has been awakened will become scheduled. Condition variables are not fair. It's possible - and even likely - that another thread will acquire the associated lock and make the condition false again before the awakened thread has a chance to reacquire the lock and return to the critical region." - Concurrent Programming On Windows, Joe Duffy [1]

One final aside: when awaiting on a Condition, the calling thread is added to a linked list stored in the Condition. If the signal method is called before this event, the thread will never know of it. That is to say, signal events are not stored for later.

[1] Quoted in a comment in Stack Overflow.

Thursday, June 27, 2013

Just Enough to be Dangerous

...probably best describes my knowledge of Oracle admin. However, these are some interesting tidbits I have found on the web that help you see what is going on in your database:

How long a transaction will take

You can estimate how long a rollback will take by polling 

SELECT V.USED_UBLK, q.sql_fulltext, S.* 
FROM V$TRANSACTION  V, v$session S, v$sql q 
where 
V.addr = S.taddr 
and q.sql_id = s.sql_id 


and seeing the value of USED_UBLK (from Ask Tom).

If this value is going up, that means the transaction is progressing (see here). if it's going down, this may indicate that a rollback is taking place. Estimating how long it takes to reach zero will give you a good idea how long you must wait.

Which are the long running transactions

long running transactions: 

select * 
from v$session_longops 
where target = 'MDS.FXVOL_CONTENT'

and  sofar != totalwork 

or whichever table you want to see. Taken from the Oracle website here.
(Text in bold added after a DBA friend recommended it to me).

Which transactions are blocked and which are blocking

See blocked processes with: 

select 
    sid, 
    sql_text 
from 
    v$session s, 
    v$sql q 
where 
    sid in 
    (select 
       sid 
    from 
       v$session 
   where 
       state in ('WAITING') 
   and 
       wait_class != 'Idle' 
   and 
       event='enq: TX - row lock contention' 
   and 
      (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id)); 


The blocking transaction is given with:

select 
    blocking_session, 
    sid,    serial#, 
    wait_class, 
    seconds_in_wait 
from 
    v$session 
where 
    blocking_session is not NULL 
order by 
    blocking_session; 


(SQL taken from here).

With this SID, you can see what SQL is running with this:

select 
    sid, 
    sql_text 
from 
    v$session s, 
    v$sql q 
where 
    q.sql_id = s.sql_id 
and s.sid=SID_YOU_JUST_FOUND 


All Active Queries

SELECT 
ss.program, 
SS.SERIAL#, 
W.SID, 
SUBSTR(SS.USERNAME,1,8) USERNAME, 
SS.OSUSER "USER", 
AR.MODULE || ' @ ' || SS.machine CLIENT, 
SS.PROCESS PID, 
TO_CHAR(AR.LAST_LOAD_TIME, 'DD-Mon HH24:MM:SS') LOAD_TIME, 
AR.DISK_READS DISK_READS, 
AR.BUFFER_GETS BUFFER_GETS, 
SUBSTR(SS.LOCKWAIT,1,10) LOCKWAIT, 
W.EVENT EVENT, 
SS.status, 
AR.SQL_fullTEXT SQL 
FROM V$SESSION_WAIT W, 
V$SQLAREA AR, 
V$SESSION SS, 
v$timer T 
WHERE SS.SQL_ADDRESS = AR.ADDRESS 
AND SS.SQL_HASH_VALUE = AR.HASH_VALUE 
AND SS.SID = w.SID (+) 
AND ss.STATUS = 'ACTIVE' 
AND W.EVENT != 'client message' 
ORDER BY  SS.LOCKWAIT ASC, SS.USERNAME, AR.DISK_READS DESC 


Script taken from here.