Re: Slow cursor performance.

Belti wrote:

> Thanks for the responses. First of all I'd like to clarify that I'm
> running an Oracle 9i (9.0.1.3) on Solaris 8.

Not an issue.

> I time the performance after all rows have been fetched in the data
> grid.

Then there is something wrong with the way you time it.

A SQL running via TOAD can not be faster than the exact same SQL running via SQL*Plus or via PL/SQL.

It is not possible. Period.

Thus, the problem is either how you measure the performance, or that you are ignoring the overheards of the PL/SQL code you use for row manipulation.

Simplistically, this is how it works:

CLIENT ---> transmit SQL ---> ORACLE
CLIENT <--- cursor <--- ORACLE
CLIENT: loop until end-of-cursor

   read row from cursor <---- cursor <--- ORACLE    process row

So what are you timing in TOAD? The time elapse between step 1 and 2 above? And comparing that with the complete PL/SQL process?

This is ignoring the fact that:
a) the read row fetches another row from the cursor

   which may not yet be available (Oracle is still busy finding it    as you're using the FIRST_ROW hint).
b) TOAD simply displays the data. Your PL/SQL does row processing.

   Of course there will be a time difference.

The FIRST_ROW hint is _not_ intended for batch processing. It is a magic parlour trick to make it seem that response is very fast. It returns the first few rows to the user, and then going back finding more rows while the user looks at the first few rows.

> Vladimir, how do I explicitly hint PL/SQL to use FIRST_ROWS instead of
> ALL_ROWS?
SQL hints in PL/SQL are no different than SQL hints in SQL*Plus.

> Toad returns exactly the same amount of rows in data grid that the
> program returns.

That is not the point. You are not comparing apples with apples with your performance measurements.

> What I'm suspecting is the cost. Since the cost of FIRST_ROWS is
> higher than ALL_ROWS (according to explain plan). However if it's
> higher why does it take a shorter time to display all records?

It does not. It is an illusion. Read again the quote I supplied from the Oracle Performance Manual about what a FIRST_ROWS hint does. You obviously are not grasping what exactly the FIRST_ROWS hint instructs Oracle to do.

--
Billy