Network Performance Stumper
- From: "Post, Ethan" <Ethan.Post@xxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 2 Feb 2004 16:30:49 -0600
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
