Strange results with Oracle 8.1.7 and statistic gathering dbms_stats vs dbms_utility 
Author Message
 Strange results with Oracle 8.1.7 and statistic gathering dbms_stats vs dbms_utility
We have a customer with a large app using WebSphere and Oracle 8.1.7.

They run:

exec dbms_utility.analyze_schema('SAP', 'COMPUTE'); weekly.

We recently ran:

dbms_stats.gather_schema_stats(ownname=>'SAP';)

on our test system and saw HUGE improvements in speed.

Why is this? What is the difference between the two.

Please help and advise.

Martin Folb
WGCUSA



Mon, 21 Mar 2005 04:38:02 GMT
 Strange results with Oracle 8.1.7 and statistic gathering dbms_stats vs dbms_utility

Quote:

> We have a customer with a large app using WebSphere and Oracle 8.1.7.

> They run:

> exec dbms_utility.analyze_schema('SAP', 'COMPUTE'); weekly.

> We recently ran:

> dbms_stats.gather_schema_stats(ownname=>'SAP';)

> on our test system and saw HUGE improvements in speed.

> Why is this? What is the difference between the two.

> Please help and advise.

> Martin Folb
> WGCUSA

Oracle only keeps the dbms_utility_analyze, and the other anlayze
methods, around for purposes of backward compatibility. They are for all
intents and purposes desupported and don't handle new features.

But I would suggest, in the future
dbms_stats.gatehr_schema_stats(ownname=>'SAP', cascade=TRUE)

so that it will also analyze the indexes.

Daniel Morgan



Mon, 21 Mar 2005 23:33:54 GMT
 Strange results with Oracle 8.1.7 and statistic gathering dbms_stats vs dbms_utility

Quote:


> > We have a customer with a large app using WebSphere and Oracle 8.1.7.
> > They run:
> > exec dbms_utility.analyze_schema('SAP', 'COMPUTE'); weekly.
> > We recently ran:
> > dbms_stats.gather_schema_stats(ownname=>'SAP';)
> > on our test system and saw HUGE improvements in speed.
> > Why is this? What is the difference between the two.

> > Please help and advise.

> > Martin Folb
> > WGCUSA

> Oracle only keeps the dbms_utility_analyze, and the other anlayze
> methods, around for purposes of backward compatibility. They are for all
> intents and purposes desupported and don't handle new features.

> But I would suggest, in the future
> dbms_stats.gatehr_schema_stats(ownname=>'SAP', cascade=TRUE)

> so that it will also analyze the indexes.

> Daniel Morgan

exec dbms_stats.gatehr_schema_stats(ownname=>'SAP', cascade=>TRUE);

the '>' is required there.

Make sure that you don't use a mix of stats gathered by the 2 methods.

also, are you certain that the parameters for 'optimizer%' are the same?

Paul



Tue, 22 Mar 2005 11:12:36 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. DBMS_UTILITY.ANALYZE_SCHEMA vs. DBMS_STATS.GATHER_SCHEMA_STATS

2. dbms_utility and dbms_stats difference difference

3. dbms_utility and dbms_stats difference

4. DBMS_STATS gather stale option

5. Strange Statistics I/O Results

6. DBMS_STATS - Global Statistics

7. Are statistics gathered on function indexes?

8. Are statistics gathered on function indexes?

9. how gather statistics for the actual user

10. Gathering application use statistics

11. Gathering statistics for cost based optimization

12. PostgreSQL vs MySQL : strange results on insertion


 
Powered by phpBB® Forum Software