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!

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

dmcghan
Answer
990870,

Please set up a demo of your problem on apex.oracle.com. Provide us with developer credentials and steps to reproduce.

Regards,
Dan

blog: http://DanielMcghan.us/
work: http://SkillBuilders.com/APEX/
Marked as Answer by Srinivash · Sep 27 2020
Howard (... in Training)
Hi 990870,

A few preliminaries. Could we get you to change your handle from a number to a name? (I'm Howard.) Also, If you haven't read the useful advice in https://forums.oracle.com/forums/ann.jspa?annID=1324 , take a look.

A demo is a great idea.

A few thoughts. [I'm only a newbie.] In what I've done, APEX seemingly constructs/makes a page (or region) visible and then updates it. So, one sees the current value and it changes to the the new value before our very eyes. A feature? It could be a useful one. But what we want is to have the page cleared before it's made visible so we don't see the former image. Right?

So, is there a way to change the page before it displays? Can we do this: Hide the region, update the region, show the updated region? Not sure.

But if it were, say, a single button -- maybe that can be done with some javascript. You already have a DA.

One could add a new action "Execute Javascript Code" and in Settings have the following Code:
[This was someone's example to show or hide a button based on a count in P9_COUNT.]
var my_cnt = $v('P9_COUNT');
if( my_cnt > 0 )
{
   $('#MY_SUB_BTN').show();
}
else
{
   $('#MY_SUB_BTN').hide();
};
So one would have three actions on the same DA. The first would hide the region, the second would hide the button and the third would show the update region without the button. Maybe it could work.

I'll see if there's any time to experiment with this today.

Regards,
Howard
Srinivash
Hi Dan,
I have made a demo app at apex.oracle.com with the following credentials
workspace-srinivash
user-srinivashvarma@yahoo.com
password-twilight


you can go to the only app present there named as 'practice dynamic action jan'.When you run the page you will get an select list item which you when change displays the report according to the empno selected.But before it shows the report it also shows no data found for few second which I dont required.Also when you select a empno and then NULL then again any other empno it will show the previous report first.
And my restriction is I cant submit page.It has to be done through dynamic action

Thanks in advance
Srinivash
Srinivash
Hi Howard,
Thanks for the suggestion I did change my handle and I think the idea you are giving will require me to page reload which I cant do in my page as it will completely change other things because of the other on load process.Please go through the demo I provided and help me.

Thanks in advance
Srinivash
HarryF
My approach is to have a conditional on the region:

:SELECT_LIST is not null

If the page action when value changed for the select list is submit page it will refresh the page and show the region.
Tom Petrus
Srinivash,

First of all, the built-in refresh has been made so that no screen-flicker-effect would occur: the refreshed region is a bit of html requested from the server, dumped in a container, and then replaces the original html (a report in this case).
What you want, from what I understand, is to hide the region when it is refreshed.

Looking at your app, you probably want to keep the region hidden during the PLSQL code and the refresh action. I just put the "Hide" effect you had under the "False" actions as first action in the "True" actions.
Doesn't that achieve what you want? I'm not sure why you put a false action under a change event. A change will always be a true action, it will only fire when there is an actual change.
dmcghan
Srinivash,

I looks like the refresh action was move to asynchronous Ajax (kinda funny, I know) and that was the problem. However, if you leverage the apexafterrefresh event that is triggered on the region after the refresh is complete, then it's an easy workaround.

I updated your demo to do this via a new dynamic action. Is that working as you wanted it to?

Regards,
Dan

blog: http://DanielMcghan.us/
work: http://SkillBuilders.com/APEX/
Srinivash
Hi Dan,
Thanks a lot Its working as required I wanted when the select list is chosen as NULL then the region should hide and I added that as a false condition and now its totally as required.So after refresh action which I was missing .Once again thanks for the help Dan.

Thanks
Srinivash
Srinivash
Hi Harry,
Actually i had the limitation of not making the page reload so i cant make the report region as conditional and also the refresh action only works on the report query it doesn't refresh or check the condition there again so I was not able to apply this approach.Thanks for your help.

Thanks
Srinivash
Srinivash
Hi Tom,
I needed that false action to hide the region when the select list is changed to NULL and when not NULL to refresh and show the region.Thanks for your suggestion that hide thing is the correct approach you are correct.

Thanks
Srinivash
1 - 10
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,749 views