Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Collecting stats takes forever after adding new index - why?

978225Jan 14 2014 — edited Jan 16 2014

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:

IndexSpace used

I_S_EVALUATION_CSC_MSG_LMID: 

152 MB

I_S_EVALUATION_CSC_MSG_IDLM:

144 MB

PK_S_RMP_EVALUATION_CSC_MESSAG: 

118 MB
I_S_RMP_EVAL_CSC_MSG_ACTIONS: 5 MB

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...

This post has been answered by Jonathan Lewis on Jan 15 2014
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 13 2014
Added on Jan 14 2014
19 comments
10,719 views