Re: processor and ram

humble wrote:

> I want to know the the CPU speed and the amount of RAM become an issue
> for SQL queries.
>
> For example when we run a query which has to do a lot of sorting and if
> the sort_area_size is not properly set, then the RAM might assume a lot
> of importance. But WHEN will the cpu speed be relevant?

CPU speed is not the factor ito of a bottlenecks. CPU capacity is. Okay, not always that clear cut as CPU speed can play a role - like a faster CPU causing a bigger bottleneck (ever seen an account manager fired for that? ;-).

With SQL queries CPU capacity is the primary CPU issue. How many SQLs can be serviced? The more CPUs, the bigger the capacity, the more SQLs can be serviced concurrently.

CPU speed determines - and to some extent - how fast a SQL can be serviced. But usually the limiting factor for a SQL is I/O and not processing speed.

CPU capacity (and speed to a lessor extent) starts to play a role when the code being run is no I/O bound, but processor bound. E.g. running a SQL that contains a very large and very complex math function. Running PL/SQL that does serious number crunching on a very small data set. Etc.

As for your question of when CPU speed and the amount of RAM become an issue for SQL queries:

CPU and RAM become an issue when it becomes an issue. IOW, it is a problem when it is diagnosed as the cause of performance problems. One thing I often harp on is accurate diagnosis of performance issues. A performance problem is not always clear cut - and for example, adding a bigger and badder and faster CPU may just cause even worse performance problems (instead of causing a 100 "performance problems" per second, the new CPU now causes 500 of these). So CPU and RAM will only be problems when accurate performance analysis shows that these are the problems.

CPU and RAM will not become real issues when the code you write is "good" and scalable code. E.g. code using bind variables. Code doing bulk processing (with a limit clause). Sensible SQL hitting a data set only once instead of using multiple passes through the same data set. All the basics that the PL/SQL Reference Guide and Application Developer Fundementals Guide talk about.

And when these do becomes issues despite "good" and scalable code, then CPU and RAM are real issues - which if addressed, will cause actual performance improvements. As oppose to treat the symptoms of a problem of which the actual cause has not been identified.

--
Billy