How and why to quiesce the database

The ability to quiesce the database was introduced in release 9, but to this day I find that many people are not aware of it. It can be really useful - so let's describe it. I'll begin by positioning the quiesce capability: when is it useful. Then detail how to do it, then reverse engineer the mechanism.

In recent releases, many operations that used to need downtime have become online operations. Obvious examples are online index creation and online table redefinition, and there are many others. According to the docs, these facilities let you do anything to your tables or indexes while the database in use. Well, it isn't true. Sure, you can do a maintenance operation such as index creation or a table redefinition without blocking user transactions. But what is not so obviously documented is that user transactions will block the maintenance operation. Jobs such as rebuilding an index require an exclusive table lock at the start. It persists only for a fraction of a second, but if there is a transaction running against the object, you are not going to get it. Your session will hang until the transaction completes. And if another transaction has started by then, your session will keep on hanging. A buddy of mine once told me that he issued a CREATE INDEX...ONLINE command, and his session hanged for two weeks before the creation actually started. Prove it for yourself:
In one session, connect as SCOTT and execute a simple DML:

delete from emp where rownum=1;

Then in a second session, try either of these to rebuild an index or move the table:

alter index scott.pk_emp rebuild online;
exec dbms_redefinition.redef_table('scott','emp',table_part_tablespace=>'USERS')

Your second session hangs, doesn't it? How "online" is that? Then COMMIT in your first session, and the second session wakes up. Unless another transaction from another session has started in the meantime, which may well have happened. If it is a busy table, you may never get that exclusive lock, and your session will hang forever.
The answer is to quiesce the database. From a session connected as SYSTEM or SYS (why these users? Hang on a bit for that) issue this command:

alter system quiesce restricted;

The command will hang until all active transactions and running SELECT statements have completed, with COMMIT or ROLLBACK. This should be maximum a second or two in an OLTP database. Then launch the DDL (one of those statements above) and the moment it is running (which will be immediately, because there are no active transactions) from another SYS or SYSTEM session:

alter system unquiesce;

The effect on other users is that from the moment you quiesce, all inactive sessions will hang if they issue any statement. Active sessions (the definition of "active" in this context is "in an uncommitted transaction, or running a SELECT") are allowed to continue to run until the transaction or the query ends - then they will hang if they issue another statement. Once you unquiesce, all other sessions will return to normal operations: any statement that they had issued while the database was quiesced will start to run.
This mechanism gives you what you need to launch an online operation: a moment of peace and quiet in the database. The duration of the quiesce should be only a few seconds at most - your users may not notice at all.

Now we need to reverse engineer this. What happens when you quiesce? It uses the Resource Manager (so we are in Enterprise Edition territory here). The command sets the Resource Manager plan to the INTERNAL_QUIESCE plan. This plan sets the active session pool to zero for all consumer groups except the SYS_GROUP group, meaning that no-one other than SYS and SYSTEM (who, by default, are the only members of this group - query DBA_USERS to confirm this) can then execute anything. Statements from all other sessions will be queued until the Resource Manager plan is reverted, which happens when you unquiesce. And in that short period when the INTERNAL_QUIESCE plan is active, your SYS_GROUP members can grab the DDL lock they need, start the online operation, and release the lock.

This is well cool - and makes these online operations online in fact, as well as in theory.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com