Performance problem .... HELP :-(

From: Biddell, Ian <Ian.Biddell_at_compaq.com>
Date: Wed, 19 Dec 2001 02:59:27 -0800
Message-ID: <F001.003DFC93.20011219025022@fatcity.com>

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)
Received on Wed Dec 19 2001 - 04:59:27 CST