Re: strange SP ??? with explan plan and tkprof report

From: Leslie Lu <leslie_y_lu_at_yahoo.com>
Date: Mon, 23 Jul 2001 11:54:20 -0700
Message-ID: <F001.00352631.20010723115735@fatcity.com>

Leslie Lu <leslie_y_lu_at_yahoo.com> wrote:
> Hi all,
>
> I have a SP which returns a refcursor. The SP
> executes immediately, but when I do a print of the
> refcursor, it takes about 2 minutes to return only 1
> row!!!
>
> SQL> var xx refcursor
> SQL>exec
> lp_ccgetcustaddracctbyacct_00('20034662',33,:xx);
> (instantaneous)
> SQL>print :xx (about 2 minutes !!!!)
>
> Any suggestion??? Thank you very much.
>
> Leslie

It's 815 on Sun 5.6.

Here are the explain plan:
Query Plan

SELECT STATEMENT Cost =
NESTED LOOPS
NESTED LOOPS OUTER

      NESTED LOOPS
        TABLE ACCESS BY INDEX ROWID CUSTOMER_ACCT
          INDEX UNIQUE SCAN PK_CUSTOMER_ACCT
        TABLE ACCESS BY INDEX ROWID CUSTOMER
          INDEX UNIQUE SCAN PK_CUSTOMER
      TABLE ACCESS BY INDEX ROWID BUSINESS
        INDEX UNIQUE SCAN PK_BUSINESS
    TABLE ACCESS BY INDEX ROWID ADDRESS
      INDEX UNIQUE SCAN PK_ADDRESS

__________________________________________________

Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/
LP_CCGETCUSTADDRACCTBYACCT_00.PRC The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail.

application/octet-stream attachment: "about this message, contact Postmaster@fatcity.com for clarification."

application/octet-stream attachment: "attachments which have been removed by ListGuru. If you have questions"

Received on Mon Jul 23 2001 - 13:54:20 CDT