实战:oracle巡检脚本v1(十五)
sdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 722.txt
column program format a10
column username format a8
column module format a12
column object_name format a12
column time_ms format 999,999,999
column pct_of_time format 99.99
column sql_text format a70
column lock_type format a4
WITH ash_query AS (
SELECT substr(event,6,2) lock_type,program,
h.module, h.action, object_name,
SUM(time_waited)/1000 time_ms, COUNT( * ) waits,
username, sql_text,
RANK() OVER (ORDER BY SUM(time_waited) DESC) AS time_rank,
ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited))
OVER (), 2) pct_of_time
FROM v\$active_session_history h
JOIN dba_users u USING (user_id)
LEFT OUTER JOIN dba_objects o
ON (o.object_id = h.current_obj#)
LEFT OUTER JOIN v\$sql s USING (sql_id)
WHERE event LIKE 'enq: %'
GROUP BY substr(event,6,2) ,program, h.module, h.action,
object_name, sql_text, username)
SELECT lock_type,module, username, object_name, time_ms,pct_of_time,
sql_text
FROM ash_query
WHERE time_rank < 2
ORDER BY time_rank;
spool off
exit;
!01
cechon "7.22 find the sql that Caused lock contention is: " red
echo
cat 722.txt
echo
rm -rf 722.txt
#7.23 top 10 waiting for long time
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 723.txt
column sql_text format a40 heading "SQL Text"
column app_time_ms format 99,999,999 heading "AppTime(ms)"
column app_time_pct format 999.99 heading "SQL App of Time%"
column pct_of_app_time format 999.99 heading "Total of App Time% "
WITH sql_app_waits AS
(SELECT sql_id, SUBSTR(sql_text, 1, 200) sql_text,
application_wait_time/1000 app_time_ms,
elapsed_time,
ROUND(application_wait_time * 100 /
elapsed_time, 2) app_time_pct,
ROUND(application_wait_time * 100 /
SUM(application_wait_time) OVER (), 2) pct_of_app_time,
RANK() OVER (ORDER BY application_wait_Time DESC) ranking
FROM v\$sql
WHERE elapsed_time >
0
AND application_wait_time>0 )
SELECT sql_text, app_time_ms, app_time_pct,
pct_of_app_time, ranking
FROM sql_app_waits
WHERE ranking <= 10
ORDER BY ranking ;
spool off
exit;
!01
cechon "7.23 top 10 waiting for long time is: " red
echo
cat 723.txt
echo
rm -rf 723.txt
#7.24 all user wait times
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 724.txt
column wait_type format a35
column lock_name format a20
column total_waits format 999,999,999
column time_waited_seconds format 999,999.99
column pct format 99.99
WITH session_event AS
(SELECT CASE WHEN event LIKE 'enq:%'
THEN event ELSE wait_class
END wait_type, e.*
FROM v\$session_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 e.sid, wait_type, event, total_waits, time_waited_micro
FROM session_event e
UNION
SELECT sid, 'CPU', stat_name, NULL, VALUE
FROM v\$sess_time_model
WHERE stat_name IN ('background cpu time', 'DB CPU')) l
WHERE wait_type <> 'Idle'
and sid in (select sid from v\$session where username
in
(
select username from dba_users where username not in('SYS','SYSTEM')
)
)
GROUP BY wait_type
ORDER BY 4 DESC;
spool off
exit;
!01
cechon "7.24 all user wait times is: " red
echo
cat 724.txt
echo
rm -rf 724.txt
#7.25 who waiting who
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执