> 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,
Yes, that's why I'm using them.
That's what...
> This app uses a lot of literals in the WHERE clause,
> and there's little I can do about it.
... meant. :)
Jared
"Jesse, Rich" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/13/2003 12:34 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: Fwd: Histograms on VARCHAR2 columns ?
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
[EMAIL PROTECTED] 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:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
