I'm trying to help another developer whose job has an informational message I've never seen before. It is a straight "Source -> Transformer -> Target" kind of job with Oracle as the target.
The warning is "Warning, insert has a high number of retries: 1,999."
This message appears several times in the director log. Performance started out around 5K RPS, but is now down to 280 RPS with over a million rows output.
Our DBA is currently unavailable to tell me if Oracle logs can shed any light on this, but I thought I'd toss it out to see if any of you have seen this before.
I do know that the Oracle system is very stressed right now, lots of things hitting it. I've just never seen it where it would have to re-process an insert, much less 2,000+ times.
Warning, Insert had a high number of retries
Moderators: chulett, rschirm, roy
-
priyadarshikunal
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Seems you are using oracle connector. Can you check what is the number of retries defined in the connector stage? It shows the number of retries when you are setting the properties for the link in oracle connector.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
My initial guess is the locking mechanism on the table is getting locked rows (or perhaps pages) caused either by another query or by the parallelism behind the load itself.
Are you running this in parallel? What level of locking did you specify (probably "read uncommitted") in DS and do you know of another process which might be locking your output table?
Are you running this in parallel? What level of locking did you specify (probably "read uncommitted") in DS and do you know of another process which might be locking your output table?
</a>-
sureshreddy2009
- Participant
- Posts: 62
- Joined: Sat Mar 07, 2009 4:59 am
- Location: Chicago
- Contact:
committ limit
This kind of oracle messages always depends on api vs direct load
Which kind of load you are doing
If you are not committing the records, then the database is open for ever which cause this kind of issues
Which kind of load you are doing
If you are not committing the records, then the database is open for ever which cause this kind of issues
Suresh Reddy
ETL Developer
Research Operations
"its important to know in which direction we are moving rather than where we are"
ETL Developer
Research Operations
"its important to know in which direction we are moving rather than where we are"
Unfortunately, there's no oversight on the group writing this code, so there are no best practices being used. I can safely assume their stages all use the default settings for ODBC stages mainly because very few developers have done anything other than "take the defaults". They are not using Oracle connectors (which are available) because the first template anyone got to work used an ODBC enterprise stage.
These jobs are all parallel jobs, but they are all "transfer from table A to table B with one transformer in the middle" kind of jobs, with "Auto" partitioning. There are hundreds of jobs in dozens of sequencers active at any given time, so it is quite possible that they are "stepping" on each other by accessing the same file at the same time.
Thanks for the suggestions - I'll see if the DBA's can give me more detail from the logs.
These jobs are all parallel jobs, but they are all "transfer from table A to table B with one transformer in the middle" kind of jobs, with "Auto" partitioning. There are hundreds of jobs in dozens of sequencers active at any given time, so it is quite possible that they are "stepping" on each other by accessing the same file at the same time.
Thanks for the suggestions - I'll see if the DBA's can give me more detail from the logs.
