根据metalink脚本学习undo（七）

--undo 压力部分与锁 还有部分的undo统计

set lines 120set pages 999clear colset termout offset trimout onset trimspool onconnect / as sysdbaalter session set nls_date_format='dd-hh24:mi';spool undopressure.outpromptprompt  ############## RUNTIME ############## promptcol rdate head "Run Time"select sysdate rdate from dual;prompt prompt  ############## WAITS FOR UNDO (Since Startup) ############## prompt col inst_id head "Instance#"col eq_type format a3 head "Enq"col total_req# format 999,999,999,999,999,999 head "Total Requests"col total_wait# format 999,999 head "Total Waits"col succ_req# format 999,999,999,999,999,999 head "Successes"col failed_req# format 999,999,999999 head "Failures"col cum_wait_time format 999,999,999 head "Cummalitve|Time"select * from v$enqueue_stat where eq_type='US' --Undo Segmentunionselect * from v$enqueue_stat where eq_type='HW'  --high water /prompt prompt  ############## LOCKS FOR UNDO ############## prompt col addr head "ADDR"col KADDR head "KADDR"col sid head "Session"col osuser format a10 head "OS User"col machine format a15 head "Machine"col program format a17 head "Program"col process format a7 head "Process"col lmode head "Lmode"col request head "Request"col ctime format 9,999 head "Time|(Mins)"col block head "Blocking?"--当前锁状态select /*+ RULE */  a.SID,      --session id b.process,  --process id b.OSUSER,   --登录者os username b.MACHINE,  --登录机器 b.PROGRAM,  --登录所用终端 addr,       -- address of lock state object kaddr,      --lock address  lmode,      --LOCK MODE request,    --Lock mode in which the process requests the lock: round(ctime/60/60,0) ctime, block  --发生的时间from v$lock a, v$session b where a.sid=b.sidand a.type='US'/prompt prompt  ############## TUNED RETENTION HISTORY (Last 2 Days) ############## prompt  ##############        LOWEST AND HIGHEST DATA        ############## prompt col low format 999,999,999,999 head "Undo Retention|Lowest Tuned Value"col high format 999,999,999,999 head "Undo Retention|Highest Tuned Value"--找出两条内tuned_undoretention最短的时段select end_time, tuned_undoretention from v$undostat where tuned_undoretention = (select min(tuned_undoretention) lowfrom v$undostatwhere end_time > sysdate-2)/--找出两条内tuned_undoretention最长的时段select end_time, tuned_undoretention from v$undostat where tuned_undoretention = (select max(tuned_undoretention) highfrom v$undostatwhere end_time > sysdate-2)/prompt prompt  ############## CURRENT TRANSACTIONS ############## prompt col sql_text format a40 word_wrapped head "SQL Code"select a.start_date,  --开始时间       a.start_scn,   --开始scn       a.status,      --事务状态       c.sql_text     --sql语句  from v$transaction a, v$session b, v$sqlarea c where b.saddr = a.ses_addr   and c.address = b.sql_address   and b.sql_hash_value = c.hash_value/select current_scn from v$database/col a format 999,999 head "UnexStolen"col b format 999,999 head "ExStolen"col c format 999,999 head "UnexReuse"col d format 999,999 head "ExReuse"prompt prompt  ############## WHO'S STEALING WHAT? (Last 2 Days) ############## prompt select unxpstealcnt a,  --试图偷取未过期undo block的次数       expstealcnt b,   --试图偷取过期undo block的次数       unxpblkreucnt c, --同一回滚段事务重用未过期undo block的数量       expblkreucnt d   --同一回滚段事务重用过期  undo block的数量from v$undostatwhere (unxpstealcnt > 0 or expstealcnt > 0)and end_time > sysdate-2/spool offset termout onset trimout offset trimspool offclear col