| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: OCI ofen array fetch performance
A copy of this was sent to eabaggot_at_HiWAAY.net (Edward Baggott) (if that email address didn't require changing) On Wed, 04 Feb 1998 16:21:37 -0600, you wrote:
>We are using ofen to array-fetch thousands of rows of data
>across the network. A typical query is something like
>"select column_1, column_2, ..., column_N from MyTable".
>We have experimented with array sizes between 500 and 5000
>and notice three things:
>
>1. There is no noticable performance gains for very large
>block sizes.
>
This is true, in fact, you may very well find that for very large array fetch sizes you will start slowing down. You must find your 'sweet' spot -- the array fetch size that works best for you.
>2. For a table with, say, 20000 records, the first fetch of
>500 takes around 20 seconds, with each successive fetch taking
>much less time, maybe a second or less.
>
What is the query? does it involve a sort, an aggregate, does the plan being used by the query make it such that we have to perform alot of work to create a temporary result set and then fetch from that? This is very typical.
If you are sorting on an unindexed column for example, we must sort all 20,000 records first -- before you get the first one. Same with group bys and other aggregates.
>3. For some tables, executing a simple query for a single row
>before the first ofen call will dramatically reduce the time
>spent in the first ofen block fetch, but for others it will not.
>
>Can someone explain this? How can we cut the time spent in that
>first ofen call?
>
Look at using the /*+ FIRST_ROWS */ hint (server tuning manual). might make the over all query run slower but gets the first rows faster.
look at using sql_trace and timed_statistics and tkprof to analyze what it is your queries are doing. You might be able to 'fix' it by indexing something for example (server tuning manual again)
>eabaggot_at_hiwaay.net
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Feb 05 1998 - 00:00:00 CST
 |
 |