oracle 監控指令碼

prompt
prompt ---------------- database information ----------------

set wrap off
set linesize 100
column name format a10
column log_mode format a16
column open_mode format a15
column remote_archive format a16
column database_role format a20

select name,log_mode,open_mode,remote_archive,database_role
from v$database;


prompt
prompt ---------------- instance information ----------------

set wrap off
set linesize 100
column instance_name format a15
column host_name format a10
column version format a10
column status format a8
column parallel format a8
column database_status format a15
column instance_role format a16

select instance_name,host_name,version,status,parallel,database_status,instance_role
from v$instance;


prompt
prompt ---------------- thread information ----------------

set wrap off
set linesize 100
column thread# format 9
column instance format a15

select thread#,status,instance,checkpoint_time
from v$thread;


prompt
prompt ---------------- SGA ----------------

select *  from v$sga;


prompt
prompt ---------------- Check User Tablespace ----------------

col username form. a15
col account_status form. a20
col default_tablespace form. a20
col temporary_tablespace form. a20

select username,account_status,default_tablespace,temporary_tablespace
from dba_users
order by username;


prompt
prompt ---------------- check tablespace ----------------

set wrap off
set linesize 100
col host_name for a10
col tablespace_name for a15

SELECT D.TABLESPACE_NAME,
       D.STATUS,
       ROUND((A.BYTES / 1024 / 1024), 2) SUM_SPACE,
       ROUND(((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024),
             2) USED_SPACE,
       ROUND(((A.BYTES / 1024 / 1024) -
             (A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024),
             2) FREE_SPACE,
       (100 - ROUND((((A.BYTES / 1024 / 1024) -
             (A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024) /
             (A.BYTES / 1024 / 1024)) * 100.0,
             2)) "USED_RATE(%)"
  FROM SYS.DBA_TABLESPACES D, SYS.SM$TS_AVAIL A, SYS.SM$TS_FREE F
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME
   AND F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
UNION ALL
SELECT d.tablespace_name,
       d.status,
       ROUND((a.bytes / 1024 / 1024), 2) SUM_SPACE,
       ROUND(NVL(t.bytes, 0) / 1024 / 1024, 2) USED_SPACE,
       ROUND((a.bytes / 1024 / 1024) - (NVL(t.bytes, 0) / 1024 / 1024), 2) FREE_SPACE,
       (100 - ROUND(100 - (NVL(t.bytes / a.bytes * 100, 0)), 2)) "USED_RATE(%)"
  FROM sys.dba_tablespaces d,
       (select tablespace_name, sum(bytes) bytes
          from dba_temp_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes_cached) bytes
          from SYS.v_$temp_extent_pool
         group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = t.tablespace_name(+)
   AND d.extent_management like 'LOCAL'
   AND d.contents like 'TEMPORARY';
prompt
prompt ---------------- check invalid objects ----------------

set wrap off
set linesize 100
col owner for a10
col object_name for a35
SELECT owner,OBJECT_TYPE,object_name,STATUS FROM ALL_OBJECTS WHERE  status<>'VALID';


prompt
prompt ---------------- check kill session ----------------

set linesize 150
col host_name for a10
col OS_USER_NAME for a20
col ORACLE_USERNAME for a20
col MACHINE for a20

select s.INST_ID,s.sid,s.SERIAL#,s.MACHINE,s.PROGRAM
from gv$session s,gv$locked_object l
where l.SESSION_ID =s.SID and l.PROCESS=s.PROCESS
order by s.MACHINE;


prompt
prompt ---------------- check locked object ----------------

set linesize 100
col username for a15
col sid for 999999
col serial# for 999999

select s.username, s.sid, s.serial#, s.logon_time
from v$locked_object l, v$session s
where l.session_id = s.sid
order by s.logon_time;


prompt
prompt ---------------- Buffer Hit Ratio ----------------

select
  round(
    (1 - (sum(decode(name, 'physical reads', value, 0)) /
    (sum(decode(name, 'db block gets', value, 0)) +
    sum(decode(name, 'consistent gets', value, 0))))) * 100
, 2) || '%' "Buffer Hit Ratio"
from v$sysstat;


prompt
prompt ---------------- Dictionary Hit Ratio ----------------

set linesize 30

select round((1 - (sum(getmisses) / sum(gets))) * 100, 2) || '%' "Dictionary Hit Ratio"
from v$rowcache;


prompt
prompt ---------------- Library Hit Ratio ----------------

set linesize 30

select round(sum(pins) / (sum(pins) + sum(reloads)) * 100, 2) || '%' "Library Hit Ratio"
from v$librarycache;


/*
prompt
prompt ---------------- Free Buffer Ratio ----------------

set linesize 100
col "Block Status" for a20

select decode(state,0,'FREE',1,decode(lrba_seq, 0, 'AVAILABLE', 'BEING USED'),3,'BEING USED',state) "Block Status",count(*)
from x$bh
group by decode(state,0,'FREE',1,decode(lrba_seq, 0, 'AVAILABLE', 'BEING USED'),3,'BEING USED',state);
*/



prompt
prompt ---------------- Redo Hit Ratio ----------------

set linesize 100
col name for a15
col ratio1 for a10
col ratio2 for a10

select name, gets, misses, immediate_gets, immediate_misses,
round((100 - decode(gets,0,0,misses/(gets+misses))*100),2) || '%' ratio1,
round((100 - decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses))*100),2) || '%' ratio2
from v$latch where name in ('redo allocation', 'redo copy');


prompt
prompt ---------------- TOP 5 SQL Used Frequently ----------------

set linesize 150
col sql_text format a85

select sql_text, executions
from
(select sql_text, executions,
rank() over (order by executions desc) exec_rank
from v$sql)
where exec_rank <= 5;


prompt
prompt ---------------- TOP 5 SQL Disk Read ----------------

set linesize 150
col sql_text format a85

select sql_text, disk_reads
from
(select sql_text, disk_reads,
dense_rank() over (order by disk_reads desc) disk_reads_rank
from v$sql)
where disk_reads_rank <= 5;


prompt
prompt ---------------- SQL Run Long Time ----------------

set wrap on

select  a.inst_id,c.PID,c.spid "spid is os_process",a.sid,a.serial#,a.user#,a.lockwait,a.status,a.MACHINE,a.schemaname,a.program,a.module,a.logon_time,a.client_info,b.SQL_TEXT
from gv$session a,gv$sqlarea b,gv$process c
where a.SQL_ADDRESS=b.ADDRESS and c.ADDR=a.PADDR
and a.SCHEMANAME<>'SYS'
and a.status<>'INACTIVE'
and a.LOGON_TIME<=trunc(sysdate-1/120,'HH24');


prompt
prompt ---------------- 碎片程度高的資料物件 ----------------

set wrap off

SELECT owner, segment_name,segment_type,tablespace_name, count(*) extents
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY owner, segment_name,segment_type,tablespace_name
HAVING COUNT(*) > 2;


prompt
prompt ---------------- Check Data File Autoextensible ----------------

set wrap off
set linesize 100
col file_name format a60
col autoextensible format a15

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


prompt
prompt ---------------- sorts in memeory and disk ----------------

SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts (memory)','sorts (disk)');


prompt
prompt ---------------- Datafile I/O ----------------

set wrap off
set linesize 150
col file# format 999
col name format a50
col status format a10
col M format 999999

select a.file#, a.name, a.status, a.bytes/1024/1024 M, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;


prompt
prompt ---------------- Tablespace I/O ----------------

set wrap off
set linesize 100
col tablespace_name format a15
col file_name format a40
col phyrds format 999999999
col phyblkrd format 999999999
col phywrts format 999999999
col phyblkwrt format 999999999

select df.tablespace_name,df.file_name,
f.phyrds,f.phyblkrd,f.phywrts, f.phyblkwrt
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;




exit;