Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2012-08-19
Medium Priority
Last Modified: 2012-08-20

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.


Question by:luchuanc
LVL 25

Assisted Solution

lwadwell earned 500 total points
ID: 38309429
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.

Author Comment

ID: 38309472
No before explain plan
LVL 74

Accepted Solution

sdstuber earned 500 total points
ID: 38309979
How did you gather stats?  What was the exact command?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 500 total points
ID: 38310689
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
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 38311330
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?

Author Closing Comment

ID: 38311860
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,

LVL 74

Expert Comment

ID: 38311963
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month11 days, 8 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question