Home -> Usenet -> comp.databases.oracle -> RE: Cost Based Optimizier

RE: Cost Based Optimizier

From: Richard Hu <richu_at_msn.com>
Date: 1996/07/28
Message-ID: <00001a1b+00007b01_at_msn.com>#1/1 







	We have a 7.1.3.2.1 production system and have just ANALYZED all the
	tables and indexes.
	Since we have done this we are suffering with response times from queries
	that join two and more tables.
	When EXPAINed/TKPROFed we have discovered that some selects are
	performing full tables scans.
	It appears that the execution plans worked out by the optimizer are not
	the best. 
	(I have checked that all the tables and indexes are analyzed - 
	the statistics were ESTIMATEd on all but one table which was be
	COMPUTEd.)
	To try and solve this we have put in HINTS but to one avail.

	Is there anyone out there who has knowledge of the COST based
	optimizer or who could offer some advise ?

	Has anyone had similar problems ? and may be gone back to using the
	RULE based optimizer ?





If your hints do not work, how do you know COST BASED isnt right? What do you
have to comapre results with?  Cost based optimizer disables indexes when 
it senses the query will return > 5% of the total# rows of a 
table...it does this


correctly since index select benefits actually erode beyond the 5% 
retrieve mark over


full table scan due to more drive head movements..



Midas well learn the intricasies of cost-based now...in the future, 
rules-based will


go away all-together.



Ric


Spectrum Group
Received on Sun Jul 28 1996 - 00:00:00 CEST












Original text of this message