Query performance

From: <alanpol_at_my-deja.com>
Date: 2000/04/11
Message-ID: <8cvhf8$sao$1@nnrp1.deja.com>#1/1

We're running 8.0.5 on an NT server.

I've a couple of quick questions on doing performance investigation.

Firstly, are there any recomended ways to definitively record response times for a query ? The method I've been using in SQL*Plus is:

set termout off
timing start a
<execute query>
set termout on
timing stop a (outputs timing - millisecs on NT)

Will this produce accurate results ? Does anyone have a better way ?

My second question is rather more general - any pointers to relevant references (especially URLs) would be great. If a query returns 200 rows, why can it be markedly quicker if the number of rows returned is limited to, say, 10 ? The execution plan should be the same, so:
- what mechanisms are involved in returning a rowset to an application
such as SQL*Plus
- how do these mechanisms impact timing and vary with the number of
rows returned

Any help would be much appreciated.

Alan

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Apr 11 2000 - 00:00:00 CDT