Database Reference
In-Depth Information
Even though an individual session requests locks, cache fusion locks are owned by instances, not individual
sessions. Let's say that another process in instance 3 accesses the same block: there is
no
additional cache fusion
processing needed, as the lock is already held in a compatible mode. As long as the lock held is in compatible mode,
any process can access the block locally without additional cache fusion processing. Of course, if the block is not in a
compatible mode, then the block lock mode must be upgraded.
■
In this chapter, I am using the term “global cache lock
requests
.” the more accurate term is “global cache
lock
conversions
.” Initially, locks are acquired in nULL mode and then converted to another higher-level mode such
as exclusive or pr mode. Use of the term “global cache lock request” is much more readable than “global cache
lock conversion.” this difference in terminology does not matter, but if you are reading internal traces this difference
becomes obvious.
Note
GRD
A combination of file_id and block_id uniquely identifies a block; BL resource names are coined using the
combination of file_id and block_id of the block. A lock on that BL resource is acquired before altering the block or
reading from the disk.
Let me explain the BL locking mechanism with a small table. The following code creates a table t_one and
populates the table with 500 rows, adds an index, and then collects statistics on the table. With this setup, I will query
a row to show how BL resources are employed to maintain cache fusion consistency.
DROP TABLE t_one;
CREATE TABLE t_one (n1 NUMBER , v1 VARCHAR2(100));
INSERT INTO t_one
SELECT n1, lpad (n1, 100,'DEADBEEF')
FROM
( SELECT level n1 FROM dual CONNECT BY level <=500
);
COMMIT;
CREATE INDEX t_one_n1 ON t_one (n1);
EXEC dbms_stats.gather_table_stats ( USER, 't_one', CASCADE =>true);
BL Resources and Locks
The query printed below has an index range scan access path, so index block is read using the predicate n1=100, and
then the table block is read using the rowid retrieved from the index entry. For this discussion, I will exclusively focus
on BL resources and locks protecting the table data block, although a similar locking scheme is applied to index block
also. As BL resource_name is derived using the combination of file_id and block_id, I will query the file_id and
block_id of a block using dbms_rowid package.
-- script tc_one_row.sql --
SELECT n1,
dbms_rowid.rowid_to_absolute_fno (rowid, user,'T_ONE') fno,
dbms_rowid.rowid_block_number(rowid) block,
dbms_rowid.rowid_object(rowid) obj,
LENGTH(v1) v1
