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

Leslie Lu wrote:
>
> --- 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
>

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: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).