Re: Strange performance problem
I have seen something like this in the past and it was because there were two tables - named the same in two different schemas (public synonym, private synonym and all that mess).. Do you know whether this could be the same case as yours? Also check to see if the explain plan differs when u run it under different schemas. Babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 30, 2002 1:33 PM I got a call from a customer earlier. He said that he was trying to run a query and it was taking way too long. He ran the same query last Friday and it came back in seconds. I looked at it in OEM and noticed that two of the tables were being accessed by full table scans. These tables have 22,000+ and 24,000+ rows each. I took the sql from OEM and ran it in a svrmgrl session (connected internal), and it came back in seconds. His still hadn't come back. To further complicate things, I connected as the owner of the tables (the same user he was using) and ran the query again. This time, I ended up killing it after 10 minutes. I'm confused as to what can cause such a difference in performance from sys to another user. The server did crash sometime over the weekend. He said that it was fine before the crash. I ran a dbverify on all of the data files and came up with nothing. The vitals are: Oracle 8.1.6.0.0 Digital Unix V4.0F (Rev. 1229) Unfortunately, upgrading Oracle isn't an option because the processor is too old. Oracle won't support it on any versions higher than 8.1.6. A patchset may be possible, though. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babu Nagarajan 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: Strange performance problem
I have seen something like this in the past and it was because there were two tables - named the same in two different schemas (public synonym, private synonym and all that mess).. Do you know whether this could be the same case as yours? Also check to see if the explain plan differs when u run it under different schemas. Babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 30, 2002 1:33 PM I got a call from a customer earlier. He said that he was trying to run a query and it was taking way too long. He ran the same query last Friday and it came back in seconds. I looked at it in OEM and noticed that two of the tables were being accessed by full table scans. These tables have 22,000+ and 24,000+ rows each. I took the sql from OEM and ran it in a svrmgrl session (connected internal), and it came back in seconds. His still hadn't come back. To further complicate things, I connected as the owner of the tables (the same user he was using) and ran the query again. This time, I ended up killing it after 10 minutes. I'm confused as to what can cause such a difference in performance from sys to another user. The server did crash sometime over the weekend. He said that it was fine before the crash. I ran a dbverify on all of the data files and came up with nothing. The vitals are: Oracle 8.1.6.0.0 Digital Unix V4.0F (Rev. 1229) Unfortunately, upgrading Oracle isn't an option because the processor is too old. Oracle won't support it on any versions higher than 8.1.6. A patchset may be possible, though. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babu Nagarajan 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: Strange performance problem
I have seen something like this in the past and it was because there were two tables - named the same in two different schemas (public synonym, private synonym and all that mess).. Do you know whether this could be the same case as yours? Also check to see if the explain plan differs when u run it under different schemas. Babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 30, 2002 1:33 PM I got a call from a customer earlier. He said that he was trying to run a query and it was taking way too long. He ran the same query last Friday and it came back in seconds. I looked at it in OEM and noticed that two of the tables were being accessed by full table scans. These tables have 22,000+ and 24,000+ rows each. I took the sql from OEM and ran it in a svrmgrl session (connected internal), and it came back in seconds. His still hadn't come back. To further complicate things, I connected as the owner of the tables (the same user he was using) and ran the query again. This time, I ended up killing it after 10 minutes. I'm confused as to what can cause such a difference in performance from sys to another user. The server did crash sometime over the weekend. He said that it was fine before the crash. I ran a dbverify on all of the data files and came up with nothing. The vitals are: Oracle 8.1.6.0.0 Digital Unix V4.0F (Rev. 1229) Unfortunately, upgrading Oracle isn't an option because the processor is too old. Oracle won't support it on any versions higher than 8.1.6. A patchset may be possible, though. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babu Nagarajan 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: Strange performance problem
!! Please do not post Off Topic to this List !! Did you check to see if there is anything else running on the server that might take resource away from Oracle? It has happened to me once that the SA was running something that he shouldn't and it's using a lot of system resources. HTH [EMAIL PROTECTED] 09/14/01 03:05PM !! Please do not post Off Topic to this List !! I have a nightly load job that was being tracked by our developers. According to their nightly logs (going back months), a query was running as far back as they can record with a sub-second response time. Then on a particular date (Aug. 23rd), the query started taking more than 20 minutes to complete. It has taken that long to complete ever since. I looked at the explain plan and it looks o.k. Indexes are being used and there are no suspicious full table scans. The init.ora file has not changed since then. We restored a full copy of the database to an alternate host using rman. It should be an exact copy as of Aug. 16th. I ran the query on the copy and on the current production database and the resulting explain plans were identical except for the number of rows returned. Total execution time and cpu times were similar. I looked through our change documentation and I do not see any record of data structure changes or any data changes at all in the database in question. I am sort of at a loss for what to try next. What sort of changes might cause such an extreme degradation in performance as this? This is an 8.1.7 database on Sun Solaris 2.8. The optimization is rule-based. No partitioning. Database is about 80 Gig in size. Following is the explain plan, if anyone is interested: SELECT ACTV_EAS_PERS_RPT_PROF_VIEW.LOGIN_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.ACCT_GRP_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.RPT_PROF_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.INS_DT_TM, ACTV_EAS_PERS_RPT_PROF_VIEW.UPD_DT_TM FROM GELCO.ACTV_EAS_PERS_RPT_PROF_VIEW call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.26 0.27 0 0 0 0 Execute 2 0.01 0.01 0 0 1 0 Fetch 128982.191026.27 1454639732999 55484 1897 --- -- -- -- -- -- -- total 131982.461026.55 1454639732999 55485 1897 Rows Row Source Operation --- --- 1897 FILTER 2041 NESTED LOOPS 2422HASH JOIN 2341 NESTED LOOPS 2342 NESTED LOOPS 2338 NESTED LOOPS 2338NESTED LOOPS 2346 NESTED LOOPS 2510 NESTED LOOPS 2510 NESTED LOOPS 2510INDEX FAST FULL SCAN (object id 17279) 5018INDEX UNIQUE SCAN (object id 17278) 5018 TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP 5018INDEX UNIQUE SCAN (object id 17266) 4854 INDEX RANGE SCAN (object id 17270) 4682 TABLE ACCESS BY INDEX ROWID EAS_PERSON_RPT_PROF_ASSGN 4682 INDEX RANGE SCAN (object id 17283) 4674VIEW ACTIVE_EAS_RPT_PROF_VIEW 100491 SORT UNIQUE 43 UNION-ALL 10 TABLE ACCESS FULL EAS_RPT_PROF 33 FILTER 34NESTED LOOPS 734 NESTED LOOPS 207976 NESTED LOOPS 207976 MERGE JOIN CARTESIAN 706INDEX FAST FULL SCAN (object id 17270) 208680SORT JOIN 295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG 415950 TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP 415950INDEX UNIQUE SCAN (object id 17266) 208708 INDEX UNIQUE SCAN (object id 17275) 766 TABLE ACCESS FULL EAS_RPT_PROF 4678 TABLE ACCESS FULL USER_SIGNON 2341 INDEX UNIQUE SCAN (object id 17275) 295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG 4461VIEW ACTIVE_EAS_PERSON_VIEW 2675205 SORT UNIQUE 1105 UNION-ALL 128 NESTED LOOPS 1107INDEX RANGE SCAN (object id 17284) 128TABLE ACCESS BY INDEX ROWID EAS_PERSON 2212 INDEX UNIQUE SCAN (object id 17277) 977 FILTER 1008NESTED LOOPS 288511 NESTED LOOPS 326271 MERGE JOIN CARTESIAN 1107 INDEX RANGE SCAN (object id 17284) 327376 SORT JOIN 295TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG 614780 TABLE ACCESS BY INDEX ROWID EAS_PERSON 652540 INDEX UNIQUE SCAN (object id 17277) 289517 INDEX UNIQUE SCAN (object id 17275) 540 SORT AGGREGATE 287TABLE ACCESS BY INDEX ROWID EAS_CLNT_GRP_STS_LOG 557 INDEX RANGE SCAN (object id 17276) 1346 SORT AGGREGATE 737TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP_STS_LOG 1412
Re: Strange performance problem
!! Please do not post Off Topic to this List !! It should be an exact copy as of Aug. 16th. I ran the query on the copy and on the current production database and the resulting explain plans were identical except for the number of rows returned. Total execution time and cpu times were similar. What's the difference in the number of rows? I see buried deep in the explain plan a Cartesian join if the numbe rows jumped significantly, that might be the problem From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Strange performance problem Date: Fri, 14 Sep 2001 11:05:29 -0800 !! Please do not post Off Topic to this List !! I have a nightly load job that was being tracked by our developers. According to their nightly logs (going back months), a query was running as far back as they can record with a sub-second response time. Then on a particular date (Aug. 23rd), the query started taking more than 20 minutes to complete. It has taken that long to complete ever since. I looked at the explain plan and it looks o.k. Indexes are being used and there are no suspicious full table scans. The init.ora file has not changed since then. We restored a full copy of the database to an alternate host using rman. It should be an exact copy as of Aug. 16th. I ran the query on the copy and on the current production database and the resulting explain plans were identical except for the number of rows returned. Total execution time and cpu times were similar. I looked through our change documentation and I do not see any record of data structure changes or any data changes at all in the database in question. I am sort of at a loss for what to try next. What sort of changes might cause such an extreme degradation in performance as this? This is an 8.1.7 database on Sun Solaris 2.8. The optimization is rule-based. No partitioning. Database is about 80 Gig in size. Following is the explain plan, if anyone is interested: SELECT ACTV_EAS_PERS_RPT_PROF_VIEW.LOGIN_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.ACCT_GRP_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.RPT_PROF_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.INS_DT_TM, ACTV_EAS_PERS_RPT_PROF_VIEW.UPD_DT_TM FROM GELCO.ACTV_EAS_PERS_RPT_PROF_VIEW call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.26 0.27 0 0 0 0 Execute 2 0.01 0.01 0 0 1 0 Fetch 128982.191026.27 1454639732999 55484 1897 --- -- -- -- -- -- -- total 131982.461026.55 1454639732999 55485 1897 Rows Row Source Operation --- --- 1897 FILTER 2041 NESTED LOOPS 2422HASH JOIN 2341 NESTED LOOPS 2342 NESTED LOOPS 2338 NESTED LOOPS 2338NESTED LOOPS 2346 NESTED LOOPS 2510 NESTED LOOPS 2510 NESTED LOOPS 2510INDEX FAST FULL SCAN (object id 17279) 5018INDEX UNIQUE SCAN (object id 17278) 5018 TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP 5018INDEX UNIQUE SCAN (object id 17266) 4854 INDEX RANGE SCAN (object id 17270) 4682 TABLE ACCESS BY INDEX ROWID EAS_PERSON_RPT_PROF_ASSGN 4682 INDEX RANGE SCAN (object id 17283) 4674VIEW ACTIVE_EAS_RPT_PROF_VIEW 100491 SORT UNIQUE 43 UNION-ALL 10 TABLE ACCESS FULL EAS_RPT_PROF 33 FILTER 34NESTED LOOPS 734 NESTED LOOPS 207976 NESTED LOOPS 207976 MERGE JOIN CARTESIAN 706INDEX FAST FULL SCAN (object id 17270) 208680SORT JOIN 295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG 415950 TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP 415950INDEX UNIQUE SCAN (object id 17266) 208708 INDEX UNIQUE SCAN (object id 17275) 766 TABLE ACCESS FULL EAS_RPT_PROF 4678 TABLE ACCESS FULL USER_SIGNON 2341 INDEX UNIQUE SCAN (object id 17275) 295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG 4461VIEW ACTIVE_EAS_PERSON_VIEW 2675205 SORT UNIQUE 1105 UNION-ALL 128 NESTED LOOPS 1107INDEX RANGE SCAN (object id 17284) 128TABLE ACCESS BY INDEX ROWID EAS_PERSON 2212 INDEX UNIQUE SCAN (object id 17277) 977 FILTER 1008NESTED LOOPS 288511 NESTED LOOPS 326271 MERGE JOIN CARTESIAN 1107 INDEX RANGE SCAN (object id 17284) 327376 SORT JOIN 295TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG 614780 TABLE
Re: Strange performance problem
!! Please do not post Off Topic to this List !! Rachel, The difference in rows is not significant anywhere in the explain plan. Thanks for your reply. Cherie Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] carmichr@hotm cc: ail.com Subject: Re: Strange performance problem Sent by: [EMAIL PROTECTED] om 09/14/01 02:55 PM Please respond to ORACLE-L !! Please do not post Off Topic to this List !! It should be an exact copy as of Aug. 16th. I ran the query on the copy and on the current production database and the resulting explain plans were identical except for the number of rows returned. Total execution time and cpu times were similar. What's the difference in the number of rows? I see buried deep in the explain plan a Cartesian join if the numbe rows jumped significantly, that might be the problem From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Strange performance problem Date: Fri, 14 Sep 2001 11:05:29 -0800 !! Please do not post Off Topic to this List !! I have a nightly load job that was being tracked by our developers. According to their nightly logs (going back months), a query was running as far back as they can record with a sub-second response time. Then on a particular date (Aug. 23rd), the query started taking more than 20 minutes to complete. It has taken that long to complete ever since. I looked at the explain plan and it looks o.k. Indexes are being used and there are no suspicious full table scans. The init.ora file has not changed since then. We restored a full copy of the database to an alternate host using rman. It should be an exact copy as of Aug. 16th. I ran the query on the copy and on the current production database and the resulting explain plans were identical except for the number of rows returned. Total execution time and cpu times were similar. I looked through our change documentation and I do not see any record of data structure changes or any data changes at all in the database in question. I am sort of at a loss for what to try next. What sort of changes might cause such an extreme degradation in performance as this? This is an 8.1.7 database on Sun Solaris 2.8. The optimization is rule-based. No partitioning. Database is about 80 Gig in size. Following is the explain plan, if anyone is interested: SELECT ACTV_EAS_PERS_RPT_PROF_VIEW.LOGIN_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.ACCT_GRP_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.RPT_PROF_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.INS_DT_TM, ACTV_EAS_PERS_RPT_PROF_VIEW.UPD_DT_TM FROM GELCO.ACTV_EAS_PERS_RPT_PROF_VIEW call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.26 0.27 0 0 0 0 Execute 2 0.01 0.01 0 0 1 0 Fetch 128982.191026.27 1454639732999 55484 1897 --- -- -- -- -- -- -- total 131982.461026.55 1454639732999 55485 1897 Rows Row Source Operation --- --- 1897 FILTER 2041 NESTED LOOPS 2422HASH JOIN 2341 NESTED
