Home » RDBMS Server » Server Utilities » Sql Loader Performance (Oracle 11G, Unix)
Sql Loader Performance [message #596413] Sun, 22 September 2013 15:23 Go to next message
nagaraju.ch
Messages: 103
Registered: July 2007
Location: bangalore
Senior Member
Hi

I have numerous text files(5000 files) which should be loaded into oracle table. Below is the oracle table DDL. It is taking more 2 hours to load the data. I am using Sql Loader to load these files into Oracle table. Could some one suggest if i could improve the performance of the sqlloader.

DDL
CREATE TABLE ERS_CERNER_SRC
(
  INSTALLATION_ID          VARCHAR2(50 BYTE)        NULL,
  TRANSACTION_ID           VARCHAR2(50 BYTE)        NULL,
  SERVER_ID                VARCHAR2(50 BYTE)        NULL,
  CLINICAL_TRANSACTION_ID  VARCHAR2(255 BYTE)       NULL,
  RESPONSE_TIME            NUMBER(10,3)             NULL,
  TRANSACTION_START_TIME   TIMESTAMP(9)             NULL,
  TRANSACTION_END_TIME     TIMESTAMP(9)             NULL,
  LOCATION_ID              VARCHAR2(50 BYTE)        NULL,
  FILE_NAME                VARCHAR2(100 BYTE)       NULL,
  STATUS                   VARCHAR2(1 BYTE)         NULL,
  ETL_DATE                 DATE                     NULL
)
PARTITION BY RANGE (ETL_DATE) 
(  
  PARTITION SRCtbl_20130901 VALUES LESS THAN (TO_DATE(' 2013-09-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS,  
  
  PARTITION SRCTBL_MAXVALUE VALUES LESS THAN (MAXVALUE)
    LOGGING
    NOCOMPRESS
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;

CREATE INDEX ETL_SRC.ERS_CERNER_SRC_IDX ON ERS_CERNER_SRC
(INSTALLATION_ID, CLINICAL_TRANSACTION_ID, LOCATION_ID)
LOGGING
NOPARALLEL;


Sql Loader Script:

        OPTIONS (DIRECT=true,ERRORS=9999999, SILENT=(HEADER,FEEDBACK))
        load data
        BADFILE '/ersdg3/ERS/ERS_INPUT_LOGS/CERNER/badfiles/DOMAIN11_LPAR1102_slainterval091713_ra71cmi102_16_15_00.csv'
        DISCARDFILE '/ersdg3/ERS/ERS_INPUT_LOGS/CERNER/discardfiles/SRC_102_16_15_00.csv'
        append into table ERS_CERNER_SRC
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        TRAILING NULLCOLS
        (FILLER1 BOUNDFILLER TERMINATED BY ',' ,
        FILLER2 BOUNDFILLER TERMINATED BY ',' ,
        SERVER_ID CHAR,
        CLINICAL_TRANSACTION_ID  CHAR,
        RESPONSE_TIME DECIMAL EXTERNAL,
        STATUS          CHAR  "UPPER(:status)",
        COLUMN1 FILLER CHAR ,
        COLUMN2 FILLER CHAR ,
        COLUMN3 FILLER CHAR ,
        COLUMN4 FILLER CHAR ,
        COLUMN5 FILLER CHAR ,
        COLUMN6 FILLER CHAR ,
        COLUMN7 FILLER CHAR ,
        COLUMN8 FILLER CHAR ,
        COLUMN9 FILLER CHAR ,
        COLUMN10        FILLER CHAR ,
        COLUMN11        FILLER CHAR ,
        COLUMN12        FILLER CHAR ,
        FILE_NAME CONSTANT  "SRC_102_16_15_00.csv",
        TRANSACTION_END_TIME "TO_TIMESTAMP(to_char(to_date(:FILLER1,'mm/dd/yy'),'mm/dd/yyyy') || ' ' || :FILLER2, 'MM/DD/YYYY HH24:MI:SSXFF')",
        TRANSACTION_START_TIME "TO_TIMESTAMP(to_char(to_date(:FILLER1,'mm/dd/yy'),'mm/dd/yyyy') || ' ' || :FILLER2, 'MM/DD/YYYY HH24:MI:SSXFF')  - NumToDsInterval(:RESP
ONSE_TIME,'SECOND')",
        LOCATION_ID  "substr('DOMAIN11_LPAR1102_slainterval091713_ra71cmi102_16_15_00.csv',INSTR('DOMAIN11_LPAR1102_slainterval091713_ra71cmi102_16_15_00.csv','_',1,1)
+1,INSTR('DOMAIN11_LPAR1102_slainterval091713_ra71cmi102_16_15_00.csv','_',2,1) -1)",
        INSTALLATION_ID "substr('DOMAIN11_LPAR1102_slainterval091713_ra71cmi102_16_15_00.csv',1,INSTR('DOMAIN11_LPAR1102_slainterval091713_ra71cmi102_16_15_00.csv','_',
1,1) -1)",
        TRANSACTION_ID "'AUTO-'||prt_trs_id_seq.nextval",
        ETL_DATE "SYSDATE"
)


