(Server=Dedicated) parameter in TNSNAMES.ORA file

[dwuser]

Hi All,

We have a condition here in Data Stage 7.5.2 with server jobs.

Currently, we are using 9.2.0.8 oracle database had a performance issue.

One of the suggestions from IBM was to set the (Server=Dedicated) parameter in the TNSNAMES.ORA in the data stage server.

After setting up the parameter as suggested we got an excellent performance (almost 50 to 100% in some cases) in our server jobs. But the DBAs debate that the database has already been setup with the "Dedicated" option at the database level so this change should have not changed the performance rather something else in DataStage should have been changed. Is there any way to dispute their argument? Please let me know

Thanks,
Chandru

[chulett]

Did you change anything else in your jobs or environment for this? If all you did was set this option in your tnsnames file and saw an immediate change, I don't see how they can assert that had 'already been setup' at the database level and it must be a DataStage thing.

[ray.wurlod]

The entry in tnsnames.ora instructs the client connection - irrespective of the actual client tool - to make use of the Dedicated features. Presumably its absence instructs the client connection not to make use of the said features.

[Yuan_Edward]

In my opinion, (SERVER = DEDICATED) should be there in the TNSNAMES.ORA if the database is set up with DEDICATED Server option.

It doesn't affect anything to add that parameter into the TNSNAMES.ORA if it's not there.

Hi All,

We have a condition here in Data Stage 7.5.2 with server jobs.

Currently, we are using 9.2.0.8 oracle database had a performance issue.

One of the suggestions from IBM was to set the (Server=Dedicated) parameter in the TNSNAMES.ORA in the data stage server.

After setting up the parameter as suggested we got an excellent performance (almost 50 to 100% in some cases) in our server jobs. But the DBAs debate that the database has already been setup with the "Dedicated" option at the database level so this change should have not changed the performance rather something else in DataStage should have been changed. Is there any way to dispute their argument? Please let me know

Thanks,
Chandru

[chulett]

It instructs it to bypass MTS and establish a dedicated connection. Dedicated = more resources at the expense of non-dedicated connections.