Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Slow cursor performance.

Re: Slow cursor performance.

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Tue, 29 Oct 2002 12:44:23 +0200
Message-ID: <aplos4$pns$1@ctb-nnrp2.saix.net>


Belti wrote:

> I'm running a select statement which returns transactions for a given
> customer. I have managed to tune the select statement using /*+
> FIRST_ROWS */ hint. To give you an idea of the response time. Before
> tuning it was taking about 8 secs but after the tuning it was taking
> 190msec. In TOAD when I run this statement the performance was better
> but then when it is included in the code and the actual fetch from the
> cursor is done it slows alot.

From the manual:
The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row).

Emphasis should be placed on that last bit in brackets.

Question. How do you determine the perfomance throuh TOAD? Do you stop the timer when the first row appears?

Now think about your stored proc/C code/whatever.

How fast does it take for it to complete processing of the first row? Does the hint you use make the second row or 20th or 1000th row available as fast? How long does your code take before asking for the 1000th row?

Oracle is giving you the first row as fast as possible - the intention being that while you are looking at the first few rows for some seconds or minutes, it can process and find the rest of the rows matching your criteria.

Your code is likely spending less than a few ms per row, before asking the next one. Which means that finding the first row as fast as possible is a bit pointless as your code does not spend any significant amount of time dealing with the first row (like a user would staring at the screen in Toad).

--
Billy
Received on Tue Oct 29 2002 - 04:44:23 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US