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 !
> --
> //www.freelists.org/webpage/oracle-l
>
____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
--
//www.freelists.org/webpage/oracle-l
Other related posts:
