实战:oracle巡检脚本v1(十四)
********************" yellow
echo
cechon "7.oracle database latch and lock:" green
echo
echo 7.1-7.20 latch
echo 7.21-7.30 lock
cechon "***********************************************************************" yellow
echo
#7.1 latch wait top 20
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 71.txt
select name,gets,misses,wait_time from v\$latch
where misses !=0 and rownum<=20
order by wait_time desc;
spool off
exit;
!01
cechon "7.1 latch wait top 20 is: " red
echo
cat 71.txt
echo
rm -rf 71.txt
#7.2 latch hits ratio
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 72.txt
SET LINESIZE 200
COLUMN latch_hit_ratio FORMAT 999.00
SELECT a.name,
a.gets,
a.misses,
((1 - (a.misses / a.gets)) * 100) AS latch_hit_ratio
FROM v\$latch a
WHERE a.gets != 0
UNION
SELECT b.name,
b.gets,
b.misses,
100 AS latch_hit_ratio
FROM v\$latch b
WHERE b.gets = 0
ORDER BY 4 DESC;
spool off
exit;
!01
cechon "7.2 latch hit ratio is: " red
echo
cat 72.txt
echo
rm -rf 72.txt
#7.3 latch hits ratio
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 73.txt
column name format a30
column pct_of_gets format 99.00 heading "Pct of Gets |"
column pct_of_misses format 99.00 heading "Pct of Misses |"
column pct_of_sleeps format 99.00 heading "Pct of Sleeps |"
column pct_of_wait_time format 99.00 heading "Pct of|Wait Time |"
WITH latch AS (
SELECT name,
ROUND(gets * 100 / SUM(gets) OVER (), 2) pct_of_gets,
ROUND(misses * 100 / SUM(misses) OVER (), 2) pct_of_misses,
ROUND(sleeps * 100 / SUM(sleeps) OVER (), 2) pct_of_sleeps,
ROUND(wait_time * 100 / SUM(wait_time) OVER (), 2)
pct_of_wait_time
FROM v\$latch)
SELECT *
FROM latch
WHERE pct_of_wait_time >
.1 OR pct_of_sleeps > .1
ORDER BY pct_of_wait_time DESC;
spool off
exit;
!01
cechon "7.3 latch hit ratio is: " red
echo
cat 73.txt
echo
rm -rf 73.txt
#7.21 total waiting time after started the database
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 721.txt
column wait_type format a35
column lock_name format a12
column total_waits format 999,999,999
column time_waited_seconds format 999,999.99
column pct format 99.99
WITH system_event AS
(SELECT CASE WHEN event LIKE 'enq:%'
THEN event ELSE wait_class
END wait_type, e.*
FROM v\$system_event e)
SELECT wait_type,SUM(total_waits) total_waits,
round(SUM(time_waited_micro)/1000000,2) time_waited_seconds,
ROUND( SUM(time_waited_micro)
* 100
/ SUM(SUM(time_waited_micro)) OVER (), 2) pct
FROM (SELECT wait_type, event, total_waits, time_waited_micro
FROM system_event e
UNION
SELECT 'CPU', stat_name, NULL, VALUE
FROM v\$sys_time_model
WHERE stat_name IN ('background cpu time', 'DB CPU')) l
WHERE wait_type <> 'Idle'
GROUP BY wait_type
ORDER BY 4 DESC
;
spool off
exit;
!01
cechon "7.21 total waiting time after started the database is: " red
echo
cat 721.txt
echo
rm -rf 721.txt
#7.22 find the sql that Caused lock contention
sqlplus -S "${ora_user}/${ora_pass} as sy