Operating System - HP-UX
1748086 Members
4877 Online
108758 Solutions
New Discussion юеВ

Re: sql loader performance

 
Edgar_10
Frequent Advisor

sql loader performance

Hi,

I have a sql loader performance problem, the window period to complete a load of 210 files(14500 rows each) takes approx.12 hrs.
Any ideas how I could bring the load time down?

The tables being loaded to are indexed; partitioned & NOLOGGING enabled,the load method is conventional.

Thanks in advance.
17 REPLIES 17
Tom Geudens
Honored Contributor

Re: sql loader performance

Hi Dean,
I think we need more information :
- type of system
- type of database
- ...

For example, for oracle I would say to use "direct" load (and rebuild the indexes afterwards).

Regards,
Tom Geudens
A life ? Cool ! Where can I download one of those from ?
twang
Honored Contributor

Re: sql loader performance

If you want to optimize performance for SQL*Loader here are a few things
to consider (for direct and conventional paths):


o Make logical record processing efficient.

- One-to-one mapping of physical to logical records. Avoid continueif
and concatenate.

- Make it easy for the software to figure out physical record
boundaries. That is, use the file processing option string "FIX
nnn" or "VAR". If you use the default (stream mode), on most
platforms (e.g., UNIX) SQL*Loader has to scan each physical record
for the terminating newline character.


o Make field setting efficient.

Field setting is the process of mapping the "fields" in the datafile
to their corresponding columns in the database. The mapping function
is controlled by the description of the fields in the control file.
Field setting is the biggest consumer of CPU time for most loads.

- Avoid delimited fields; use positional fields. If you use
delimited fields, SQL*Loader has to scan the input data looking
for the delimiter(s)/enclosure(s). If you use positional fields,
SQL*Loader just increments a pointer to get to the next field
(very fast).

- If you are using positional fields, avoid trimming white space.
That is, use PRESERVE BLANKS.


Note that a common theme in points 1 and 2 above is to avoid scanning
the input data.


o Make conversions efficient.

There are several conversions that SQL*Loader does for you;
character set conversions and datatype conversions.

- Avoid character set conversions if you can. SQL*Loader supports
three character sets:

a) Client character set (NLS_LANG of the sqlldr process.)
b) Server character set.
c) Datafile character set.

Performance is optimized if all three are the same, most importantly
b) and c). Also, memory for character set conversion buffers is not
allocated if these are the same.

- Avoid multi-byte character sets if you can.

- As for datatype conversions (SQL*Loader datatype to database column
datatype), char to char is efficient if the same character set is in
use for the datafile and the server. That is, no conversion is fast.
Therefore, try to minimize the number of conversions that you have
to do.


o If you can, use the "unrecoverable" option on direct path loads.


o Even for conventional path loads, always run SQL*Loader directly on the server rather than across a network.
.
Edgar_10
Frequent Advisor

Re: sql loader performance

Hi Tom,

The server is a Sun server runin Oracle 8.1.7.0.0. I dont want to use direct mode since I have daily partitions & indexes as well as users querying the tables.

Thanks!
Edgar_10
Frequent Advisor

Re: sql loader performance

Hi Twang,

Thanks for the input. The data to be loaded is extracted from a CSV file and not all the felds within a row are populated so using positional functionality is not viable. Do you know what the syntax is for specifying "NOLOGGING" for a sql load?

Thanks
Yogeeraj_1
Honored Contributor

Re: sql loader performance

hi,

Sounds like

o archives filled up or
o checkpoint not complete (you can look at the alert log to check those, look for cannot allocate new log)
o there is a unique index on the table and something else is playing with the table -- blocking you (they inserted a row you wanted to)
o the table is locked (is is a child table in a foreign key relationship without indexes on the fkey?)

Are you using locally managed tablespaces?

hope this helps!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Edgar_10
Frequent Advisor

Re: sql loader performance

Hi Yogeeraj,

Thanks for input. The database is running in "NOACHIVE" mode. The tables have nonunique idexes and there are no foreign keys on tables.

Thanks.
twang
Honored Contributor

Re: sql loader performance

The operations can make use of no-logging mode for SQL*Loader is "direct load":
% sqlldr / load.ctl DIRECT=TRUE

This is a summary of the features and restrictions of the DIRECT PATH option:
-Cannot load clustered tables.

-There can be no active transactions in the loaded tables.

-There can be no SQL functions used in the control file.

-If the target table is indexed, then no SELECT statements may be issued
against the table during the load.

Tom Geudens
Honored Contributor

Re: sql loader performance

Hi again,
A datawarehouse by any chance ;-) ? Ok, I understand the "qualifications". It is however always a tradeoff. If you're not allowed to use the tricks, your users will have to wait longer before having the data available. If you can use the tricks, there'll be a small downtime for your users.

Here's what I would do (having your options). I believe (check this information) you can turn the indexes to "unusable". In that state they are not updated during a load (reducing the impact for the load). Your users will get "hit" too, since their queries will be a lot less performant. After the the loads you rebuild the indexes.

Hope this helps,
Tom Geudens
A life ? Cool ! Where can I download one of those from ?
Edgar_10
Frequent Advisor

Re: sql loader performance

Hi Tom,

Thanks for the input, and yes its sort of a data warehouse database that for 1 day could contain approx.20 million records. I will test the load with the idexes disabled and assess load time, despite the impact users will experience.

Regards