Home » RDBMS Server » Performance Tuning » Histograms - good or bad? (split from http://www.orafaq.com/forum/mv/msg/197539/638244/#msg_638244)
Histograms - good or bad? (split from http://www.orafaq.com/forum/mv/msg/197539/638244/#msg_638244) [message #639224] |
Fri, 03 July 2015 02:26 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
Kevin Meade wrote on Fri, 12 June 2015 22:18Read up on collecting histograms.
Additionally also read up on DYNAMIC SAMPLING as if the table is sampled, the CBO will also figure out there are few S and lots of N from the sampling and so still use or not use the index correctly.
Kevin
Histograms prior to 12c can really burn you, you really need to know your data exceptionally well and not trust the database to do these automatically. Even then, prior to the hybrid histograms in 12c it's a serious minefield in my experience.
They can work well in places, no doubt, but folks need to be super careful about putting them in place.
They are probably fine in this case, unless someone adds a new possible value, but I feel compelled to add a note of caution to histograms (prior to 12c, 12c they are different and FAR BETTER beasts).
[Updated on: Mon, 06 July 2015 13:47] by Moderator Report message to a moderator
|
|
|
|
Re: Oracle does not use a conditional index [message #639316 is a reply to message #639290] |
Mon, 06 July 2015 03:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
In this exact case, if the code and skew we've been given are all 100% accurate - it will be completely fine.
My post was more in case other folks stumble upon it and use them without researching them first.
Fortunately I have a couple of links I keep for topics like these, I should really have linked them originally - oversight/short on time on my part:
http://allthingsoracle.com/histograms-part-1-why/
http://allthingsoracle.com/histograms-pt-2/
http://allthingsoracle.com/histograms-part-3-when/
Common "mistakes" (I use the term loosely because these can be a bit tricky)
- I see are people setting low bucket numbers
- Using bind variables (like we always yell at people to use, except they are risky with histograms)
- Gathering stats at the wrong time of the day (you see much more severe problems with histograms than usual stats with this - common examples are orders only in a given state at during the day and a stat gather through the night)
- Not setting them manually, allowing the default job to gather them - this is the killer in my view. It gathers them based on col_usage$ meaning someone running an ad hoc query midweek can spawn a histogram on the next stat gather. Granted, no such thing should happen in production, but we live in a pragmatic world, this isn't typically possible.
I'm fortunate here in that we have good designers who typically tend to get ahead of things like this (looking for "active" cases in a case management system for example) and use a virtual column and an index over that. The benefits are of keeping space down, column can quickly be redefined without a code change to the app if we need it to alter and no statistic instability. This isn't always possible, of course, but it's a really good way to bring stability to the table.
In cases where we absolutely have to have them, I'd be doing analysis on the column, the NDV, the usage, time sensitivity, expected growth/change in the future. Depending where that took us, we can either let it run with defaults or take Mr Lewis' approach of manually setting them when you get a good set.
|
|
|
|
|
|
Re: Histograms - good or bad? [message #639397 is a reply to message #639373] |
Mon, 06 July 2015 13:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I've split these messages off, because it seems to be a very different topic now. And, guess what, I want to contribute
I disagree with Jonathan Lewis (which is very unusual) and RC (equally unusual) on this. I came to the conclusion some time ago that when JL suggests that histograms might not be a Good Thing, he is making an enormous assumption: he is assuming that the code is written by developers who know what they are doing, and that the database is well administered. He may even be assuming that the code is written by, and the database administered by, him. For your typical database (with awful code and minimal admin) histograms are what you need. THe nature of my work for years has been short-term contracts. I see zillions of databases where simply gathering stats, with histograms, solves a multitude of problems. THen of course there are a few SQLs left that need special attention: nine times out of ten, they need extended stats. More histograms! I've been in love with extended stats since they frst appeared, and in 12.x they are better than ever:
http://skillbuilders.com/webinars/webinar.cfm?id=97&w=Tuning-Oracle-SQL-with-Extended-Statistics
http://skillbuilders.com/Oracle/Oracle-Consulting-Training.cfm?category=blogs&tab=john-watsons-blog&node=2870
|
|
|
Re: Histograms - good or bad? [message #639434 is a reply to message #639397] |
Tue, 07 July 2015 02:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
Argh, my post was eaten
They certainly have a place, I suspect our differing viewpoints are probably the nature of the work we do. I'm perm in a decent sized (1500+, some big DBs in there) estate and some of these systems if they fall over make the headlines/regulators ( ) - I'm undoubtedly strongly influenced by that, so really pretty cautious when it comes to generic advice.
For the small things live devtest/non load testing I'm a bit looser but when it comes to prod I'm a bit of a puritan, maybe even to a fault if I'm completely honest.
I can see your point, smaller systems, ill administered, overstretched DBAs they might work ok (a bit like AMM too) but I always try to advise based on the working theory of a "big important" system because, well, it's the safest recourse. A lot of things oracle will carry you through "ready, fire!, aim" but others can get you in a lot of trouble real fast or worse, toss the problem up the hill and the resulting snowball comes back down bigger and badder later on.
Things like these are the poster child for the goto answer of "It depends", which was more my main point which it has expended from quite a bit ![Smile](images/smiley_icons/icon_smile.gif)
Do they have a time and a place, absolutely, but I'd never just lob them in without analysis first, if I have any choice in the matter (fortunately, I usually do).
Extended stats, on the other hand, are fabulous
|
|
|
Re: Histograms - good or bad? [message #639446 is a reply to message #639373] |
Tue, 07 July 2015 04:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Kevin Meade wrote on Mon, 06 July 2015 15:15It is a great help. I see new material for a second revision of my book.
Kevin May I presume to advise? Stop revising your book! Accept the fact that nothing will ever be perfect! More importantly, look ahead. I already have your book, and I would not buy a copy of the next edition. I will however buy a copy of your next one, as will everyone who has it. It's been a year, hasn't it? Definitely time to start work on number two. The only problem will be your family. They will be thinking "Thank heavens - he has finally stopped working on the thing. But.. Surely not... He isn't starting another, is he? Please, no!"
If you run out of ideas, crowd surf for them. Or float an idea for a cooperative venture (which is what I've been trying to get going for a couple of years now, but with no success).
[Updated on: Tue, 07 July 2015 04:23] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Sep 07 20:15:11 CDT 2024
|