Sokrates on Oracle

V$BLOCKING_QUIESCE and the difference between killing and disconnecting

Posted by Matthias Rogel on 5. January 2012

Testing how to quiesce an instance today:

SID 94 > alter system quiesce restricted;
(15 minutes ago on test-db 11.2 )
Ok, it waits for several sessions to finish, right ?
( see Doc
Oracle Database waits for all existing transactions in all instances that were initiated by a user other than SYS or SYSTEM to finish (either commit or abort). Oracle Database also waits for all running queries, fetches, and PL/SQL procedures in all instances that were initiated by users other than SYS or SYSTEM and that are not inside transactions to finish. … )

So, we queried V$BLOCKING_QUIESCE and killed all sessions listed there via
alter system kill session ....
and saw them disappear in V$BLOCKING_QUIESCE thereafter.

However, after killing the last one we expected SID 94 to finish the quiesce – command, but: it didn’t.

SQL > select * from v$blocking_quiesce;

no rows selected

SQL > select sw.event
2 from v$session_wait sw
3 where sw.sid=94;

wait for possible quiesce finish

… for several minutes.

bouncing and restarting the instance and repeating the same experiment but using

alter system disconnect session .... immediate

rather than

alter system kill session ....

gave the expected result.

So, when we want to quiesce our instance we’ll use this algorithm.

(We’ll do that only on a  weekend maintenance windows when no interactive user is connected, all batch jobs running will be restartet automatically by our scheduling system when system is unquiesced again.)

But: seems to be a bug to me that V$BLOCKING_QUIESCE showed no entry though quiescing was apparently blocked for minutes.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: