Re: Multi Threaded server not working for many inserts?

"Royco" <bla_at_bla.nl> wrote

> For your information : A software company who sold us an Oracle based
> application advised us against using MTS because of the strange behaviour.

So what? Microsoft will advice you to use SQL-Server instead. France will advice you to put your head up your ass when it comes to supporting your allies. And the Americans will advice you that french toast is actually freedom toast and the French have their heads up their asses.

> At this moment however we are 'forced' to use Multi Threaded Server because
> some components under w2k are transactional AND uses databaselinks. (or else
> ORA 24777: Use of Non-migratable database link not allowed).
> We did investigate and our DBA is at a loss. Traces don't work en 'killing'
> some database-processen is the only way (apart from a restart) to make the
> instances responsive again.

Again, why do you say MTS is a problem? Until you stated that you have *tested* the *same* connection and SQL via a dedicated server and that it worked, you have no evidence that MTS is to blame.

So have you tried it with dedicated server yet? And if not, why not? That should have been the *first* thing that your DBA should have done when the assumption was made that MTS is too blame.

And I find it hard to believe that "tracing does not work". Have you enabled SQL tracing for that session? Have you enabled Net tracing?

> Furher 'investigation' made us find out that a 'pull' mechanisme works fine.
> So
> insert into tableA_at_instanceA select field1, field2, field3 from
> tableB_at_instanceB

Not really a valid test. In one case the INSERT trans is local and in the other case it is distributed. This does not proof much and provides very little ito isolating the error.

Troubleshooting 101. Do short incremental tests to isolate the error, or discard that which can potentially cause the error.

--
Billy