[Oracle-l] N/w Bottleneck Check using tnsping under Tran. Load ... Basic Qs

Folks

Banking Java Application uses Oracle thin driver to connect to the Database. This Oracle client (I assume) goes thru the Listener on DB Server.
Benchmark doing OLTP Nature of Transactions

Is following an appropriate pointer to check for Network Choking under (Benchmark) Transaction Load?

Running of tnsping periodically during Peak Benchmark Transaction Load hitting the SAME IP Address / Port Num as the Application.
tnsping under concurrent Benchmark Tran load is giving a Timing of 118 milli seconds. This seems to indicate a Network choke.
(Under NO Load tnsping returns a Timing of 0 milli seconds.)

Any Other HP-UX Command(s) that give % of Network Usage (of 100 MBPS LAN) between APP & DB Server?

NOTE - Dedicated Server process connections to Database.

CONFIG:-

HP-UX
Oracle 10.2

Cheers & Thanks indeed

Vivek

CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

Hi,


The listener process needs to get onto CPU in order to answer to your
tnsping request, so if your CPU runqueues are long during peak load test, it
will take a while before listener can reply to your request.


For testing purposes you could set the listener onto real time priority
class and see if the results change.


But your network guys should be able to say whether your network is
saturated, as they have access to various link level stats etc.

Thanks V much Tanel for responding, as always.

CPU Usage of BOTH DB & APP Servers is about 30 % only at Peak Load when tnsping is giving a Timing of 118 ms. Hence do Not expect any CPU runqueue.

From: Tanel Poder
Sent: Friday, May 30, 2008 8:27 PM
Hi,

The listener process needs to get onto CPU in order to answer to your tnsping request, so if your CPU runqueues are long during peak load test, it will take a while before listener can reply to your request.

For testing purposes you could set the listener onto real time priority class and see if the results change.

But your network guys should be able to say whether your network is saturated, as they have access to various link level stats etc.

Regards,
Tanel Poder

From: oracle-l-bounce_at_freelists.org On Behalf Of VIVEK_SHARMA
Sent: Friday, May 30, 2008 19:15
Folks
. . .
Is following an appropriate pointer to check for Network Choking under (Benchmark) Transaction Load?

Running of tnsping periodically during Peak Benchmark Transaction Load hitting the SAME IP Address / Port Num as the Application.
tnsping under concurrent Benchmark Tran load is giving a Timing of 118 milli seconds. This seems to indicate a Network choke.
(Under NO Load tnsping returns a Timing of 0 milli seconds.)

Any Other HP-UX Command(s) that give % of Network Usage (of 100 MBPS LAN) between APP & DB Server?

NOTE - Dedicated Server process connections to Database.

CONFIG:-

HP-UX
Oracle 10.2

CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

Does plain Unix ping also experience similar latency increase?

YES Tanel. ping is also giving a similar increased Timing of about 118 ms under Peak Load.

Cheers

From: Tanel Poder
Sent: Saturday, May 31, 2008 8:53 AM

Does plain Unix ping also experience similar latency increase?

From: oracle-l-bounce_at_freelists.org On Behalf Of VIVEK_SHARMA
Sent: Saturday, May 31, 2008 03:52
To: Tanel Poder; oracle-l_at_freelists.org

Thanks V much Tanel for responding, as always.
CPU Usage of BOTH DB & APP Servers is about 30 % only at Peak Load when tnsping is giving a Timing of 118 ms. Hence do Not expect any CPU runqueue.

CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

Hi,


It takes at most ~1.475MB of data in your servers network stack send buffers
to cause a 118ms jump in reply time ( 100Mbit / 8 * 0.118 = 1.475 MB ).


In practice this number is probably lower.


You can run netstat in HP-UX couple of times and calculate how many bytes
worth of TCP packets are sent out:


netstat -f inet -p tcp


And this command (depending on your OS version) should show a statistic
called "outbound queue length". If this is constantly high (tens of
thousands packets I'd say) then there's a network bandwidth issue, also if
"outbound discards" is non-zero then there's definitely some problem in
network transmission.


/usr/sbin/lanadmin -g mibstats


Solutions? IF the commands above show a bandwidth bottleneck (high
utilization and long outbound queues) then get higher bandwidth network
interfaces or multiple network interfaces. Otherwise the problem is
somewhere else.


One specific case may be that the NIC interrupt handler is bound to a
specific CPU but can't get onto CPU enough due an Oracle process running
there with the SCHED_NOAGE bit which keeps the process on CPU until it
voluntarily yields it. But that's just a guess, I suspect that your issue is
just in the low-throughput NIC.

Did you try increasing the listener queue size?

From: oracle-l-bounce_at_freelists.org
On Behalf Of Tanel Poder

Sent: Friday, May 30, 2008 11:23 PM
To: VIVEK_SHARMA_at_infosys.com; oracle-l_at_freelists.org
Subject: RE: N/w Bottleneck Check using tnsping under Tran. Load

... Basic Qs



Does plain Unix ping also experience similar latency increase?

Folks

Benchmarking a Banking Java Application using Oracle thin driver to connect to the Database. OLTP Nature of Transactions
NOTE - Dedicated Server process connections to Database.

INSERT INTO SaleBackEnd(SaleBackEnd.ATMPinStatus, ... VALUES ( :1, :2, ... )

Below is the SQL Trace taken under Tran. Load (10046 at Level 12).
Qs Is the INSERT facing performance issues?

CONFIG:-

HP-UX
Oracle 10.2

Will share Detailed SQL Trace file, as needed.

Cheers & Thanks indeed

Vivek

P.S. SQL Trace under Load :-

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

Parse 13 0.00 0.00 0 0 0 0
Execute 13 0.20 0.57 0 29 184 13
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 26 0.20 0.58 0 29 184 13

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 26 (CRMUSER)

Rows Execution Plan
------- ---------------------------------------------------

0 INSERT STATEMENT MODE: ALL_ROWS

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 13 0.00 0.00
SQL*Net message from client 13 0.30 0.35

**************** CAUTION - Disclaimer *****************

This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

Folks

Why the 13 RE-parses, when using Bind Variables?

Cheers

Sent: Thursday, June 12, 2008 4:57 PM
Folks

Benchmarking a Banking Java Application using Oracle thin driver to connect to the Database. OLTP Nature of Transactions
NOTE - Dedicated Server process connections to Database.

INSERT INTO SaleBackEnd(SaleBackEnd.ATMPinStatus, ... VALUES ( :1, :2, ... )

Below is the SQL Trace taken under Tran. Load (10046 at Level 12).
Qs Is the INSERT facing performance issues?

CONFIG:-

HP-UX
Oracle 10.2

Will share Detailed SQL Trace file, as needed.

Cheers & Thanks indeed

Vivek

P.S. SQL Trace under Load :-

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

Parse 13 0.00 0.00 0 0 0 0
Execute 13 0.20 0.57 0 29 184 13
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 26 0.20 0.58 0 29 184 13

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 26 (CRMUSER)

Rows Execution Plan
------- ---------------------------------------------------

0 INSERT STATEMENT MODE: ALL_ROWS

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 13 0.00 0.00
SQL*Net message from client 13 0.30 0.35

**************** CAUTION - Disclaimer *****************

This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

One of the pitfalls to TKPROF is that none of the "sort" options (yet)
include wait event timings, except perhaps "elapsed", but still not
perfectly.

Please be sure to specify "sort=prsela,exeela,fchela" on the TKPROF
command-line for the best results.

But even sorting by "prsela,exeela,fchela" misses a huge chunk of
time, usually accounted to "log file sync", that occurs when the
transaction commits. Just an anomaly of the aged and band-aided
TKPROF program...

I would suggest searching for the phrase "log file sync" in your
TKPROF report and finding where your transaction is committing, and
seeing if you have huge waits there. Just bear in mind how redo is
generated by a session and how it is flushed to the online redo log
files and when. Reviewing most of the excellent articles on Steve
Adams' website at http://www.ixora.com.au on LGWR and "log file sync"
would be useful. In particular, his script "lgwr_waits.sql" (I think
that's what it's called) and the "sync wait ratio" might be
illuminating...

Quoting VIVEK_SHARMA :

Note that you could be hitting one of the multiple ASSM bugs related to
indexes. Like this one for example:

Bug # 4664513 Slow Insert - Waiting For 'log File Sync' - High 'transaction
Rollback' Stat
( Base bug# 4288876 )

You can easily diagnose it - if you see "transaction rollbacks" in V$SESSTAT
increasing without corresponding "user rollbacks" for that session (you can
use Snapper of taking snapshots). Another symptom is that you see "log file
sync" waits during inserts even if you are not committing (as the session
waits for log file sync when a transaction rollback happens as well).

Hi Vivek


I dont think so
Did you really see a performancve issue with the insert ?
Except that parse and execute are each 13 maybe some other
guy will say why is the execute =parse

regards
Hrishy

Sent from Yahoo! Mail.
A Smarter Email http://uk.docs.yahoo.com/nowyoucan.html