Aug 14, 2015 5:28:00 PM by Quest Software
Execution of a SQL statement in an Oracle Parallel Server environment is slightly different then in a single instance environment. In an OPS environment additional instructions are executed to obtain and release PCM (Parallel Cache Management) locks on the data blocks accessed by the SQL. To provide an understanding of the processing that occurs, we give below a step-by-step description of the execution flow when a Query/DML statement is issued in an OPS environment. Here we consider two simple cases.
The block(s) needed are locked by other instances in an exclusive mode,
In this example we explain a simple Select statement (with no update clause). The foreground process that executes the SQL statement on the user's behalf requests a SHARED lock on the accessed block(s). The execution of DML i.e. SQL with Update, Insert, Delete or Select for Update will be similar except that the lock mode requested by the foreground server process will be EXCLUSIVE mode instead of SHARED mode.
The term "pinging" refers to exchange of blocks amongst instances. This occurs when a request is made for a block on one node while that block or some other block covered by the same DLM resource is held in the cache of some other node in a mode that is incompatible with requestor's mode.
In the example below (refer to figure 2), assume Node 1 has a resource locked in EXCLUSIVE mode. This resource covers several database blocks. Node 2 would like to acquire the same resource with a lock in SHARED mode to service a Select statement issued on that node which requires one or more of the blocks covered by the DLM lock. This will cause the blocks, which are dirty in the cache of node 1 to be written to disk before the lock request is granted to node 1.
Data blocks after being read from disk may require rollback segment (undo) information to be applied to them
The first case is made more efficient by setting an init.ora parameter DELAYED_LOGGING_BLOCK_CLEANOUTS = true, by default. With this, the block cleanout (marking the changed block with the commit time) is done as a part of current block change and thus the redo records of the block change and the cleanout changes are piggybacked. This also is a 'fast commit' method since no redo records will be generated for cleanout purposes when the block comes into SGA next time during a DML execution. The ping, down-convert and extra DLM processing on the transaction (rollback segment) header block of the instance, which did the previous changes, are also eliminated. Furthermore, only minimal work is carried out on the current dirty block as a part of cleanout process (changing cleanout SCN) so as to eliminate the above mentioned extra work. Hence, this feature is sometimes also called as 'no logging block cleanout'. Most of the blocks will be cleaned out in this way with the exception of blocks changed by long running transactions for consistent reads.
Thus, this feature is useful for short OLTP transactions, which are committed as soon as they are done. For long running queries typical to DSS environment, the dirty blocks will age out of the SGA without cleanout since the transaction will not have been committed.
Doing a consistent read may require reading the rollback segment of remote instances to obtain the information for read consistency. The blocks required may be just the segment header containing the transaction table or the undo blocks. Such requests for remote undo segment header and/or undo blocks cause pings to occur as the instance owning the rollback segment is forced to downgrade locks and possibly write the blocks to disk if dirty so that the requesting instance may have them. This WRITER/READER contention not only requires the needed data blocks to be pinged but also the related rollback segment and rollback segment header blocks. This has been optimized in Oracle8i via Cache Fusion.
In the example below (refer to figure 3), assume Node 1 has a resource locked in EXCLUSIVE mode. This resource covers several database blocks. Node 2 would like to acquire the same resource with a lock in SHARED mode to service a Select statement issued on that node which requires one or more of the blocks covered by the DLM lock.
Written by Quest Software