RE: Know anyone?

From: mkb <mkb125_at_yahoo.com>
Date: Fri, 22 Jul 2005 06:00:32 -0700 (PDT)
Message-ID: <20050722130033.6218.qmail@web32802.mail.mud.yahoo.com>

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_at_WalkerFirst.com>


> 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

Received on Fri Jul 22 2005 - 08:02:33 CDT