Re: Oracle Query optimizer

From: Kirill Sheynkman <kirills_at_delphi.com>
Date: Fri, 8 Jul 94 04:25:33 -0500
Message-ID: <ZUyRJ-t.kirills_at_delphi.com>

Hugh Fraser <hfraser_at_maccs.mcmaster.ca> writes:  

>Have I misinterpreted something? If not, how do other people handle the
>growing world of ad-hoc query activity?
 

Well, having worked at Oracle for 5 years and now having spent a year puttin big data warehouses on Oracle, I've come to know an love the optimizer. The way it is supposed to work:

	Things such as order of joins should not matter, especially
	when the cost-based optimizer is used.  Oracle specifically
	makes a point about this for the COST based case.  The old
	rule-based optimizer of V6 does react differently depending
	on thing such as join order, what columns are selected
	how, etc.
..sounds great so far, but...
	I've dome many serious benchmarks on Oracle starting with
	7.0.6 and ending with 7.0.16 and 7.1.3, and in no benchmarks
	has the optimizer workedd as expected and I always resorted to
	turning it off and using the rule-based optimizer.  The cost-
	based just never gave the right execution plan and always
	resorted to a full table scan of the major table -- a wrong
	answer because the rule-based version got the answer much
	faster.  In 7.1 the cost-based optimizer refused to use
	indexes, period.

This is obviously a problem in an ad-hoc environment. So what can one do?

	Play a lot with the typical SQL statement and figure out if
	the cost-based optimizer is working (--+ RULE and --+ COST hints).
	Chances are it doesn't.  (This has been confirmed by some of
 the sources at Oracle I still have).  If that fails, use the
	rule based and hope you have indexed well.
 
Just some thoughts.
				-Kirill Sheynkman
				Stanford Technology Group, Inc.

Received on Fri Jul 08 1994 - 11:25:33 CEST