Re: Is Oracle Simply a Pig - The Worst I've See

In article <Dec.1.09.56.40.1993.11573_at_andromeda.rutgers.edu> holowcza_at_andromeda.rutgers.edu (Richard D Holowczak) writes:
>mloennro_at_se.oracle.com (Magnus Lonnroth) writes:
>
>>>>>>> "Jim" == Jim Kramer <kramer_at_ash.sps.mot.com> writes:
>
>
>> Jim> Yes! Oracle is a GIANT SQUEELER! However, I'm pretty sure
>> Jim> that all relational databases are. Remember you're dealing
>> Jim> with something that spends all of it's time talking to the
>> Jim> disk! This is why DB tuning is SOOOOO IMPORTANT. The other
>> Jim> thing you can do is buy really fast disk drives (fast & wide
>> Jim> SCSI, etc). You really do have to select the right
>> Jim> computer/peripherals for the job. This ain't no DISKO, ya
>> Jim> know!
 

>> Jim> Jim Kramer Motorola
 

>>The original poster was complaining about excessive memory demands
>>by Oracle front-end processes - not I/O contention. Oracle should
>>never be I/O bound, it should always be CPU bound - that's our whole
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
>>strategy. CPUs get cheaper and faster all the time, disks don't (at
>>least not yet).
>
> This is news to me! As I pointed out in my last posting,
> PC Magazine did a test of SQL databases. They found the CPU
> usage of Oracle to be quite low compared to other DBs. Their
> conclusion was that Oracle (on Netware at least) was I/O
> bound due to the limitations of the server's architecture.
>
> I take all such tests with a grain of salt. Can anyone
> confirm or refute these contraints ?
>
>Rich Holowczak
>Rutgers University
>holowcza_at_andromeda.rutgers.edu

I really didn't want to get into discussion over this, but maybe a few words would do:

Generally speaking stating that Oracle is or is not I/O bound, in my opinion, is not entirely correct. In fact, the way the Oracle 6 and 7 architecture are designed, they minimize DBMS I/O activities tremendously by piggy backing writes and the use of asynchronous write-aheads and other usefule techniques. However, notice that I say "RDBMS I/O" and not application I/O. There is a lot to be said about tuning you application and database to avoid I/O contention. I'm sure most people know the basic segragation of data/index etc. But in versions 6 and 7, log files are actually the main I/O bottleneck  and special attention must be given to separating them from updateable data as well as the size of the files.

My experience has shown that with proper tuning you can pretty well eliminate all unnecessary I/O in Oracle. Achieving a cache hit ratio of 98-99% is not unusual even for large databases. The trick is that this requires a lot more memory than most Oracle manuals or 3-rd party books normally suggest. CPU, well lets leave that to some other time as it is quite late at night here.

In brief, consult an Oracle expert for DB and Appl. tuning and minimize your overall I/O but be ready to bite the bullet on HUGE amounts of memory. Oracle 7.0 has not yet convinced me that it has less of an apetite for memory, but test are continuing.

Regards,

S. Tedjarati
Unisys Hong Kong and China Limited Received on Wed Dec 01 1993 - 16:46:44 CET