asked on

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

SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

luchuanc

ASKER

No before explain plan

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

luchuanc

ASKER

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

Sean Stuber

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