- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- sql loader performance
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-15-2003 10:03 PM
тАО07-15-2003 10:03 PM
sql loader performance
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-15-2003 10:30 PM
тАО07-15-2003 10:30 PM
Re: sql loader performance
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-15-2003 10:34 PM
тАО07-15-2003 10:34 PM
Re: sql loader performance
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.
.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-15-2003 10:37 PM
тАО07-15-2003 10:37 PM
Re: sql loader performance
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-15-2003 11:00 PM
тАО07-15-2003 11:00 PM
Re: sql loader performance
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-15-2003 11:09 PM
тАО07-15-2003 11:09 PM
Re: sql loader performance
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-15-2003 11:26 PM
тАО07-15-2003 11:26 PM
Re: sql loader performance
Thanks for input. The database is running in "NOACHIVE" mode. The tables have nonunique idexes and there are no foreign keys on tables.
Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-15-2003 11:28 PM
тАО07-15-2003 11:28 PM
Re: sql loader performance
% sqlldr
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-15-2003 11:38 PM
тАО07-15-2003 11:38 PM
Re: sql loader performance
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-15-2003 11:46 PM
тАО07-15-2003 11:46 PM
Re: sql loader performance
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