Slow cursor performance.

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 Tue Oct 29 2002 - 03:04:07 CST