Maximizing
SQL*Loader Performance
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.
For 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 row.
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 concurrently.
$ 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
unrecoverable
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
later.
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.
Also see:
|
|
 |
|
Get the Complete
Oracle Utility Information
The landmark book
"Advanced Oracle
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
buy it
for 30% off directly from the publisher.
|
|
Market Survey of SSD vendors for
Oracle:
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 2015, 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:
| manufacturer |
model |
technology |
interface |
performance metrics and notes |
|
IBM |
RamSan-400 |
RAM SSD |
Fibre
Channel
InfiniBand
|
3,000MB/s random
sustained external throughput, 400,000 random IOPS |
|
Violin Memory |
Violin 1010 |
RAM SSD
|
PCIe
|
1,400MB/s read,
1,00MB/s write with ×4 PCIe, 3 microseconds latency |
|
Solid Access Technologies |
USSD 200FC |
RAM SSD |
Fibre Channel
SAS
SCSI
|
391MB/s random
sustained read or write per port (full duplex is 719MB/s), with
8 x 4Gbps FC ports aggregated throughput is approx 2,000MB/s,
320,000 IOPS |
|
Curtis |
HyperXCLR R1000 |
RAM SSD |
Fibre Channel
|
197MB/s sustained
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.:
 for
SSD support details. DRAM SSD
vs. Flash SSD
With all
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
|
|
Platter disk
|
10.0 ms.
|
7.0 ms.
|
|
DRAM SSD
|
0.4 ms.
|
0.4 ms.
|
|
Flash SSD
|
1.7 ms.
|
94.5 ms.
|
|
 |
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. |
|
