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)
