Row fetches by a cursor

From: Sanjeev Jain <sanjeev.jain_at_mail.tju.edu>
Date: Mon, 09 Aug 1999 15:09:08 -0400
Message-ID: <37AF2753.825750A9@mail.tju.edu>

Hi all,

I have a pl/sql procedure that has a cursor cur_pse which selects rows from a remote table over a database link. One row is fetched from that cursor and passed to another procedure that does appropriate calculations on that.

Program is like this:

cursor cur_pse is select * from ps_pay_earnings_at_abc where ------- --- ---;

FOR rec_cur_pse IN cur_pse(gv_pay_end_dt)

      LOOP
          MainProcess(rec_cur_pse);
          gv_ActiveRtn     := 'Load_LD_pay_earn';
      END LOOP;

mainprocess does manipulation with the record. I was trying to tune this process and found this trace.

SELECT *
FROM
 PS_PAY_EARNINGS WHERE PAY_END_DT = :b1 AND PAY_LINE_STATUS = :b2 AND

  INSTR(:b3,SINGLE_CHECK_USE) > 0 ORDER BY COMPANY,PAYGROUP,PAY_END_DT,   OFF_CYCLE,PAGE#,LINE#,ADDL#,ACCT_CD DESC

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.01       0.04          0          0
2           0
Execute      1      0.02       0.05          0          0
0           0
Fetch    20793     24.93    2548.26          0          0          0

216162258
------- ------ -------- ---------- ---------- ---------- ----------

---

total 20795 24.96 2548.35 0 0 2 216162258

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 21 (LD) (recursive depth: 1)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE (REMOTE)
      0   SORT (ORDER BY)
      0    TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'PS_PAY_EARNINGS'

               [ABC.WORLD]
      0     INDEX (RANGE SCAN) OF 'PS1PAY_EARNINGS' (NON-UNIQUE)
                [ABC.WORLD]

Obviously query is taking very long time because of remote table involved. So I created a local copy of this table and the trace looks like this.

SELECT *
FROM
 PS_PAY_EARNINGS WHERE PAY_END_DT = :b1 AND PAY_LINE_STATUS = :b2 AND

  INSTR(:b3,SINGLE_CHECK_USE) > 0 ORDER BY COMPANY,PAYGROUP,PAY_END_DT,   OFF_CYCLE,PAGE#,LINE#,ADDL#,ACCT_CD DESC

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.00          0          0
0           0
Execute      2      0.01       0.00          0          0
1           0
Fetch    20640     17.04      32.43        811      20896
327       20639

------- ------ -------- ---------- ---------- ---------- ----------

---

total    20643     17.05      32.43        811      20896
328       20639

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 21 (LD) (recursive depth: 1)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE

  20639 SORT (ORDER BY)
  20639 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'PS_PAY_EARNINGS'   20880 INDEX (RANGE SCAN) OF 'PS1PAY_EARNINGS' (NON-UNIQUE)

---

You can see performance improvement is enormous. But the big unanswered question is WHY DOES REMORE QUERY FETCH SOOOO! MANY MORE ROWS THAN A LOCAL QUERY? I'll appreciate any insight into this.

Thanks and regards,
Sanjeev

Received on Mon Aug 09 1999 - 14:09:08 CDT