|
Category :
Database
Resources -> SQL and PLSQL
DB Version
:
Oracle 9i
OS Details :
Sun Solaris9
Bulk
data loading with optimal performance is becoming
an important requirement in all production environments and in this
article, you'll find a few suggestions on improving performances of
bulk data loading and processing.
(1) Partitioning:
Make sure the destination tables
(tables to which data's being loaded in to) are partitioned. If the
destination tables are partitioned then subsets of data can be loaded
at the partition level. Use Create Table As Select (CTAS) statements
with the NOLOGGING clause to create staging tables. Once the tables
have been created, exchange the table with the partition with the
"without validation" clause. This will make the partition swaps
faster.
(2) Tablespaces:
- Make sure the tablespaces on
which the tables and indexes reside are locally managed. Locally
managed tablespaces offer better performance than dictionary managed
tablespaces due to the elimination of recursive calls for space
management. Locally managed tablespaces also removes space
fragmentation.
- Try and pre-estimate the space
usage for your process and pre-allocate the extents for the tables and
the indexes by issuing an "alter table <name> allocate extent"
command.
(3) Storage definition:
Starting 9i, space managements at
the block level can be automated through the use of the automatic
segment space management feature. This feature eliminates the need for
freelists and is defined during the tablespace creation.
(4) Restart ability:
Another interesting feature
available starting 9i is the resumable space management feature. This
features allows the process to resume even though there was a space
issue. If the resumable space management feature's enabled for the
session then the process waits for a specified amount of time for
space to be added and resumes after the space issues have been resolved.
(5) Indexes and
constraints:
If the table to which bulk data's
being loaded to has too many indexes then explore the option of
dropping the indexes and constraints on the table before the load and
re-enabling them after the load has been completed.
(6) SQL Statements:
Make sure the SQL statements
being used by the loading process are fully optimized and the tables
and indexes being used by the statements to fetch data from have valid
statistics in them.
(7) Smaller units of work:
If possible, try and break the
work in to smaller units and make multiple sessions process these
units of work in parallel. For example, if you are designing a data
loading process involving 10 million rows then break it up in to 10
processes with each handling a million rows.
(8) Avoid hard parses:
Make sure the SQL statements
being used as part of the loading process use proper bind variables
which will eliminate hard parses.
|
