Slower Connection/Logon after DOA 4.0.7.1 upgrade from DOA 3

[AJStadlin1]

Yesterday, I deployed our application into our production environment and noticed that initial connection to our Oracle server seems slower than the previous version of our application. The difference was that I replaced the old DOA 3 component with DOA 4.0.7.1. I never really noticed this during development, but users immediately noticed it, and I confirmed it.

If I run our DOA 3 version, the connection/logon takes about 1 second (or less). The DOA 4.0.7.1 version of our application takes about 5 seconds. We can live with it, but, if there is a way to tune the connect/login to be quicker, it would be nice. Perhaps this is related to some of the new features/settings of DOA 4? Let me know if you have any suggestions.

Other info:

In our production systems, we have not changed the database (Oracle 8.1.7.4.18) and have not changed the client (Oracle 8.1.7.3). Only the DOA component version was changed.

We already comment out the #SQLNET.AUTHENTICATION_SERVICES= (NTS)
line in the sqlnet.ora file to improve performance.

 

[Marco Kalter]

Version 4.0 should connect just as fast as 3.x.

Do you have multiple Oracle Homes on these PC's? It may be that 4.0.7 selects a different home than 3.x.

Also, is the 1 second vs. 4 seconds difference purely the connect time (just TOracleSession.LogOn)? Or are there other queries involved before the application is considered "logged on"?

 

[AJStadlin1]

The system only has one Oracle Home. There are 2 TNS names which work. I'll have to review the code for the login. That code hasn't changed for almost a year (I changed it last to update our copyright info). The login is followed by a few queries that retrieve configuration information for the software and user profile. These things have not really changed.

Several hours ago users (about 25 users) were experiencing other performance problems and a couple "lock ups" so I rolled back to the DOA 3 version of our application until I can perform some more aggressive and exhaustive testing. I probably didn't detect the performance difference because my development server is 3x faster than our productdion server and dual core - also, I'm the only user of the development server. The would be usable without the lock-ups - which preliminary observations seem to be related to closing datasets when exiting. At the moment, the lock-up problems are not repeatable, so the lockups may be a multi-user issue (in our software, not DOA).

 

[AJStadlin1]

I have been debugging this problem. The execution time of the OracleSession.LogOn function is taking about 5 times longer when I use DOA 4 than when I use DOA 3. Other parts of the application are also much slower, so there is definitely something changed between the versions that I need to fix. My DOA 4, Oracle 10 test server (Pentium D, 3GHz) takes about 3 times longer to logon than the DOA 3, on Pentium 3, 1GHz, Oracle 8. DOA 4 in the P3/Oracle 8 environment takes about 6 seconds to execute the session LogOn. DOA 3 Session takes about 1 second. If it was just the logon, then we could live with the problem, but since other sections of the system are also slower, and the Logon issue is 100% repeatable, it is a good place to start. Any settings changes suggestions?

 

[AJStadlin1]

Please run the following tests for me on your systems if you can:

Delphi 6, Enterprise, Update 2, RTL 3 (last updates) Add a TOracleSession object to the form. Set the LogonDatabase, LogonPassword, and LogonUsername properties. Set ThreadSafe property to True.

Use a stopwatch and Click/switch the Connected property from False to True.

If I run the experiment with DOA 3, I get less than 1 second. If I run the above experiment with DOA 4, I get more than 5 seconds.

I've tried various TOracleSession settings but haven't found any that significantly improve the DOA 4 performance.

Perhaps it is an Oracle setting, but other than turning MTS options off in the Oracle control file and turning off the NTS option in the network connector, I don't know what to tweak next.

Until I can solve this issue, I will be limited to DOA3 and Oracle 8 (unless the latest DOA3 version supports Oracle 10)

Please help me solve this issue.