Xref: alice comp.databases.oracle.server:56416 Path: alice!news-feed.fnsi.net!netnews.com!news.maxwell.syr.edu!diablo.theplanet.net!news.theplanet.net!newspost.theplanet.net!not-for-mail From: Connor McDonald Newsgroups: comp.databases.oracle.server Subject: Re: Cost as used in 'EXPLAIN PLAN' Date: Wed, 07 Jul 1999 22:34:19 +0800 Organization: Customer of Planet Online Lines: 95 Message-ID: <3783656B.2BB1@yahoo.com> References: <929627997.10544@www.remarq.com> <376a0732.6835168@newshost.us.oracle.com> <7ltoc3$o60$1@nnrp1.deja.com> Reply-To: connor_mcdonald@yahoo.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: news5.svr.pol.co.uk 931383038 14870 62.136.196.161 (7 Jul 1999 21:30:38 GMT) NNTP-Posting-Date: 7 Jul 1999 21:30:38 GMT X-Complaints-To: abuse@theplanet.net X-Mailer: Mozilla 3.01 (Win95; I) To: raghuram_govind@my-deja.com raghuram_govind@my-deja.com wrote: > > Hi Guys: > > I have been a oracle developer for some time and > am just getting into optimization. The explain > plan for a SQL statement is giving lots of info > from which I am finding it difficult to understand > what each one of those statements mean (physical > reads, disk access, recursive calls, consistent > gets ...). Can some one suggest a web site / > document where I can find this info. > > In article <376a0732.6835168@newshost.us.oracle.com>, > tkyte@us.oracle.com wrote: > > A copy of this was sent to Greg Akins > > (if that email address didn't require changing) > > On Thu, 17 Jun 1999 05:59:55 -0800, you wrote: > > > > >Hi, > > > > > >I understand that cost is a relative measure of query > > >efficiency. Can somebody give me a rough idea of scale. > > > > > >For example, I do an explain plan and get some processes > > >returned as Cost=1 or Cost=1 2. Then some get returned > > >with Cost>5000. > > > > > >Is Cost>5000 an unreasonably large figure. Those costs are > > >related to a sort that appears to stem from a 'select > > >distinct' What can I do to minimize the cost and still > > >effect the 'select distinct' > > > > > >-greg > > > > > > > > > > > >**** Posted from RemarQ - http://www.remarq.com - Discussions Start > Here (tm) **** > > > > the costs within a query plan are useless to you. they mean nothing. > ignore > > them. > > > > the optimizer assigns costs to each phase of MANY different plans. It > then > > takes each plan and performs a function on the costs of each step. It > then > > picks the plan with the lowest cost. > > > > a query plan with the cost of 5,000,000 might very well run faster > then a plan > > with a cost of 1. You cannot compare the costs of 2 different plans > ever -- it > > doesn't make sense. only the optimizer can compare costs and then > only for a > > set of plans developed for the same query. > > > > the costs of certain steps are affected by many many things -- > statistics (or > > lack thereof), heuristics, HINTS (thats how hints work -- by > artifically > > reducing the cost of certain steps in the hopes that makes the > optimizer pick > > the plan with that step in it), and so on... > > > > See http://www.oracle.com/ideveloper/ for my column 'Digging-in to > Oracle8i'... > > Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/ > > > > Current article is "Fine Grained Access Control", added June 8'th > > > > Thomas Kyte tkyte@us.oracle.com > > Oracle Service Industries Reston, VA USA > > -- > > Opinions are mine and do not necessarily reflect those of Oracle > Corporation > > > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't. "Oracle Server Tuning" manual... You can actually see the doco from technet.oracle.com - you need to sign up (but it is free) HTH ;-) -- =========================================== Connor McDonald "These views mine, no-one elses etc etc" connor_mcdonald@yahoo.com "Some days you're the pigeon, and some days you're the statue."