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

Posted on 2012-08-19
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

    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

    No before explain plan
    LVL 73

    Accepted Solution

    How did you gather stats?  What was the exact command?
    LVL 36

    Assisted Solution

    by:Geert Gruwez
    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 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    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

    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 73

    Expert Comment

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now