Kubernetes Business Insights

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

3 min read

Dec 22, 2014

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 “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. 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. [code lang="sql" highlight="21"]create table z_tst(num number, rn number, name varchar2(200)) compress for query high partition by list(num) ( partition p1 values(1), partition p2 values(2)); 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; [/code] 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. Let's take a look into the block dump: [code lang="text"]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 } [/code] 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% pctfee defaults here since it's OLTP compression. Let's try to update two different rows in the same type 64 compressed block: [code lang="sql"]select rn from z_tst where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) = 3586 and rownum <= 4; RN ---------- 1360 1362 1364 1366[/code]

From the first session:

[code lang="sql"]update z_tst set name = 'a' where rn = 1360;[/code]

From the second:

[code lang="sql"]update z_tst set name = 'a' where rn = 1362; -- waiting here[/code] Second session waits on "enq: TX - allocate ITL entry" event.

Summary

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.

← Watch: Hadoop vs. Oracle Exadata

Ingest a single table from Microsoft SQL Server Data into Hadoop →

Get Email Notifications

No Comments Yet

Let us know what you think

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

Summary

Share this

You May Also Like

Distinguish real SQL execution plans from fake ones!

GoldenGate 12.2 big data adapters: part 1 - HDFS

Database 12c: What's New with Data Pump? Lots.

Get Email Notifications

No Comments Yet