RE: RE: RULE versus CHOOSE

Histogramms make sence only for columns with skewed data and only if literals used in where clause - not bind variables. To improve optimizer you need to change parameters OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ. Get an article The Search for Intelligent Life in the Cost-Based Optimizer from www.evdbt.com - it ex-plains use of these parameters. Also check your parameter DB_FILE_MULTIBLOCK_READ_COUNT - maybe you need to increase it depending on your platform and your block size and your max IO size (Solaris 5.5 and up it is configurable) and your extent sizes.

Alex Hillman

-----Original Message-----
Sent: Friday, July 06, 2001 10:55 AM
To: Multiple recipients of list ORACLE-L

Tom:

I never did any official benchmark studies, per se. I studied the CBO/histograms about 4 months ago when we were converting an app over from Oracle 7 to 8i. The new version of the app had queries that were very different from its earlier version and so performance on 8i as compared to 7 was dramatically worse. Of course, the app owners blamed the database. In the course of my defense of the db, I discovered that the data (being primarily composed of case studies) was severely skewed to the more recent dates (i.e., the further back you went, the sparser the number of cases). I discovered that the optimizer was doing a lot of range scans for queries when it should have been doing full table scans according to the CBO thresholds. Histograms on certain date fields throughout the schema dropped times on certain large report queries from 30 minutes to under 2.

That's all I know. I can't give you an hard empirical evidence, just anecdotal evidence that, when properly used, histograms do seem to have dramatic impacts.

--

Jon Walthour, OCDBA
Oracle DBA
Computer Horizons
Cincinnati, Ohio




>--- Original Message ---

>From: "Terrian, Tom" <tterrian_at_daas.dla.mil>

>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

>Date: 7/6/01 9:20:24 AM

>




>Jon,

>

>Great write up.

>

>Several times you mentioned creating Histograms for skewed data

distributions.


>I am just curious if you have ever studied the performance impact

with and


>without them?  We used to maintain histograms but when we studied

the


>performance impact (with and without them) we determined that

there was very


>little benefit with histograms.  The down side with them is

that they


>drastically increased the amount of time it took to analyze

the tables at night.


>We decided to do without them.  Have you ever studied their

benefits verse


>drawbacks?

>

>Tom

>

>Tom Terrian

>Oracle DBA

>WPAFB - DAASC

>tterrian_at_daas.dla.mil

>937-656-3844 

>




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jon Walthour
  INET: jonw_at_fuse.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Hillman, Alex
  INET: Alex.Hillman_at_usmint.treas.gov

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).