1 Reply Latest reply: Nov 26, 2012 3:54 AM by 975952 RSS

    lmnop

    975952
      ===========================================
      DB-Maintenance checks
      ===========================================

      What is the Library Cache Hit Ratio, it should be >90%

      SELECT SUM (PINHITS) / SUM (PINS) * 100 FROM V$LIBRARYCACHE;

      What is Library Cache Reloads Ratio, It should be <1%

      SELECT SUM (PINS), SUM (RELOADS), SUM (RELOADS) / SUM (PINS) FROM V$LIBRARYCACHE;

      Dictionary Cache Miss Ratio should be <15%

      SELECT (SUM (GETMISSES) / SUM (GETS)) * 100 FROM V$ROWCACHE;

      Hit Ratio For DB Buffer Cache should be >90%

      Select (sum(GETS-GETMISSES)) / SUM(GETS)*100 "Dictionary Cache Hit Ratio" From v$rowcache;

      Full Table Scans Ratio should be <5%

      SELECT D.VALUE "disk", M.VALUE "mem", (D.VALUE / M.VALUE) * 100 "Ratio" FROM V$SYSSTAT M, V$SYSSTAT D WHERE M.NAME = 'sorts (memory)' AND D.NAME = 'sorts (disk)';

      If Full Table Scan is more than 5% run below query to find full details:-

      SELECT * FROM V$SYSSTAT WHERE NAME LIKE '%table scan%';


      #echo 'ALERT - Oracle Access (HRMI - 192.168.68.212) on:' `date` `who` | mail -s "Alert: Oracle Access from `who -m | cut -d"(" -f2 | cut -d")" -f1`" pradeep.v@four-soft.com

      =======================================================


      RMAN Backup job details:
      ------------------------

      select to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,INPUT_TYPE,STATUS,to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
      elapsed_seconds/3600 hrs,OUTPUT_BYTES_DISPLAY from V$RMAN_BACKUP_JOB_DETAILS order by session_key ;


      RMAN Backup details:
      --------------------
      select to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,INPUT_TYPE,STATUS,to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
      elapsed_seconds/3600 hrs,OUTPUT_BYTES_DISPLAY from V$RMAN_BACKUP_JOB_DETAILS order by session_key ;


      Waits by class:
      ---------------
      Select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits) Sum_Waits From v\$system_wait_class Group by wait_class Order by 3 desc;


      waits by instance:
      ------------------
      select event, time_waited from v\$system_event where ROWNUM <= 10 order by 1;


      waits datafile level:
      ---------------------
      select f.file_name "Data File",count(*) "Wait Number",sum(h.time_waited) "Total Time Waited" from v$active_session_history h,dba_data_files f where h.sample_time between sysdate - 1/24 and sysdate
      and h.current_file#=f.file_id
      group by f.file_name
      order by 3 desc;

      waiting sessions sql:
      ---------------------
      select h.user_id,u.username,s.sql_text,sum ( h.wait_time + h.time_waited ) "Total wait time" from v$active_session_history h, v$sqlarea s,dba_users u,v$event_name e where h.sample_time between sysdate - 1/24 and sysdate and h.sql_id=s.sql_id and h.user_id = u.user_id and e.event_id = h.event_id and e.wait_class <> 'idle'
      group by h.user_id,s.sql_text,u.username order by 4 desc;

      what are users currently waiting on:
      ------------------------------------
      select s.sid,s.username,sum(h.wait_time+h.time_waited) " Total Waited Time " from v$active_session_history h,v$session s,v$event_name e where h.sample_time between sysdate - 1/24 and sysdate
      and h.session_id = s.sid
      and e.event_id = h.event_id
      and e.wait_class <> 'idle'
      and s.username is not null
      group by s.sid,s.username
      order by 1;



      buffer busy waits:
      ------------------
      select owner, segment_name, segment_type from dba_extents a, v$session_wait b
      where b.event='buffer busy waits' and a.file_id=b.p1;


      blocked sessions:
      -----------------
      SELECT b.session_id AS sid,
      NVL(b.oracle_username, '(oracle)') AS username,
      a.owner AS object_owner,
      a.object_name,
      Decode(b.locked_mode, 0, 'None',
      1, 'Null (NULL)',
      2, 'Row-S (SS)',
      3, 'Row-X (SX)',
      4, 'Share (S)',
      5, 'S/Row-X (SSX)',
      6, 'Exclusive (X)',
      b.locked_mode) locked_mode,
      b.os_user_name
      FROM dba_objects a,
      v$locked_object b
      WHERE a.object_id = b.object_id
      ORDER BY 1, 2, 3, 4;


      large objects in shared pool:
      -----------------------------

      select OWNER,NAME||' - '||TYPE object,SHARABLE_MEM
      from v\$db_object_cache
      where SHARABLE_MEM > 10000
      and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
      order by SHARABLE_MEM desc;


      blocked sessions:
      -----------------

      SELECT b.session_id AS sid,
      NVL(b.oracle_username, '(oracle)') AS username,
      a.owner AS object_owner,
      a.object_name,
      Decode(b.locked_mode, 0, 'None',
      1, 'Null (NULL)',
      2, 'Row-S (SS)',
      3, 'Row-X (SX)',
      4, 'Share (S)',
      5, 'S/Row-X (SSX)',
      6, 'Exclusive (X)',
      b.locked_mode) locked_mode,
      b.os_user_name
      FROM dba_objects a,
      v\$locked_object b
      WHERE a.object_id = b.object_id
      ORDER BY 1, 2, 3, 4;


      Find out SGA usage:
      ------------------

      select round(used.bytes /1024/1024 ,2) used_mb,
      round(free.bytes /1024/1024 ,2) free_mb,
      round(tot.bytes /1024/1024 ,2) total_mb
      from
      (select sum(bytes) bytes from v$sgastat where name != 'free memory') used ,
      (select sum(bytes) bytes from v$sgastat where name = 'free memory') free ,
      (select sum(bytes) bytes from v$sgastat) tot

      ==============================================


      disk capacity: fdisk -l

      Total memory: grep MemTotal /proc/meminfo

      CPU Deatils: cat /proc/cpuinfo

      OS BIt: uname -a

      OS Version: cat /etc/redhat-release

      Check CPU is 32/64 Bit: getconf LONG_BIT


      =====================================================
      Sun OS:
      ==========
      check Total physical memory:

      # prtdiag -v | grep Memory

      # prtconf | grep Memory

      -----------------------

      check Free physical Memory:

      # top (if available)

      # sar -r 5 10
      Free Memory=freemen*8 (pagesize=8k)

      # vmstat 5 10
      Free Memory = free

      --------------------

      For swap:

      # swap -s
      # swap -l


      OS BIt: isalist (sparcv9,amd64 then 64bit)


      OS Version: cat /etc/release

      CPU Deatils: psrinfo -v
      ================================================
      Established sessions for specific port:

      netstat -an|grep :1800|sort|wc -l

      netstat -an|grep :1800|sort|grep 'ESTABLISHED'|wc -l

      =====================================================