Performance Issue

sriec12 · Post by **sriec12** » Fri May 10, 2013 8:53 am

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.

chulett · Post by **chulett** » Fri May 10, 2013 9:26 am

It's not about how long it takes to "load" but rather how long before the first record gets to the target. I'd look into optimizing your source SQL as much as possible.

sriec12 · Post by **sriec12** » Fri May 10, 2013 9:40 am

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?

PaulVL · Post by **PaulVL** » Fri May 10, 2013 11:13 am

Are you using DataStage 8.7? Maybe you can see what Balance Optimizer could do for you?

Throwing the work back onto Oracle might net you better performance for your job.

How long is Oracle taking to build the result set?

sriec12 · Post by **sriec12** » Fri May 10, 2013 12:33 pm

we are using 8.5 ....................is Balance Optimizer any thing called new stage ?

chulett · Post by **chulett** » Fri May 10, 2013 2:10 pm

Are those two tables in the same database by chance? Wondering if you really need to be doing the join "in job" and can't tell from what you've posted so far...

sriec12 · Post by **sriec12** » Fri May 10, 2013 3:30 pm

My bad, Actually two table are on same database oracle

I can use one oracle connector stage to join two tables. The reason for not using is both tables are in huge volume so my understanding is ETL will be more efficient

ray.wurlod · Post by **ray.wurlod** » Fri May 10, 2013 3:41 pm

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".

chulett · Post by **chulett** » Fri May 10, 2013 4:33 pm

sriec12 wrote:both tables are in huge volume so my understanding is ETL will be more efficient

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 · Post by **sriec12** » Wed May 22, 2013 1:25 pm

Contacted Database Admin, he gave new user id and pwd.........Our Idle time is 15mins.........Thanks everyone