Strange performance problem
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).
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).
Temp extent sizes and Strange performance problem
I posted earlier about my SQL statement that overnight went from less than 2 minutes to about 25 minutes on our nightly data warehouse loads. We used RMAN to move an exact copy of the database from before the process started running long to a different unix box. After running sql_trace and tkprof on the SQL statement in question (see below) on both the current and pre-problem database, the execution times were similar and the explain plans were identical except for minor differences in the number of rows returned. I then looked at all of the initialization parameters and they look the same except that we created the rman copy with a smaller shared pool (due to resource constraints on the box we moved the copy to). One thing that I noticed was that the extent sizes for the TEMP tablespace is different. The day that we started having this problem, we had a disk failure. The TEMP tablespace was on the failed disk. Another DBA dropped the TEMP tablespace and recreated it on a different disk (apparently with a larger extent size). The current next_extent size is 4194304. The next_extent size on the pre-problem TEMP tablespace is 40960. Is it possible that this difference in extent size in the TEMP tablespace could cause a ten-fold degradation in performance? Cherie Richard Ji [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] m cc: Sent by: Subject: Re: Strange performance problem [EMAIL PROTECTED] om 09/14/01 02:46 PM Please respond to ORACLE-L !! 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
Re: Temp extent sizes and Strange performance problem
Its unlikely - but is it possible you had a tempfile before and now a datafile for TEMP? hth connor --- [EMAIL PROTECTED] wrote: I posted earlier about my SQL statement that overnight went from less than 2 minutes to about 25 minutes on our nightly data warehouse loads. We used RMAN to move an exact copy of the database from before the process started running long to a different unix box. After running sql_trace and tkprof on the SQL statement in question (see below) on both the current and pre-problem database, the execution times were similar and the explain plans were identical except for minor differences in the number of rows returned. I then looked at all of the initialization parameters and they look the same except that we created the rman copy with a smaller shared pool (due to resource constraints on the box we moved the copy to). One thing that I noticed was that the extent sizes for the TEMP tablespace is different. The day that we started having this problem, we had a disk failure. The TEMP tablespace was on the failed disk. Another DBA dropped the TEMP tablespace and recreated it on a different disk (apparently with a larger extent size). The current next_extent size is 4194304. The next_extent size on the pre-problem TEMP tablespace is 40960. Is it possible that this difference in extent size in the TEMP tablespace could cause a ten-fold degradation in performance? Cherie Richard Ji [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] m cc: Sent by: Subject: Re: Strange performance problem [EMAIL PROTECTED] om 09/14/01 02:46 PM Please respond to ORACLE-L !! 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
Strange performance problem
!! 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 INDEX RANGE SCAN (object id 17270) 3938 SORT AGGREGATE 2066TABLE ACCESS BY INDEX ROWID EAS_PERSON_ASSGN_STS_LOG 4035 INDEX RANGE SCAN (object id 17279) 680SORT AGGREGATE 355 TABLE ACCESS BY INDEX ROWID EAS_CLNT_GRP_STS_LOG 696 INDEX RANGE SCAN (object id 17276) 2614
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
