Re: Oracle NT Profile Performance Mystery

carol.mcalear_at_pulsion.co.uk (Carol) wrote:

>Server: Oracle 8i on NT 4
>Client: Oracle 8i Client, Windows NT4 using application written in
>Delphi 5 to connect to database.

Is the Delphi app using BDE or ODBC?

>When a new Windows NT user tries to connect to the database using the
>Delphi app the performance is really bad but if the user logs on as
>another NT user the performance is good, this is on the same machine.
>The Network administrator has assured me that there is no difference
>between the NT profiles.

Okay, if we can assume that the two NT client user profiles are the same, then the problem must be on the backend. How is that possible? Are you using os authentication maybe in Oracle and not plan vanilla Oracle userids and passwords? But even then - I am unsure how one userid can give a problem wrt performance and not another. Unless different authentication methods are used between the two (which is possible in Oracle).

What you can do is, is to edit the listener.ora file on the server and enable tracing. Connect from both client users and see what the logfile has to say.

My gut feel is that your Network Admin is wrong. Or more correctly - he is maybe correct that there are no differences between the userid profile settings of these users in User Domain Administrator. But then that is not the only place to check to see if users are equal (it is the first and basic place to _start_ looking, but you should not stop there).

A silly example. Both users have read access to a DLL in a folder called C:\FOO (which is what the Network Admin checked). One user's access is slow. The other's not.

The reason. The slow user has a PATH environment string of 10KB, pointing to every single folder on the system, with the folder C:\FOO last in the list. This means that the entire system is searched for that DLL, before Windows eventually finds it in the right folder. The "smart" user has a 1KB PATH with C:\FOO right there up front.

Another example. The "dumb" user thinks he is clever and creates his own TNSNAMES.ORA and SQLNET.ORA files, and use an environmental variable (called TNSADMIN if I'm not mistaken) to point to the directory in which his TNSNAMES.ORA and SQLNET.ORA is located in.

The network changes and the Oracle Name Server is upgraded/changed. The "dumb" user never updated his SQLNET.ORA file, which results in a long delay while Net8 attempts to resolve the TNS connection (I have seen this type of thing firsthand).

The "clever" user never changed anything and uses the updated and default TNSNAMES and SQLNET files in the Oracle Home.

Ditto for things like BDC. If a db alias is used by the application, then that db alias can be differently defined for every single NT user on that machine. ODBC also support local aliases.

The "slow" user could be using an ODBC driver for his Delphi db alias and the "fast" user could be using BDE's native Oracle driver.

So when your Network Admin is saying that the user profiles are equal just because he quickly checked in User Administrator (which is what my experience of working with NT sysadmins tells me), he is not much of a sysadmin IMO.

If he did a proper check, then I apologise upfront for questioning his admin skills. :-)

--
Billy