Re: Performance Question

A copy of this was sent to lisa.bogart_at_eds.com (if that email address didn't require changing) On Wed, 05 May 1999 13:51:31 GMT, you wrote:

>I am a newbie to Oracle and only an application developer but my DBA is
>seriously overworked and I am hoping someone might be able to give me some
>clues. I have a select that I am running that initially gives me almost
>immediate response but after some of the data is retrieved, it chokes for
>almost a minute before that last few records are retrieved. The select is a
>join of 4 tables but only returns less than 55 rows but it takes over a minute
>to get ALL the data even though I see almost instantanious response at first.
>When I run the select in SQL*Plus, it throws up the first 45 rows and then
>chokes for over a minute before it displays the rest. I am trying to create a
>disconnected recordset in a Visual Basic component running in Microsoft
>Transaction Server and this kind of response is unacceptable. I am not an
>Oracle person at all but maybe someone can point me in the right direction.
>Any help would be greatly appreciated.
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Sounds like the query is doing a full scan of a table and the data you want is near the 'top' of the table (it happens to be physically stored in the first extent(s) of the table). We scan the rest of the table to find more data but don't find any more -- while you wait. The first rows pop right up since we find them right away and then the rest of the time we are just reading and looking for more data.

To see what the query plan is and how we are accessing the data (perhaps an index would help here) you could use autotrace in sqlplus. For the quick and dirty directions on autotrace see

 http://www.oracle.com/ideveloper/99article/di01_1a.html

it shows how to set it up and what it does. It will point you in the right direction...

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed May 05 1999 - 09:29:47 CDT