Oracle SQL*Loader is flexible and offers many options that
should be considered to maximize the speed of data loads. These include:
1. Use Direct Path Loads - The conventional path
loader essentially loads the data by using standard insert statements. The
direct path loader (direct=true) loads directly into the Oracle data
files and creates blocks in Oracle database block format. The fact that SQL is
not being issued makes the entire process much less taxing on the database.
There are certain cases, however, in which direct path loads cannot be used
(clustered tables). To prepare the database for direct path loads, the script
$ORACLE_HOME/rdbms/admin/catldr.sql.sql must be executed.
2. Disable Indexes and Constraints.
conventional data loads only, the disabling of indexes and constraints can
greatly enhance the performance of SQL*Loader.
3. Use a Larger Bind Array. For conventional data
loads only, larger bind arrays limit the number of calls to the database and
increase performance. The size of the bind array is specified using the bindsize parameter. The bind array's size is equivalent to
the number of rows it contains (rows=) times the maximum length of each
4. Use ROWS=n to Commit Less Frequently.
For conventional data loads only, rows specifies the number of rows per
commit. Issuing fewer commits will enhance performance.
5. Use Parallel Loads. Available with direct path
data loads only, this option allows multiple SQL*Loader jobs to execute
$ sqlldr control=first.ctl parallel=true direct=true
$ sqlldr control=second.ctl parallel=true direct=true
6. Use Fixed Width Data. Fixed width data format
saves Oracle some processing when parsing the data. The savings can be
tremendous, depending on the type of data and number of rows.
7. Disable Archiving During Load.
While this may
not be feasible in certain environments, disabling database archiving can
increase performance considerably.
8. Use unrecoverable. The
option (unrecoverable load data) disables the writing of the data to the redo
logs. This option is available for direct path loads only.
Please note that the UNRECOVERABLE option has been deprecated and replaced
with the NOLOGGING option.
Import Speed Benchmarks:
Oracle guru Steve
Callan notes that he has run parallel Data Pump import jobs to load 1.8
terabytes (1,800 gigabytes) in less than a day, an import load rate of over
75 gigabytes per hour.
“The target box was an AIX 5L using LPARS,
pretty sure it was 32 CPU and 64GB RAM. There were several distinct schemas,
so the data pump export/import was a matter of divide and conquer (i.e.,
stream several jobs/sessions). I think the largest chunk was around 900GB.
Statistics gathering took a while, but that time was
separate from the actual import, and some indexes were skipped/re-built
An old import version of this lasted right around
three days, and the data pump version was about a third of that time.”
As we see, the import speed is far greater using the newer Data Pump
import utility (impdp) and imports run faster of faster servers.
Get the Complete
Oracle Utility Information
The landmark book
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.
Market Survey of SSD vendors for
There are many vendors who offer rack-mount solid-state disk that
work with Oracle databases, and the competitive market ensures that
product offerings will continuously improve while prices fall.
SearchStorage notes that SSD is will soon replace platter disks and that
hundreds of SSD vendors may enter the market:
"The number of vendors in this category could rise to several
hundred in the next 3 years as enterprise users become more familiar
with the benefits of this type of storage."
As of January 2013, many of the major hardware vendors (including Sun and
EMC) are replacing slow disks with RAM-based disks, and
Sun announced that all
of their large servers will offer SSD.
Here are the major SSD vendors for Oracle databases
(vendors are listed alphabetically):
2008 rack mount SSD Performance Statistics
SearchStorage has done a comprehensive survey of rack mount SSD
vendors, and lists these SSD rack mount vendors, with this showing the
fastest rack-mount SSD devices:
||performance metrics and notes
sustained external throughput, 400,000 random IOPS
1,00MB/s write with ×4 PCIe, 3 microseconds latency
Solid Access Technologies
sustained read or write per port (full duplex is 719MB/s), with
8 x 4Gbps FC ports aggregated throughput is approx 2,000MB/s,
R/W transfer rate, 35,000 IOPS
Choosing the right SSD for Oracle
When evaluating SSD for Oracle databases you need
to consider performance (throughput and response time), reliability (Mean Time Between failures) and
TCO (total cost of ownership). Most SSD vendors will provide a
test RAM disk array for benchmark testing so that you can choose the
vendor who offers the best price/performance ratio.
Burleson Consulting does not partner with any SSD vendors and we
provide independent advice in this constantly-changing market. BC
was one of the earliest adopters of SSD for Oracle and we have been
deploying SSD on Oracle database since 2005 and we have experienced SSD
experts to help any Oracle shop evaluate whether SSD
is right for your application. BC experts can also help you choose
the SSD that is best for your database. Just
call 800-766-1884 or e-mail.:
SSD support details.
vs. Flash SSD
the talk about the Oracle “flash cache”, it is important to note that there
are two types of SSD, and only DRAM SSD is suitable for Oracle database
storage. The flash type SSD suffers from serious shortcomings, namely
a degradation of access speed over time. At first, Flash SSD is 5
times faster than a platter disk, but after some usage the average read time
becomes far slower than a hard drive. For Oracle, only rack-mounted
DRAM SSD is acceptable for good performance:
Avg. Read speed
Avg. write speed
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.