RE: SQL Query tuning help
Thank you Stephane! Your final idea of FIRST_ROWS as a winner! Oracle slapped the data back in just a second. Thanks everyone for the ideas to try. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 10, 2002 3:42 PM To: Multiple recipients of list ORACLE-L DENNIS WILLIAMS wrote: 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] Dennis, I note that your select list is only made of columns from am. Your entry points are so.code and sa.ret, the second one being the most selective. I don't think that on such a volume a nested loop would be any better than a hash join between the two, so this part of the Oracle plan needs no change. However, a nested loop is probably what you need with am. I would try things such as SELECT am.lid, am.name FROM am WHERE am.lid in (SELECT so.lid from so, sa WHERE so.key_ = sa.so_key AND so.code = 11 AND sa.ret = 'SB') AND am.active = 1 ORDER BY am.name which may give the same plan as your first example; if this is the case, perhaps that SELECT am.lid, am.name FROM (SELECT so.lid from so, sa WHERE so.key_ = sa.so_key AND so.code = 11 AND sa.ret = 'SB') x, am WHERE am.lid = x.lid AND am.active = 1 ORDER BY am.name will give a better result. If it still doesn't, try the ORDERED hint after the first SELECT. If it still doesn't, add USE_NL(am) after ORDERED but I'd rather avoid it. Thinking while I'm typing, perhaps that all you need is a FIRST_ROWS hint. -- HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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:
RE: SQL Query tuning help
Dennis, If you use the ordered hint and have sa then so then am and also hint to use the index on sa(ret) then I think that would be about the best as you'd be starting with the best filter ie 1.3m/281 giving less than 5000 on average (assuming ret is indexed). I don't know if you'd have to through in an use_nl also. Iain Nicoll -Original Message- Sent: Tuesday, September 10, 2002 8: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: Nicoll, Iain \(Calanais\) 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).
Re: SQL Query tuning help
DENNIS WILLIAMS wrote: 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] Dennis, I note that your select list is only made of columns from am. Your entry points are so.code and sa.ret, the second one being the most selective. I don't think that on such a volume a nested loop would be any better than a hash join between the two, so this part of the Oracle plan needs no change. However, a nested loop is probably what you need with am. I would try things such as SELECT am.lid, am.name FROM am WHERE am.lid in (SELECT so.lid from so, sa WHERE so.key_ = sa.so_key AND so.code = 11 AND sa.ret = 'SB') AND am.active = 1 ORDER BY am.name which may give the same plan as your first example; if this is the case, perhaps that SELECT am.lid, am.name FROM (SELECT so.lid from so, sa WHERE so.key_ = sa.so_key AND so.code = 11 AND sa.ret = 'SB') x, am WHERE am.lid = x.lid AND am.active = 1 ORDER BY am.name will give a better result. If it still doesn't, try the ORDERED hint after the first SELECT. If it still doesn't, add USE_NL(am) after ORDERED but I'd rather avoid it. Thinking while I'm typing, perhaps that all you need is a FIRST_ROWS hint. -- HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
Re: SQL Query tuning help
Dennis, What is the distribution of sa.ret? I didn't see it included in an index. Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/10/2002 12:18 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Query tuning help 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: 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).
RE: SQL Query tuning help
Dennis, You're better off not having an index on the AM table. With 220,000 out of 250,000 rows having the same value, an index will do you more harm than good. You're not much better off on the SO table with only 12 different values out of 1.3 million. The final table SA has 281 different out of 1.3 million. I see why the optimizer chose a table scan. It has to look through most of the table anyway. I would try it with an index of each of your join fields plus a separate index on the ret field of the SA table. I wouldn't even try to index any other fields on the AM or SO tables. Actually, 16 second response time didn't sound too bad to me considering the tables you described. Bill Carle ATT Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 10, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Subject:SQL Query tuning help 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: Carle, William T (Bill), ALCAS 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).
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
RE: SQL Query tuning help
Just in case anyone out there is interested, we use the term Mickey Mouse schema to refer to a very specific design tactic. We're *not* using the term's slang meaning of unimportant or uninspired. (...Which always seemed odd to me, because MM is a really strong, high quality brand.) Specifically, when we use the term Mickey Mouse schema when discussing SQL optimization (and we probably shouldn't), we mean that the subschema being queried looks something like this: TABLE -- FILTER / TABLE \ TABLE -- FILTER If you draw circles around the entities and rotate this drawing 90 degrees to the left, you'll see that it kinda looks like the shape of Mickey Mouse's head. It's really difficult to optimize queries that look this way, because you have to do potentially very inefficient joins before you can apply all the filter conditions. Something else you might be able to consider (I know, your list is long enough as it is) is the new bitmap join index. I think this is supposed to put join information in a single index out in the ears where it might save a lot of workload. But watch out: anytime you see the word bitmap, it means you'd better not be doing much DML on the participating segments. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on Oracle® System Performance, Feb 9-12 Dallas - Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark -Original Message- WILLIAMS Sent: Tuesday, September 10, 2002 4:17 PM To: Multiple recipients of list ORACLE-L 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
RE: SQL Query tuning help
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
