| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Performance tuning in complex environment
All,
This sounds waaaay too familiar to me.
My (blind) guess is that sql*net round trips is killing performance.
System-wide could indicate this, but, as Jared states, trace out a specific session, and grab the session-specific info from v$sesstat, before and after.
We brute forced the issue of sqlnet round trips in the past by having the site upgrade the Citrix Servers from fast ethernet to gigabit network cards. Most new servers these days in the intel space ship with 2 integrated gigabit NICs, is just a matter of having GigE switched ports available.
If you're moving too much data between client and server, increasing the SDU could help, but your mileage will vary.
Pd
Jared Still <jkstill_at_cybcon.com> wrote:
The wholesale system wide collection of timing data is not generally
a good way to go about trouble shooting performance issues.
You need to pick a process, collect the timing data for that process, and *only* that process, diagnose where the most time is being spent, and determine what can be done to speed it up.
This in a nutshell is the basis of Cary's book, at least per my reading of it.
Always try to fix the stuff with the biggest payoff.
It could be a SQL statement, it could be a misconfigured or malfunctioning network card. It could be that a developer is filling a temporary table with lots of data during a transaction, then deleting the data and doing it over and over again, all the while doing full table scans. FTS is expensive when you want to retrieve 3 rows from a temp table with 500 meg of extents in it.
Just for grins though, how about running this script and posting the output for us? Sometimes you get lucky, and something may appear really out of whack. No guarantees though. Troubleshooting system performance problems takes more than an email.
HTH Jared
col event format a35 head 'EVENT NAME'
col total_waits format 999,999,999 head "TOTAL|WAITS"
col total_timeouts format 999,999,999 head "TOTAL|TIMEOUTS"
col time_waited format 999,999,999 head "TIME|WAITED|SECONDS"
col average_wait format 99999 head "AVG|WAIT|100ths"
set line 150
set trimspool on
select
event,
total_waits,
total_timeouts,
time_waited/100 time_waited,
average_wait
from v$system_event
order by time_waited
/
On Thu, 2003-12-11 at 10:34, Avnish.Rastogi_at_providence.org wrote:
> Hello Everyone, I am trying to get some help/suggestions reg. how to troubleshoot performance issues.
>
> Little back ground about our environment. Its third party application (Logician) from GE. There are total 11 databases, all on oracle 8174 H-UX 11i in cluster environment. All the databases are on EMC Symmetrix using 6 disks. All the clients are connecting to database thru Citrix terminal servers.
> In last one year we spend lots of time/money in tuning databases, replacing Citrix servers but end result is same. I was wondering if anybody out there has ran into same kind of situation. Our (DBAs) guess is the disk layout is not optimal but we also dont have any data to prove that disks are the bottleneck. Is there any way to collect these kinds of stats in Oracle. We aren't getting much help from our SAN administrator.
>
>
>
> DISCLAIMER:
> This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: > INET: Avnish.Rastogi_at_providence.org
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: jkstill_at_cybcon.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --------------------------------- Do you Yahoo!? Free Pop-Up Blocker - Get it now --0-550119929-1071252481=:27442 Content-Type: text/html; charset=us-ascii <DIV>All,</DIV> <DIV> </DIV> <DIV>This sounds waaaay too familiar to me.</DIV> <DIV>My (blind) guess is that sql*net round trips is killing performance.</DIV> <DIV>System-wide could indicate this, but, as Jared states, trace out a specific session, and grab the session-specific info from v$sesstat, before and after.</DIV> <DIV> </DIV> <DIV>We brute forced the issue of sqlnet round trips in the past by having the site upgrade the Citrix Servers from fast ethernet to gigabit network cards. Most new servers these days in the intel space ship with 2 integrated gigabit NICs, is just a matter of having GigE switched ports available.</DIV> <DIV> </DIV> <DIV>If you're moving too much data between client and server, increasing the SDU could help, but your mileage will vary.</DIV> <DIV> </DIV> <DIV>Pd<BR><BR><B><I>Jared Still <jkstill_at_cybcon.com></I></B> wrote:</DIV> <BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">The wholesale system wide collection of timing data is not generally<BR>a good way to go about trouble shooting performance issues.<BR><BR>You need to pick a process, collect the timing data for that process,<BR>and *only* that process, diagnose where the most time is being spent,<BR>and determine what can be done to speed it up.<BR><BR>This in a nutshell is the basis of Cary's book, at least <BR>per my reading of it.<BR><BR>Always try to fix the stuff with the biggest payoff.<BR><BR>It could be a SQL statement, it could be a misconfigured or<BR>malfunctioning network card. It could be that a developer <BR>is filling a temporary table with lots of data during a transaction,<BR>then deleting the data and doing it over and over again, all the<BR>while doing full table scans. FTS is expensive when you want to <BR>retrieve 3 rows from a temp table with 500 meg of extents in it.<BR><BR>Just for grins though, how about running this script and posting<BR>the output for us? Sometimes you get lucky, and something may<BR>appear really out of whack. No guarantees though. Troubleshooting<BR>system performance problems takes more than an email.<BR><BR>HTH<BR><BR>Jared<BR><BR>=============================================<BR><BR>col event format a35 head 'EVENT NAME'<BR>col total_waits format 999,999,999 head "TOTAL|WAITS"<BR>col total_timeouts format 999,999,999 head "TOTAL|TIMEOUTS"<BR>col time_waited format 999,999,999 head "TIME|WAITED|SECONDS"<BR>col average_wait format 99999 head "AVG|WAIT|100ths"<BR><BR>set line 150<BR>set trimspool on<BR><BR>select<BR>event,<BR>total_waits,<BR>total_timeouts,<BR>time_waited/100 time_waited,<BR>average_wait<BR>from v$system_event<BR>order by time_waited<BR>/<BR><BR>================================================<BR><BR><BR><BR>On Thu, 2003-12-11 at 10:34, Avnish.Rastogi_at_providence.org wrote:<BR>> Hello Everyon! e, I am trying to get some help/suggestions reg. how to troubleshoot performance issues.<BR>> <BR>> Little back ground about our environment. Its third party application (Logician) from GE. There are total 11 databases, all on oracle 8174 H-UX 11i in cluster environment. All the databases are on EMC Symmetrix using 6 disks. All the clients are connecting to database thru Citrix terminal servers. <BR>> In last one year we spend lots of time/money in tuning databases, replacing Citrix servers but end result is same. I was wondering if anybody out there has ran into same kind of situation. Our (DBAs) guess is the disk layout is not optimal but we also dont have any data to prove that disks are the bottleneck. Is there any way to collect these kinds of stats in Oracle. We aren't getting much help from our SAN administrator.<BR>> <BR>> <BR>> <BR>> DISCLAIMER:<BR>> This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.<BR>> -- <BR>> Please see the official ORACLE-L FAQ: http://www.orafaq.net<BR>> -- <BR>> Author: <AVNISH.RASTOGI@PROVIDENCE.ORG<BR>> INET: Avnish.Rastogi@providence.org<BR>> <BR>> Fat City Network Services -- 858-538-5051 http://www.fatcity.com<BR>> San Diego, California -- Mailing list and web hosting services<BR>> ---------------------------------------------------------------------<BR>> To REMOVE yourself from this mailing list, send an E-Mail message<BR>> to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>> the message BODY, include a l! ine containing: UNSUB ORACLE-L<BR>> (or the name of mailing list you want to be removed from). You may<BR>> also send the HELP command for other information (like subscribing).<BR>> <BR><BR><BR>-- <BR>Please see the official ORACLE-L FAQ: http://www.orafaq.net<BR>-- <BR>Author: Jared Still<BR>INET: jkstill@cybcon.com<BR><BR>Fat City Network Services -- 858-538-5051 http://www.fatcity.com<BR>San Diego, California -- Mailing list and web hosting services<BR>---------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).</BLOCKQUOTE><p><hr SIZE=1> Do you Yahoo!?<br> <a href="http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/">Free Pop-Up Blocker - Get it now</a> --0-550119929-1071252481=:27442-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Drake INET: discgolfdba_at_yahoo.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Dec 12 2003 - 12:09:26 CST
 |
 |