sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle统计会话UNDO使用情况脚本案例

时间:2016-10-23 10:24   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oralce数据库在运行过程中统计会话UNDO使用情况脚本案例。

set echo off
set verify off
SET PAGES 5000
set linesize 500
col NAME_COL_PLUS_SHOW_PARAM for a20;
col VALUE_COL_PLUS_SHOW_PARAM for a20;
col parameter for a40
col session_value for a20
col instance_value for a20
col description for a60

show parameter undo ;
SELECT a.ksppinm AS parameter,
b.ksppstvl AS session_value,
c.ksppstvl AS instance_value,
a.ksppdesc AS description
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE ‘/_%’ ESCAPE ‘/’
AND a.ksppinm LIKE ‘%_undo_autotune%’
ORDER BY a.ksppinm
/
col name for a25
col program for a30
col xacts for 99
col sid for 99999
col serial# for 99999
col sqlid for a18
col status for a15
col sid:serial:ospid for a25
col io for a30 heading ‘LOG_IO:PHY_IO|CR_GET:CR_CHANGE’
select *
from (select to_char(sysdate, ‘hh24:mi:ss’) as curtime,
e.start_time,
a.name,
round(f.bytes / 1024 / 1024, 2) as mbytes,
b.xacts,
c.sid || ‘:’ || c.serial# || ‘:’ || g.spid "sid:serial:ospid",
decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) as hash_value,
DECODE(c.sql_id, ”, c.prev_sql_id, c.sql_id) || ‘:’ ||
sql_child_number AS SQLID,
decode(instr(C.PROGRAM, ‘(TNS’),
0,
c.PROGRAM,
substr(c.program, 1, instr(C.PROGRAM, ‘(TNS’) – 1)) PROGRAM,
e.LOG_IO||’:’||e.PHY_IO||’:’||e.CR_GET||’:’||e.CR_CHANGE io
from v$rollname a,
v$rollstat b,
v$session c,
v$transaction e,
dba_segments f,
v$process g
where a.usn = b.usn
and b.usn = e.xidusn
and c.saddr = e.ses_addr
and g.addr = c.paddr
and a.name(+) = f.segment_name
order by mbytes DESC)
where rownum <= 20;

select to_char(sysdate, ‘hh24:mi:ss’) as curtime,
status,
TRUNC(SUM(BYTES) / 1024 / 1024) "size(M)",
count(*) total_extent
from dba_undo_extents
where tablespace_name =
(select VALUE from v$parameter where name = ‘undo_tablespace’)
group by status;
col "Tablespace Name" for a40
select tablespace_name,
trunc(a.Free_Space) "Free_space(M)",
trunc(b.TOTAL_SPACE) "TOTAL_SPACE(M)",
trunc((1 – a.Free_Space / b.TOTAL_SPACE) * 100) "USED(%)"
from (select tablespace_name, sum(bytes / 1024 / 1024) Free_Space
from dba_free_space
where tablespace_name =
(select VALUE from v$parameter where name = ‘undo_tablespace’)
group by tablespace_name) a,
(select sum(bytes / 1024 / 1024) TOTAL_SPACE
from v$datafile a, v$tablespace b
whOracle о ere a.ts# = b.ts#
and b.name =
(select VALUE from v$parameter where name = ‘undo_tablespace’)) b;

本文固定链接: http://www.htz.pw/2014/05/30/%e5%b8%b8%e7%94%a8%e8%84%9a%e6%9c%ac3%ef%bc%9a%e7%bb%9f%e8%ae%a1%e4%bc%9a%e8%af%9dundo%e4%bd%bf%e7%94%a8%e6%83%85%e5%86%b5.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle统计会话UNDO使用情况脚本案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1043.html

Oracle研究中心

关键词:

如何统计Oracle会话UNDO使用情况

统计Oralce undo使用情况的脚本