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
Roachcoach
Messages: 1508
Registered: May 2010
Location: UK
Senior Member
Kevin Meade wrote on Fri, 12 June 2015 22:18
Read 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 #639290 is a reply to message #639224] Sat, 04 July 2015 20:04 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
Caution is always in order, good to remind us. Proper testing is also a fine safeguard.

Still, histograms, and dynamic sampling are two solutions for this specific problem. You are correct as usual to promote caution, but I would not want people to avoid histograms altogether out of fear.

What are you recommending as a solution?

Kevin
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 messageGo to next message
Roachcoach
Messages: 1508
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: Oracle does not use a conditional index [message #639369 is a reply to message #639316] Mon, 06 July 2015 08:40 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
This is very cool stuff Roachcoach, and the kind of detail that can really help people. Thanks for sharing it. Makes me proud to be a member of OraFAQ.

Kevin
Re: Oracle does not use a conditional index [message #639370 is a reply to message #639369] Mon, 06 July 2015 08:56 Go to previous messageGo to next message
Roachcoach
Messages: 1508
Registered: May 2010
Location: UK
Senior Member
Always happy to help Smile
Re: Oracle does not use a conditional index [message #639373 is a reply to message #639370] Mon, 06 July 2015 09:15 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
It is a great help. I see new material for a second revision of my book.

Kevin
Re: Histograms - good or bad? [message #639397 is a reply to message #639373] Mon, 06 July 2015 13:59 Go to previous messageGo to next message
John Watson
Messages: 7212
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 Smile

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 messageGo to next message
Roachcoach
Messages: 1508
Registered: May 2010
Location: UK
Senior Member
Argh, my post was eaten Confused

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 (Sad) - 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


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 Very Happy
Re: Histograms - good or bad? [message #639446 is a reply to message #639373] Tue, 07 July 2015 04:22 Go to previous message
John Watson
Messages: 7212
Registered: January 2010
Location: Global Village
Senior Member
Kevin Meade wrote on Mon, 06 July 2015 15:15
It 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

Previous Topic: Oracle does not use a conditional index
Next Topic: Please provide help for tuning the sql Query
Goto Forum:
  


Current Time: Tue Jan 16 14:17:21 CST 2018

Total time taken to generate the page: 0.01894 seconds