Performance Issue
Moderators: chulett, rschirm, roy
Performance Issue
I am facing strange Issue.
First my job is aborting due to ORA-02396: exceeded maximum idle time, please connect again.
Well Coming to my job design.
Step 1: Doing Inner join on 2 tables and I will get 140 million records and I get distinct records ...... finally I get 80 million records.
Step 2: These 80 million records will load some part into one oracle table and some part into data set.
Question 1: Our Oracle maximum idle time is 15 mins................I am running ETL job means I am accessing database ......How it will be failed and throw error as ORA-02396 ? In my view few ETL jobs will run more than 15 or 30 mins to load target database.
First my job is aborting due to ORA-02396: exceeded maximum idle time, please connect again.
Well Coming to my job design.
Step 1: Doing Inner join on 2 tables and I will get 140 million records and I get distinct records ...... finally I get 80 million records.
Step 2: These 80 million records will load some part into one oracle table and some part into data set.
Question 1: Our Oracle maximum idle time is 15 mins................I am running ETL job means I am accessing database ......How it will be failed and throw error as ORA-02396 ? In my view few ETL jobs will run more than 15 or 30 mins to load target database.
well two oracle connector using join stage to 80 million records.
In each oracle stage its just a simple select statement.
First Oracle connector stage 188 M will be populated in 12 to 13 mins and other oracle connector stage it populates 90 M records in 10 to 12 mins to reach join stage.
One strange thing is both oracle stage is populating one after one another (I mean one stage will pushes all the records to join stage, after that other stage works).........Both connectors will not working parallel......
I am using Array size as 2000 , prefetch row count as 1 and record count as 2000............. please let me know if can i do anything else?
In each oracle stage its just a simple select statement.
First Oracle connector stage 188 M will be populated in 12 to 13 mins and other oracle connector stage it populates 90 M records in 10 to 12 mins to reach join stage.
One strange thing is both oracle stage is populating one after one another (I mean one stage will pushes all the records to join stage, after that other stage works).........Both connectors will not working parallel......
I am using Array size as 2000 , prefetch row count as 1 and record count as 2000............. please let me know if can i do anything else?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If the join key is indexed in Oracle it is likely that the join will be resolved in the indexes. Get Oracle to EXPLAIN its plan for resolving the join. This is likely to be far more efficient than anything you can accomplish in DataStage, and will involve less network traffic because you only send the result of the join "across the wire".
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Generally that would not be true. Suggest you leave the Join stage for when you absolutely need it, i.e. for disparate sources. Now, if the source database is under-powered or over-utilitized and you are just making matters worse there, then move the work to the ETL server.sriec12 wrote:both tables are in huge volume so my understanding is ETL will be more efficient
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers