Leslie Lu wrote:
>
> --- Leslie Lu <[EMAIL PROTECTED]> 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
>
Leslie,
As you have probably noticed, your attachments have been removed, so
...
I don't know exactly how SQL*Plus handles ref cursors, but parsing is
peanuts, especially today (since parsing is now done without contacting
the server), where it is mainly checking that your cursor is
syntactically correct. The bulk of the job is done during the 'exec',
which does everything to be ready to immediately get the first row at
the first 'fetch' call. Quite obviously, your 'exec' takes a long time.
There is nothing obviously wrong in your explain plan - except that
perhaps you are not accessing tables in the proper order. If your key is
the customer id, just try to rewrite your query as
select /*+ ORDERED */ ...
from CUSTOMER,
CUSTOMER_ACCT,
BUSINESS,
ADDRESS
where ...
It may be faster. Always start with the table the key of which is
provided, then follow the links suggested by foreign keys.
--
Regards,
Stephane Faroult
Oriole Corporation
Voice: +44 (0) 7050-696-269
Fax: +44 (0) 7050-696-449
Performance Tools & Free Scripts
--------------------------------------------------------------
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--------------------------------------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
