Hi,
I skimmed your post. We had many difficulties with
older versions of PowerBuilder that worked with Oracle
v7, but had minor issues both upgrading PB and when we
switched to Oracle 8i and again 9i.
In summary, make sure you know exactly what client
network configuration, protocols, and dll's are being
used in each case. Then try your tests all over again.
Possible issues we found:
1) Multiple Oracle home on client, with mixed versions
of OCI DLL in the same WinNT directory. The only way
to isolate what dll you are using in PB is to reboot
WinNT after removing (renaming) the DLL you are
testing. If you found it, then the application will
fail to connect. Check the version with file
properties, and get it all straight for your tests.
2) PB pbl's may need to be rebuilt or recompiled
during development if you change the Oracle version.
We found problems with development pbl's SQL after
switching Oracle versions (behind the scenes). We
suspect PB creates different 'hidden' data in a pbl
depending on the PB version and/or connected to a
newer version of Oracle. Therefore, if you just grab
older version of pbl's out of PVCS(whatever) and run
you can get inconsistent Oracle results (we did). I
wrote a java parser to read all the pbl text to search
for which pbl's to rebuild/update based on which
tables were included in the 'problem' business area.
3) SQL*Plus is not necessarily doing the same thing as
PB. I recommend a small PB application, connecting to
both databases, as your test vehicle.
I don't want to scare you with random thoughts of
potential problems (trial and error cycle-of-doom). So
as start, I recommend you make sure your tests (below)
are valid comparisons by going deep enough in your
configurations, and protocol stacks (e.g. OCI), so
attempting the comparison that you actually 'break'
the connection. This should help insure an
apple-to-apple comparision.
Good luck. Sorry for the 'random' thoughts, I just
typed as it came to mind.
Regards,
Mike Thomas
- "Post, Ethan" <Ethan.Post_at_ps.net> wrote:
> 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
>
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/
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 - 23:29:10 CST
