Dennis, I noticed HASH JOINS in your query. Did you look at playing around with the value of HASH_AREA_SIZE and/or SORT_AREA_SIZE as well as adjust HASH_MULTIBLOCK_IO_COUNT?
John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointments are inevitable in Life, but discouragement is optional. You decide! ** The opinions and statements above are entirely my own and not those of my employer or clients ** > -----Original Message----- > From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, September 10, 2002 2:17 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: SQL Query tuning help > > > Thanks everyone for your wonderful suggestions. And thanks > for leaving the > "hey stupid" off your reply header :-) > > Rachel - Thanks for the bitmapped idea. These tables don't > change often, so > that may be a good alternative. > > Iain - Thanks so much for the detailed suggestions. > > Rick - Good sanity check, yes, I analyzed the tables. > > Jared - RET has 281 values, pretty evenly distributed > > Cary - Query returns 185 rows. > > Bill - Thanks for the suggestions and insights. > > Stephane - Good notice that only am values are used. Guess that is why > Oracle accessed the data blocks anyway with my new indexes. > Duh. Good ideas. > > Jeff - Thanks for the "Mickey Mouse" tag. I may need that in > the future. > Previously this data was on an old mainframe and the business > itself was > restricted by the inflexibility. My gut reaction was that they > overcompensated. > > Thanks everyone for the wonderful ideas. I was just given a > hot project, so > it may be a day or two before I get a chance to explore all > of them, but > I'll let you know. > > > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > > > -----Original Message----- > Sent: Tuesday, September 10, 2002 2:19 PM > To: Multiple recipients of list ORACLE-L > > > I am trying to tune a SQL query on Oracle 8.1.6. I have tried several > optimizations, but > so far have made no improvements. I would appreciate any suggestions. > > SELECT am.lid, am.name > FROM am, so, sa > WHERE so.lid = am.lid > AND so.key_ = sa.so_key > AND am.active = 1 > AND so.code = 11 > AND sa.ret = 'SB' > ORDER BY am.name > > Tables: > am - 250,000 rows, 220,000 rows have active = 1, the others are 0. > so - 1.3 million rows, lid has 250,000 distinct values, > key_ is unique, > code has 12 values, evenly distributed. > sa - 1.3 million rows, ret has 281 values, fairly evenly > distributed. > so_key is pretty unique. > > Now, you'll probably say there is essentially a 1-1 > relationship between so > and sa. You are right, but the developer insists this flexibility is > essential. > > The query executes in 16 seconds and returns 185 rows. This > is felt to be > too slow for an online lookup screen. > > explain plan results: > > SELECT STATEMENT Cost = 2955 > SORT ORDER BY > HASH JOIN > HASH JOIN > TABLE ACCESS FULL SA > TABLE ACCESS FULL SO > TABLE ACCESS FULL AM > > Here is what I've tried so far: > > Using hints to force Oracle to use indexes. > > Query Plan > -------------------------------------------------------------- > -------------- > ---- > SELECT STATEMENT Cost = 62031 > SORT AGGREGATE > NESTED LOOPS > HASH JOIN > TABLE ACCESS BY INDEX ROWID SA > INDEX FULL SCAN SO_KEY3 > TABLE ACCESS BY INDEX ROWID SO > INDEX RANGE SCAN PRG_CODE3 > TABLE ACCESS BY INDEX ROWID AM > INDEX UNIQUE SCAN LID6 > > Timing result 25 minutes > > Next I tried creating new indexes that combine both the > accessing column as > well as the retrieved column, thinking that Oracle could get > the result from > the index block and not need to retrieve the data block. > create index test1 on am (lid, active); > create index test2 on sa (so_key, code); > > SELECT STATEMENT Cost = 2951 > SORT AGGREGATE > HASH JOIN > HASH JOIN > INDEX FULL SCAN TEST2 > TABLE ACCESS FULL SO > TABLE ACCESS BY INDEX ROWID AM > INDEX RANGE SCAN TEST1 > > Hinting so Oracle will use the new indexes, for one table > Oracle uses the > index only and for the other table, Oracle hits both the > index and table > itself. Response time is slightly longer than the original > query. At this > point I'm fresh out of ideas, so any ideas would be > appreciated. Thanks. > > > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: John Kanagaraj INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
