Re: Cursor fetch loop taking a long time

Hi.

 What optimizer are you using ( COST or RULE based )?

 If you are using COST - try to analyze your table  ( ANALYZE TABLE table_name COMPUTE STATISTICS FOR ALL COLUMNS )

 otherwise check the EXPLAIN plan with and without parameters and  compare it ( using INDEX hint may solve the problem ).

 HTH. Michael

In article <85hr0e$rbh$1_at_nnrp1.deja.com>,   pmartin513_at_my-deja.com wrote:
> Hello,
>
> I've got a bit of a problem with a cursor fetch loop in Oracle 7.3.4
> plsql 3.3.4.
>
> The cursor is in a procedure in a package with the variables needed in
> the query where clause passed as parameters.
>
> Ie.
>
> Procedure TestFetch(P1 in tableColumn%Type,
> P2 in tableColumn%Type);
>
> Cursor cTest is
> Select Column1,Column2
> From Table
> Where Column3 = P1
> and Column4 = P2
>
> To use the cursor I use
>
> Open cTest;
> Loop;
> Fetch cTest Into V1,V2;
> Exit when cTest%NOTFOUND;
> End Loop;
>
> When this procedure is run it takes about 30 min. for 14000 rows.
>
> However the SAME query run in a third party tool
(sqlNavigator,SqlPlus)
> takes 1min. to return ALL the 14000 rows.
>
> Now for the weird part.
>
> If I modifiy the cursor in the package and instead of passing the
> parameters I put then in the query
>
> ie.
>
> Cursor cTest is
> Select Column1,Column2
> From Table
> Where Column3 = 1 /*P1*/
> and Column4 = 2 /*p2*/
>
> the same cusor loop as before only takes 1 min.
>
> Can anybody explain this difference ?
>
> Thanks in advance for any answers
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 13 2000 - 15:45:49 CST