oracle sql running 4 times slows after truncate table, reload data, rebuild indexes


We truncated a table, dropped indexes, insert data back with 0.1% new data, rebuild indexes,
analyzed table estimate 33%. But our sqls based on this table run about at least 4  times slower , some of them hung there. Did we miss a step?

Please advise ASAP.


Who is Participating?
How did you gather stats?  What was the exact command?
Did you happen to capture the explain plans for the SQL's before the truncate etc.?  With a change in the table size with the new stats ... the explain plans may have shifted so having the reference point to prior the change can help analyse the problem.
luchuancAuthor Commented:
No before explain plan
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Geert GOracle dbaCommented:
did you reuse or drop the storage
with reuse, you may have a loft of "unused" space in the table

if the table is currently in tablespace A

alter table move tablespace B;
alter table move tablespace A;

this should shrink the table to the correct size
slightwv (䄆 Netminder) Commented:
It's also possible the 33% estimate was not enough to get a valid representation of the data.

Can you post the current execution plan?
luchuancAuthor Commented:
Hi All,

We ran the following command the that seems resolved the problem:
exec dbms_stats.gather_table_stats('AXIUM','HISTORY', estimate_percent=>20, method_opt=>'FOR ALL COLUMNS SIZE 1', cascade=>true);

Thanks a lot,

20% is really big

if you're going to sample such a large portion of the table, you're probably better off doing a full compute (NULL) rather than estimate.

my guess is the first time you didn't have cascade so the indexes were missed
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.