Home » SQL & PL/SQL » SQL & PL/SQL » Quick question about statistics
Quick question about statistics [message #193842] Tue, 19 September 2006 11:59 Go to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
I used this on my schema...

EXEC DBMS_STATS.gather_schema_stats('SCOTT');

Does this just collect statistics on the tables? I noticed that the indexes don't have statistics. Is it important to have statistics on indexes and other objects or is the method I'm using sufficient?

Thanks.

[Updated on: Tue, 19 September 2006 12:00]

Report message to a moderator

Re: Quick question about statistics [message #193843 is a reply to message #193842] Tue, 19 September 2006 12:02 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Yes, I would recommend gathering stats on the indexes also:

DBMS_STATS.gather_schema_stats('SCOTT', cascade=>true);
Re: Quick question about statistics [message #193856 is a reply to message #193843] Tue, 19 September 2006 13:21 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
Thanks Todd. Conversely, is there a way to remove ALL statistics. I've tried this...

EXEC DBMS_STATS.delete_schema_stats('ENVDB');

And there still seems to be statistics on somethings. And the optimizer is choosing CBO still (which to me indicates that some statistics are still around).

I tried using...

EXEC DBMS_STATS.delete_schema_stats('ENVDB', cascade=>true);

But it doesn't like that.

Thanks.
Re: Quick question about statistics [message #193861 is a reply to message #193856] Tue, 19 September 2006 13:44 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Are You on 9i or 10g?
You might see dynamic sampling kicking in.
- It is the optimizer sampling a very small amount of data at before parsing Your statement.

Why do You remove the statistics?

Re: Quick question about statistics [message #193883 is a reply to message #193861] Tue, 19 September 2006 15:51 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
We are afraid the our queries haven't been optimized for CBO. They were all written for RBO. We want to test on test database before going to production. I added stats to a few tables in production and now I can't seem to get rid of them. My production queries seem to use CBO even though it looks like the stats are gone.

9i release one.

EDIT: We are going to promote to 10g soon but our resources are stretched thin right now - we have the license.

[Updated on: Tue, 19 September 2006 15:52]

Report message to a moderator

Re: Quick question about statistics [message #193894 is a reply to message #193883] Tue, 19 September 2006 17:22 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Why would You upgrade to such an old version og Oracle?

Anyway - You probably need stats on all tables, as they will be ignored when joining to tables without stats.

Gather full stats (compute), incl. indexes, all columns with histograms, and see if You should be able to run using the CBO.

It might work!
Re: Quick question about statistics [message #193898 is a reply to message #193894] Tue, 19 September 2006 18:02 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
kimant wrote on Tue, 19 September 2006 17:22

Why would You upgrade to such an old version og Oracle?


10g is old???

Quote:

Anyway - You probably need stats on all tables, as they will be ignored when joining to tables without stats.

Gather full stats (compute), incl. indexes, all columns with histograms, and see if You should be able to run using the CBO.


Yes, Todd provided a method for collecting all statistics...

DBMS_STATS.gather_schema_stats('SCOTT', cascade=>true);

I was wondering if there was a way to remove all statistics since this...

EXEC DBMS_STATS.delete_schema_stats('ENVDB');

Does not seem to do it. But it is beginning to look like the answer is no (though manually doing it is a possibility).
Re: Quick question about statistics [message #193899 is a reply to message #193898] Tue, 19 September 2006 18:35 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
This is why I want to remove statistics...

select /*+ rule */ * from 
tblSamplePrep,tblSample
where tblSamplePrep.sampleindex = tblSample.sampleindex;

select /*+ rule */ * from tblSample,tblAnalysis
where tblSample.sampleindex = tblAnalysis.sampleindex;

select * from 
tblSamplePrep,tblSample
where tblSamplePrep.sampleindex = tblSample.sampleindex;

select * from tblSample,tblAnalysis
where tblSample.sampleindex = tblAnalysis.sampleindex;


[1]: 5 Row(s) selected in 0.48 sec
[2]: 5 Row(s) selected in 0.17 sec
[3]: 5 Row(s) selected in 2.89 sec
[4]: 5 Row(s) selected in 2.63 sec

UPDATE:

This does appear to remove all statistics...

EXEC DBMS_STATS.delete_schema_stats('ENVDB');

After removing statistics...

[1]: 5 Row(s) selected in 0.25 sec
[2]: 5 Row(s) selected in 0.31 sec
[3]: 5 Row(s) selected in 0.53 sec
[4]: 5 Row(s) selected in 0.31 sec

[Updated on: Tue, 19 September 2006 18:39]

Report message to a moderator

Re: Quick question about statistics [message #193948 is a reply to message #193899] Wed, 20 September 2006 02:02 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
From what You wrote, it seems You are testing on "9i release one"?

You cannot really compare runtimes, as they are influenced by many factors.
See the two runs with RULE hinted. Although they completely ignore the stats/missing stats, the runtime is the half/the double of the previous run. Something else has influenced here, possibly other users of the database, server or disksystem.

Instead compare consistent gets, cpu usage etc.

And I do not think that those queries without the hints are "designed for the RULE optimizer". They are plain and simple joins between two tables.

Try running with this:

begin
dbms_stats.gather_schema_stats(ownname => user, cascade => true, method_opt => 'for all columns size 254');
end;
/

If You are still not satisfied, check the execution plans, and find out what is wrong/where the optimizer goes the wrong path.
Adjusting optimizer_index_cost_adj could be an option.

Just upgrading and expecting everything to be working perfectly is .... naive. There are added so many new features, options etc. to each version, and I do not think we can expect Oracle to predict every customers requirement, so they (usually) set some common parameters that sometimes needs adjustments...

I do not mean to flame You or anything, but I have heard it many times, that it is "impossible" to upgrade to new versions, as performance degrades. Every time I was allowed, it was indeed possible to upgrade, but it sometimes took some time to get it right, performance wise.

Smile
Kim
Re: Quick question about statistics [message #194054 is a reply to message #193948] Wed, 20 September 2006 09:55 Go to previous message
ferrethouse
Messages: 43
Registered: August 2006
Member
kimant wrote on Wed, 20 September 2006 02:02

From what You wrote, it seems You are testing on "9i release one"?


Correct.

Quote:

You cannot really compare runtimes, as they are influenced by many factors.


It is a dev box and I'm the only one hitting it. But I understand that there are still other factors (though I ran the queries many times and confirmed those results.

Quote:

If You are still not satisfied, check the execution plans, and find out what is wrong/where the optimizer goes the wrong path.
Adjusting optimizer_index_cost_adj could be an option.


Yes. We have explore this and setting optimizer_index_cost_adj to 1 and optimizer_index_cache to 100 does have a positive impact - we just aren't sure how they impact the 200 other queries already in production.

Quote:

I do not mean to flame You or anything, but I have heard it many times, that it is "impossible" to upgrade to new versions, as performance degrades. Every time I was allowed, it was indeed possible to upgrade, but it sometimes took some time to get it right, performance wise.


I don't call the shots. The wheels are in motion (setting up a test database with 10g is the first step) but I work in a large company and caution is the word of the day.

Thanks for your help! I will try that gather stats method you provided.
Previous Topic: Bulk Fetch
Next Topic: Pls correct this simple stored procedure...need help..
Goto Forum:
  


Current Time: Fri Dec 09 15:51:28 CST 2016

Total time taken to generate the page: 0.32557 seconds