RE: Know anyone?

From: Magnus Andersen <Magnus.Andersen_at_WalkerFirst.com>
Date: Fri, 22 Jul 2005 09:28:35 -0400
Message-ID: <D30EE05C6F719448A35DD64AFD6B38510BC77B@orbis.walkerassoc.com>

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_at_yahoo.com]
Sent: Friday, July 22, 2005 9:01 AM
To: Magnus.Andersen_at_WalkerFirst.com
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_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:30:41 CDT