Re: Creating Histograms
- From: "David" <thump@xxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 21 Jul 2004 11:11:46 -0700 (PDT)
I believe there is a method_opt of skewonly, which will build histograms
only where skewed data is found. That does'nt satisfy the check for
column usage, but does take care of determining if the data is skewed or
not. Note that I found there to be a pretty big overhead in terms of how
long this process takes. Perhaps if you indexed the columns that are used
and skewed and then specified to create buckets on indexed columns
only....you may want to have indexes for other purposes other than driving
this histogram analysis, but it's a DW, so maybe not...
--
..
David
> I'm trying to improve my knowledge of tuning and the performance of my 9i
> Ver 9.2.0.4 data warehouse by creating histograms on columns used in where
> clauses with skewed data values. I have extracted the columns included
> in where clauses, determined the distinct number of values and count using
> a "select distinct (value), count(*), group by statement" and think I have
> identified which are some good candidates for histograms. The
> documentation and articles about this issue I have read make it clear that
> histograms should only be used on columns being used, having skewed data.
> However, the dbms_stats.gather_schema_stats procedure with method_opt
> seems to create histograms whether the column is being used or the data is
> skewed. The 'gather_schema_stats' procedure is the only one demonstrated
> and seems to violate the rule. The 'gather_table_stats" procedure seems
> like it would give you more selectivity. I'm having a little trouble
> with this topic. When I checked, there are values for all tables in
> dba_histograms although I have never run schema_stats with the method_opt
> set to anything but default. =20
>
> Am I on the right track here? How do I use this feature without
> violating the 'rules?' I'm guessing if I run it and screw up my
> performance I can reset my histograms by rerunning dbms_stats with the
> default method_opt. Any advice useful or otherwise is welcome.
>
> Thanks,
>
> Don Freeman
> Database Administrator 1
> Pennsylvania Dept of Health
> Bureau of Information Technology
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
