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,

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" <Rich.Jesse_at_qtiworld.com>
Sent by: root_at_fatcity.com
 02/13/2003 12:34 PM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        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
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: 
  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).