How to set oracle paremeter for particular DS project

[raj158347]

Hello frnds,
Due to performance, we want to Initialize the session variable in the oracle only for the particular Project
Is there is any way to trigger the oracle parameter for particular project?

Because our DS project has lot of OCI stage ,so Initialize the oracle session by using before query in OCI stage is not good for maintains purpose so i wish to do it for the particular DS project

Please give your valuable suggestions


Regards
Raj

[ray.wurlod]

Define "performance" in an ETL context.

The short answer is that each job must initialize its own connection(s) to Oracle - what you seek to do is not possible on a project-wide basis.

Version 8 gives you Connector technology, which improves developer productivity, but it remains the case that, at run time, each job establishes its own connection(s), gets its own SQL prepared, establishes its own transaction handling, and so on.

[chulett]

Because our DS project has lot of OCI stage ,so Initialize the oracle session by using before query in OCI stage is not good for maintains purpose

Unfortunately, since each stage establishes its own connection / session, that is your only option. You would need to use a Job Parameter for the 'alter session' statement in order to control it per Project.

Out of curiousity, what 'Oracle session variable'?

[raj158347]

Hi,
Thanks for reply,
We are planning to set the following session variable to avoid the full table scan.

" alter session set OPTIMIZER_INDEX_COST_ADJ =25; "

Because some time oracle miss judge full table scan plan is better than other plan (index/range plan) to avoid this we are planning set this session variable.

Regards
Raj

[ray.wurlod]

Our answer is unchanged. Each DataStage job is a separate instance of an Oracle client; there is no common session.

[raj158347]

Hi Ray,
Thanks for your answer, I will use job parameter in before SQL in OCI stage.


Regards
Raj