RE: Know anyone?
- From: mkb <mkb125@xxxxxxxxx>
- To: Magnus.Andersen@xxxxxxxxxxxxxxx
- Date: Fri, 22 Jul 2005 06:00:32 -0700 (PDT)
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:
