Oracle Performance - Oracle Essentials - page 196

should also take care to write their SQL statements so that they can be easily reused, by
specifying bind variables instead of different hardcoded values in their SQL statements.
Memory for sorting within the PGA
Each server process uses memory in its PGA for sorting rows before returning them to
the user. If the memory allocated for sorting is insufficient to hold all the rows that need
to be sorted, the server process sorts the rows in multiple passes called runs . The inter‐
mediate runs are written to the temporary tablespace of the user, which reduces sort
performance because it involves disk I/O.
Sizing the sort area of the PGA was a critical tuning point in Oracle Database releases
prior to Oracle Database 10 g . A sort area that was too small for the typical amount of
data requiring sorting would result in temporary tablespace disk I/O and reduced per‐
formance. A sort area that was significantly larger than necessary would waste memory.
Since Oracle Database 10 g , Oracle provides automatic sizing for the PGA. By default,
this memory management is enabled, and sizing for PGA work areas is based on 20
percent of the SGA memory size. By using automatic sizing for the PGA, you eliminate
the need to size individual portions of the PGA, such as SORT_AREA_SIZE.
Oracle Database 11 g introduced automatic memory management that spans both the
SGA and the PGA. By setting a single MEMORY_TARGET initialization parameter
(given that the PGA size can be based on a percentage of the SGA memory size), the
PGA and SGA will be automatically set to appropriate initial values. Oracle then tunes
memory for optimal SGA and PGA performance on an ongoing basis.
Oracle and CPU Resources
The Oracle Database shares the CPU(s) with all other software running on the server.
If there is a shortage of CPU power, reducing Oracle or non-Oracle CPU consumption
will improve the performance of all processes running on the server.
If all the CPUs in a machine are busy, the processes line up and wait for a turn to use
the CPU. This is called a run queue because processes are waiting to run on a CPU. The
busier the CPUs get, the longer processes can spend in this queue. A process in the queue
isn't doing any work, so as the run queue gets longer, response times degrade.
You can use the standard monitoring tools for your particular operat‐
ing system to check the CPU utilization for that machine.
Tuning CPU usage is essentially an exercise in tuning individual tasks by reducing the
number of commands required to accomplish the tasks and/or reducing the overall
number of tasks to be performed. You can do this tuning through workload balancing,