Oracle-L: RE: Fwd: Histograms on VARCHAR2 columns ?

From: Nelson, Allan <anelson_at_midf.com>
Date: Thu, 13 Feb 2003 13:54:27 -0800
Message-ID: <F001.0054CDEA.20030213135427@fatcity.com>

Actually, even if you use bind varaibles histograms affect the density number used by the optimizer, see http://www.centrexcc.com/papers where he shows that without histograms the optimizer ususally makes density equal to the reciprocal of the Number of Distinct Values. With histograms in place,this is no longer true.

Allan

-----Original Message-----
Sent: Thursday, February 13, 2003 2:34 PM To: Multiple recipients of list ORACLE-L

Remember the caveat that histograms will only work if the SQL isn't using bind variables, the instance isn't set for CURSOR_SHARING=FORCE, and the DB version < 9i (the optimizer can peek at bind variable values in 9i).

Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI

USA -----Original Message-----
Sent: Thursday, February 13, 2003 11:55 AM To: Multiple recipients of list ORACLE-L

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.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).

This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216]

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Nelson, Allan
INET: anelson_at_midf.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------