Lately, we faced an issue in my project where we noticed that when running 3 jobs that loaded tables of 3.5 Gb,1Gb,2gb at the same time choked the Oracle Database. The job were ran on 2 nodes, which turns out to be 3 x 2 is 6 sessions in parallel. The Dba noticed I/O waits on the Transaction log. This was due to the number of inserts being two much for the database which cause it to lock up.
We also noticed some strange Oracle sessions with SYS user performing UPDATEs on obj$. the Sessions kept growing after its intial appearance.
Another strange thing was the Oracle Dev Database worked well with the same amount of load, when ran in parallel.
SOLUTION
If you notice that the Oracle Database is hanging up when running Datastage jobs. Try to reduce the number of processes ran in Parallel. A rule of thumb is to run any table more than 3GB as a standalone then run the other two jobs (2gb and 1gb) in Parallel
Subscribe to:
Post Comments (Atom)
-
1. Good Foundational Knowledge of SQL Programming (SQL Query writing) Paid Course The best SQL course that I know. (I have not affili...
-
Hi I was recently working on extracting a table that has 400 columns from a SQL Server DB to load into an Oracle Target Database. I design...
-
In this blog post I will show you how to implement your own custom logging in Azure Data Factory Before you do this please note that Azur...
No comments:
Post a Comment