|
Category :
Database
Resources -> RDBMS Server
DB Version
:
Oracle 8i /
Oracle 9i
OS Details :
Sun Solaris9
Companies
today want to buy machines with high-powered CPU configurations and
large amounts
of memory. This allows for high processing speeds and capability to
cache large amounts of data. But all the data that's being cached and
processed by these high powered CPUs have to be at some point in time
written to disks or read from the disks and that's when bottlenecks
occur. To counter the IO bottlenecks, oracle software and the database
has in built features and parallel query is one feature that allows a
large unit of work to be broken up in to multiple smaller units.
What is parallel query?
When we have to handle a lot
of data in operations, like full table scans of large tables, or the
creation of large indices, we can divide the work. Instead of using a
single process for one statement, the work can be spread to multiple
processes. This is called parallel execution or parallel processing.
Parallel execution normally reduces response time when you are
accessing large amount of data, but it is not always faster than a
serial operation.
Parallel execution is useful for many types of operations like:
- Queries requiring large table scans,
joins, or partitioned index scans
- Creation of large indices and large tables
- Bulk inserts, updates and deletes
- Aggregations
These are some high-level disk
configuration suggestions made by Oracle on improving the performance
of parallel queries:
A RAID 1
(mirroring) disk configuration offers more tuning options than RAID
0+1 (striping+mirroring) because objects can be manually striped
across multiple physical devices. Arguably, RAID1 takes a
bit more administration because of the manual striping effort, but the
resulting performance is worth it.
For a
fixed quantity of disk capacity, more smaller mount points are likely
to perform better than a few large ones. For example, for a
700GB disk configuration, 10 - 70GB disks will perform better than 4 -
180GB disks. Having more disks reduces physical disk contention.
Adding
high throughput disk controllers (eg. Fiber Channel) will increase
throughput between the disk subsystem and the host. PX utilizes
Direct Path operations whenever possible. This typically occurs
when performing full table and partition scans, and when specified on
INSERT statements. In the absence of the buffer cache
bottleneck, remarkably high I/O throughput rates can be achieved if
the I/O subsystem is properly designed and configured.
For a
fixed quantity of CPU capacity, more less powerful CPUs are likely to
perform better than fewer more powerful ones. For a fixed amount
of performance (eg. SpecINT92, SPECbase, SPECrate), if the choice is
between a 4-CPU or a 12-CPU configuration, the 12-CPU configuration is
likely to perform better by reducing CPU contention. The
assumption here is that there is adequate bus bandwidth to support the
higher
number of CPUs.
|
