Dropping the stats

Does anyone know which tables the stats are in, I need to drop these as they are not required. ??

You have to delete the statistics for each table that you want:

EXAMPLE:
Deleting Statistics The following statement deletes statistics about the CUST_HISTORY table and all its indexes from the data dictionary:

ANALYZE TABLE cust_history DELETE STATISTICS;

I'm probably going to regret asking this, but: Why? Why do you feel the need to delete statistics? The CBO can't work without them. They certainly don't take up much room. The RBO is being phased out. Why would you want to delete them?

Just curious,
- Chris

Many thanks for that it has cured the problem!

For the answer above, the reason I needed to Delete the stats is that the users querys use the rule based optimizer. However if the stats or optimized then oracle will uses the core space, which makes rule based querys take forever and uses up a large quantity of temp space.

Once again rhanks for the help

Okay, aside from my concern over your users writing RBO-based queries, I am more interested in what you mean by:

"However if the stats or optimized then oracle will uses the core space, which makes rule based querys take forever and uses up a large quantity of temp space. "

I fully admit to being rather light on the admin side of things, but this seems odd to me. If your default optimizer_mode is CHOOSE, then the existance of statistics will cause the optimizer to use CBO mode. This can be fixed by changing the default mode or adding a RULE hint to those specific queries. However, it sounds like you are saying that the queries are definitely using the RBO, but the existance of statistics is still causing issues. That is the part I don't get. First of all, what is 'core' space and why would the simple existance of statistics cause Oracle to use core space on RBO-based queries when it normally wouldn't (so you seem to say)? And why would the simple existance of statistics cause Oracle to use more temp space on an RBO query thatn it otherwise would (so you seem to say)?

Confused,

- Chris

I would also like to hear the explanation. Quite a while ago, we were working with a previous versio of Siebel, unsing Oracle 7.3. I was trying to help with some performance tuning, and analyzed the tables. This did not exactly give the desired result - the response time increased.
I dropped the statistics, and it sped up to its previous slow response time.
In talking to the folks familiar with Siebel, that version used the RBO.
Unfortunatley, at that point, we were unable to devote the time to delve into why, but it's something that has puzzled me ever since.

I'm just pinging this thread again because I don't want it to drop off the page and I really am interested in the answer. Can anyone shed any light on birdax1's assertion, or shall I simply assume that he was seeing the RBO-written queries actually using the CBO and thereby getting different plans?

- Chris