Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Getting results from oracle query suck!

Re: Getting results from oracle query suck!

From: <mpir_at_compuserve.com>
Date: Tue, 04 May 1999 20:06:18 GMT
Message-ID: <7gnjvm$rgf$1@nnrp1.dejanews.com>


PMJI, but there are some environmental factors that can cause a major delay: 1-swapping memory. I did not see what is doing the retrieval, sga size vs available ram, etc. but that kind of delay can happen if the system has started swapping buffers.

2-Swapping can be aggrevated or caused if the i/o is to the same disk, that is if you are writing to a file that is on the same disk as the db, etc. This is particularly true if the swapping is happening within Oracle (db block buffers in SGA vs PGA buffers)

3-You say you have no network but are NT. You may be running locally, but NT is still subject to assorted network and other priority interrupts if your machine is on a network.

4-How big are the rows? 1000 5K rows may take 5 seconds, but 1000 5 byte rows shouldn't. NT's screen displays are not the fastest for text modes.

In article <7gnemr$rho7_at_amber.alltel.com>,   "MScheuter" <mike.scheuter_at_alltel.com> wrote:
> Thanks for the response.
>
> The query is essentially:
> select * from CName where LastName like 'Nec%'
> Again the query execution time is great, in the range of 180 ms. The time
> it takes to retrieve what I consider a small amount of data, about 1000
> rows, is terrible. Is it reasonable for oracle to take 5000 ms to send back
> to a program 1000 rows of data with no network in the picture? I think not
> since this same operation is MS Access can once again be measured in the
> hundreds of milliseconds.
>
> I tend to think that there is a gross parameter error within the database on
> NT. Our DBA is experienced with Oracle on Unix but not NT and such has not
> found anything to tune. We have been doing traces and cost tracking through
> explain plan but this has not yeilded the information that we need. I tells
> us what we already know, that the query (build of the result set) works
> great. Explain plan does not tell us what is going on when reading the
> result set. Is there additional monitoring options that can be enabled
> within Oracle?
>
> thanks again
> -mike
>
> Sybrand Bakker wrote in message
> <925837438.21998.0.muttley.d4ee154e_at_news.demon.nl>...
> >Most likely this is a sql statement tuning issue.
> >We need to have your statement and details about the tables.
> >Then: are you using array fetching. This is possible in Pro*C and without
> >doubt it is possible in OCI. Fetching a record is the costly operation
> there
> >is. Fetch your records in batches of 100 or even 1000. Also makes sure
> >Oracle traces this session. This will provide useful detail from the server
> >side. Make sure sql_trace = true and timed_statistics = true in
> >init<sid>.ora, bounce the database and see what happens
> >
> >Hth,
> >
> >Sybrand Bakker, Oracle DBA
> >
> >
> >MScheuter wrote in message <7gn1s6$rho6_at_amber.alltel.com>...
> >>I have a table that contains customer name information (about 10 fields).
> >>There are about 1.9 million rows in the table. The table is index by the
> >>last name. I am writing application code in both C++ using oracle oci and
> >>java using again the supplied oracle oci drivers (see oracle web site).
> In
> >>a test program I am timing the execution of the select statment and then
> >>timing how long it takes to 'read' (or drain) the rows of data from the
> >>database. The timing results are as follows:
> >>
> >>Rows in Result Set Query Time (ms) Read results time (ms)
> >>2824 187 11875
> >>211 156 969
> >>1330 187 4984
> >>1740 78 6062
> >>
> >>The test program was run on both a workstation using net8 and locally on
> >the
> >>server that is running the database using a local connection (no network
> >>involved) and the results were similiar.
> >>
> >>The server is a 2 way 200 mhz intell box with 512 meg ram 80 gig hd space.
> >>Can someone please explain some areas that I can look at in order to
> >improve
> >>the performance of reading the result set?
> >>
> >>Thanks
> >>-mike
> >>
> >>
> >
> >
>
>

--
Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue May 04 1999 - 15:06:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US