Re: Slow cursor

Hi.

 Is it possible that you are using parameters in PL/SQL and values  within SQLPlus?
 Are you using COST or RULE based optimizer?  If you are using COST - did you run ANALYZE TABLE with FOR ALL COLUMN  clause?
 Can you post:

1. EXPLAIN for PL/SQL and SQLPlus.
2. Run it with SQL trace and post TKPROF results.

 HTH. Michael.

In article <383bcd03.8303309_at_news.space.net>,   jmayer_at_ratundtat.com wrote:
> Michael,
>
> you're right, there ARE additional conditions, but the execution plan
> tells me that Oracle doesn't make a full table scan on Tab A or B.
> And, what makes me so sure, is the fact, that calling the same
> select-statement from the SQL*Plus-Prompt takes less than a second for
> execution. Only within the package the cursor is slow.
>
> I got an email concerning this problem. The author mentioned, that
> the difference between PL/SQL and SQL*Plus is, that SQL*Plus supports
> Array-fetching. In 7.3.4, PL/SQL doesn't support this technique and
> perhaps that's the cause why the cursor is only slow in the package.
>
> On Tue, 23 Nov 1999 21:33:51 GMT, michael_bialik_at_my-deja.com wrote:
>
> >Hi.
> >
> > Are you sure you don't have additional conditions in WHERE clause?
> > It looks like you supposed to see FULL table access to either tabA
or
> > tabB.
> >
> > Michael.
> >
> >
> >
> >In article <383ac111.23589830_at_news.space.net>,
> > jmayer_at_ratundtat.com wrote:
> >> Hi there,
> >>
> >> we have a problem with the performance of a cursor. The cursor is a
> >> join of two tables A and B, A has about 500.000 records, B about
> >> 10.000.000 records. Primary key of A is col1, primary key of B is
> >> col1,col2. The join is like
> >>
> >> select ...
> >> from a,b
> >> where a.col1 = b.col1
> >>
> >> The execution plan uses a unique index access for table A and a
index
> >> range scan for table B
> >>
> >> We're using this cursor in a package, where it needs 4 seconds to
> >> retrieve about 10 records. In my opinion, this is SLOW! If we test
the
> >> same select-statement directly from the SQL*Plus-Prompt, the DB
> >> answers in less than 1 seconds.
> >>
> >> How can we tune this ? The cursor has to be executed several
thousand
> >> times, this will last for hours....
> >>
> >> Can anyone help ?
> >>
> >> Jens
> >>
> >> -
> >> Jens Mayer
> >> Rat & Tat GmbH
> >> Hamburg, Germany
> >>
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
> -
> Jens Mayer
> Rat & Tat GmbH
> Hamburg, Germany
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 24 1999 - 14:32:36 CST