Oracle PL/SQL Programming, Third Edition

Hardware for PL/SQL: Bigger = Better?

Let’s close out this chapter with a few observations about server hardware and its effect on PL/SQL performance. Typically, database server hardware is one of the following:

• A single-processor computer

• A symmetric multiprocessor (SMP) computer

• A “clustered” computer

Oracle, like any software that runs on SMP or clustered machines, won’t automatically benefit from extra processors. Only when the software is carefully crafted to delegate computing tasks to different CPUs will those extra processors pay off. The smart programmers at Oracle have done a lot of work to enable the server to exploit multiprocessing hardware.

Not all Oracle editions have this capability, though. To take advantage of SMP hardware, you must be using either the Enterprise edition or the Personal edition of Oracle. The one exception to this rule has to do with bulk data loads; all editions of Oracle are capable of parallelizing loads on SMP machines. To take advantage of clustered computers, you must not only be running Oracle’s Enterprise edition, you must also license an extra option called Real Application Clusters (RAC) in Oracle9i, or Oracle Parallel Server (OPS) prior to that.

But will the extra expense translate into better PL/SQL performance? Let’s look at the three major processor families.

The Single-Processor Variation

If you’re a lone developer running Oracle on a desktop machine, you’re probably running Oracle on a single-processor computer. In this configuration, ...