Re: select * performance

From: Peter Sylvester <peters_nospam_please_at_mitre.org>
Date: Wed, 26 Feb 2003 10:45:00 -0500
Message-ID: <b3inds$hh8$1@newslocal.mitre.org>

Well, I just ran some tests using 9.2 thin driver. The difference was not as great as I've seen in the past, maybe the drivers have gotten better.

Here's what I saw, using "select * form dba_objects", no terminal output, java client on separate box.

(30122) records, fetchSize=10 etime=11356 ms
(30122) records, fetchSize=10 etime=10795 ms
(30122) records, fetchSize=50 etime=14311 ms
(30122) records, fetchSize=100 etime=9263 ms
(30122) records, fetchSize=200 etime=9273 ms
(30122) records, fetchSize=500 etime=8743 ms
(30122) records, fetchSize=1000 etime=9114 ms
(30122) records, fetchSize=2000 etime=8633 ms
(30122) records, fetchSize=5000 etime=9694 ms
(30122) records, fetchSize=10000 etime=9384 ms

Looks like the driver defaults to 10, and in this example using fetchSize of 500 did a bit better. The first query was run a couple times to get caching out of the way.

--Peter

Sybrand Bakker wrote:
> On Fri, 21 Feb 2003 11:35:18 -0500, Peter Sylvester
> <peters_nospam_please_at_mitre.org> wrote:
>
>

>>This causes more buffering and generally improves large queries.

>
>
> Not necessarily. If the total bytes per array exceed either the SDU of
> Oracle or the MTU of the network card, this will have a big adverse
> effect on performance, as I found out executing benchmarks.
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Wed Feb 26 2003 - 09:45:00 CST