Professional Web Applications Themes

dbms_stats default - Oracle Server

John Darrah wrote: > I was looking at the dbms_stats package and noticed that the > gather_table_stats procedure defaults to gathering statistics for all > columns on a table. It seems to me that the only columns I would want to > gather stats on would be those that are indexed. Does anybody know if the > optimizer can do anything with stats on non indexed columns? It can't effect > join order but does it affect how predicates in the where clause are > applied? Any help would be appreciated. > > Thanks > > John Do you only ...

  1. #1

    Default Re: dbms_stats default

    John Darrah wrote:
    > I was looking at the dbms_stats package and noticed that the
    > gather_table_stats procedure defaults to gathering statistics for all
    > columns on a table. It seems to me that the only columns I would want to
    > gather stats on would be those that are indexed. Does anybody know if the
    > optimizer can do anything with stats on non indexed columns? It can't effect
    > join order but does it affect how predicates in the where clause are
    > applied? Any help would be appreciated.
    >
    > Thanks
    >
    > John
    Do you only use indexed columns in your WHERE clauses?

    Daniel Morgan

    DA Morgan Guest

  2. #2

    Default Re: dbms_stats default


    John,

    I think I missed the point of your original question
    as I tend to read the expression 'column stats' to
    mean 'multi-bucket histograms'.

    I think the detail that you need to know is that
    whether you
    yze table X compute statistics
    or execute
    dbms_stats.gather_table_stats(..... table_name =>'X', ..)

    In both cases, you get a 'one-bucket histogram' on each
    column. If you check the trace files, both calls will update
    the hist_head$ table, but not the histgrm$ table.

    The other point -- the YZE command doesn't appear
    to run any SQL to do the necessary sorting and counting,
    whereas the actual count, count(distinct) max() min()
    SQL appears in the trace for dbms_stats - I don't think
    this is an issue: it is work that has to be done somehow
    to get the results - the fact that the (deprecated) yze
    command did it without admitting it is (probably) not a
    significant point.



    --
    Regards

    Jonathan Lewis
    [url]http://www.jlcomp.demon.co.uk[/url]

    Coming soon a new one-day tutorial:
    Cost Based Optimisation
    (see [url]http://www.jlcomp.demon.co.uk/tutorial.html[/url] )

    Next Seminar dates:
    (see [url]http://www.jlcomp.demon.co.uk/seminar.html[/url] )

    ____England______January 21/23


    The Co-operative Oracle Users' FAQ
    [url]http://www.jlcomp.demon.co.uk/faq/ind_faq.html[/url]





    John Darrah wrote in message ...
    >Sorry for the vague response. The 3X increase was on the yze
    operation
    >not queries against the table. I thought there was a difference
    between
    >gathering column stats and creating histograms. I thought that
    gathering
    >stats with a size of 1 meant that I would only be populating the
    columns in
    >the col$ table and not be creating histograms. It turns out that
    gathering
    >for columns does create a histogram on that column. It seems strange
    that
    >the default behavior of the dbms_stats package would be to create a
    one
    >bucket histogram for every column in the table but I guess the
    rational is
    >to err on the side of too much information rather than not enough. I
    wasn't
    >aware that the optimizer was sophisticated enough to order predicates
    >according by what will most reduce the row source, that's good to
    know.
    >Would your strategy be to gather table stats, without gathering
    column stats
    >and then selectively gather stats on columns with appropriately sized
    >histograms? The only bad thing I can see with this method is that 1)
    the
    >avg_row_len of the table may be inaccurate and 2) there will be
    columns in
    >the table with no stats which could pose a problem for future
    queries.
    >Finally, I just wanted to say how much I enjoyed your seminar. Its
    made me
    >want to go back and take a more in-depth look at a lot of the things
    I
    >thought I understood.
    >
    >Thanks for the reply,
    >
    >John


    Jonathan Lewis Guest

Similar Threads

  1. question about the default.mxml and Default.mxml
    By guru~k in forum Macromedia Flex General Discussion
    Replies: 2
    Last Post: June 15th, 03:00 AM
  2. Replies: 4
    Last Post: April 9th, 05:53 PM
  3. set a default for INC
    By Sam in forum PERL Miscellaneous
    Replies: 2
    Last Post: September 19th, 04:40 PM
  4. question about DBMS_STATS package/Statistics?
    By energie in forum Oracle Server
    Replies: 1
    Last Post: December 17th, 05:53 PM
  5. DBMS_STATS.GATHER_SYSTEM_STATS
    By Stephan Bressler in forum Oracle Server
    Replies: 0
    Last Post: December 13th, 09:47 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139