Home » RDBMS Server » Performance Tuning » Oracle (HP-UX) connection very slow from Windows client (Oracle 9.2.0.7 installed on HP-UX)
Oracle (HP-UX) connection very slow from Windows client [message #557971] Mon, 18 June 2012 06:38 Go to next message
C4poster
Messages: 7
Registered: June 2012
Junior Member
Hi all,
I'm facing a very strange issue.
I have an Oracle database (9.2.0.7) installed on a HP-UX server.
When trying to access this database from another HP-UX or Linux server, connection is fine.
But when trying to connect from a Windows based client, connection is very slow (almost 1 minute to return the result of a 'select count(*)' like query, which is immediate from the Linux client).

Here are some facts I can add :
- Clients and servers are on the same network segment (it is not a network matter)
- No matter which client version I use, there no difference
- I tried to know what happens on the Oracle server when performing my sample query using tusc command : the result is that the server is performing exactly the same actions when sending my query from a Linux client or a Windows client
- The only relevant difference seems to be the client OS

If somebody has any idea to help, it is welcome.
Don't hesitate to ask me for further information.

Thanks.
Re: Oracle (HP-UX) connection very slow from Windows client [message #557972 is a reply to message #557971] Mon, 18 June 2012 06:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
First, welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Can you run your query in SQL*Plus on the HP and Windows clients with timing, plans, and statistics enabled, but suppressing the output? Like this,
orcl> set timing on
orcl> set autotrace traceonly
orcl> select count(*) from scott.emp;

Elapsed: 00:00:00.12

Execution Plan
----------------------------------------------------------
Plan hash value: 2937609675

-------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |        |     1 |            |          |
|   2 |   INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
         43  recursive calls
         46  db block gets
         21  consistent gets
          1  physical reads
      13020  redo size
        422  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

orcl>
Re: Oracle (HP-UX) connection very slow from Windows client [message #557978 is a reply to message #557972] Mon, 18 June 2012 07:13 Go to previous messageGo to next message
C4poster
Messages: 7
Registered: June 2012
Junior Member
Hi,
thanks for this quick reply.

So, here is the result from my Linux client :

SQL> set timing on
SQL> set autotrace traceonly
SQL> select count(*) from C_TGDM_RPT_AUTO;

Elapsed: 00:00:03.24

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1991 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'C_TGDM_RPT_AUTO' (Cost=1991 Card
=466227)





Statistics
----------------------------------------------------------
247 recursive calls
0 db block gets
21505 consistent gets
20687 physical reads
54288 redo size
520 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>


And here is the result form my Windows client :

SQL> set timing on
SQL> set autotrace traceonly
SQL> select count(*) from C_TGDM_RPT_AUTO;

¥Ø«e¾ú®É: 00:12:52.08

°õ¦æ­pµe
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1991 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'C_TGDM_RPT_AUTO' (Cost=1991 Card
=340918)





²Î­p­È
----------------------------------------------------------
0 recursive calls
0 db block gets
20705 consistent gets
20686 physical reads
0 redo size
209 bytes sent via SQL*Net to client
276 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Re: Oracle (HP-UX) connection very slow from Windows client [message #558076 is a reply to message #557978] Tue, 19 June 2012 05:01 Go to previous messageGo to next message
C4poster
Messages: 7
Registered: June 2012
Junior Member
I can see some differences but I don't know how to analyze them.

If someone can help...

Thanks !
Re: Oracle (HP-UX) connection very slow from Windows client [message #558124 is a reply to message #558076] Tue, 19 June 2012 09:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I am not convinced both sessions are same user to same DB.

post results from SQL below from both clients

SELECT USER, DBID,NAME FROM V$DATABASE;
Re: Oracle (HP-UX) connection very slow from Windows client [message #558125 is a reply to message #558124] Tue, 19 June 2012 09:21 Go to previous messageGo to next message
C4poster
Messages: 7
Registered: June 2012
Junior Member
From my Linux client :

SQL> SELECT USER, DBID,NAME FROM V$DATABASE;

USER DBID
------------------------------------------------------------ ----------
NAME
------------------
APPRO 1647320200
TWAEDI01


Fro my Windows client :

SQL> SELECT USER, DBID,NAME FROM V$DATABASE;

USER DBID
------------------------------------------------------------ ----------
NAME
------------------
APPRO 1647320200
TWAEDI01


Regards.
Re: Oracle (HP-UX) connection very slow from Windows client [message #558132 is a reply to message #558125] Tue, 19 June 2012 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Oracle (HP-UX) connection very slow from Windows client [message #558135 is a reply to message #558132] Tue, 19 June 2012 10:49 Go to previous messageGo to next message
C4poster
Messages: 7
Registered: June 2012
Junior Member
Hi Michel,
OK I will be carefull in my next posts.
I think that for this one, we can easily see the result is the same.

Oracle version is mentioned in the first post (9.2.0.7).

Regards
Re: Oracle (HP-UX) connection very slow from Windows client [message #558139 is a reply to message #558135] Tue, 19 June 2012 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, I will try to help in your next posts.

Regards
Michel
Re: Oracle (HP-UX) connection very slow from Windows client [message #558163 is a reply to message #557971] Tue, 19 June 2012 23:20 Go to previous messageGo to next message
bobbydurrettdba
Messages: 15
Registered: April 2012
Location: Phoenix, Arizona
Junior Member
Try a ping or nslookup of the database server host from your windows client.

It could be a DNS issue.

Also, there was an issue years ago with the sqlnet.ora on the windows client. You had to remove a line from it from some sort of windows name lookup. I forget the details. If you post the sqlnet.ora from your windows client I'll probably remember.

- Bobby
Re: Oracle (HP-UX) connection very slow from Windows client [message #558195 is a reply to message #558163] Wed, 20 June 2012 03:03 Go to previous messageGo to next message
C4poster
Messages: 7
Registered: June 2012
Junior Member
Hi,

I don't use DNS resolution to contact my database server.
But ping and nslookup are responding correctly, I just tested it.

Here is the sqlnet.ora file on my Windows server :

# SQLNET.ORA Network Configuration File: D:\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# SQLNET.AUTHENTICATION_SERVICES= (NONE)

NAMES.DIRECTORY_PATH= (TNSNAMES) #, ONAMES, HOSTNAME)

----------------------------------------------------------

I can add another thing : I have no delay when just connecting the database.
It is just very slow when performing a SQL query.

Thanks,
Regards.

Oracle Version : 9.2.0.7 (on HP-UX)
Re: Oracle (HP-UX) connection very slow from Windows client [message #558246 is a reply to message #558195] Wed, 20 June 2012 06:51 Go to previous messageGo to next message
bobbydurrettdba
Messages: 15
Registered: April 2012
Location: Phoenix, Arizona
Junior Member
I would do a 10046 trace with waits and tkprof the output. You want a profile of the session from the windows client that includes thw waits and cpu and elapsed time.

You could also do a client side sqlnet trace - enable this in your sqlnet.ora on your windows client. You would be looking for some big network delay I guess.

Is there firewall/proxy server between your windows client and the database?

Is your windows client bogged down with other processing - maybe virus checker?

- Bobby
Re: Oracle (HP-UX) connection very slow from Windows client [message #558257 is a reply to message #558246] Wed, 20 June 2012 07:28 Go to previous messageGo to next message
C4poster
Messages: 7
Registered: June 2012
Junior Member
Ok, but how can I do this ?

To answer your question, there is no firewall between the two servers.
i also try my test while antivirus is desactivated, and still have the same result.

Thanks.

Oracle Version 9.2.0.7
Re: Oracle (HP-UX) connection very slow from Windows client [message #558287 is a reply to message #558257] Wed, 20 June 2012 11:19 Go to previous message
bobbydurrettdba
Messages: 15
Registered: April 2012
Location: Phoenix, Arizona
Junior Member
Here is how to do a 10046 trace:

set echo on
set termout on

spool trace.log

alter session set max_dump_file_size = unlimited;
ALTER SESSION SET tracefile_identifier = 'windowsclient';

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

select count(*) from C_TGDM_RPT_AUTO;

ALTER SESSION SET EVENTS '10046 trace name context OFF';

show parameter user_dump_dest

spool off


On my 9.2.0.5 system this looks like this:

SQL> 
SQL> alter session set max_dump_file_size = unlimited;

Session altered.

SQL> ALTER SESSION SET tracefile_identifier = 'windowsclient';

Session altered.

SQL> 
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Session altered.

SQL> 
SQL> select count(*) from C_TGDM_RPT_AUTO;

  COUNT(*)                                                                      
----------                                                                      
      2734                                                                      

SQL> 
SQL> ALTER SESSION SET EVENTS '10046 trace name context OFF';

Session altered.

SQL> 
SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
user_dump_dest                       string      /var/opt/oracle/admin/DWDEV9/u 
                                                 dump                           
SQL> 
SQL> spool off


On my database server in the udump directory I found the trace file like this:

<hp823npc>
<EDWDBA><oracle></var/opt/oracle/admin/DWDEV9/udump>
$ ls -l *windowsclient*
-rw-r--r--   1 oracle     dba           2340 Jun 20 11:12 dwdev9_ora_8266_windowsclient.trc


So I recommend you do this trace from your windows client and then also your linux client. But from linux change the tracefile_identifier to linuxclient. Then look for differences between the two trace files. You may be able to post them here if they are small enough.

- Bobby
Previous Topic: How can i trace a different from current session with LEVEL 4?
Next Topic: How to determine the system memory usage by oracle processes
Goto Forum:
  


Current Time: Thu Mar 28 07:43:21 CDT 2024