RE: Perfomrance Issue When using BEQ protocol
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 8 Jul 2004 07:05:20 -0400
Are you trying to measure the time to connect or the time to suck matching
amounts of data through the straw?
I think what you are actually measuring is how long it takes TNSPING to
parse your arguments and determine whether a straw that can be used exists.
From your data it appears you might be better off with TCP if you are
chronically connecting, grabbing a very small amount of data, and
disconnecting (since the difference in elapsed time of the connection
location might be a significant component of the overall task.) To really
tell, though, you would need to make a connection, since there is
significant overhead in an actual connection beyond verifying that contact
is possible. Most of that overhead is probably the same for both methods,
since it is largely internal to the database engine.
Now if that really is your job profile, you're probably a candidate for some
sort of connection pooling or multiple threading.
If, on the other hand, you're trying to measure which straw has bigger
capacity, I think you want a test that draws a lot of data through the straw
per connection. To make the test fair for your apparent purpose, you'll want
to check that you've done everything you can to advise the Oracle software
and the OS that your connection is actually local (short of specifying BEQ)
for the TCP connection.
Interesting thought pattern to consider TNSPING in the context of PING to
check the data movement capacity. I wonder if Oracle could add data packet
shoving with PING-like specifications to TNSPING without creating a security
hole or destabilizing an "always works" diagnostic tool. I don't think they
had the capacity testing aspect of PING in mind when they named the tool.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Ahmed Abdel Fattah
Sent: Thursday, July 08, 2004 3:10 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Perfomrance Issue When using BEQ protocol
Dear All,
I have an environment that has two databases, OFSA & ODS.
Database ODS has a database link to the OFSA database. This
database link uses a connect string called OFSA, which is defined
as follows:
OFSA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = CI-DW-PRD)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =OFSA)
)
)
There is a batch job that run on the server and that uses the above
database link to retrieve some data from the OFSA database.
Due to performance issues on the above batch job, I suggested to implement
the BEQ protocol in the connect string that is used by the above databse
link
as long as the two databases are on the same server, and because I read some
documents that BEQ is faster than TCP/IP. So, I created another connect
string
as follows:
PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = BEQ)(PROGRAM = oracle)(ARGV0 = oracleOFSA)(ARGS =
'(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS =
'ORACLE_SID=OFSA,ORACLE_HOME=/apps/oracle/product/8.1.7'))
)
The I test connection using the two connect strings, using the tnsping
utility
over a period of time, and I found that using the BEQ connect string is much
slower than using the TCP/IP connect string. Following are sample test
results:
==============
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=CIB-DW-PRD)(PORT=1521))
OK (10 msec)
TNS Ping Utility for Solaris: Version 8.1.7.2.0 - Production on 07-JUL-2004
17:4
5:56
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact
(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleOFSA)(
ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS='ORACLE_SID=O
FSA,
ORACLE_HOME=/apps/oracle/product/8.1.7'))
OK (160 msec)
==============
TNS Ping Utility for Solaris: Version 8.1.7.2.0 - Production on 07-JUL-2004
17:4
6:00
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=CIB-DW-PRD)(PORT=1521))
OK (10 msec)
TNS Ping Utility for Solaris: Version 8.1.7.2.0 - Production on 07-JUL-2004
17:4
6:00
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact
(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleOFSA)(
ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS='ORACLE_SID=O
FSA,
ORACLE_HOME=/apps/oracle/product/8.1.7'))
OK (70 msec)
==============
TNS Ping Utility for Solaris: Version 8.1.7.2.0 - Production on 07-JUL-2004
17:4
6:03
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=CIB-DW-PRD)(PORT=1521))
OK (10 msec)
TNS Ping Utility for Solaris: Version 8.1.7.2.0 - Production on 07-JUL-2004
17:4
6:03
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact
(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleOFSA)(
ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS='ORACLE_SID=O
FSA,
ORACLE_HOME=/apps/oracle/product/8.1.7'))
OK (60 msec)
==============
TNS Ping Utility for Solaris: Version 8.1.7.2.0 - Production on 07-JUL-2004
17:4
6:06
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=CIB-DW-PRD)(PORT=1521))
OK (10 msec)
TNS Ping Utility for Solaris: Version 8.1.7.2.0 - Production on 07-JUL-2004
17:4
6:06
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact
(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleOFSA)(
ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS='ORACLE_SID=O
FSA,
ORACLE_HOME=/apps/oracle/product/8.1.7'))
OK (70 msec)
==============
TNS Ping Utility for Solaris: Version 8.1.7.2.0 - Production on 07-JUL-2004
17:4
6:10
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=CIB-DW-PRD)(PORT=1521))
OK (0 msec)
TNS Ping Utility for Solaris: Version 8.1.7.2.0 - Production on 07-JUL-2004
17:4
6:10
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Attempting to contact
(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleOFSA)(
ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS='ORACLE_SID=O
FSA,
ORACLE_HOME=/apps/oracle/product/8.1.7'))
OK (150 msec)
==============
Can someone till me if this is a normal behavior?
Is I missing some information?
Thanks & Best Regards
Ahmed
---------------------------------
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
