Re: Slow cursor performance.

Hi all,

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.

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

Vladimir, how do I explicitly hint PL/SQL to use FIRST_ROWS instead of ALL_ROWS? Toad returns exactly the same amount of rows in data grid that the program returns.

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?

Regards

Belti

"Steve Johnson" <johnst_at_ncs.com> wrote in message news:<urt78irhg69acc_at_corp.supernews.com>...
> You have to remember that Toad doesn't fetch all records. It just fetches
> enough to fill the grid you have displayed. Your program probably fetches
> all records.
>
> HTH
> Steve...
>
> "Belti" <daniel_borg_at_hotmail.com> wrote in message
> news:bee6cab9.0210290104.29019ddc_at_posting.google.com...
> > Hello! I'm wondering whether somebody can help me figure out this
> > problem.
> >
> > 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.
> >
> > Have you got any idea why this could be? I haven't found anything on
> > the Net that says why the fetch of the cursor slows down the
> > performance and when I run the select statement on it's own it's fast.
> >
> > The indexes are not fragmented cos they have been rebuilt recently and
> > also when I run the select statement on it's own it makes full use of
> > the indexes.
> >
> > This is the tuned select statement.
> >
> > SELECT /*+ FIRST_ROWS */ *
> > FROM transaction_list_user_view
> > WHERE user_id=17628
> > AND lang_id='eng'
> > AND transaction_date BETWEEN sysdate-30 AND sysdate
> > ORDER BY transaction_date DESC, transaction_id desc;
> >
> > Thanks in advance
> >
> > Belti
Received on Wed Oct 30 2002 - 03:42:38 CST