bd response very slow.

[John Flanagan]

running Oracle 8.1.7 on HP-UX 11.11

I have a some users using a word macro which picks up data from the oracle database using oracle in process server. This normally works well but sometimes the response from the database is very slow.

I have tried using sar / sar -v / sar -d and top to see the problem but all looks ok.

Has anybody any ideas on how to trouble shoot this problem.

Regrada,

John.

[TwoProc]

John - can you see the sessions from Enterprise Manager and see the costs? Are they higher at these times? Can you get a trace maybe? Are the statistics old on the table that they're querying?

[Jean-Luc Oudart]

Hi John,

there are a few actors in the process :
word / macro / odbc (I suppose) / SQLnet / oracle / server

Does the problem happen all the time for a specific macro ?
Any other (oracle and non-oracle) process affected too ?

The idea is to identify where the problem sits (client/network/server).

If this is at Oracle level you may have to trace those processes to analyze the SQL.

Regards
Jean-Luc

[John Flanagan]

To answer some of your questions.

Querys used are very simple. Things like selecting a list of open workorders.

Connection is using 'Oracle InProc Server'. Respoonse is the same with both version 8 and 9 on the client.

Poor response relates not only to the query but also to the time to establish the connection.

I experience similar delay in a dot net application that reads data from the same database. I suspect that the problem is related to some of the oracle configuration and probably to do with the listener.

Any ideas?

John.

[TwoProc]

John, Are you using the ODBC driver supplied by Microsoft, or the one provided by Oracle? Microsoft has one - but I've seen it exhibit the same traits that you're speaking of. This was fixed by using the Oracle ODBC drivers supplied with the Oracle Client CD's.

[TwoProc]

Another thought John,
If this symptom is not exhibited enterprise-wide - that is, some users are slow while others are fine at the same time, then I've seen a pretty good collection of crud running as processes on the Windows box. Basically, the few people who had intermittent problems were getting cpu bottlenecks on their cpu, and it had nothing do with the oracle server. Resolution was to dispatch a PC-Tech to remove as many auto-start programs from those PC's as possible. Many things were just nice-to-have and really didn't need to be loaded (extra goodies in the taskbar and such).

[John Flanagan]

Hi,

I am not using ODBC. I use the oracle provided 'Oracle in process server' provided by oracle. Much better than ODBC!.

I have also seen the problems on my pc. I have been watching cpu utilisation during the process.

Have a good weekend.

John.

[TwoProc]

Well, I'm unfamiliar with Oracle Inprocess Server - so that means I need to check it out to see if it's better than what we've been using. Thanks for the info, sorry I wasn't able to help more.

[Greg OBarr]

Hi John,

Because you say "...This normally works well but sometimes the response from the database is very slow...", I understand that there are times when everything works well and you are pleased with the response time from all connection methods. Is this true? Or are there some workstations where the response time is always good or always bad?

I would ask these:

- Do you have multiple subnets? If so, are all of the affected systems on the same subnet(s)? Could you be experiencing a network storm, some sort of denial of service attack, etc on the affected subnet(s)?

- When the problem occurs, have you tried pinging the server IP address and using tnsping to ping the listener? I would try these when performance is good and compare when performance is bad. If you can still ping the server with a good response time using "ping", but "tnsping" takes much longer during your problem times, that definitely points to the listener rather than the network in general.

- Are all workstations using the same version of the Oracle client? Are they all on the same operating system (XP, NT, etc) at the same patch levels? Different versions of the Oracle client may require some variations in the syntax of the "tnsnames.ora" and "sqlnet.ora" files.

- Do your Oracle clients use the same "tnsnames.ora" and "sqlnet.ora" files (via shared network drive) or do they all have their own such files? If they all have their own, compare them.

- Use "search" to make sure your Oracle client installs have access to only ONE tnsnames.ora and sqlnet.ora file. If there are multiple files with this name on the workstation and the Oracle client can see them, it can sometimes get confused, even if all the files are exactly the same.

- Check the network I/O on the Oracle server as well, not just the CPU load. You could have some other process (backup, large ftp session or other file copy, etc) stealing all your network bandwith without necessarily showing up on the CPU load.

I'm sure there are many more possibilities, but these are just a few things that come to mind immediately.

-greg

[John Flanagan]

John Joubert

Oracle InProc Server code for refrence.

Dim OraSession As New OracleInProcServer.OraSessionClass
Dim OraDatabase As OracleInProcServer.OraDatabase
Dim OraDynaset As OracleInProcServer.OraDynaset
OraDatabase = OraSession.OpenDatabase("myDB", "name/pass", 0&)
OraDynaset = OraDatabase.CreateDynaset(myQuerySQL, 0&)

to move throuth a table
oradynaset.movenext

to access a field
oradynaset.items(2).value

to execute SQL
OraDatabase.ExecuteSQL(myUpdateSQL)

Regards,

John,