Thanks in advance.
icon14.gif  Re: Sql Loader Performance [message #596415 is a reply to message #596413] Sun, 22 September 2013 15:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use external table.

Re: Sql Loader Performance [message #596416 is a reply to message #596415] Sun, 22 September 2013 16:01 Go to previous messageGo to next message
nagaraju.ch
Messages: 103
Registered: July 2007
Location: bangalore
Senior Member
You mean the target table should be External table?

Also, I just tested load without DIRECT=true clause in CONTROL file. It is faster. How come this is faster as DIRECT method directly loads into datafiles and should be quicker?

Any suggestions, if i need to modify my table DDL

Stats were
DIRECT=true
Duration = 1.10hrs
No. of Files = 1000

nO DIRECT METHOD
Duration = 5 Mins
No. of Files = 1000

[Updated on: Sun, 22 September 2013 16:06]

Report message to a moderator

icon2.gif  Re: Sql Loader Performance [message #596417 is a reply to message #596416] Sun, 22 September 2013 16:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No, I meant load from an external table, the link will lead you to the ages explaining what is such tables and how to efficiently use them.
Re: Sql Loader Performance [message #596418 is a reply to message #596417] Sun, 22 September 2013 16:25 Go to previous messageGo to next message
nagaraju.ch
Messages: 103
Registered: July 2007
Location: bangalore
Senior Member
Thanks Michael

but i will have 5000 files every day. How do we extracts from multiple files using external table in Sql Loader?
Re: Sql Loader Performance [message #596419 is a reply to message #596418] Sun, 22 September 2013 16:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
External file or SQL*Loader are different alternatives to accomplish the same task.
One way to handle many files using either approach would be to hard code a single filename & the use OS soft link to redirect to each of the files individually.

icon8.gif  Re: Sql Loader Performance [message #596420 is a reply to message #596418] Mon, 23 September 2013 00:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please DO read the link I gave you.
If you don't you can't use the external table anyway.

[Updated on: Tue, 24 September 2013 23:58]

Report message to a moderator

Re: Sql Loader Performance [message #596457 is a reply to message #596413] Mon, 23 September 2013 06:17 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
nagaraju.ch wrote on Mon, 23 September 2013 01:53

I have numerous text files(5000 files) which should be loaded into oracle table. Below is the oracle table DDL. It is taking more 2 hours to load the data. I am using Sql Loader to load these files into Oracle table. Could some one suggest if i could improve the performance of the sqlloader.


You are using direct path load. You can do it more efficiently, try these parameters along with it -
parallel=true , multithreading=true , skip_index_maintenance=true


After setting those parameters, run the sqlldr and check the sqlldr log, you should see the following things -
Path used: Direct 
Insert option in effect for this table: APPEND 
..............
..............
Total stream buffers loaded by SQL*Loader main thread: xxxx
Total stream buffers loaded by SQL*Loader load thread: xxxx


For reference, have a look at this asktom link, where I had a similar issue with sqlldr not being much efficient and T.Kyte clarified how things works : Multithreading with direct path load

Regards,
Lalit
Re: Sql Loader Performance [message #596465 is a reply to message #596416] Mon, 23 September 2013 10:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Where are you making this call to sqlldr from? Server or client?

Re: Sql Loader Performance [message #596540 is a reply to message #596465] Tue, 24 September 2013 10:15 Go to previous messageGo to next message
nagaraju.ch
Messages: 103
Registered: July 2007
Location: bangalore
Senior Member
I am invoking this through Shell Mahesh
Re: Sql Loader Performance [message #596541 is a reply to message #596457] Tue, 24 September 2013 10:18 Go to previous messageGo to next message
nagaraju.ch
Messages: 103
Registered: July 2007
Location: bangalore
Senior Member
Thanks Lalit,

It worked well. Half of the time is saved. But here is the problem when we use these Parallel and multithreading parameters, I use Sequence to generate unique numbers for every transaction. It is skipping this task. I mean nulls are inserting into that field.

Can't we use Sequences with these parameters?
Re: Sql Loader Performance [message #596574 is a reply to message #596541] Tue, 24 September 2013 16:48 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
nagaraju.ch wrote on Tue, 24 September 2013 20:48

It worked well. Half of the time is saved. But here is the problem when we use these Parallel and multithreading parameters, I use Sequence to generate unique numbers for every transaction. It is skipping this task. I mean nulls are inserting into that field.

Can't we use Sequences with these parameters?


It is not a restriction with parameters Parallel and multithreading, it is a restriction on using Direct path load.

How does a SQL*Loader work in conventional path load? Answer is simple, SQL*Loader simply builds an insert statement to load the data into the table.

So, you cannot create sequences in direct path loads, since, there is no SQL being generated to fetch the next value since direct path does not generate INSERT statements.

Check the documentation, jumt to "Restrictions on Using Direct Path Loads" section - Restrictions on Using Direct Path Loads

Regards,
Lalit

[Updated on: Tue, 24 September 2013 16:49]

Report message to a moderator

Re: Sql Loader Performance [message #596579 is a reply to message #596574] Tue, 24 September 2013 19:57 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Conventional path might be faster because of index maintenance. If you Direct Path load a small volume of data into a large indexed table, the load can be slower than conventional path.

Sounds like maybe conventional path will be better for you.

If you are determined to use Direct Path then follow Michel's advice and investigate External Tables; you will be able to use sequences and Direct Path inserts.

Ross Leishman
Previous Topic: getting count of objects after schema refresh
Next Topic: import
Goto Forum:
  


Current Time: Thu Mar 28 14:56:13 CDT 2024