This discussion is archived
7 Replies Latest reply: May 15, 2012 2:53 PM by rp0428 RSS

Statistics - To collect histograms or not to collect

937246 Newbie
Currently Being Moderated
Hi,

I was discussing with a friend about collecting statistics with histogram, if it worth spend time studying the table, its queries and where predicates or if the performance gains worth the spent time.

Checking the documentation, the default statistics gathering method for 10g and onwards is FOR ALL COLUMNS SIZE AUTO, so, it gets some workload to calculate the statistics, unlike the 9i version, which uses FOR ALL COLUMNS SIZE 1.

Also, checking the Ask Tom webpage (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:897382100346033938#2468091800346350999), he says that either:

a) do not gather histograms
b) you use the maximum buckets, if there are less than 254, we only use what we need and get exact cardinalities for values. If there are more, we use 254 and 'guess' at the cardinalities.

Since there's no magic formula to check which tables are elegible, how many buckets and which histogram statistics should be collected (height-balanced of frequency), I'd like to know the opinion of you guys to this question.

Thanks in advance
  • 1. Re: Statistics - To collect histograms or not to collect
    rp0428 Guru
    Currently Being Moderated
    >
    I was discussing with a friend about collecting statistics with histogram, if it worth spend time studying the table, its queries and where predicates or if the performance gains worth the spent time.
    >
    If it ain't broke don't fix it.
  • 2. Re: Statistics - To collect histograms or not to collect
    Iordan Iotzov Expert
    Currently Being Moderated
    Welcome to the forum!

    It is all about weighing the issues caused by automatically created histograms with the benefit they bring in your system. Here are some things to consider:

    Oracle version – Oracle 10g was not particularly good in picking which columns should get histograms and in calculating density/cardinality in some cases (http://richardfoote.wordpress.com/2008/01/04/dbms_stats-method_opt-default-behaviour-changed-in-10g-be-careful/) . Oracle 11g is so much better.

    Type of system – if you have OLTP and use bind variables (as you should) then you should be careful about histograms - http://jonathanlewis.wordpress.com/2009/05/06/philosophy-1/. Adaptive cursor sharing, an Oracle 11g feature, is a step towards reducing those problems.
    If you have DW, and most of you queries use literals, then histograms could be quite useful.

    Please note that even if you choose not to gather histograms by default, you can still manually add a histogram after carefully studying the table data and the load.

    Iordan Iotzov
    http://iiotzov.wordpress.com/
  • 3. Re: Statistics - To collect histograms or not to collect
    937246 Newbie
    Currently Being Moderated
    Thanks, dude! That's what I wanted to know!
  • 4. Re: Statistics - To collect histograms or not to collect
    jgarry Guru
    Currently Being Moderated
    rp0428 wrote:
    >
    I was discussing with a friend about collecting statistics with histogram, if it worth spend time studying the table, its queries and where predicates or if the performance gains worth the spent time.
    >
    If it ain't broke don't fix it.
    Ain't the default broke?
  • 5. Re: Statistics - To collect histograms or not to collect
    rp0428 Guru
    Currently Being Moderated
    >
    Ain't the default broke?
    >
    I have no idea. OP did not say what version of Oracle or what settings, default or otherwise, are being used.
  • 6. Re: Statistics - To collect histograms or not to collect
    jgarry Guru
    Currently Being Moderated
    rp0428 wrote:
    >
    Ain't the default broke?
    >
    I have no idea. OP did not say what version of Oracle or what settings, default or otherwise, are being used.
    From the OP:
    Checking the documentation, the default statistics gathering method for 10g and onwards is FOR ALL COLUMNS SIZE AUTO, so, it gets some workload to calculate the statistics, unlike the 9i version, which uses FOR ALL COLUMNS SIZE 1.
    Don't a lot of people think the 10g default is broke? Isn't the more fundamental problem trying to munge OLTP and DSS and DW together into one db?
  • 7. Re: Statistics - To collect histograms or not to collect
    rp0428 Guru
    Currently Being Moderated
    >
    Don't a lot of people think the 10g default is broke?
    >
    Seems like you assumed that the statement 'the default statistics gathering method for 10g and onwards' means that the OP is using 10g.

    I try not to make assumptions like that. That is why I said
    >
    OP did not say what version of Oracle or what settings, default or otherwise, are being used.
    >
    OP can easily obtain and post that information and didn't. All OP related was
    >
    was discussing with a friend about collecting statistics with histogram, if it worth spend time studying the table, its queries and where predicates or if the performance gains worth the spent time.
    >
    I interpreted that to mean that OP has no actual issue but was having an intellectual discussion.

    So if it ain't broke, don't fix it.

    The first step is to identify that an issue exists. In OP's case that would mean identifying a query or other issue that was causing a problem.

    Once a problem is identified potential action that might mitigate the problem can be determined. If one of those actions is related to statistics gathering, with or without historgrams then one would collect information about the Oracle version and default values being used that relate to statistics.

    None of that appears to have been done.

    So if it ain't broke, don't fix it. Meaning why worry about it if it isn't a problem.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points