This discussion is archived
1 Reply Latest reply: Nov 26, 2012 1:54 AM by 975952 RSS

lmnop

975952 Newbie
Currently Being Moderated
===========================================
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

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points