日常监控一的结果贴不出来,郁闷.本来可以分享下的

现在贴个check检查的脚本,检查最坏的sql.,热对象,当前锁以及,sort,sga各种

hit,buffer cache hit等.测试过的结果下次贴,篇幅有限- -!

#!/bin/bash
source ~/.bash_profile

LOC=/home/oracle/script/dbmon
find ~/ -name "$LOC"

if [ $? -ne 0 ];
then mkdir -p $LOC
fi
############Check start!###########################

if [ -f '$LOC/check.log' ];
then rm -rf $LOC/check.log
fi

sqlplus / as sysdba <

spool '$LOC/check.lst';

select '********This is the Highest Fragment tablespace********' from dual;
select tablespace_name,count(tablespace_name) "FRAGMENT_NUM_CNT" from dba_free_space group by tablespace_name having count(tablespace_name)>10;

select '********This is the IO of all the tablespaces********' from dual;
set line 400;
column name format a100;
select substr(a.file#,1,2) "#", substr(a.name,1,70) "Name",a.status, a.bytes/(1024*1024) "Size(M)", b.phyrds, b.phywrts from v\$datafile a, v\$filestat b where a.file# = b.file# order by 5,6 desc;

select '********This is the TEMP Space Usage********' from dual;
select tmp.TABLESPACE_NAME,tmp.FILE_ID,tmp.BYTES_USED/(1024*1024) "USED(M)",tmp.BYTES_FREE/(1024*1024) "FREE(M)",tmp.BLOCKS_USED "BLOCK_USED",tmp.BLOCKS_FREE "BLOCK_FREE",tmp.RELATIVE_FNO from v\$temp_space_header tmp;

select '********This is the Undo Space Usage********' from dual;
select name, waits, gets, 1-waits/gets "Ratio(%)" from v\$rollstat a, v\$rollname b where a.usn = b.usn;

select '********This is the Hotest Object********' from dual;
column object_name format a30;
select object_id,object_name,object_type from dba_objects where object_id=(select * from (select obj from x\$bh order by tch desc) where rownum<=1);

select '********This is the Worsted Sql********' from dual;
set head on;
set line 250;
column sql_text format a100;
select * from (select a.sql_id,SQL_TEXT,a.BUFFER_GETS,a.EXECUTIONS,a.BUFFER_GETS/a.EXECUTIONS avg from v\$sqlarea a where a.EXECUTIONS>0 and a.BUFFER_GETS<10000 order by 5 desc) where rownum<=10;

select '********This is the Frequentest Sql********' from dual;
set line 400;
select a.SQL_ID,a.SQL_FULLTEXT,a.EXECUTIONS from v\$sqlarea a where a.EXECUTIONS=(select max(executions) from v\$sqlarea );

select '********This is the Full Scan Tables********' from dual;
column operation format a20;
column options format a20;
select * from (select p.SQL_ID,p.OPERATION,p.OPTIONS,p.OBJECT#,p.OBJECT_OWNER,p.OBJECT_NAME,p.CPU_COST,p.IO_COST from v\$sql_plan p where p.operation='TABLE ACCESS' and p.options='FULL' and p.object_owner not in('SYS','SYSAUX','SYSTEM') order by 7,8) where rownum<=10;

select '********This is User Lock status of current Database,not Include backupreound process.If no rows return,Congratulations!********' from dual;

set line 250;
column username format a15;
column terminal format a20;
column l_type format a20;
column tab format a15;
column owner format a15;
select l.sid,s.serial#,s.username,s.terminal, decode(l.type,'RW','RW - Row Wait Enqueue', 'TM','TM - DML Enqueue', 'TX','TX - Trans Enqueue', 'UL','UL - User',l.type||'System') l_type, substr(t.name,1,10) tab,u.name owner, l.id1,l.id2, decode(l.lmode,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Shr Row Excl', 6,'Exclusive',null) lmode, decode(l.request,1,'No Lock', 2,'Row Share', 3,'Row Excl', 4,'Share', 5,'Shr Row Excl', 6,'Exclusive',null) request from v\$lock l, v\$session s, sys.user\$ u,sys.obj\$ t where l.sid = s.sid and s.type != 'BACKGROUND' and t.obj# = l.id1 and u.user# = t.owner#;

select '********This is Top 10 CPU Usage Process and Sessions********' from dual;
set line 200;
column prog format a20;
select * from (select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value from v\$session a,v\$process b,v\$sesstat c where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc) where rownum<=10;

select '********This is Wait Events.If now rows return,There seems now problem!********' from dual;
select * from (select event,sum(wait_Time) "Pre", sum(wait_Time) "Cur",count(*) "Total_Record" from v\$session_Wait where wait_time >0 group by event order by 4 desc) where rownum<=5;

select '********This is Libarary Hit,Suggestion>90%********' from dual;
select 1-sum(l.RELOADS)/sum(l.PINS) "Librarycache hit(%)" from v\$librarycache l;

select '********This is Dictionary Hit,Suggestion>90%********' from dual;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE HINT Ratio(%)" FROM V\$ROWCACHE;

select '********This is Buffer Cache Hit,Suggestion>90%********' from dual;
SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value) "Buffer cache Hit(%)" FROM v\$sysstat P1, v\$sysstat P2, v\$sysstat P3 WHERE P1.name = 'db block gets' AND P2.name = 'consistent gets' AND P3.name = 'physical reads';

select '********This is Memory/Total Hit,Suggestion>90%********' from dual;
select sort_mem.value "Sort memory" ,sort_dsk.value "Sort disk",(sort_mem.value/(sort_dsk.value+sort_mem.value))*100 "Sort mem_hit(%)" from v\$sysstat sort_mem,v\$sysstat sort_dsk where sort_mem.name like '%sort%memory%' and sort_dsk.name like '%sorts%disk%';

select '********Log Buffer Hit,Suggestion>90%********' from dual;
column name format a20;
SELECT name, gets, misses, immediate_gets, immediate_misses,Decode(gets,0,0,1-misses/gets*100) "Hit1(%)",Decode(immediate_gets+immediate_misses,0,0,1-immediate_misses/(immediate_gets+immediate_misses)*100) "Hit2(%)" FROM v\$latch WHERE name IN ('redo allocation', 'redo copy');

select '********This Sorft Parse,Suggestion>90%********' from dual;
select p_hard.value "Parse Hard",p_fail.value "Parse Fail",p_total.value "Parse Total",(1-(p_hard.value+p_fail.value)/p_total.value) "Soft Parse Hit(%)" from v\$sysstat p_hard,v\$sysstat p_total,v\$sysstat p_fail where p_hard.name like '%parse%hard%' and p_fail.name like '%parse%failures%' and p_total.name like '%parse%total%';

select '********This is End of Check ********' from dual;
spool off;
EOF

more $LOC/check.lst |sed '/SQL>/d'>/$LOC/check.log_$(date +'%F-%X')

if [ $? -eq 0 ];
then rm -rf $LOC/check.lst
fi

find /share -name "check_cur"

if [ $? -ne 0 ];
then mkdir -p /share/check_cur
fi

mv $LOC/check.log_$(date +'%F-%X') /share/check_cur

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15072844/viewspace-622010/ ，如需转载，请注明出处，否则将追究法律责任。

ITPub博客

日常监控二

最新文章