RE: Histogram Helper -- Follow up

THANKS for all your feedback. To sum up the thread and my conclusions...

>I had fair amount of success using Histograms for all indexed columns. I
>have been using it since 7.3.4.x version. However, histograms do not offer
>any help if the SQL uses bind variables. Most of our application code does
>use bind variables and hence does not benefit from this really neat
feature.
>- Kirti Deshpande

Thanks Kirti and good point however this is what I found in the Oracle Docs: "Histograms are NOT useful for columns with the following characteristics: ALL, yes ALL [emphasis mine] predicates on the column use bind variables..." The query in question only needed one bind variable but the same column appeared elsewhere in the WHERE clause so the histograms helped.

>The documentation has under-sold this feature. Even for columns that do not
>look very skewed at all, histograms helps to turn ugly explain-plans to
very
>fast ones. Somewhere inside the optimizer, histograms help it decide on
>sensible join-orders amongst other things. You didn't say, but I suspect
>your dramatic improvement is in the different join order.

>If you can, compute for table for ALL columns for all indexes (3 alls and
>leave out the indexed keyword). This will do all columns, but may not be
>feasible because it could blow out sys.histgrm$ depending on how many
>tables/columns/partitions you have. Do not specify the buckets -- let the
>analyze choose. It will vary the number depending on what it finds.
>Author: Binley Lim INET: Binley.Lim_at_ird.govt.nz

Here's what I did:
execute DBMS_STATS.CREATE_STAT_TABLE('RZUPROD','RZUSTATS','P2PTBL128K'); execute DBMS_STATS.GATHER_SCHEMA_STATS( -

        ownname    => 'RZUPROD', -
        method_opt => 'FOR ALL INDEXED COLUMNS SIZE 100', -
        cascade    => TRUE, -
        options    => 'GATHER', -
        stattab    => 'RZUSTATS', -
        statid     => '1' );

I figured I could judge the impact on the data dictionary by storing the statistical results in my own table first. When I reviewed the table I could see the stats but I had a heck of a time trying to figure out where the histogram data was. (It's a funky table.) Eventually it dawned on me... this nice procedure from Oracle does store the stats in your own table... WITH THE EXCEPTION OF THE HISTOGRAM DATA! Thanks a lot Oracle. The purpose of this procedure is to get the results from an analyze without affecting the dictionary. Nothing in the docs about histograms being the exception. Luckily the schema didn't have a ton of tables and indexes so the impact was acceptable. After tracing through catalog.sql and sql.bsq I concluded the data was stored in the SYS.HIST_HEAD$ and HISTGRM$ tables with HISTGRM$ in the C_OBJ#_INTCOL# cluster. Total storage came to just over 2MB so no prob. A word to the wise, if you're going to do histograms on all columns or all indexed columns, first figure out how many columns that is then guess how many rows will be generated and calculate the storage requirements based on the column definitions you'll find in sql.bsq. The best you can do is guestimate because you don't know how many buckets will be created for a given column. You might want to limit the buckets to 10 on the first pass. Or better yet, use Steve Adams' fantastic script! Here's Steve's comments:

>Good idea, but unfortunately the table statistics and the basic column
>statistics (called single-bucket histograms) that are available if you only
>analyze the table are not sufficient to determine whether the distribution
>of values for any column is uniform or skewed. That means that we cannot do
>this with a data dictionary query; we will have to scan the table.
>Based on your suggestion I have written "consider_histogram.sql" at
>http://www.ixora.com.au/scripts/query_opt.htm#consider_histogram...

THANKS! Great script. That's exactly what I had in mind and I was prepared to take on the task but I went home for the evening and in your email this morning I found that you had already done it all! And better than I could have done as I was entertaining PL/SQL. Great technique with inline views and CASE expressions! In your script comments you wrote: -- Author: Steve Adams -- based a suggestion from Steve Orr Well at least I got to do something :-) Thanks again Bro, er, Mate. :-)

One final (edited) gem from Cherie...
>I too experienced dramatic improvement when using a couple of histograms on
>very heavily skewed columns. The main thing with histograms is that you
need
>to keep them updated. I experimented with creating histograms on all
indexed
>columns. Didn't hurt anything but didn't help anything unless the data was
>skewed. I've posted several times in the past about histograms and recieved
>very few replies. That leads me to believe that not many people are using
or
>are interested in histograms. I don't see them heavily emphasized at
>conferences or in books.

Well said Cherie and I couldn't agree more. So when you present this topic at the next Oracle conference I'll be there. :-)

The SQL in question (not mine!) is executed several times a minute via DBMB_JOBS and several JAVA threads. Here are my before and after stats: Rows scanned per second went from 300,000 to <500; consistent reads per second went from 75,000 to <500; CPU usage went from 85-100% to 5%; buffer cache hit ratio went from 95% to 99%, and the physical I/O rate dropped significantly.

Histogram Happy,
Steve Orr

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve Orr
  INET: sorr_at_arzoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).