Histogram Maintenance

[chrisrlong]

I am in the middle of a performance tuning exercise. Unfortunately, I just realized that I accidentally excluded 40 statements from my baseline. Therefore, I am restoring the database to baseline mode, which means a lot of things including the removal of a bunch of newly-created histograms. What is the preferred method of eliminating these beasts? :)

Of course, once I'm done re-running my baseline, I need to re-create these same histograms (and everything else, of course). Is there any way to do this short of re-building them? Note that we are using DBMS_STAT to flip the stats between the baseline and current. Do histograms play into this at all?

Now, the restrictions are that I am a designer/developer and not a DBA, nor do I have DBA rights. I also do not have, nor care to have, any knowledge of or access to Unix :), so this must all be done within SQL. I can manually work around all of these issues, so I'm not sweating this, but I am always looking for the 'better way'.

Thanks in advance,

- Chris

[pando]

Hey chris sorry to disappoint you but I havent used histogram at all.
I was wondering if you could have a look in my tricky SQL post since I am not very SQL efficient :P

Thanks

[chrisrlong]

Yeah, I saw that one lurking around :) - just hadn't had time to look at it. But far be it from me to refuse a personal request :), so I gave it a shot - hope it helps.

- Chris

[chrisrlong]

Whoa - relegated to page 2 already. Sorry for pinging my own thread, but I really would like some feedback on this.

Any input would be appreciated.

Thanks,

- Chris

[LND]

do you mean clearing 'wrong' histograms from database (via 'analyze delete statistics') ? To check if really deleted use DB views.

[chrisrlong]

I am just looking for the proper way to surgically remove a specific histogram.

'analyze delete statistics' would whack *all* the statistics for a table or column, which is more than what I want to get rid of.

Any other thoughts?

- Chris

[LND]

Why don't you want to replay statisticcs generation scripts for a table anew? Performance issue?
Anyway, it looks quite dangerous to rebuild only part of statistics instead of all as they can be left inconsistent, isn't it?

[chrisrlong]

Well, it's more of a conceptual issue. If one can specifically create a histogram without affecting anything else, why can one not remove that same histogram without affecting anything else? I can create and drop a specific index. Why can I not do the same for a histogram? It just seems silly. Mind you, I have noticed that if I re-analyze the column with a size of 1 - that *seems* to get me back to my starting point (effectively eliminating the histogram), but it takes just as long as creating the bloody histogram in the first place. Makes sense, I guess, since it is actually making a new, 1-bucket histogram for the field. Luckily, this is pretty much what a standard analyze does, which is why I say it has the net effect of 'eliminating' the histogram. Again, however, it takes too long, IMHO - there should be a surgical strike to drop a histogram.

- Chris

[LND]

it is about dependencies:
it could well be that oracle makes use of some other statistics to calculate histogram.
So it simply recalculates eveything before calculating histogram itself.
As for performace, then if you need histogram => you deal with DDS system => this is not a 24*7 system => you can run that jobs when system is not used.