Re: Row fetches by a cursor

I believe the problem lies in that the query processing of the cursor is done on the local machine and thus has to get all the data from the remote table to do the WHERE clause filtering. Perhaps if you could open the cursor on the remote machine in a remote procedure you would have better performance.

Sanjeev Jain wrote:

> 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:48:06 CDT