Re: How to measure the speed

In article <364s83$oei_at_netnews.upenn.edu> ling_at_underground.irhe.upenn.edu (Mark Ling) writes:
>Hi , there:
> I have a very simple question: My client asked me that How long I need to
>search one recorder from a 500*half million data table in Oracle data base,
>the Oracle was installed in 486DX66 server which is hooked up by ether net.
>My client's bottom line is 3 second, is it possible? how can I know whether
>it's possible or not? If it is impossible, updating the computer server, say
>using Pentinm 100 , can reach that goal(3 second for searching one
>recorder)?
>
> Thank you for your help in advance.
>

Well, this is not a simple question. First of all, I assume you mean record. In relational databases, we call those rows. So you are asking, "How long will it take to find any random record out of a 500,000 row table?"

The answer is, not only do I not know, but there isn't even any theory to figure it out. The reason for that is there is no time in relational theory. What has been proven is, that does not mean other theoretical models will provide better performance. The question you have asked presupposes a hierarchical model - there you can use various queueing theories to figure out how long it will take. Basically, you would figure out how long it takes to get a particular record, including time for head movement and disk rotational latency and all that bs.

With Oracle, there are many more variables that can affect what is happening - some are tunable parameters, some are built-in tricks. So, if you are the only one on the database, and ask for a particular record, the chances are quite likely that the database will have to go out to disk to get part of an index, go back out to disk to get another part of the index, then go back out to the disk again to actually get the row. Or worse. So then, that situation would be limited by disk speed.

BUT! And I say again, BUT! That is a completely unlikely, artificial situation (if that is the actual situation, you might as well get some stupid PC database). In the real world of multiuser databases, you are more likely to have some users pounding the db in a semi-random update fasion, while others select groups of records to do reports on, etc. It is in this complicated scenario that Oracle will be amazingly fast. I say that for this reason: One of the things Oracle does, is buffer the records, er, rows in an area of memory. Things that are in memory and ignored eventually leave, while things that are accessed a lot tend to stay. So, since several people accessing the same table with the same program can be using the same index, that index could stay in memory. That could even happen with some of the rows. So, could Oracle find a row in 3 seconds? Heck, it could find it in milliseconds if it doesn't have to go to the disk! And note, in that scenario it actually runs faster with several users than one user, on the average!

So, if your requirement is a GUARANTEED access in 3 seconds, sorry, it can't be done. If your requirement is most accesses in 3 seconds, the answer is, "it depends..." On how much memory you have, more than anything, how many users, how often they are updating, how often they are inquiring, what else is going on, how well the application is designed and how well the design is executed. Among other things. Some situations will be cpu-bound, some disk-bound, some memory-bound, and the only way to tell if a particular configuration will work is empirically. A corollary is that, with several different access types (reports, update screens, batch updates), one is bound to be much slower.

Sorry if this isn't what your client wants to hear. Personally, if I have a situation where performance is an issue, I'd go right to a sparc. That way you don't have to deal with a low ceiling of Intel performance.

-- 
Joel Garry           joelga_at_amber.rossinc.com            Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.  
panic: ifree: freeing free inodes...