| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Fwd: Histograms on VARCHAR2 columns ?
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_at_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_at_qtiworld.com Quad/Tech International, Sussex, WIUSA
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 servicesReceived on Thu Feb 13 2003 - 16:29:37 CST
---------------------------------------------------------------------
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).
 |
 |