OLTP type 64 compression and 'enq: TX - allocate ITL entry' on Exadata

Recently we've seen a strange problem with the deadlocks at the client database on Exadata, Oracle version 11.2.0.4. Wait events analysis showed that sessions were waiting for the “enq: TX - allocate ITL entry” event. It was strange because there are at most two sessions making DMLs and at least two ITL slots are available in the affected tables blocks.

I made some block dumps and found that affected blocks contain the OLTP-compressed data, Compression Type = 64 (DBMS_COMPRESSION Constants - Compression Types). Actually table has the “compress for query high” attribute, but direct path inserts have never used, so I'm not expecting any compressed data here.

Investigating the ITL Mystery and Compression Type 64

Compression Type 64 is very specific type. Oracle migrates data out of HCC compression units into Type 64 compression blocks in case of updates of HCC compressed data. We made some tests and were able to reproduce Type 64 compression without direct path operations. Here is one of the test cases. MSSM tablespace has been used, but problem is reproducible with ASSM too.

Test Case: Reproducing Compression Without Direct Path Operations

create table z_tst(num number, rn number, name varchar2(200))  partition by list(num)  ( partition p1 values(1), partition p2 values(2)) compress for query high;   Table created.   insert into z_tst select mod(rownum , 2) + 1, rownum, lpad('1',20,'a')  from dual connect by level <= 2000;   2000 rows created.   commit;   Commit complete.   select dbms_compression.get_compression_type(user, 'Z_TST', rowid) comp, count(*) cnt  from Z_tst group by dbms_compression.get_compression_type(user, 'Z_TST', rowid);   COMP CNT  ---------- ----------  64 2000   select dbms_rowid.rowid_block_number(rowid) blockno, count(*) cnt  from z_tst a group by dbms_rowid.rowid_block_number(rowid);   BLOCKNO CNT  ---------- ----------  3586 321  2561 679  3585 679  2562 321   select name, value from v$mystat a, v$statname b  where a.statistic# = b.statistic#  and lower(name) like '%compress%' and value != 0;   NAME VALUE  -------------------------------------------------- ----------  heap block compress 14  HSC OLTP Compressed Blocks 4  HSC Compressed Segment Block Changes 2014  HSC OLTP Non Compressible Blocks 2  HSC OLTP positive compression 14  HSC OLTP inline compression 14  EHCC Block Compressions 4  EHCC Attempted Block Compressions 14   alter system dump datafile 16 block min 2561 block max 2561; 

We can see that all rows are compressed by compression type 64. From the session statistics we can see that HCC had been in place before the data was migrated into OLTP Compressed Blocks. I think, this is not an expected behavior and there is should not be any compression involved at all.

Deep Dive into the Block Dump

Let's take a look into the block dump:

Block header dump: 0x04000a01  Object id on Block? Y  seg/obj: 0x6bfdc csc: 0x06.f5ff8a1 itc: 2 flg: - typ: 1 - DATA  fsl: 0 fnx: 0x0 ver: 0x01   Itl Xid Uba Flag Lck Scn/Fsc  0x01 0x0055.018.0002cd54 0x00007641.5117.2f --U- 679 fsc 0x0000.0f5ffb9a  0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000   bdba: 0x04000a01  data_block_dump,data header at 0x7fbb48919a5c  ===============  tsiz: 0x1fa0  hsiz: 0x578  pbl: 0x7fbb48919a5c  76543210  flag=-0----X-  ntab=2  nrow=680  frre=-1  fsbo=0x578  fseo=0x5b0  avsp=0x6  tosp=0x6  r0_9ir2=0x1  mec_kdbh9ir2=0x1  76543210  shcf_kdbh9ir2=----------  76543210  flag_9ir2=--R-LNOC  Archive compression: N  fcls_9ir2[3]={ 0 32768 32768 }  perm_9ir2[3]={ 0 2 1 } 

It's bit odd that avsp (available space) and tosp (total space) = 6 bytes. So there is no free space in the block at all, but I'm expecting to see 10% pctfree defaults here since it's OLTP compression.

Simulating the Concurrent Update Failure

Let's try to update two different rows in the same type 64 compressed block:

select rn from z_tst  where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) = 3586  and rownum <= 4;   RN  ----------  1360  1362  1364  1366 

From the first session:

update z_tst set name = 'a' where rn = 1360; 

From the second session:

update z_tst set name = 'a' where rn = 1362; -- waiting here 

The second session waits on the "enq: TX - allocate ITL entry" event.

Summary and Key Findings

• In some cases HCC and subsequent OLTP, type 64 compression can take place even without direct path operations (probably a bug).
• OLTP, type 64 compressed block, in contrast to regular OLTP compression, can have no free space after data load.
• In case of DML operations, the whole type 64 compressed block gets locked (probably a bug).
• Better not to set HCC attributes on segments until the real HCC compression operation.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?