ALL ABOUT SERVER MONITORING

ALL ABOUT SERVER MONITORING

This post is about all the various things you can or you have to check while monitoring your database server,you can use this post to chech various different information related to your database server.

SERVER MONITORING

       1)      CHECKING FREE SPACE IN TABLESPACES

SELECT /* + RULE */  df.tablespace_name "Tablespace",

       df.bytes / (1024 * 1024) "Size (MB)",

       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",

       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

  FROM dba_free_space fs,

       (SELECT tablespace_name,SUM(bytes) bytes

          FROM dba_data_files

         GROUP BY tablespace_name) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,df.bytes

UNION ALL

SELECT /* + RULE */ df.tablespace_name tspace,

       fs.bytes / (1024 * 1024),

       SUM(df.bytes_free) / (1024 * 1024),

       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),

       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)

  FROM dba_temp_files fs,

       (SELECT tablespace_name,bytes_free,bytes_used

          FROM v$temp_space_header

         GROUP BY tablespace_name,bytes_free,bytes_used) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

 ORDER BY 4 DESC;

2)      CHECKING TABLEPACES FOR AUTOEXTENSIBLE MODE

select file_name,autoextensible from dba_data_files where autoextensible='NO';

3)      CHECKING TEMPORARY TABLEPACES FOR AUTOEXTENSIBLE 

MODE

select file_name,autoextensible from dba_temp_files;

      4)  CHECKING THE DATABASE  FOR 32 AND 64 BIT

 select
   length(addr)*4 || '-bits' word_length
from
   v$process
where
   ROWNUM =1;

      

      5)      CHECKING THE DATABASE SIZE

       select sum(bytes / (1024*1024*1024)) "DB Size in GB" from dba_data_files;

6)     CHECKING DATAFILES IN PARTICULAR TABLESPACE

       select tablespace_name,file_name from dba_data_files;

7)    CHECKING SEGMENT SIZE IN ORACLE

       select sum(bytes/1024/1024/1024) "SEGMENT Size in GB"  from dba_segments;

8)      CHECKING LOG SWITCH INTERVAL FOR THE DAY

         SELECT

    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)                          DAY

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23

  , COUNT(*)                                                                      TOTAL

FROM

  v$log_history  a

WHERE

    (TO_DATE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')

     >=

     TO_DATE('&startDate', 'DD-MON-YYYY')

     )

     AND

    (TO_DATE(substr(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')

     <=

     TO_DATE('&endDate', 'DD-MON-YYYY')

     )

GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)

/

     9)    CHECKING FOR CHAINED ROWS

       select * from v$sysstat where name like 'table scan rows gotten';

       select * from v$sysstat where name like 'table fetch by rowid';

       select * from v$sysstat where name like 'table scan blocks gotten';

       select * from v$sysstat where name like 'table fetch continued row';

    10)   CHECKING TOTAL I/O PER USER

 

         select

   sid,
   username,
   round(100 * total_user_io/total_io,2) tot_io_pct
from
(select

     b.sid sid,
     nvl(b.username,p.name) username,
     sum(value) total_user_io
 from

     sys.v_$statname c, 
     sys.v_$sesstat a,
     sys.v_$session b,
     sys.v_$bgprocess p
 where

      a.statistic#=c.statistic# and
      p.paddr (+) = b.paddr and
      b.sid=a.sid and
      c.name in ('physical reads',
                 'physical writes',
                 'physical writes direct',
                 'physical reads direct',
                 'physical writes direct (lob)',
                 'physical reads direct (lob)')
group by

      b.sid, nvl(b.username,p.name)),
(select

      sum(value) total_io
 from

      sys.v_$statname c,
      sys.v_$sesstat a
 where

      a.statistic#=c.statistic# and
      c.name in ('physical reads',
                 'physical writes',
                 'physical writes direct',
                 'physical reads direct',
                 'physical writes direct (lob)',
                 'physical reads direct (lob)'))
order by

      3 desc;

 

              

11)   CHECKING DATABASE FOR VALID AND INVALID OBJECTS

 

 select count(*)  from dba_objects where status='VALID';

 select count(*)  from dba_objects where status='INVALID';

 

 

 

              12)   CHECK THE DATABASE WETHER IT IS BEING UP BY PFILE OR SPFILE

                  

 select decode(count(*), 1, 'spfile', 'pfile' ) from v$spparameter where rownum=1 and  isspecified='TRUE';

                               

              13)   CHECKING THE CONTENT OF THE CONTROLFILE

                       SELECT * FROM v$controlfile_record_section;

              14)   CREATING PFILE FROM SPFILE

        

            create pfile='/oracle/oracle/newpfile.ora' from spfile;

             15)   CHECKING FREE SPACE IN TEMPORARY TABLESPACE

select TABLESPACE_NAME, sum(BYTES_USED/1024/1024/1024)  from V$TEMP_SPACE_HEADER group by tablespace_name;

select TABLESPACE_NAME, sum(BYTES_FREE/1024/1024/1024) from V$TEMP_SPACE_HEADER group by tablespace_name;

           

16)  VIEWING  TOTAL SIZE OF TEMPORARY TABLESPACE

               select sum(bytes / (1024*1024*1024)) "TEMPFILE Size in GB" from dba_temp_files;

