| Oracle Answers
|
 |
Optimizing
database performance with parallel Oracle
Feb 15, 2001
Donald Burleson |
|
| |
In "Using
parallel Oracle for high-speed e-commerce systems," I
explained how Oracle's parallel query and Parallel Server
features can help you build a better e-commerce system. Now it's
time to turn our attention to some additional approaches for
achieving that speedy e-commerce system.
Oracle parallel query is very important as Oracle database move onto
SMP servers with 8, 16, 32 and 64 CPU processors. On these
servers, the speed of full-table scans and index fast-full scans can be
greatly improved:
Invoking Parallelism
There are several ways to invoke Oracle parallel query, and some of
them are very dangerous because they influence the costs assigned to
full-table scans.
- System-level parallelism - Setting the
parallel_automatic_tuning parameter may cause the
cost-based optimizer to perceive full-table scans as cheaper.
- Session-level parallelism - Using the alter session
force parallel query syntax.
- SQL-level parallel query - This parallel hint is the
preferred method for invoking parallel query, selectively, based on
the operation:
select /*+
parallel (c, 31) (e, 31) */ . . . .
Parallel DBA operations
With 24/7 e-commerce databases on the Web, the
Oracle DBA is challenged to perform maintenance duties as fast as
possible. Oracle has several tools that can implement the
parallelization approach to improve manageability and speed up
the processing performance of Oracle database administration.
There are several areas where the DBA can use parallel operations
to speed Oracle maintenance.
- Parallelize the Oracle backup
The time required for backups can be both multiplexed
(through Oracle's Enterprise Backup Utility [EBU] or
Recovery Manager [RMAN]) and parallelized (with such
third-party tools as Legato, ADSM, or Veritas) to speed
the elapsed time for the backup. Creating a separate
backup thread for each available tape drive will
accomplish this. Hence, a backup that takes eight hours
with one tape drive will only take 30 minutes with 16
tape drives.
- Parallelize the index rebuilds
Oracle indexes become out-of-balance and inefficient in
highly active e-commerce databases. It is possible to
submit many index rebuild tasks concurrently by
performing parallel ALTER INDEX REBUILD commands. This
can also be done within an OPS environment without
excessive pinging, so long as the Web servers are
disconnected from the database.
- Parallelize the table reorganizations
Oracle tables will fragment and become less efficient
over time, and the DBA sometimes needs to reorganize the
tables to improve performance. Before Oracle9i, if you are reorganizing
the Oracle tables to re-sequence the rows, you can't use
parallelism with the CREATE TABLE AS SELECT (CTAS)
statement because you need to use an index hint to force
the sequence of the rows. You can't use an index hint and
a parallel hint in the same statement. However, you can
submit concurrent CTAS tasks, one for each table, such
that the elapsed time for the reorganization becomes the
maximum time required for the largest table.