Saturday, July 09, 2011

SOA 11g PS3/PS4: Significant Purging Performance Improvement

Create the following index in the *_soainfra schema to improve the purging significantly. And with significant I mean SIGNIFICANT in upper-case :-). We have loads of instances too purge, +100K. The purging first took more than one hour. After applying the index it took minutes...

CREATE INDEX DLV_MESSAGE_CIKEY_IDX1 ON DLV_MESSAGE
(CIKEY)
LOGGING
TABLESPACE SOAINFRA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

Post a Comment