question about internals: what exactly does a partition-wise compress do?
Date: Fri, 21 Oct 2011 08:39:00 -0500
Message-ID: <CAPZQniXga1mAyY7WCFp1R4v8_zUZN-ANeqU92oGA8up=6QLHug_at_mail.gmail.com>
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 -- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 21 2011 - 08:39:00 CDT