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

Hi,

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.

Thanks,

Luchuan
luchuancAsked:
Who is Participating?
 
sdstuberCommented:
How did you gather stats?  What was the exact command?
0
 
lwadwellCommented:
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.
0
 
luchuancAuthor Commented:
No before explain plan
0
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
0
 
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?
0
 
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,

Luchuan
0
 
sdstuberCommented:
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
0
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.