Message-ID: <F001.0054CF66.20030213142937@fatcity.com>
Date: Thu, 13 Feb 2003 14:29:37 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Jesse, Rich" <Rich.Jesse@qtiworld.com>
Subject: RE: Fwd: Histograms on VARCHAR2 columns ?
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

I like white papers.  It looks like I'm going to learn something new again!

I'll set aside some time tomorrow to peruse it (I have to go drink beer
now).  Thanks, Allan!  :)

Rich


Rich Jesse                        System/Database Administrator
rich.jesse@qtiworld.com           Quad/Tech International, Sussex, WI USA


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


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@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@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@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).