17)    FOR CHECKING THE GETS AND WAITS OF THE ROLLBACK SEGMENTS

                 SELECT rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets",

                rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits",

                rs.Shrinks "# Shrinks", rs.Extends "# Extends"

               FROM   sys.v_$rollName rn, sys.v_$rollStat rs

               WHERE  rn.usn = rs.usn;

18)    FOR CHECKING THE TOTAL NUMBER OF ROLLBACK SEGMENTS 

REQUIRED

show parameter transaction;

NOTE:- now divide the total number of transactions by the transaction_per_rollback_segments then you will get the total number of  rollback segments required

              19)   CHECKING THE ONLINE STATUS FOR THE DATAFILE

select status,tablespace_name from v$datafile_header;

select tablespace_name,online_status from dba_data_files;

    20)  CHECKING THE MAXIMUM NUMBER OF ROLES ENABLED IN THE DATABASE

SELECT grantee, count(*)

FROM (SELECT grantee, granted_role

FROM dba_role_privs

CONNECT BY PRIOR grantee = granted_role)

GROUP BY grantee

HAVING count(*) = (SELECT max(count(*))

FROM (SELECT grantee, granted_role

FROM dba_role_privs

CONNECT BY PRIOR grantee=granted_role)

GROUP BY grantee);

show parameter max_enabled_roles;

21)  CHECKING THE TOTAL NUMBER OF ROLES GRANTED TO USERS

select "Grantee", count(*) "Role Number" from
(
select distinct connect_by_root grantee "Grantee", granted_role
from dba_role_privs
connect by prior granted_role=grantee
)
group by "Grantee"
order by "Role Number"
/

22)  CHECKING THE LOG GENERATION TIME

select SEQUENCE#,to_char(COMPLETION_TIME,'dd-mon-yy hh24:mi:ss') from v$archived_log;

23)  CHECKING LOCKING STATUS

      select OBJECT_ID,SESSION_ID,ORACLE_USERNAME from  v$locked_object;

     

24)  FOR GETTING RESORCE USAGE STATUS

select * from v$resource_limit;

25)   CALCULATING BUFFER CACHE HIT RATIO

select 'BUFFER CACHE HIT RATIO -- '|| ROUND((1 - (sum(decode(name,'physical reads',value,0) )/(sum(decode(name,'consistent gets',value,0))+sum(decode(name,'db block gets',value,0)))) )*100,2) HIT_RATIO

from v$sysstat

where name in ('physical reads','consistent gets','db block gets');

26)  CALCULATING SQL AREA HIT RATIO

SELECT 'SQL AREA HIT RATIO  -- '||ROUND((1 - SUM(RELOADS)/(SUM(PINS)+ SUM(RELOADS)))*100,2) FROM V$LIBRARYCACHE;

27)  CALCULATING DB CACHE HIT RATIO

SELECT 'DB CACHE HIT RATIO -- '|| ROUND((1 - SUM(GETMISSES)/(SUM(GETS) + Sum(getmisses)))*100,2) FROM V$ROWCACHE;

28)  CALCULATING LATCH HIT RATIO

SELECT 'LATCH HIT RATIO -- ' || ROUND((1 - (Sum(misses) / Sum(gets))) * 100,2) FROM   v$latch;

29)  CHECKING THE LIBRARY CACHE HIT RATIO

select sum(pins),sum(reloads),sum(reloads)/sum(pins) from v$librarycache;

30)  DETERMINE CURRENT ONGOING TRANSACTIONS

SELECT a.name,b.status

FROM v$rollname a, v$rollstat b

WHERE a.name IN ( SELECT segment_name

FROM dba_segments

WHERE tablespace_name = 'UNDOTBS1'

)

AND a.usn = b.usn;

31)   CHECKING LOCKING TABLES

select
  object_name,
  object_type,
  session_id,
  type,                 -- Type or system/user lock
  lmode,        -- lock mode in which session holds lock
  request,
  block,
  ctime                 -- Time since current mode was granted
from
  v$locked_object, all_objects, v$lock
where
  v$locked_object.object_id = all_objects.object_id AND
  v$lock.id1 = all_objects.object_id AND
  v$lock.sid = v$locked_object.session_id
order by
  session_id, ctime desc, object_name
/

32)  CHEKING THE SPEED OF IMPORT

SELECT

        SUBSTR(sql_text, INSTR(sql_text,'INTO "'),30) table_name

      , rows_processed

      , ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes

      , TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute

    FROM

        sys.v_$sqlarea

    WHERE

          sql_text like 'INSERT %INTO "%'

      AND command_type = 2

      AND open_versions > 0;

33)   CHECKING THE SIZE NEEDED FOR UNDO TABLESPACE

SELECT (UR * (UPS * DBS)) + (DBS * 24) AS " Bytes"

FROM (SELECT value AS UR

FROM v$parameter

WHERE name = 'undo_retention'),

(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS

FROM v$undostat),

(SELECT value AS DBS

FROM v$parameter

WHERE name = 'db_block_size');

34)   CHECKING FREE SPACE IN TABLESPACES

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

35)  CHEKING SIZE OF CONTROLFILE

select name,BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024 as MB from v$controlfile;