| Optimization of sqlldr performance [message #683656] |
Wed, 10 February 2021 13:26  |
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  |
John Watson
Messages: 8944
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 |
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 #683666 is a reply to message #683664] |
Thu, 11 February 2021 21:54 |
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 #683669 is a reply to message #683666] |
Fri, 12 February 2021 01:32 |
John Watson
Messages: 8944
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 |
John Watson
Messages: 8944
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 |
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 #683698 is a reply to message #683696] |
Sun, 14 February 2021 10:49 |
John Watson
Messages: 8944
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 #683701 is a reply to message #683698] |
Mon, 15 February 2021 04:12 |
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 |
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 |
John Watson
Messages: 8944
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 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
