question about internals: what exactly does a partition-wise compress do?

  • From: Charles Schultz <sacrophyte@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 21 Oct 2011 08:39:00 -0500

Good day, listers,
I have two questions wrapped up in one issue. :)
1) What exactly is "enq: SI - contention" - it does not appear to be
documented, and not many relevant hits on google
2) Why would a subpartition compress operation block simultaneous compress
operations in different subpartitions or partitions?

*Environment*
Oracle 11.1.0.7.5 on Solaris 10
178 million row table with 15 partitions and 15 subpartitions each;
non-uniform distribution of data

*Explanation*
Compress one subpartition in background: alter TABLE FGBTRND move
subpartition FS04_P02 compress;
Attempt to compress another subpartition at the same time: alter TABLE
FGBTRND move subpartition FS04_P03 compress;

A 10046 trace on the second will show wait events similar to the following:
nam='enq: SI - contention' ela= 4951635 name|mode=1397293062 object #=44908

If I issue yet a third compress operation, I start seeing deadlock issues.
All these events seem to be based on contention in the table itself
(object_id = 44908 is table FGBTRND), not the sub/partitions.

If there is a different trace event that would make this more clear/obvious,
I am all ears. :) I tried looking through the list of trace events that I
could set, and was unable to pinpoint one that might be helpful in this
case.

Any help would be much appreciated.
Thanks much,


-- 
Charles Schultz


--
//www.freelists.org/webpage/oracle-l


Other related posts: