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

In article <1993Dec1.084410.1443_at_eisner>, comarow_at_eisner.decus.org (Bob Comarow) writes:
> In article <MLOENNRO.93Dec1114426_at_demo1.se.oracle.com>, mloennro_at_se.oracle.com (Magnus Lonnroth) writes:
>

 ... stuff deleted ...
>
> Actually, my concern was the amount of CPU required for the tiny amounts of
> I/O. Some of the question's posted by our DB group involved questions
> that involved memory.
>
> But, just a few Oracle users ate up the CPU of a 6630. A few Oracle report
> writers ate up a 4000-400.
>
> And, yes, we have had Oracle consultants tell us our system is well tuned,
> the DB is well tuned, and we've had tons of memory thrown at the beast.
> Our SGA's are huge. Our working sets incredible. that was all documented
> in the original note.

A well tuned O/S and a well tuned database (internals) will give you about a 10% improvement in throughput on a badly tuned O/S and database combination.

Tuning an application's database access can give you a 1% to 1000+% percent improvement.

Tuning Oracle is a balancing act. You balance I/O vs CPU vs Memory. One of them will be your bottleneck no matter what you do. What you need to do is push each as close to "the wall" (85% on a VAX) as possible while ensuring that CPU becomes your bottleneck.

You mention giving it a lot of memory (in SGA) and running out of CPU. This simply means that you've reduced your database I/O at the expense of using more memory and hitting the wall on CPU.

If you have a large SGA and each process is using bulk CPU then it sounds like your doing inefficient row scans (or full table scans) and most of them are in the SGA buffers.

If this is the case then it's your application that needs tuning.

Have you turned on trace and timing for your database and then run the trace files through the TKPROF utility? This gives excellent feedback on how each database call is being processed. Also shows the execution path and what indexes are being used.

ReportWriter can be a dog if you haven't written the report with performance in mind.

> Bob Comarow
> Grumman Melbourne System's Division

I think I said some of this before but not directly to this news group.

Contact me if you want more specific advice.

-- 
Bruce...        pihlab_at_cbr.hhcs.gov.au

"If you swallow a live frog first thing in the morning ...
 Nothing worse will happen to either of you for the rest of the day."

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of                                      * 
*          Health, Housing, Local Government & Community Services *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************