Oracle (HP-UX) connection very slow from Windows client [message #557971] |
Mon, 18 June 2012 06:38 |
|
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 |
John Watson
Messages: 8951 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 #558287 is a reply to message #558257] |
Wed, 20 June 2012 11:19 |
|
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
|
|
|