Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g577A5H24617 for ; Fri, 7 Jun 2002 03:10:05 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id OAA82966; Thu, 6 Jun 2002 14:55:56 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 00476ECB; Thu, 06 Jun 2002 14:51:59 -0800 Message-ID: Date: Thu, 06 Jun 2002 14:51:59 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Gurelei Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Gurelei Subject: Re: explain plan question Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-MIME-Autoconverted: from 8bit to quoted-printable by newsfeed.cts.com id OAA82966 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by naude.co.za id g577A5H24617 Peter, Charile: Sore area size is 5 times more in prod DB, shared pool is larger in dev, db_block_buffers in dev is twice the size in prod. I guess this answers my question. thanks Gene --- Peter Gram wrote: > Gurelei > > Are the parameter's the same ? > > sort* > hash* > *pool* > db* > > Gurelei wrote: > > >Hi. > > > >I have executed an explain plan on a dev and prod > >databases. Both databases have the same data, use > the > >same version of ORacle (8.1.7.0) and the same OS > (AIX > >4.3.3). All the tables are analyzed. The plans > however > >are somewhat different (below). What could explan > the > >differences? For example, index usage (one plan > uses > >an index to get to all the data, another - access > the > >index and then the table). All the indices are the > >same on both databases. when I tried to force > ORacle > >to use the indices with hints, the cost grew from > 322 > >to 566. MY concernt is that I may not be able to > tune > >a query if I can't replicate the explain plan > exactly > >in dev as it is in production. > > > >Thanks for any input > > > >Gene > > > >0-0-3211.321 SELECT STATEMENT SQL1 Cost = 321 > >1-0-1 2.1 SORT GROUP BY > >2-1-1 3.1 NESTED LOOPS > >3-2-1 4.1 HASH JOIN OUTER > >4-3-1 5.1 HASH JOIN OUTER > >5-4-1 6.1 HASH JOIN > >6-5-1 7.1 TABLE ACCESS FULL TELESLS_EMPL > >7-5-2 7.2 INDEX RANGE SCAN TS_EMP_HST_ALT1 > UNIQUE > >8-4-2 6.2 INDEX FAST FULL SCAN PERF_STATS_ALT1 > >UNIQUE > >9-3-2 5.2 INDEX FAST FULL SCAN PRDCT_STAT_ALT1 > >UNIQUE > >10-2-2 4.2 INDEX UNIQUE SCAN ORG_UNT_HST_PK > UNIQUE > > > >vs > > > >0-0-3231.323 SELECT STATEMENT SQL1 Cost = 323 > >1-0-1 2.1 SORT GROUP BY > >2-1-1 3.1 NESTED LOOPS OUTER > >3-2-1 4.1 NESTED LOOPS OUTER > >4-3-1 5.1 NESTED LOOPS > >5-4-1 6.1 HASH JOIN > >6-5-1 7.1 TABLE ACCESS FULL TELESLS_EMPL > >7-5-2 7.2 INDEX RANGE SCAN TS_EMP_HST_ALT1 > UNIQUE > >8-4-2 6.2 INDEX UNIQUE SCAN ORG_UNT_HST_PK > UNIQUE > >9-3-2 5.2 TABLE ACCESS BY INDEX ROWID PERF_STATS > >10-9-1 6.1 INDEX RANGE SCAN PERF_STATS_FK2_X > >NON-UNIQUE > >11-2-2 4.2 TABLE ACCESS BY INDEX ROWID > PRDCT_STATS > >12-11-1 5.1 INDEX RANGE SCAN PRDCT_STAT_FK1_X > >NON-UNIQUE > > > > > >__________________________________________________ > >Do You Yahoo!? > >Yahoo! - Official partner of 2002 FIFA World Cup > >http://fifaworldcup.yahoo.com > > > > > > -- > > /regards > > Peter Gram > > Mobil : +45 2527 7107 > Fax : +45 4466 8856 > > Miracle A/S > Kratvej 2 > 2760 Måløv > http://miracleas.dk > > /* > The process of preparing programs for a digital > computer is especially > attractive, not only because it can be economically > and scientifically > rewarding, but also because it can be an aesthetic > experience much like > composing poetry or music > > Donald Knuth > */ > > > > ATTACHMENT part 2 application/x-pkcs7-signature name=smime.p7s __________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: gurelei@yahoo.com 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@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).