RE: Know anyone?
- From: Magnus Andersen <Magnus.Andersen@xxxxxxxxxxxxxxx>
- To: 'mkb' <mkb125@xxxxxxxxx>
- Date: Fri, 22 Jul 2005 09:28:35 -0400
1. Iostat looks good. Vmstat indicated memory contention, because RedHat cache has it all. 2. Sar indicates that all looks good. 3. Been all over it and used it together with Oracle docs to setup server. 4. Great script and I am going to use it next spike. Thanks. Also, I have sarcheck installed on the server and the analysis from it is that the server can support a substantial increase in workload before impending CPU, memory, or disk bottlenecks. I've tried to tune the vm parameters and it help changing the pagecache parameter to '1 2 7' instead of default '1 15 30'. Before I did that I had a huge load average all the time. I've also played with bdflush without any gain in perfomance. Thanks, Magnus -----Original Message----- From: mkb [mailto:mkb125@xxxxxxxxx] Sent: Friday, July 22, 2005 9:01 AM To: Magnus.Andersen@xxxxxxxxxxxxxxx Cc: Oracle-L (E-mail) Subject: RE: Know anyone? Sounds a similar config to what I have. 1. Have you run vmstat and iostat during that period? Be interesting to see what it shows (vmstat 5, iostat 5 and iostat -x 5 or 10 second intervals). 2. Have you looked at sar output? Should be a bunch of sar files in /var/log/sa. 3. Check http://www.puschitz.com/TuningLinuxForOracle.shtml for more information. 4. Personally, I also dump all active SQL statements to see which is taking the most time and investigate further. Here is my version of the script: dump_sql.sql set serveroutput on size 1000000 declare num_rows integer := 0; begin for recSQL_HASH in (select username, last_call_et, to_char(logon_time, 'YYYYMMDD hh24miss') logon_time, sid, serial#, sql_hash_value from v$session where username is not null and status = 'ACTIVE' and sql_hash_value > 0 order by last_call_et) loop dbms_output.put_line('________________________________________________'); dbms_output.put_line('username last_call_et logon_time sid serial# hash'); dbms_output.put_line(recSQL_HASH.username||' '|| recSQL_HASH.last_call_et||' '|| recSQL_HASH.logon_time||' '|| recSQL_HASH.sid||' '|| recSQL_HASH.serial#||' '|| recSQL_HASH.sql_hash_value); dbms_output.put_line('________________________________________________'); for recSQL_TEXT in (select sql_text from v$sqltext where hash_value = recSQL_HASH.sql_hash_value order by piece) loop dbms_output.put_line(recSQL_TEXT.sql_text); end loop; num_rows := num_rows + 1; end loop; dbms_output.put_line('________________________________________________'); dbms_output.put_line('Number of Active Sessions = '||num_rows); dbms_output.put_line('________________________________________________'); exception when others then dbms_output.put_line('SQL ERROR '||sqlcode||' '||sqlerrm); end; / -- mohammed --- Magnus Andersen <Magnus.Andersen@xxxxxxxxxxxxxxx> > Magnus Andersen wrote: > > Hi All, > > > > I am in need of a consultant that is heavy on > RedHat Linux real world > > experience for an emergency situation. > > > > Here is the situation: I have recently gone live > with our production > > database on RedHat Linux 3 AS and I am having a > few issues. My biggest > > problem right now is that I am seeing spikes in my > load average, > > resulting in my system slowing down, throughout > the day as soon as I > > have 6 - 7 or more two tier connections to the > database. > > > > Hardware: HP DL 580, 4 3 GHZ CPUs with 512k L1 > Cache and 4 MB L2 Cache, > > HDDs is in RAID 10 config, all Ultra 320 36 GB > 15000RPM. > > > > Thanks in advance, > > > > Magnus Andersen > > Systems Administrator / Oracle DBA > > Walker & Associates, Inc. > > > > -- > Legal Disclaimer: The statements expressed here are > of my own and do not > represent those of Yahoo Inc ! > -- > http://www.freelists.org/webpage/oracle-l > ____________________________________________________ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- http://www.freelists.org/webpage/oracle-l
- Re: Know anyone?
- From: Nuno Souto
- Re: Know anyone?