Home » RDBMS Server » Server Utilities » Optimization of sqlldr performance (sqlldr version 19.3.0.0.0)
Optimization of sqlldr performance [message #683656] Wed, 10 February 2021 13:26 Go to next message
bgraupe
Messages: 25
Registered: July 2009
Location: Windhoek Namibia
Junior Member
Good day,

I am trying to optimize the sqlldr loading process. I am running the sqlldr process from a Redhat Linux server and inserting data into Oracle relational database. The process is slow and I need to improve the loading. I have done research on this topic and did some changes to the sqlldr parameters. I am still not able to load the required number of files per day. On average I need to load 17,000 files per day. File sizes vary between 1.5M and 5M per file. Most of the files are 1.5M
I am using the conventional path load. (Cannot use direct path). Each raw file on the server is processed into one parent as well as three child tables (to be inserted into the Oracle DB).

current parameter values
sqlldr userid=xxxxx/xxxxx@xxxxx control=fgcharge.dat log=$logfile discard=$CUR_DIR$oldfilename.discard bad=$CUR_DIR$oldfilename.bad silent=feedback errors=1000000 bindsize=20000000 readsize=20000000 rows=100000

I manage to load about 14,000 files per day with the above sqlldr command. 9 files per minute. To manage the number of files per day that I need to process, I have to load about 12 files per minute.

There must be a bottleneck somewhere in the process to get the files on the server, convert it from HEX to ASCII (this is very fast - milliseconds) and use sqlldr to insert the records into the Oracle database. I am not a DBA or Networks specialist. I requested all relevant information for my teams. Please see all the information in the attachment.

Any advise will be appreciated.

Kind regards

Re: Optimization of sqlldr performance [message #683661 is a reply to message #683656] Thu, 11 February 2021 06:34 Go to previous messageGo to next message
John Watson
Messages: 8618
Registered: January 2010
Location: Global Village
Senior Member
If you can post an AWR or Statspack report that would help to identify where the problem might be.

What are you doing with the rows once you have loaded them? If there is any sort of post-load processing, then reading the files through an external table rather than with SQL*Loader would save a lot of time.
Re: Optimization of sqlldr performance [message #683663 is a reply to message #683661] Thu, 11 February 2021 09:26 Go to previous messageGo to next message
bgraupe
Messages: 25
Registered: July 2009
Location: Windhoek Namibia
Junior Member
Good day,

please see AWR report for the past 7 days attached.

"What are you doing with the rows once you have loaded them? If there is any sort of post-load processing, then reading the files through an external table rather than with SQL*Loader would save a lot of time."
The data is inserted into the Oracle DB into relational tables (parent with 3 child tables) and used by business for reporting.

Kind regards
Re: Optimization of sqlldr performance [message #683664 is a reply to message #683663] Thu, 11 February 2021 12:00 Go to previous messageGo to next message
John Watson
Messages: 8618
Registered: January 2010
Location: Global Village
Senior Member
Nothing attached!

As for "what are you doing to the rows", let me try again. Are you doing anything with the rows after you load them into one table and before you read them for reporting?


Re: Optimization of sqlldr performance [message #683666 is a reply to message #683664] Thu, 11 February 2021 21:54 Go to previous messageGo to next message
bgraupe
Messages: 25
Registered: July 2009
Location: Windhoek Namibia
Junior Member
Good day,

the files are coming in as HEX code (telecommunication ANS.1 files), my scripts encode/decode the records within each file from HEX to ASCII(this is done in milliseconds and not an issue) and write output files (One parent and 3 child tables). The sqlldr command is then used to insert the converted records into the DB. (Four sqlldr calls in my script to load the one parent and three child tables). The information in the DB is used for reporting purposes.

I had to split up the report into three due to the size limitation. I will unfortunately do 3 uploads.

Please keep in mind that I am not constantly loaded (which might be seen in stats). I am testing in phases. Whenever there is usage stats available, this is when I am loading.

Kind regards
Re: Optimization of sqlldr performance [message #683667 is a reply to message #683666] Thu, 11 February 2021 21:55 Go to previous messageGo to next message
bgraupe
Messages: 25
Registered: July 2009
Location: Windhoek Namibia
Junior Member
Additional stats files submitted
Re: Optimization of sqlldr performance [message #683668 is a reply to message #683666] Thu, 11 February 2021 21:56 Go to previous messageGo to next message
bgraupe
Messages: 25
Registered: July 2009
Location: Windhoek Namibia
Junior Member
Last stats file attached
Re: Optimization of sqlldr performance [message #683669 is a reply to message #683666] Fri, 12 February 2021 01:32 Go to previous messageGo to next message
John Watson
Messages: 8618
Registered: January 2010
Location: Global Village
Senior Member
I'll keep trying because it is an interesting problem. Once again: what are you doing with these rows when they are loaded? For example, are you loading them into one table, and then copying them into another? Is there ANYTHING done to the rows after SQL*Loader reads them?

For example, just to pick out one high load statement, is this part of the process:

INSERT INTO CDRDBUSER.HD4G_COCCONDITIONS_TB (COCC_DATE, COCC_LOCAL_SEQ_NR, COCC_LINE_NR, COCC_REQ_QOS_INFO, COCC_NEG_QOS_INFO, COCC_UPL, COCC_DOWNL, COCC_CHNG_CONDITION, COCC_CHANGE_DATE, COCC_FAIL_HANDL_CONTIN, COCC_MCC, COCC_MNC, COCC_QCI, COCC_MAXREQ_BANDW_UPL, COCC_MAXREQ_BANDW_DOWNL, COCC_GUAR_BIT_UPL, COCC_GUAR_BIT_DOWNL, COCC_ARP, COCC_APN_AGGR_MAXBIT_UPL, COCC_APN_AGGR_MAXBIT_DOWNL, COCC_MCC2, COCC_MNC2, COCC_TAC, COCC_E_CELL_ID, COCC_GW_ADDRESS) VALUES (CAST(TO_TIMESTAMP(:COCC_DATE, 'rrmmdd hh24miss') AS DATE) , :COCC_LOCAL_SEQ_NR, :COCC_LINE_NR, :COCC_REQ_QOS_INFO, :COCC_NEG_QOS_INFO, :COCC_UPL, :COCC_DOWNL, :COCC_CHNG_CONDITION, CAST(TO_TIMESTAMP(:COCC_CHANGE_DATE, 'rrmmdd hh24miss') AS DATE) , :COCC_FAIL_HANDL_CONTIN, :COCC_MCC, :COCC_MNC, :COCC_QCI, :COCC_MAXREQ_BANDW_UPL, :COCC_MAXREQ_BANDW_DOWNL, :COCC_GUAR_BIT_UPL, :COCC_GUAR_BIT_DOWNL , :COCC_ARP, :COCC_APN_AGGR_MAXBIT_UPL, :COCC_APN_AGGR_MAXBIT_DOWNL, :COCC_MCC2, :COCC_MNC2, :COCC_TAC, :COCC_E_CELL_ID, :COCC_GW_ADDRESS)
Re: Optimization of sqlldr performance [message #683670 is a reply to message #683666] Fri, 12 February 2021 01:38 Go to previous messageGo to next message
John Watson
Messages: 8618
Registered: January 2010
Location: Global Village
Senior Member
Your reports are unreadable. Please just load up the html file. However, it is just about possible to see that your database is hardly working at all. If you think it is a problem, then generate a report (just the absolutely ordinary report you get by running the OH/rdbms/admin/awrrpt.sql script) for an hour or two when it busiest.
Re: Optimization of sqlldr performance [message #683672 is a reply to message #683669] Fri, 12 February 2021 02:29 Go to previous messageGo to next message
bgraupe
Messages: 25
Registered: July 2009
Location: Windhoek Namibia
Junior Member
Good day,

thank you so much for the willingness to assist.

I will request our DBA team to run statistics report for a short period (2 hours) during my peak loading and provide an updated stats file.

To answer the question
For example, are you loading them into one table, and then copying them into another? Is there ANYTHING done to the rows after SQL*Loader reads them?

Each raw ASN.1 file coming in to the server is going through a ANS.1 conversion and the results of this process produce three/four individual tables (parent and child tables - relational database design). Immediately after this process I am inserting each of the three/four output converted files individually with the sqlldr statement into Oracle database into the relevant tables. Once successfully inserted, then I zip the original raw ANS.1 file again and move it to a separate directory (then I know the ASN.1 files was successfully inserted and dealt with.)

I have attached another document to show the process. Please note that I have removed a number of lines which was just for me to monitor the steps (such as echo commands etc.) So this script is not my actual script. It will however explain the process.

Kind regard
Re: Optimization of sqlldr performance [message #683696 is a reply to message #683670] Sun, 14 February 2021 07:26 Go to previous messageGo to next message
bgraupe
Messages: 25
Registered: July 2009
Location: Windhoek Namibia
Junior Member
Good day,

please find the AWR statistics file attached.

Kind regards
Bonita
Re: Optimization of sqlldr performance [message #683698 is a reply to message #683696] Sun, 14 February 2021 10:49 Go to previous messageGo to next message
John Watson
Messages: 8618
Registered: January 2010
Location: Global Village
Senior Member
You are losing 34.4% of your DB Time on log switch issues. Your DBA should have fixed this already. You need to replace the online logfile groups with ones that are (to take a guess) double the size they are currently, and you likely need twice as many logfile groups too. Once that is done, take another two AWR reports: one from the CDB root container, and one from the PDB. HTML format, please. It should look a bit better.
Re: Optimization of sqlldr performance [message #683699 is a reply to message #683698] Sun, 14 February 2021 21:06 Go to previous messageGo to next message
bgraupe
Messages: 25
Registered: July 2009
Location: Windhoek Namibia
Junior Member
Good day,

thank you very much. I will take this up with the DBA team and revert.

Kind regards
Bonita
Re: Optimization of sqlldr performance [message #683701 is a reply to message #683698] Mon, 15 February 2021 04:12 Go to previous messageGo to next message
bgraupe
Messages: 25
Registered: July 2009
Location: Windhoek Namibia
Junior Member
Dear John,

may I kindly ask for your analysis on the attached AWR report as well. It is exactly the same environment (same loading of files into Oracle DB with sqlldr utility). Performance on this DB degraded to a level that there is no response from the system. We need to critically resolve this issue.

Kind regards
Bonita
Re: Optimization of sqlldr performance [message #683718 is a reply to message #683698] Tue, 16 February 2021 07:59 Go to previous messageGo to next message
bgraupe
Messages: 25
Registered: July 2009
Location: Windhoek Namibia
Junior Member
Good day,

html file format upload is not allowed. I have tried.

As a work around, I have changed the file extension to .txt in order to upload the files. May I ask that you kindly change the extension back to .html before evaluation the files?

Please see documents attached (in two replies as I may only submit one document at a time.

Kind regards
Bonita

Re: Optimization of sqlldr performance [message #683720 is a reply to message #683718] Tue, 16 February 2021 08:40 Go to previous messageGo to next message
John Watson
Messages: 8618
Registered: January 2010
Location: Global Village
Senior Member
That report looks a lot better, the redo log waits have disappeared. You should have seen an improvement. Perhaps not enough, but certainly noticeable? For example, the INSERTs into CDRDBUSER.HD4G_TB are now taking 0.0193 seconds each, previously they were 0.0270 seconds. This corresponds well with the 33% saving I was expecting.

To take it further, you could reconfigure the loading process. Instead of doing it with SQL*Loader managed by a shell script, do it all within the database: Read the input files with external tables. That will give you a lot more control. You will be able to parallelize the loads better, loading more files at once. You could use a Scheduler File Watcher to pick up the converted files as they are generated. I know that would be a big change, but I can't think of any way to optimize it further with SQL*Loader, you say you have already tried to tune that.

Perhaps someone else can help.

Re: Optimization of sqlldr performance [message #683721 is a reply to message #683720] Tue, 16 February 2021 09:12 Go to previous messageGo to next message
bgraupe
Messages: 25
Registered: July 2009
Location: Windhoek Namibia
Junior Member
Good day Mr. Watson,

your assistance is highly appreciated.

I have also uploaded an AWR file on 15 February at 12:12. On this system the performance is so bad that we are unable to get anything from the system (Loading, insert as well as select from the DB are all EXTREMELY slow.)

Do you perhaps have recommendation on this one so that I can ask the DBA team to assist?

Kind regards
Bonita
Re: Optimization of sqlldr performance [message #683722 is a reply to message #683721] Tue, 16 February 2021 09:36 Go to previous messageGo to next message
John Watson
Messages: 8618
Registered: January 2010
Location: Global Village
Senior Member
Better create a separate topic for that other problem. IT makes no sense to me.
Re: Optimization of sqlldr performance [message #683723 is a reply to message #683722] Tue, 16 February 2021 12:06 Go to previous message
bgraupe
Messages: 25
Registered: July 2009
Location: Windhoek Namibia
Junior Member
Thank you,

I will start a new discussion.

Kind regards
Bonita
Previous Topic: SQL Loader
Next Topic: Unable to load CSV file to Oracle Table
Goto Forum:
  


Current Time: Sat Oct 16 16:11:27 CDT 2021