Re: Fwd: Histograms on VARCHAR2 columns ?

I setup histograms on a table a little over a year ago to help with a query that was taking much too long.

The query:

SELECT MIN(DOCNUM)
FROM
SAPEDIDC
WHERE RECSTAT = 'U' All values for recstat:

SELECT recstat, count(*) recstat_count
FROM
cimuser.SAPEDIDC
group by recstat
/

R RECSTAT_COUNT
- -------------

D         14247
Q             2

The SQL is from a canned app: I can't change it. This app uses a lot of literals in the WHERE clause, and there's little I can do about it.

Adding a histogram to the column caused the CBO to decide that an index should be used, and the query time went from 30 seconds to 0.01 seconds, as FTS was not longer used.

This same result could be achieved with setting 'alter session set optimizer_index_cost_adj = 40', which is now done in a logon trigger for this app's accounts.

I also create histograms on all indexed columns for this app. It has been running with acceptable performance now for quite some time.

Unfortunately, I am spending all this week helping to install the new and improved version of this app, with a *much* more complex schema. ( You would have to see it to believe it. )

I look forward to a new round of tuning. :)

HTH, Jared

Hemant K Chitale <hkchital_at_singnet.com.sg> Sent by: root_at_fatcity.com
 02/13/2003 07:24 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Fwd: Histograms on VARCHAR2 columns ?

Resending and hoping for some responses, even some real-world stories ... ?...

>Date: Sun, 09 Feb 2003 18:38:35 -0800
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>X-Sender: Hemant K Chitale <hkchital_at_singnet.com.sg>
>Subject: Histograms on VARCHAR2 columns ?
>
>
>
>
>I have just begun trying Column histograms and have
>seen a better execution plan for a query
>on LOTRUN.RECTYPE when
> a) LOTRUN had about 5million records
> b) RECTYPE='A' is only 1570 records
> c) RECTYPE='H' is the other 5mn-1570 records
>We wanted the RECTYPE Index to be used because all
>of our queries are on RECTYPE='A'
>The RBO would do an Index Range Scan which was quite good
>However, the CBO under CHOOSE would do an Index Fast Full
>Scan for both values.
>
>Once I collected column statistics with
>ANALYZE TABLE LOTRUN COMPUTE STATISTICS FOR COLUMNS RECTYPE SIZE 10 {I
>know, I don't need 10 buckets},
>a query for RECTYPE='A' does an Index Range Scan with 10 consistent gets
>while the query for RECTYPE='H' does an
>Index Fast Full Scan with 10060 consistent gets.
>
>Good !
>So far so good, I should say.
>
>I look at Note 72539.1 on MetaLink and I find this paragraph :
>
> STORING CHARACTER VALUES IN HISTOGRAMS
> --------------------------------------
>
> Character columns have some exceptional behaviour, in as much as we
store
> histogram data for the first 5 bytes of any string. Any predicates
that
> contain strings greater than 5 characters will not use histogram
> information
> and the selectivity will be 1 / DISTINCT.
>
>
>
>Does this mean that a column with, say, 10 or 12 character
>values but with the same character-string in the first
>5 positions would not get meaningful histogram statistics ?
>

Hemant K Chitale
My web site page is : http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).