Message-ID: <DD0385472EABFB40A8A492087DEC551E081E903B@dalexch03.rmf.ps.net>
From: "Post, Ethan" <Ethan.Post@ps.net>
To: oracle-l@freelists.org
Subject: Network Performance Stumper
Date: Mon, 2 Feb 2004 16:30:49 -0600 
MIME-Version: 1.0
Content-type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-To: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: oracle-l@freelists.org

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@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
-----------------------------------------------------------------