Hybrid Columnar Compression - Oracle Exadata Recipes: A Problem-Solution Approach - page 490

OBJECT_ID RowID File# Blk#
-------------- ----------------------- ----- -------
2 AAAG2vAAAAABcyDAAw 9 380035
3 AAAG2vAAAAABcyDAAF 9 380035
4 AAAG2vAAAAABcyDAAx 9 380035
5 AAAG2vAAAAABcyDAAa 9 380035
When using DBMS_ROWID.ROWID_BLOCK_NUMBER on HCC tables, the block refers to the compression unit so we
know that all of the rows above are located in the same CU. Next, let's update the first row with OBJECT_ID=2 from one
session, leaving the transaction uncommitted:
SQL> update d14.myobj_dmltest
2 set object_name='X123'
3 where object_id=2;
1 row updated.
SQL>
From a different session, let's attempt to perform a SELECT FOR UPDATE NOWAIT on OBJECT_ID 3:
SQL> select object_id from d14.myobj_dmltest
2 where object_id=3 for update nowait;
select object_id from d14.myobj_dmltest
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL>
As you can see, the SELECT FOR UPDATE failed—not because the row with OBJECT_ID=3 was locked, but because
the entire CU containing this row had an uncommitted update on a different row being held.
If you find yourself waiting on row locks ( enq: TX - row lock contention waits), run the script in Listing 16-12
to identify the lock holder and CU block information.
Listing 16-12. lst16-12-hcclocks.sql
SQL> select row_wait_block#,row_wait_row#,
2 blocking_instance,blocking_session
3 from v$session
4 where sid='&&waiting_sid'
5 /
Enter value for waiting_sid: 1237
old 4: where sid='&&waiting_sid'
new 4: where sid='1237'
ROW_WAIT_BLOCK# ROW_WAIT_ROW# BLOCKING_INSTANCE BLOCKING_SESSION
---------------- -------------- -------------------- --------------------------
380035 5 1 394
How It Works
There a few important design characteristics for HCC segments with respect to DML. First, as discussed in
Recipes 16-6 and 16-7, data in HCC segments is only compressed according to the segment's compression type when
loaded via direct path insert operations. Second, when data is updated, the updated row is migrated to another block