Oracle Database Discussions

Hello everybody,

I am on Oracle 11.2.0.2. Collecting statistics for one of my tables takes extremely long (several hours), and I don't know why.

I am inserting about 11.000 new records into a table with 5.3 million existing records using an INSERT INTO ... SELECT ...

This insert takes a little more than 3 minutes

Then I collect the stats using :

dbms_stats.gather_table_stats('SCOTT', 'S_RMP_EVALUATION_CSC_MESSAGE', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

This now takes 2 hours.

If the number of records increases, the time it takes for the stats increases as well (8 hours after 70.000 newly inserted records)

I did not have this problem until I recently created the index I_S_RMP_EVAL_CSC_MSG_ACTIONS, but I don't understand why this would cause a drastic change like this. Especially since inserting the records and updating the index takes only a few minutes.

I am posting the create statements for the table and the indexes on the table below.

There are about 5.3 million records in the table. The table uses about 7.8 GB of space for "regular" table data and 37.6 GB for the LOB. The indexes are using:

CREATE TABLE "QQRCSBI0"."S_RMP_EVALUATION_CSC_MESSAGE" 
( "ID" NUMBER(22,0) NOT NULL ENABLE, 
  "XML_MESSAGE_TEXT" CLOB, 
  "CREATION_TIME" TIMESTAMP (6), 
  "LAST_UPDATE_TIME" TIMESTAMP (6), 
  "NEXT_UPDATE_SYNC_TS" TIMESTAMP (6), 
  "SW_VERSION_XML" VARCHAR2(100 BYTE), 
  "DWH_LM_TS_UTC" DATE DEFAULT NULL NOT NULL ENABLE, 
  CONSTRAINT "PK_S_RMP_EVALUATION_CSC_MESSAG" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STAGING"  ENABLE
) SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "STAGING" 
LOB ("XML_MESSAGE_TEXT") STORE AS BASICFILE (
  TABLESPACE "STAGING" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
) ;


CREATE INDEX "QQRCSBI0"."I_S_EVALUATION_CSC_MSG_IDLM" ON "QQRCSBI0"."S_RMP_EVALUATION_CSC_MESSAGE" ("ID", "DWH_LM_TS_UTC") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STAGING" ;


CREATE INDEX "QQRCSBI0"."I_S_EVALUATION_CSC_MSG_LMID" ON "QQRCSBI0"."S_RMP_EVALUATION_CSC_MESSAGE" ("DWH_LM_TS_UTC", "ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STAGING" ;


CREATE BITMAP INDEX "QQRCSBI0"."I_S_RMP_EVAL_CSC_MSG_ACTIONS" ON "QQRCSBI0"."S_RMP_EVALUATION_CSC_MESSAGE" (DECODE(INSTR("XML_MESSAGE_TEXT",'<actions>'),0,0,1)) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STAGING" ;

What causes this extreme long duration for collecting the stats, and what can I do to fix that (apart from removing the index again)? Why does collecting the stats take so much longer than updating data and indexes?

Thank you...