Performance problem .... HELP :-(
Title: Performance problem HELP :-( Hi all, Hoping someone can shed some light on a problem I have. We a particular cursor in a batch program running in production at a client site which has suddenly decided to work really badly. The program hasn't been changed but I think the customer has done some sort of reorg on the database. I traced the program on their server and also on a copy of the database on our server (our copy taken before the reorg) As can be seen from the tkprof output from a trace on the program for about an hour theirs does a lot of buffer IO for few rows returned compared to ours. The execution path in the explain is the same but the row counts down the side are different. Does anyone have any idea why this would be happening or what further investigation I can do. All access is via PK so it should be flying like the second example. Thanks, Ian CLIENT SERVER TRACE call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 1 0.00 0.04 0 0 0 0 Execute 600 0.09 0.12 0 0 0 0 Fetch 1294 2448.98 2918.79 48 83060760 1200 694 --- -- -- -- -- -- -- total 1895 2449.07 2918.95 48 83060760 1200 694 Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 12 SORT (ORDER BY) 0 FILTER 0 NESTED LOOPS 0 NESTED LOOPS 0 NESTED LOOPS 512750 NESTED LOOPS 769296 NESTED LOOPS 1869552 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'FINANCIAL_TRANSACTION_B' 2541882 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'FINANCIAL_TRANSACTION_PK' (UNIQUE) 487200 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_SCHEDULE_LINK_B' 179385326 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_SCHEDULE_LINK_PK' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_VERSION_B' 36834 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_VERSION_PK' (UNIQUE) 249381 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_VERSION_B' 445 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_VERSION_PK' (UNIQUE) 36 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'BILL_HEADER_B' 48 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'BILL_HEADER_PK' (UNIQUE) 12 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ALLOCATION_TRANSACTION_A_PK' (UNIQUE) 12 NESTED LOOPS 24 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'ACCOUNT_ENTITLEMENT_B' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ACCOUNT_ENTITLEMENT_PK' (UNIQUE) 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'INDICATOR_DESC_PK' (UNIQUE) PROD DATABASE COPY ON OUR SERVER call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 1 0.07 0.08 0 0 0 0 Execute 482 0.20 0.25 0 0 0 0 Fetch 4573 86.71 89.05 93 1450283 0 4090 --- -- -- -- -- -- -- total 5056 86.98 89.38 93 1450283 0 4090 Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 848 SORT (ORDER BY) 11660 FILTER 8790 NESTED LOOPS 8790 NESTED LOOPS 8790 NESTED LOOPS 8790 NESTED LOOPS 8790 NESTED LOOPS 25596 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'FINANCIAL_TRANSACTION_B' 25752 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'FINANCIAL_TRANSACTION_PK' (UNIQUE) 12869 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_SCHEDULE_LINK_B' 16078 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_SCHEDULE_LINK_PK' (UNIQUE) 26131 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_VERSION_B' 37867 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_VERSION_PK' (UNIQUE) 30064 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_VERSION_B' 41800 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_VERSION_PK' (UNIQUE) 11736 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'BILL_HEADER_B' 11736 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'BILL_HEADER_PK' (UNIQUE) 23396 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ALLOCATION_TRANSACTION_B_PK' (UNIQUE) 667 NESTED LOOPS 8764 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'ACCOUNT_ENTITLEMENT_B' 12620 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ACCOUNT_ENTITLEMENT_PK' (UNIQUE) 848 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'INDICATOR_DESC_PK' (UNIQUE)
Re: Performance problem .... HELP :-(
Biddell, Ian wrote: Hi all, Hoping someone can shed some light on a problem I have. We a particular cursor in a batch program running in production at a client site which has suddenly decided to work really badly. The program hasn't been changed but I think the customer has done some sort of reorg on the database. I traced the program on their server and also on a copy of the database on our server (our copy taken before the reorg) As can be seen from the tkprof output from a trace on the program for about an hour theirs does a lot of buffer IO for few rows returned compared to ours. The execution path in the explain is the same but the row counts down the side are different. Does anyone have any idea why this would be happening or what further investigation I can do. All access is via PK so it should be flying like the second example. Thanks, Ian Ian, It's wrong to believe that because you are using PKs everything should fly. What blinks before my eyes is this : 179385326 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_SCHEDULE_LINK_PK' (UNIQUE) Oracle chooses to access a PK, but as an alternative to scanning what looks like a table implementing a (n-n) relationship of death. In other words, it is taking the query by the wrong end. Check what you want (the select list), what you feed in (criteria), and try to coax Oracle in doing it logically, starting from the table for which the best, in terms of selectivity, criterion has been provided. In this kind of case, the /*+ ORDERED */ hint often proves helpful. -- HTH, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance problem .... HELP :-(
Hi Stephane, Thanks for writing back, I would normally look at some hints or something like that but as far as I can tell it's going through the tables in the correct way. My problem is when we run it on a Production copy on my server we don't get that big number against that table. The tkprof explain shows the access path as the same in both cases. Could it be doing it differently when it actually executes? The only difference is their dba has done some sort of reorg since we got our copy of the database. Thanks again Ian -Original Message- Sent: Wednesday, 19 December 2001 21:55 To: Multiple recipients of list ORACLE-L Biddell, Ian wrote: Hi all, Hoping someone can shed some light on a problem I have. We a particular cursor in a batch program running in production at a client site which has suddenly decided to work really badly. The program hasn't been changed but I think the customer has done some sort of reorg on the database. I traced the program on their server and also on a copy of the database on our server (our copy taken before the reorg) As can be seen from the tkprof output from a trace on the program for about an hour theirs does a lot of buffer IO for few rows returned compared to ours. The execution path in the explain is the same but the row counts down the side are different. Does anyone have any idea why this would be happening or what further investigation I can do. All access is via PK so it should be flying like the second example. Thanks, Ian Ian, It's wrong to believe that because you are using PKs everything should fly. What blinks before my eyes is this : 179385326 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_SCHEDULE_LINK_PK' (UNIQUE) Oracle chooses to access a PK, but as an alternative to scanning what looks like a table implementing a (n-n) relationship of death. In other words, it is taking the query by the wrong end. Check what you want (the select list), what you feed in (criteria), and try to coax Oracle in doing it logically, starting from the table for which the best, in terms of selectivity, criterion has been provided. In this kind of case, the /*+ ORDERED */ hint often proves helpful. -- HTH, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Biddell, Ian 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: Performance problem .... HELP :-(
Did they rebuild their indexes after this reorg? It could be that they simply exported/imported the table without rebuilding the appropriate indexes? Just a thought.. Mark -Original Message- Ian Sent: 19 December 2001 12:55 To: Multiple recipients of list ORACLE-L Hi Stephane, Thanks for writing back, I would normally look at some hints or something like that but as far as I can tell it's going through the tables in the correct way. My problem is when we run it on a Production copy on my server we don't get that big number against that table. The tkprof explain shows the access path as the same in both cases. Could it be doing it differently when it actually executes? The only difference is their dba has done some sort of reorg since we got our copy of the database. Thanks again Ian -Original Message- Sent: Wednesday, 19 December 2001 21:55 To: Multiple recipients of list ORACLE-L Biddell, Ian wrote: Hi all, Hoping someone can shed some light on a problem I have. We a particular cursor in a batch program running in production at a client site which has suddenly decided to work really badly. The program hasn't been changed but I think the customer has done some sort of reorg on the database. I traced the program on their server and also on a copy of the database on our server (our copy taken before the reorg) As can be seen from the tkprof output from a trace on the program for about an hour theirs does a lot of buffer IO for few rows returned compared to ours. The execution path in the explain is the same but the row counts down the side are different. Does anyone have any idea why this would be happening or what further investigation I can do. All access is via PK so it should be flying like the second example. Thanks, Ian Ian, It's wrong to believe that because you are using PKs everything should fly. What blinks before my eyes is this : 179385326 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_SCHEDULE_LINK_PK' (UNIQUE) Oracle chooses to access a PK, but as an alternative to scanning what looks like a table implementing a (n-n) relationship of death. In other words, it is taking the query by the wrong end. Check what you want (the select list), what you feed in (criteria), and try to coax Oracle in doing it logically, starting from the table for which the best, in terms of selectivity, criterion has been provided. In this kind of case, the /*+ ORDERED */ hint often proves helpful. -- HTH, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Biddell, Ian 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: Mark Leith 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: Performance problem .... HELP :-(
Title: Performance problem HELP :-( Hi Ian, take a careful look at fragmentation of their indexes and possible chained rows in the tables. Probably RATE_SCHEDULE_LINK_PK is a good start point Also the cardinality(estimated numbers of output rows for each step) may confuse you if their statistics is lost or obsolete for some objects Regards,Ed - Original Message - From: Biddell, Ian To: Multiple recipients of list ORACLE-L Sent: Wednesday, December 19, 2001 1:50 PM Subject: Performance problem HELP :-( Hi all, Hoping someone can shed some light on a problem I have. We a particular cursor in a batch program running in production at a client site which has suddenly decided to work really badly. The program hasn't been changed but I think the customer has done some sort of reorg on the database. I traced the program on their server and also on a copy of the database on our server (our copy taken before the reorg) As can be seen from the tkprof output from a trace on the program for about an hour theirs does a lot of buffer IO for few rows returned compared to ours. The execution path in the explain is the same but the row counts down the side are different. Does anyone have any idea why this would be happening or what further investigation I can do. All access is via PK so it should be flying like the second example.
Re: Performance problem .... HELP :-(
Ian, What kind of a reorg was done? So the RATE_SCHEDULE_LINK_B table has about the same number of rows in both instances? The explain plans are the same. It looks like one just has more records to access. Both could be improved by changing the sql to be more selective. Mike From: Biddell, Ian [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Performance problem HELP :-( Date: Wed, 19 Dec 2001 02:50:23 -0800 Hi all, Hoping someone can shed some light on a problem I have. We a particular cursor in a batch program running in production at a client site which has suddenly decided to work really badly. The program hasn't been changed but I think the customer has done some sort of reorg on the database. I traced the program on their server and also on a copy of the database on our server (our copy taken before the reorg) As can be seen from the tkprof output from a trace on the program for about an hour theirs does a lot of buffer IO for few rows returned compared to ours. The execution path in the explain is the same but the row counts down the side are different. Does anyone have any idea why this would be happening or what further investigation I can do. All access is via PK so it should be flying like the second example. Thanks, Ian CLIENT SERVER TRACE call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.00 0.04 0 0 0 0 Execute600 0.09 0.12 0 0 0 0 Fetch 1294 2448.982918.79 48 83060760 1200 694 --- -- -- -- -- -- -- total 1895 2449.072918.95 48 83060760 1200 694 Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 12 SORT (ORDER BY) 0FILTER 0 NESTED LOOPS 0 NESTED LOOPS 0 NESTED LOOPS 512750NESTED LOOPS 769296 NESTED LOOPS 1869552 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'FINANCIAL_TRANSACTION_B' 2541882 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'FINANCIAL_TRANSACTION_PK' (UNIQUE) 487200 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_SCHEDULE_LINK_B' 179385326 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_SCHEDULE_LINK_PK' (UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_VERSION_B' 36834 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_VERSION_PK' (UNIQUE) 249381TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_VERSION_B' 445 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_VERSION_PK' (UNIQUE) 36 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'BILL_HEADER_B' 48INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'BILL_HEADER_PK' (UNIQUE) 12 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ALLOCATION_TRANSACTION_A_PK' (UNIQUE) 12 NESTED LOOPS 24 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'ACCOUNT_ENTITLEMENT_B' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ACCOUNT_ENTITLEMENT_PK' (UNIQUE) 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'INDICATOR_DESC_PK' (UNIQUE) PROD DATABASE COPY ON OUR SERVER call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.07 0.08 0 0 0 0 Execute482 0.20 0.25 0 0 0 0 Fetch 4573 86.71 89.05 931450283 0 4090 --- -- -- -- -- -- -- total 5056 86.98 89.38 931450283 0 4090 Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 848 SORT (ORDER BY) 11660FILTER 8790 NESTED LOOPS 8790 NESTED LOOPS 8790 NESTED LOOPS 8790NESTED LOOPS 8790 NESTED LOOPS 25596 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'FINANCIAL_TRANSACTION_B' 25752 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'FINANCIAL_TRANSACTION_PK' (UNIQUE) 12869 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_SCHEDULE_LINK_B' 16078 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_SCHEDULE_LINK_PK' (UNIQUE) 26131 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'RATE_VERSION_B' 37867 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_VERSION_PK' (UNIQUE) 30064TABLE ACCESS GOAL: ANALYZED
