Ovulation Calcualtior March Of Dimes

As UNDO troubleshoot when

can we do to detect problems cpu oi / or without having to seek assistance from the Operating Systems group?, Sounds complicated, no?. Well ... 10g from an option, at least to be having a first scenario, is to see if something is happening with the case where our database consulting in the AWR repository. To externalize these metrics there is historical view: DBA_HIST_OSSTAT, which contains 10g as follows: -------------------------------------------

STAT_NAME
BUSY_TIME

AVG_IDLE_TIME
NUM_CPUS
AVG_BUSY_TIME
OS_CPU_WAIT_TIME
VM_IN_BYTES
AVG_USER_TIME
AVG_SYS_TIME
LOAD
SYS_TIME
RSRC_MGR_CPU_WAIT_TIME

 IDLE_TIME 
 USER_TIME 
 PHYSICAL_MEMORY_BYTES 
 IOWAIT_TIME 
 AVG_IOWAIT_TIME 
 VM_OUT_BYTES <10000; i++)
 11g R1 
 and adds the following: 
 
 TCP_SEND_SIZE_DEFAULT 
 TCP_RECEIVE_SIZE_DEFAULT TCP_RECEIVE_SIZE_MAX 
 
 NUM_CPU_SOCKETS 
 TCP_SEND_SIZE_MAX 
 NUM_CPU_CORES 
 
 

Below, I copy a script I created to see some important metrics. The report takes as its only parameter the number of hours that I want to look backwards. September



line 120 pagesize

 September 9999 September 
 verify off accept hours 
 prompt "Enter number of hours you wish to report backwards" snap 
 format a20 col 
 
 <10000; i++)
 select unique to_char (snap, 'DD-MON- YYYY HH24 ') snap, 
 avg_idle_time-lead (avg_idle_time) over (partition by order by snap off st) avg_idle_time, 
 avg_user_time-lead (avg_user_time) over (partition by order by snap off st) avg_user_time, 
 avg_sys_time-lead (avg_sys_time ) over (partition by order by snap off st) avg_sys_time, 
 avg_iowait_time-lead (avg_iowait_time)  over (partition by st order by snap desc) avg_iowait_time, 
        os_cpu_wait_time-lead(os_cpu_wait_time) over (partition by st order by snap desc) os_cpu_wait_time 
 from 
 (select s.end_interval_time snap, 
         s.startup_time st, 
        max(decode(stat_name,'AVG_IDLE_TIME',value,null)) AVG_IDLE_TIME, 
        max(decode(stat_name,'AVG_USER_TIME',value,null)) AVG_USER_TIME, 
        max(decode(stat_name,'AVG_SYS_TIME',value,null)) AVG_SYS_TIME, 

max(decode(stat_name,'AVG_IOWAIT_TIME',value,null)) AVG_IOWAIT_TIME,
max(decode(stat_name,'OS_CPU_WAIT_TIME',value,null)) OS_CPU_WAIT_TIME
from dba_hist_osstat os,
dba_hist_snapshot s

 where s.snap_id = os.snap_id 
 group by s.end_interval_time,s.startup_time) 
 where snap > sysdate-&horas/24 
 order by snap desc 
 / 
  
  <10000; i++)
 SNAP         AVG_IDLE_TIME AVG_USER_TIME AVG_SYS_TIME AVG_IOWAIT_TIME OS_CPU_WAIT_TIME 
 25-FEB-10 09 211854       108399     40156  99439  2087400 
 25-FEB-10 08 271995       61131     27296  97923  1199800 
 25-FEB-10 07 236760       85951     31938  84592  1489500 
 25-FEB-10 06 180172       130112     50168  90475  2281500 
 25-FEB-10 05 182995       123247     54623  105254  2274500 
 25-FEB-10 04 193166       116204     51902  127028  2197000 
 25-FEB-10 03 195871       119849 
 45241 130806 2126400 25-FEB-10 02 236610 85916 37782 160617 1574500 
 
 

Obviously, to get an accurate diagnosis of OS resource usage ideally want the groups responsible for administering the OS, networking and storage reports historical details of activity, but if we have a first picture in fast to what is happening with only access the repository of the database, we can use the above query or any change (you can add columns to report virtual memory usage , network traffic, etc) for this to have a first impression.