Network Performance Stumper

From: Post, Ethan <Ethan.Post_at_ps.net>
Date: Mon, 2 Feb 2004 16:30:49 -0600
Message-ID: <DD0385472EABFB40A8A492087DEC551E081E903B@dalexch03.rmf.ps.net>

OK, I am officially stumped. Here is the issue.

Server A Oracle 9.2.0.4 AIX 64 bit
Server B Oracle 8.1.4.7 AIX 64 bit

Take a powerbuilder based client app which connected to each database via SQL*Net. Occurs on Oracle clients for 8i and 9i.

Time to login to server A averages 30-50 seconds.

Time to login to server B average 3-10 seconds.

10046 trace shows most of the time is fetching a single cursor and waiting on SQL*Net message from client.

FTP tests show I can move a 37MB file to server A in 10 secs, 3 secs on server B.

A sql script run from client which does "select * from dba_tables where table_name='blah'" and goes through all tables repeatedly runs in 1:11 secs on server A and 1:25 secs on server B.

These tests are showing that while a small network performance issues exists, it is not big enough to account for the big differences we see in log in time. Repeat calls to the database over network which the selects from dba_tables simulate does not show a huge performance hit, but for some reason the PB app is hit big time.

I will include SQLNet trace summaries below also. Any ideas?

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 96 0.28 0.29 0 919 0 25389
------- ------ -------- ---------- ---------- ---------- ----------

total 98 0.28 0.29 0 919 0 25389

Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 438 (DAVIS)

Rows Row Source Operation

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

25389 TABLE ACCESS FULL OBJECT_TOKEN (cr=919 r=0 w=0 time=90314 us)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: RULE

25389 TABLE ACCESS (FULL) OF 'OBJECT_TOKEN' Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

Waited ----------
SQL*Net message to client 96 0.00 0.00 SQL*Net message from client 96 0.88 14.41 SQL*Net more data to client 843 0.00 0.02

SQL Net Trace to Server B

Trace File Statistics:

Start Timestamp : 02-FEB-2004 16:02:06:181 End Timestamp : 02-FEB-2004 16:02:23:315 Total number of Sessions: 2

DATABASE:
Operation Count: 0 OPENS, 23 PARSES, 23 EXECUTES, 326 FETCHES Parse Counts:

       2 PL/SQL,    18 SELECT,      0 INSERT,     1 UPDATE,     0 DELETE,
       0 LOCK,       0 TRANSACT,    1 DEFINE,     0 SECURE,     1 OTHER
    Execute counts with SQL data:
       2 PL/SQL,    12 SELECT,      0 INSERT,     1 UPDATE,     0 DELETE,
       0 LOCK,       0 TRANSACT,    1 DEFINE,     0 SECURE,     1 OTHER

Packet Ratio: 15.434782608695652 packets sent per operation Currently opened Cursors: 0
Maximum opened Cursors : 0

ORACLE NET SERVICES:

  Total Calls  :       355 sent,       1711 received,         348 oci
  Total Bytes  :     39914 sent,    3107159 received
    Average Bytes:       112 sent per packet,       1815 received per packet
    Maximum Bytes:      1391 sent,       2019 received

Grand Total Packets: 355 sent, 1711 received

SQL Net trace to server A

Trace File Statistics:

Start Timestamp : 02-FEB-2004 15:55:40:836 End Timestamp : 02-FEB-2004 15:56:30:498 Total number of Sessions: 2

DATABASE:
Operation Count: 0 OPENS, 23 PARSES, 23 EXECUTES, 326 FETCHES Parse Counts:

       2 PL/SQL,    18 SELECT,      0 INSERT,     1 UPDATE,     0 DELETE,
       0 LOCK,       0 TRANSACT,    1 DEFINE,     0 SECURE,     1 OTHER
    Execute counts with SQL data:
       2 PL/SQL,    12 SELECT,      0 INSERT,     1 UPDATE,     0 DELETE,
       0 LOCK,       0 TRANSACT,    1 DEFINE,     0 SECURE,     1 OTHER

Packet Ratio: 15.434782608695652 packets sent per operation Currently opened Cursors: 0
Maximum opened Cursors : 0

ORACLE NET SERVICES:

  Total Calls  :       355 sent,       1487 received,         348 oci
  Total Bytes  :     21320 sent,    2545401 received
    Average Bytes:        60 sent per packet,       1711 received per packet
    Maximum Bytes:      1370 sent,       2011 received

Grand Total Packets: 355 sent, 1487 received

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


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

Received on Mon Feb 02 2004 - 16:30:49 CST