RE: Slow query
two functions versus one maybe -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: woensdag 5 maart 2003 7:34To: Multiple recipients of list ORACLE-LSubject: Slow queryDear all, Please find below the 2 queries : select dep_date from test_date1 where to_char(dep_date,'dd-mon-') = '12-jan-2002' select dep_date from test_date1 where TRUNC(dep_date) = to_DATE( '12-jan-2002','dd-mon-') The execution plan for both the queries shows a FTS on test_date1. Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'TEST_DATE1' The number of rows to be retrieved are 120010. The first query took 10msec to execute whereas the second took about 2 secs. Could anyone help me figure out what the problem would be. TIA Best RegardsJai
Re: Slow query
Which version of Oracle ? The second query has to do a trunc() and a to_date() on every single row. The first only has to do a to_char on every single row. There is a CPU difference between the two queries and if you run the execution plan through v9 with cpu_costing, you will find a difference in cost - though probably not enough to agree with your clock test. A simple test on 8.1.7.4 and 9.2.0.2 where I created a table of 128,000 rows with a date column scattered inside a single date showed that one query used about twice the CPU of the other in v9, and four times in v8. (Unfortunately, in all cases, the CPU reported by Oracle was consistently larger than the elapsed time reported by SQL*Plus). First check, though, is the test repeatable, or did something happen elsewhere to affect the elapsed time. (e.g. was one tested subject to a lot of delayed block cleanout). NB - The execution plan shown is not the plan for either of the two statements, and I am fairly sure that neither statement could actually return 120,000 rows in 10 ms. Do your timings refer to a test where you changed the query to do a count() ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 05 March 2003 06:33 Dear all, Please find below the 2 queries : select dep_date from test_date1 where to_char(dep_date,'dd-mon-') = '12-jan-2002' select dep_date from test_date1 where TRUNC(dep_date) = to_DATE( '12-jan-2002','dd-mon-') The execution plan for both the queries shows a FTS on test_date1. Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 10 SORT (AGGREGATE) 21 TABLE ACCESS (FULL) OF 'TEST_DATE1' The number of rows to be retrieved are 120010. The first query took 10msec to execute whereas the second took about 2 secs. Could anyone help me figure out what the problem would be. TIA Best Regards Jai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Slow query
Dear all, Please find below the 2 queries : select dep_date from test_date1 where to_char(dep_date,'dd-mon-') = '12-jan-2002' select dep_date from test_date1 where TRUNC(dep_date) = to_DATE( '12-jan-2002','dd-mon-') The execution plan for both the queries shows a FTS on test_date1. Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE 10 SORT (AGGREGATE) 21 TABLE ACCESS (FULL) OF 'TEST_DATE1' The number of rows to be retrieved are 120010. The first query took 10msec to execute whereas the second took about 2 secs. Could anyone help me figure out what the problem would be. TIA Best Regards Jai --- --- Which problem? I guess that, if the execution plans are identical, what you are experiencing is just the effect of caching. For a reason or another the data already was in memory in one case, and had to be loaded from disk in the other one. Check your trace statistics under SQL*Plus, logical IOs will probably be more or less identical and physical IOs wildly different. Note that as long as you apply a function to dep_date, your only way to escape the FTS is (supposing 8.1.7 or above) function-based indexes. If dep_date is indexed, then you should use something like dep_date = some_date and dep_date some_date + 1 instead of trunc(dep_date) = some_date It will allow for range scans. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Slow query
A small follow up question: Why will oracle do a to_date() on second query for each row returned, as the date mentioned is a literal and doesn't change for the duration of the query. Won't it be calculated once and used for all the rows with trunc() applied to each row? Regards Naveen -Original Message- Sent: Wednesday, March 05, 2003 3:04 PM To: Multiple recipients of list ORACLE-L Which version of Oracle ? The second query has to do a trunc() and a to_date() on every single row. The first only has to do a to_char on every single row. There is a CPU difference between the two queries and if you run the execution plan through v9 with cpu_costing, you will find a difference in cost - though probably not enough to agree with your clock test. A simple test on 8.1.7.4 and 9.2.0.2 where I created a table of 128,000 rows with a date column scattered inside a single date showed that one query used about twice the CPU of the other in v9, and four times in v8. (Unfortunately, in all cases, the CPU reported by Oracle was consistently larger than the elapsed time reported by SQL*Plus). First check, though, is the test repeatable, or did something happen elsewhere to affect the elapsed time. (e.g. was one tested subject to a lot of delayed block cleanout). NB - The execution plan shown is not the plan for either of the two statements, and I am fairly sure that neither statement could actually return 120,000 rows in 10 ms. Do your timings refer to a test where you changed the query to do a count() ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 05 March 2003 06:33 Dear all, Please find below the 2 queries : select dep_date from test_date1 where to_char(dep_date,'dd-mon-') = '12-jan-2002' select dep_date from test_date1 where TRUNC(dep_date) = to_DATE( '12-jan-2002','dd-mon-') The execution plan for both the queries shows a FTS on test_date1. Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 10 SORT (AGGREGATE) 21 TABLE ACCESS (FULL) OF 'TEST_DATE1' The number of rows to be retrieved are 120010. The first query took 10msec to execute whereas the second took about 2 secs. Could anyone help me figure out what the problem would be. TIA Best Regards Jai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Slow query
You would have thought to, but it doesn't seem to be the case. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 05 March 2003 11:34 A small follow up question: Why will oracle do a to_date() on second query for each row returned, as the date mentioned is a literal and doesn't change for the duration of the query. Won't it be calculated once and used for all the rows with trunc() applied to each row? Regards Naveen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: slow query help
Joan, Can't this be done as a series of ors instead of the union alls as this would presumably reduce it to one full table scan of each table. e.g. SELECT T11.TRUNK TRUNK_FOUND ,T21.IDTARGET_ID ,T21.SSN TARGET_SSN ,T21.FULLNAME TARGET_FULLNAME ,T21.LASTNAME TARGET_LASTNAME ,T21.MIDDLENAMETARGET_MIDDLENAME ,T21.FIRSTNAME TARGET_FIRSTNAME ,T21.DOB TARGET_DOB ,T21.GENDERTARGET_GENDER FROM SM_NEW_LOAD T21 ,PR_IDENTITY T11 WHERE (T21.SSN IN ( T11.SSN,T11.HRID,SISID,MEDID,AFFID ) OR T21.ID IN (T11.SSN,T11.HRID,SISID,MEDID,AFFID ) OR EXISTS (SELECT 1 FROM PR_ALT_IDS WHERE TRUNK = T11.TRUNK AND ALT_ID IN (T21.SSN,T21.ID))) OR (REPLACE(UPPER(T11.LASTNAME),'-',' ') = REPLACE(UPPER(T21.LASTNAME),'-',' ') AND UPPER(T11.FIRSTNAME) = UPPER(T21.FIRSTNAME) ) OR (REPLACE(UPPER(T11.LASTNAME),'-',' ') = REPLACE(UPPER(T21.LASTNAME),'-',' ') AND ((INSTR(UPPER(T11.FIRSTNAME),UPPER(T21.MIDDLENAME),1) = 1 AND INSTR(UPPER(T11.MIDDLENAME),UPPER(T21.FIRSTNAME),1) = 1 ) OR (INSTR(UPPER(T21.FIRSTNAME),UPPER(T11.MIDDLENAME),1) = 1 AND INSTR(UPPER(T21.MIDDLENAME),UPPER(T11.FIRSTNAME),1) = 1 ))) OR (UPPER(T11.LASTNAME) = UPPER(T21.FIRSTNAME) AND UPPER(T11.FIRSTNAME) = UPPER(T21.LASTNAME)) OR ((INSTR(' '||REPLACE(UPPER(T11.LASTNAME),'-',' ')||' ',' '||UPPER(T21.LASTNAME)|| ' ' ) 0 OR INSTR(' '||REPLACE(UPPER(T21.LASTNAME),'-',' ')||' ',' '||UPPER(T11.LASTNAME)||' ') 0 ) AND UPPER(T11.FIRSTNAME) = UPPER(T21.FIRSTNAME) AND (T11.MIDDLENAME IS NULL OR T21.MIDDLENAME IS NULL OR UPPER(SUBSTR(T11.MIDDLENAME,1,1)) = UPPER(SUBSTR(T21.MIDDLENAME,1,1)) )) OR (UPPER(T21.LASTNAME) = UPPER(T11.LASTNAME) AND (INSTR(UPPER(T21.FIRSTNAME),UPPER(T11.FIRSTNAME),1) 0 OR INSTR(UPPER(T11.FIRSTNAME),UPPER(T21.FIRSTNAME),1) 0 ) AND (INSTR(UPPER(T21.MIDDLENAME),UPPER(T11.MIDDLENAME),1) 0 OR INSTR(UPPER(T11.MIDDLENAME),UPPER(T21.MIDDLENAME),1) 0 )) -Original Message- Sent: 17 December 2002 19:35 To: Multiple recipients of list ORACLE-L Hi, This is the query bothered us very much recently. It run at least 15 min. and sometimes crashed the temp tablespace. Do you have any idea how to make it run better. Our developer tried used two cursors to compare the result, but the result is not optimized. We tried used last name and first name function based index on sm_new_load table. If someone interested in this query, I can sent you the execution plan. Thanks in advanced, Joan SELECT T11.TRUNK TRUNK_FOUND,T21.ID TARGET_ID,T21.SSN TARGET_SSN,T21.FULLNAME TARGET_FULLNAME,T21.LASTNAME TARGET_LASTNAME,T21.MIDDLENAME TARGET_MIDDLENAME,T21.FIRSTNAME TARGET_FIRSTNAME,T21.DOB TARGET_DOB, T21.GENDER TARGET_GENDER FROM SM_NEW_LOAD T21,PR_IDENTITY T11 WHERE ( T21.ROWID,T11.ROWID ) IN (SELECT T22.ROWID,T12.ROWID FROM PR_IDENTITY T12,SM_NEW_LOAD T22 WHERE T22.SSN IN ( T12.SSN,T12.HRID,SISID,MEDID,AFFID ) OR T22.ID IN ( T12.SSN,T12.HRID, SISID,MEDID,AFFID ) OR EXISTS (SELECT 1 FROM PR_ALT_IDS WHERE TRUNK = T12.TRUNK AND ALT_ID IN ( T22.SSN,T22.ID )) UNION ALL SELECT T22.ROWID, T12.ROWID FROM SM_NEW_LOAD T22,PR_IDENTITY T12 WHERE REPLACE(UPPER(T12.LASTNAME),'-',' ') = REPLACE(UPPER(T22.LASTNAME),'-',' ') AND UPPER(T12.FIRSTNAME) = UPPER(T22.FIRSTNAME) UNION ALL SELECT T23.ROWID,T13.ROWID FROM SM_NEW_LOAD T23,PR_IDENTITY T13 WHERE REPLACE(UPPER(T13.LASTNAME),'-',' ') = REPLACE(UPPER(T23.LASTNAME),'-',' ') AND ((INSTR(UPPER(T13.FIRSTNAME),UPPER(T23.MIDDLENAME),1) = 1 AND INSTR(UPPER(T13.MIDDLENAME),UPPER(T23.FIRSTNAME),1) = 1 ) OR (INSTR(UPPER(T23.FIRSTNAME),UPPER(T13.MIDDLENAME),1) = 1 AND INSTR(UPPER(T23.MIDDLENAME),UPPER(T13.FIRSTNAME),1) = 1 )) UNION ALL SELECT T24.ROWID,T14.ROWID FROM SM_NEW_LOAD T24,PR_IDENTITY T14 WHERE UPPER(T14.LASTNAME) = UPPER(T24.FIRSTNAME) AND UPPER(T14.FIRSTNAME) = UPPER(T24.LASTNAME) UNION ALL SELECT T25.ROWID,T15.ROWID FROM SM_NEW_LOAD T25,PR_IDENTITY T15 WHERE (INSTR(' ' || REPLACE(UPPER(T15.LASTNAME),'-',' ') || ' ' ,' ' || UPPER(T25.LASTNAME) || ' ' ) 0 OR INSTR(' ' || REPLACE(UPPER(T25.LASTNAME),'-',' ') || ' ' , ' ' || UPPER(T15.LASTNAME) || ' ' ) 0 ) AND UPPER(T15.FIRSTNAME) = UPPER(T25.FIRSTNAME) AND (T15.MIDDLENAME IS NULL OR T25.MIDDLENAME IS NULL OR UPPER(SUBSTR(T15.MIDDLENAME,1,1)) = UPPER(SUBSTR(T25.MIDDLENAME,1, 1)) ) UNION ALL SELECT T27.ROWID,T17.ROWID FROM PR_IDENTITY T17, SM_NEW_LOAD T27 WHERE UPPER(T27.LASTNAME) = UPPER(T17.LASTNAME) AND (INSTR(UPPER(T27.FIRSTNAME),UPPER(T17.FIRSTNAME),1) 0 OR INSTR(UPPER(T17.FIRSTNAME),UPPER(T27.FIRSTNAME),1) 0 ) AND
RE: slow query help
Joan - Just a random thought inspired by your explanation. Is this query a part of cleaning data for a data warehouse? Or worse yet, is the developer attempting to use this query in an attempt to avoid creating a data warehouse? Normally the best solution for the situation you describe is to create a data warehouse. If those words scare you, just say that it is a couple of tables to provide cleaned data. Then you can pull data from one system at a time, clean it, and store it. Based on your description, be aware that even if the query is made to work, as my grandpappy would say with bailing wire and chewing gum, it may be very fragile and need constant attention. Developers have this nasty habit of moving along to the next development project and you, the DBA, are stuck with a problem. The developer gets all the glory for making it work. You get all the blame because it keeps breaking. Not pretty. Anyway, this is what I got out of your explanation, and if I'm off base, just ignore this message. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 17, 2002 8:49 PM To: Multiple recipients of list ORACLE-L Mark, This is a new in-house developed the school directory system. The source data come from all over the different systems. The data quite dirty in some case. I am not sure how the business rule was defined yet. The developer knew her query was nasty, so she turned on me for help. This query is part of the whole process. As you said, it compares all the possible same person feed from all the systems (med, sis,law...)and insert it to another table. I think you and Stephane's suggestions are very valuable and I will definitely ask her to review her design of this processing. (sigh, we are not that experienced and good on this) After I work more with developer and get more knowledge of this application. I'd like to ask some detail of your experience in such searching logic. many thanks, Joan Mark Richard wrote: I tend to agree with Stephane... It looks like that query was possibly generated by a tool. If not you should go talk to the person who wrote it. Find out what the business rules are and write it from scratch. In reality the query keeps hitting the same two tables, presumably looking for rows that have just been loaded which match existing rows in another table based on the names matching in some fashion (ie: perhaps incorrect firstname/middlename usage, etc). Some of Stephane's suggestions will also help. We store customer names as case sensitive but we obviously need to do a case-insensitive search from our application to find customers. The easiest way out - store the name twice (we have firstname and firstnameasupper, lastname and lastnameasupper, etc). Obviously the indexes sit on the asupper columns. Sure there is a slight amount of duplicated data, but it is done for a reason and let's us search customers very fast whist still preserving the nuance's that customers like to see when you send a bill to them. Perhaps you need to store some columns which have had replace and upper applied. Finally since the query seems to accept pretty much any combination of lastname, firstname, middlename you could concatenate the three columns into a namesearch column are just perform three instr searches on that column, as opposed to trying every possibility - that should save Oracle some heartache. Regards, Mark. Stephane Faroult To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] sfaroult@orio cc: le.com Subject: Re: slow query help Sent by: [EMAIL PROTECTED] om 18/12/2002 07:58 Please respond to ORACLE-L Joan Hsieh wrote: Hi, This is the query bothered us very much recently. It run at least 15 min. and sometimes crashed the temp tablespace. Do you have any idea how to make it run better. Our developer tried used two cursors to compare the result, but the result is not optimized. We tried used last name and first name function based index on sm_new_load table. If someone interested in this query, I can sent you the execution plan. Thanks in advanced, Joan SELECT T11.TRUNK TRUNK_FOUND,T21.ID TARGET_ID,T21.SSN TARGET_SSN,T21.FULLNAME TARGET_FULLNAME,T21.LASTNAME TARGET_LASTNAME,T21.MIDDLENAME TARGET_MIDDLENAME,T21.FIRSTNAME TARGET_FIRSTNAME,T21.DOB TARGET_DOB, T21.GENDER TARGET_GENDER FROM SM_NEW_LOAD T21,PR_IDENTITY T11 WHERE ( T21.ROWID,T11.ROWID ) IN (SELECT T22.ROWID,T12.ROWID FROM PR_IDENTITY T12,SM_NEW_LOAD T22 WHERE T22.SSN IN ( T12.SSN,T12.HRID,SISID,MEDID,AFFID ) OR T22.ID IN ( T12.SSN,T12.HRID, SISID,MEDID,AFFID ) OR EXISTS
RE: slow query help
Title: RE: slow query help Joan, Here is a suggestion ... if this is going to be your most used part, I'd look into Intermedia ... you'll have a lot more options to work with and they will work good. Until then, I'd recommend replacing instr() with appropriate LIKE clause because at-least it will help you use an index. Through a DB trigger ensure that all names (first/middle/last) are always in UPPER so you don't have to pur UPPER or deal with FBI. Initially I thought of merging all clauses into one, but then I realized, Oracle will end up doing what I un-did (it will replace all OR conditions to UNION ALL anyways). Happy Holidays Everyone !! Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: slow query help
Raj, I thought it would only do the union all if it was able to use an index and all the instr's look as though they'd stop that. Even then I thought it was generally just rule that would do that unless you used the use_concat hint. I can't see why a full table scan of each wouldn't be fairly efficient unless the result set is only a small fraction of the total possible rows. I may have missed some e-mails which make the above statements irrevelant, if so my apologies. Iain -Original Message- Sent: 18 December 2002 13:55 To: Multiple recipients of list ORACLE-L Joan, Here is a suggestion ... if this is going to be your most used part, I'd look into Intermedia ... you'll have a lot more options to work with and they will work good. Until then, I'd recommend replacing instr() with appropriate LIKE clause because at-least it will help you use an index. Through a DB trigger ensure that all names (first/middle/last) are always in UPPER so you don't have to pur UPPER or deal with FBI. Initially I thought of merging all clauses into one, but then I realized, Oracle will end up doing what I un-did (it will replace all OR conditions to UNION ALL anyways). Happy Holidays Everyone !! Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: slow query help
Joan Hsieh wrote: Hi, This is the query bothered us very much recently. It run at least 15 min. and sometimes crashed the temp tablespace. Do you have any idea how to make it run better. Our developer tried used two cursors to compare the result, but the result is not optimized. We tried used last name and first name function based index on sm_new_load table. If someone interested in this query, I can sent you the execution plan. Thanks in advanced, Joan SELECT T11.TRUNK TRUNK_FOUND,T21.ID TARGET_ID,T21.SSN TARGET_SSN,T21.FULLNAME TARGET_FULLNAME,T21.LASTNAME TARGET_LASTNAME,T21.MIDDLENAME TARGET_MIDDLENAME,T21.FIRSTNAME TARGET_FIRSTNAME,T21.DOB TARGET_DOB, T21.GENDER TARGET_GENDER FROM SM_NEW_LOAD T21,PR_IDENTITY T11 WHERE ( T21.ROWID,T11.ROWID ) IN (SELECT T22.ROWID,T12.ROWID FROM PR_IDENTITY T12,SM_NEW_LOAD T22 WHERE T22.SSN IN ( T12.SSN,T12.HRID,SISID,MEDID,AFFID ) OR T22.ID IN ( T12.SSN,T12.HRID, SISID,MEDID,AFFID ) OR EXISTS (SELECT 1 FROM PR_ALT_IDS WHERE TRUNK = T12.TRUNK AND ALT_ID IN ( T22.SSN,T22.ID )) UNION ALL SELECT T22.ROWID, T12.ROWID FROM SM_NEW_LOAD T22,PR_IDENTITY T12 WHERE REPLACE(UPPER(T12.LASTNAME),'-',' ') = REPLACE(UPPER(T22.LASTNAME),'-',' ') AND UPPER(T12.FIRSTNAME) = UPPER(T22.FIRSTNAME) UNION ALL SELECT T23.ROWID,T13.ROWID FROM SM_NEW_LOAD T23,PR_IDENTITY T13 WHERE REPLACE(UPPER(T13.LASTNAME),'-',' ') = REPLACE(UPPER(T23.LASTNAME),'-',' ') AND ((INSTR(UPPER(T13.FIRSTNAME),UPPER(T23.MIDDLENAME),1) = 1 AND INSTR(UPPER(T13.MIDDLENAME),UPPER(T23.FIRSTNAME),1) = 1 ) OR (INSTR(UPPER(T23.FIRSTNAME),UPPER(T13.MIDDLENAME),1) = 1 AND INSTR(UPPER(T23.MIDDLENAME),UPPER(T13.FIRSTNAME),1) = 1 )) UNION ALL SELECT T24.ROWID,T14.ROWID FROM SM_NEW_LOAD T24,PR_IDENTITY T14 WHERE UPPER(T14.LASTNAME) = UPPER(T24.FIRSTNAME) AND UPPER(T14.FIRSTNAME) = UPPER(T24.LASTNAME) UNION ALL SELECT T25.ROWID,T15.ROWID FROM SM_NEW_LOAD T25,PR_IDENTITY T15 WHERE (INSTR(' ' || REPLACE(UPPER(T15.LASTNAME),'-',' ') || ' ' ,' ' || UPPER(T25.LASTNAME) || ' ' ) 0 OR INSTR(' ' || REPLACE(UPPER(T25.LASTNAME),'-',' ') || ' ' , ' ' || UPPER(T15.LASTNAME) || ' ' ) 0 ) AND UPPER(T15.FIRSTNAME) = UPPER(T25.FIRSTNAME) AND (T15.MIDDLENAME IS NULL OR T25.MIDDLENAME IS NULL OR UPPER(SUBSTR(T15.MIDDLENAME,1,1)) = UPPER(SUBSTR(T25.MIDDLENAME,1, 1)) ) UNION ALL SELECT T27.ROWID,T17.ROWID FROM PR_IDENTITY T17, SM_NEW_LOAD T27 WHERE UPPER(T27.LASTNAME) = UPPER(T17.LASTNAME) AND (INSTR(UPPER(T27.FIRSTNAME),UPPER(T17.FIRSTNAME),1) 0 OR INSTR(UPPER(T17.FIRSTNAME),UPPER(T27.FIRSTNAME),1) 0 ) AND (INSTR(UPPER(T27.MIDDLENAME),UPPER(T17.MIDDLENAME),1) 0 OR INSTR(UPPER(T17.MIDDLENAME),UPPER(T27.MIDDLENAME),1) 0 )) -- Joan, I think that you have a design problem here. First it is obvious that if all your data was entered in uppercase, it would help. If you are on 8.1.7 or above, function-based index should help. Otherwise the code is obviously awkward. What use is (a.rowid, b.rowid) in (select c.rowid, d.rowid ...) ? Quite obviously you are scanning the same data twice. All your 'replace', 'instr', 'upper' etc. are performance killers. The easiest thing to do is possibly to denormalise PR_IDENTITY, add a column (trigger-entered) which contains data you can search without applying functions of death to it - and index it. -- 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 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: slow query help
I tend to agree with Stephane... It looks like that query was possibly generated by a tool. If not you should go talk to the person who wrote it. Find out what the business rules are and write it from scratch. In reality the query keeps hitting the same two tables, presumably looking for rows that have just been loaded which match existing rows in another table based on the names matching in some fashion (ie: perhaps incorrect firstname/middlename usage, etc). Some of Stephane's suggestions will also help. We store customer names as case sensitive but we obviously need to do a case-insensitive search from our application to find customers. The easiest way out - store the name twice (we have firstname and firstnameasupper, lastname and lastnameasupper, etc). Obviously the indexes sit on the asupper columns. Sure there is a slight amount of duplicated data, but it is done for a reason and let's us search customers very fast whist still preserving the nuance's that customers like to see when you send a bill to them. Perhaps you need to store some columns which have had replace and upper applied. Finally since the query seems to accept pretty much any combination of lastname, firstname, middlename you could concatenate the three columns into a namesearch column are just perform three instr searches on that column, as opposed to trying every possibility - that should save Oracle some heartache. Regards, Mark. Stephane Faroult To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] sfaroult@orio cc: le.com Subject: Re: slow query help Sent by: [EMAIL PROTECTED] om 18/12/2002 07:58 Please respond to ORACLE-L Joan Hsieh wrote: Hi, This is the query bothered us very much recently. It run at least 15 min. and sometimes crashed the temp tablespace. Do you have any idea how to make it run better. Our developer tried used two cursors to compare the result, but the result is not optimized. We tried used last name and first name function based index on sm_new_load table. If someone interested in this query, I can sent you the execution plan. Thanks in advanced, Joan SELECT T11.TRUNK TRUNK_FOUND,T21.ID TARGET_ID,T21.SSN TARGET_SSN,T21.FULLNAME TARGET_FULLNAME,T21.LASTNAME TARGET_LASTNAME,T21.MIDDLENAME TARGET_MIDDLENAME,T21.FIRSTNAME TARGET_FIRSTNAME,T21.DOB TARGET_DOB, T21.GENDER TARGET_GENDER FROM SM_NEW_LOAD T21,PR_IDENTITY T11 WHERE ( T21.ROWID,T11.ROWID ) IN (SELECT T22.ROWID,T12.ROWID FROM PR_IDENTITY T12,SM_NEW_LOAD T22 WHERE T22.SSN IN ( T12.SSN,T12.HRID,SISID,MEDID,AFFID ) OR T22.ID IN ( T12.SSN,T12.HRID, SISID,MEDID,AFFID ) OR EXISTS (SELECT 1 FROM PR_ALT_IDS WHERE TRUNK = T12.TRUNK AND ALT_ID IN ( T22.SSN,T22.ID )) UNION ALL SELECT T22.ROWID, T12.ROWID FROM SM_NEW_LOAD T22,PR_IDENTITY T12 WHERE REPLACE(UPPER(T12.LASTNAME),'-',' ') = REPLACE(UPPER(T22.LASTNAME),'-',' ') AND UPPER(T12.FIRSTNAME) = UPPER(T22.FIRSTNAME) UNION ALL SELECT T23.ROWID,T13.ROWID FROM SM_NEW_LOAD T23,PR_IDENTITY T13 WHERE REPLACE(UPPER(T13.LASTNAME),'-',' ') = REPLACE(UPPER(T23.LASTNAME),'-',' ') AND ((INSTR(UPPER(T13.FIRSTNAME),UPPER(T23.MIDDLENAME),1) = 1 AND INSTR(UPPER(T13.MIDDLENAME),UPPER(T23.FIRSTNAME),1) = 1 ) OR (INSTR(UPPER(T23.FIRSTNAME),UPPER(T13.MIDDLENAME),1) = 1
RE: slow query help
Title: RE: slow query help It need not be generatd by a tool, I have worked for a blood bank in one of my previous projects. When it comes to matching a donor in th records, you have to take a lot of precautions to see if you have a duplicate donor etc. To me this seems to be logic to find duplicate customers ... our algorithm was worse though . Raj -Original Message- From: Mark Richard [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 4:51 PM To: Multiple recipients of list ORACLE-L Subject: Re: slow query help I tend to agree with Stephane... It looks like that query was possibly generated by a tool. If not you should go talk to the person who wrote it. Find out what the business rules are and write it from scratch. In reality the query keeps hitting the same two tables, presumably looking for rows that have just been loaded which match existing rows in another table based on the names matching in some fashion (ie: perhaps incorrect firstname/middlename usage, etc). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: slow query help
Mark, This is a new in-house developed the school directory system. The source data come from all over the different systems. The data quite dirty in some case. I am not sure how the business rule was defined yet. The developer knew her query was nasty, so she turned on me for help. This query is part of the whole process. As you said, it compares all the possible same person feed from all the systems (med, sis,law...)and insert it to another table. I think you and Stephane's suggestions are very valuable and I will definitely ask her to review her design of this processing. (sigh, we are not that experienced and good on this) After I work more with developer and get more knowledge of this application. I'd like to ask some detail of your experience in such searching logic. many thanks, Joan Mark Richard wrote: I tend to agree with Stephane... It looks like that query was possibly generated by a tool. If not you should go talk to the person who wrote it. Find out what the business rules are and write it from scratch. In reality the query keeps hitting the same two tables, presumably looking for rows that have just been loaded which match existing rows in another table based on the names matching in some fashion (ie: perhaps incorrect firstname/middlename usage, etc). Some of Stephane's suggestions will also help. We store customer names as case sensitive but we obviously need to do a case-insensitive search from our application to find customers. The easiest way out - store the name twice (we have firstname and firstnameasupper, lastname and lastnameasupper, etc). Obviously the indexes sit on the asupper columns. Sure there is a slight amount of duplicated data, but it is done for a reason and let's us search customers very fast whist still preserving the nuance's that customers like to see when you send a bill to them. Perhaps you need to store some columns which have had replace and upper applied. Finally since the query seems to accept pretty much any combination of lastname, firstname, middlename you could concatenate the three columns into a namesearch column are just perform three instr searches on that column, as opposed to trying every possibility - that should save Oracle some heartache. Regards, Mark. Stephane Faroult To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] sfaroult@orio cc: le.com Subject: Re: slow query help Sent by: [EMAIL PROTECTED] om 18/12/2002 07:58 Please respond to ORACLE-L Joan Hsieh wrote: Hi, This is the query bothered us very much recently. It run at least 15 min. and sometimes crashed the temp tablespace. Do you have any idea how to make it run better. Our developer tried used two cursors to compare the result, but the result is not optimized. We tried used last name and first name function based index on sm_new_load table. If someone interested in this query, I can sent you the execution plan. Thanks in advanced, Joan SELECT T11.TRUNK TRUNK_FOUND,T21.ID TARGET_ID,T21.SSN TARGET_SSN,T21.FULLNAME TARGET_FULLNAME,T21.LASTNAME TARGET_LASTNAME,T21.MIDDLENAME TARGET_MIDDLENAME,T21.FIRSTNAME TARGET_FIRSTNAME,T21.DOB TARGET_DOB, T21.GENDER TARGET_GENDER FROM SM_NEW_LOAD T21,PR_IDENTITY T11 WHERE ( T21.ROWID,T11.ROWID ) IN (SELECT T22.ROWID,T12.ROWID FROM PR_IDENTITY T12,SM_NEW_LOAD T22 WHERE T22.SSN IN ( T12.SSN,T12.HRID,SISID,MEDID,AFFID ) OR T22.ID IN ( T12.SSN,T12.HRID, SISID,MEDID,AFFID ) OR EXISTS (SELECT 1 FROM PR_ALT_IDS WHERE TRUNK = T12.TRUNK AND ALT_ID IN ( T22.SSN,T22.ID )) UNION ALL SELECT T22.ROWID, T12.ROWID FROM SM_NEW_LOAD T22,PR_IDENTITY T12 WHERE REPLACE(UPPER(T12.LASTNAME),'-',' ') = REPLACE(UPPER(T22.LASTNAME),'-',' ') AND UPPER(T12.FIRSTNAME) = UPPER(T22.FIRSTNAME) UNION ALL SELECT T23.ROWID,T13.ROWID FROM SM_NEW_LOAD T23,PR_IDENTITY T13 WHERE REPLACE(UPPER(T13.LASTNAME),'-',' ') = REPLACE(UPPER(T23.LASTNAME),'-',' ') AND ((INSTR(UPPER(T13.FIRSTNAME),UPPER(T23.MIDDLENAME),1) = 1 AND INSTR(UPPER(T13.MIDDLENAME),UPPER(T23.FIRSTNAME),1) = 1 ) OR (INSTR(UPPER(T23.FIRSTNAME),UPPER(T13.MIDDLENAME),1) = 1 AND INSTR(UPPER(T23.MIDDLENAME),UPPER(T13.FIRSTNAME),1) = 1 )) UNION ALL SELECT T24.ROWID,T14.ROWID FROM SM_NEW_LOAD T24,PR_IDENTITY T14 WHERE UPPER(T14.LASTNAME) = UPPER(T24.FIRSTNAME) AND UPPER(T14.FIRSTNAME) = UPPER(T24.LASTNAME) UNION ALL SELECT T25.ROWID,T15.ROWID FROM SM_NEW_LOAD T25,PR_IDENTITY T15 WHERE (INSTR(' ' || REPLACE(UPPER(T15.LASTNAME),'-',' ') || ' ' ,' ' || UPPER(T25.LASTNAME) || ' ' ) 0 OR INSTR(' ' || REPLACE(UPPER(T25
Re: slow query help
Hey Raj, I think it generated by a tool. The original code had all kinds of hints, I asked her where comes from the hints, she said from the tool. Anyway, no suggestion? That's not like you. :) joan Jamadagni, Rajendra wrote: It need not be generatd by a tool, I have worked for a blood bank in one of my previous projects. When it comes to matching a donor in th records, you have to take a lot of precautions to see if you have a duplicate donor etc. To me this seems to be logic to find duplicate customers ... our algorithm was worse though . Raj -Original Message- From: Mark Richard [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 4:51 PM To: Multiple recipients of list ORACLE-L Subject: Re: slow query help I tend to agree with Stephane... It looks like that query was possibly generated by a tool. If not you should go talk to the person who wrote it. Find out what the business rules are and write it from scratch. In reality the query keeps hitting the same two tables, presumably looking for rows that have just been loaded which match existing rows in another table based on the names matching in some fashion (ie: perhaps incorrect firstname/middlename usage, etc). Name: ESPN_Disclaimer.txt ESPN_Disclaimer.txtType: Plain Text (text/plain) Encoding: 7BIT -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Slow query
Oh No, Oh No - Be nice now ... (I can see all the 'undocumented' parameters being hauled out again ...) (Roland, that is a bit like asking 'How do I implement world peace?' or 'How do I understand women?' - we can't help make a query run faster per se - a little detail would be nice - like 'what is the query?', and 'what have you done already?' and 'what is your configuration?' (if your query is to the telephone company, trying to find my telephone number, I've already solved that one, because its at the end of my mail ...) The trite answer to your answer is 'get a faster machine, and select less data ...' With more info, we might be able to suggest some strategies! Regards Oweson Flynn -- Certified Oracle DBA The Flynn Consultancy Tel: 082-600-7-006 Fax: (011) 782-9313 EMail: [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: 29 May 2001 14:01 To: Multiple recipients of list ORACLE-L Hallo you DBAs; How can I make a query run faster, I have some queries and they tend to be be very slow. Roland Sköldblom -- 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). *** This message may contain information which is confidential and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Oweson Flynn 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: Slow query
Chase it with a big stick Nah just kidding Provide some more detail like version and what you are trying to actually do and you are going to get a better answer. As I always understood it, to get the best performance all other things being correct with the system is that you have to make your query use indexes. Avoid the commands that cause full table scans and determine the best structure. for example to find all the customers with the surname Simple in the states of Utah, Texas and Kansas make your query work so that it firstly pulls the smallest data set and searches that data set. This concept would only apply if you data had a many more clients from those three states than clients named Simple. I would find all the Simples and then check there state rather then the other way around HTH Peter At 04:00 AM 29/05/2001 -0800, you wrote: Hallo you DBAs; How can I make a query run faster, I have some queries and they tend to be be very slow. Roland Sköldblom -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter McLarty 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: Slow query
On Tue, 29 May 2001,Oweson Flynn scribbled on the wall in glitter crayon: -(Roland, that is a bit like asking 'How do I implement world peace?' or 'How -do I understand women?' - we can't help make a query run faster per se - a you do not understand women, if you are very, very, very lucky you find one you can trust completely, but you never understand them. -- Bill Thater Certifiable ORACLE DBA Telergy, Inc.[EMAIL PROTECTED] ~~ You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. ~~ Duct tape is like the force. It has a light side, a dark side, and it holds the universe together -- Carl Zwanzig -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thater, William 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).