Avnish, The book goes through this a bit on p61. There are a few tools out there, including three from Oracle. As I mention in the book, I use our own Hotsos Profiler, described at www.hotsos.com/products/profiler.html.
Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -----Original Message----- [EMAIL PROTECTED] Sent: Friday, December 12, 2003 3:44 PM To: Multiple recipients of list ORACLE-L I will try to get the output of v$system_event and will send it you guys. In the mean time I have more question.. I am reading Cary's 'Optimizing Oracle Performance Book'. I am half way thru and over looked rest of the chapters but didnt find an easy way to analyze thousands of lines trace file. I am not very good in analyzing big trace files and wondering how you guys analyze do that. Do you do it manually or use any tool to get summarized report. I didnt see anything in that book. I am also planning to take class from HotSos in Feb, 2004 in Seattle to see if that will help. I really appreciate all of your input. Thanks -----Original Message----- Jared Still Sent: Thursday, December 11, 2003 10:04 PM To: Multiple recipients of list ORACLE-L 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, [EMAIL PROTECTED] 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: <[EMAIL PROTECTED] > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). 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: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Cary Millsap INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
