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 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) > > > > > > _________________________________________________________________ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Killough 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).
