【转】ashtop 方便看当前活动会话 -凯发app官方网站

-- licensed under the apache license, version 2.0. see license.txt for terms & conditions.
--------------------------------------------------------------------------------
--
-- file name: ashtop.sql v1.2
-- purpose: display top ash time (count of ash samples) grouped by your
-- specified dimensions
--
-- author: tanel poder
-- 凯发app官方网站 copyright: (c) http://blog.tanelpoder.com
--
-- usage:
-- @ashtop
--
-- example:
-- @ashtop username,sql_id session_type='foreground' sysdate-1/24 sysdate
--
-- other:
-- this script uses only the in-memory gv$active_session_history, use
-- @dashtop.sql for accessiong the dba_hist_active_sess_history archive
--
--------------------------------------------------------------------------------
col "%this" for a7
--col p1 for 99999999999999
--col p2 for 99999999999999
--col p3 for 99999999999999
col p1text for a30 word_wrap
col p2text for a30 word_wrap
col p3text for a30 word_wrap
col p1hex for a17
col p2hex for a17
col p3hex for a17
col dop for 99
col aas for 9999.9
col totalseconds head "total|seconds" for 99999999
col dist_sqlexec_seen head "distinct|execs seen" for 999999
col event for a42 word_wrap
col event2 for a42 word_wrap
col time_model_name for a50 word_wrap
col program2 for a40 truncate
col username for a20 wrap
col obj for a30
col objt for a50
col sql_opname for a20
col top_level_call_name for a30
col wait_class for a15
select
*
from (
with bclass as (select /* inline */ class, rownum r from v$waitstat)
select /* leading(a) use_hash(u) */
count(*) totalseconds
, round(count(*) / ((cast(&4 as date) - cast(&3 as date)) * 86400), 1) aas
, lpad(round(ratio_to_report(count(*)) over () * 100)||'%',5,' ')||' |' "%this"
, &1
, to_char(min(sample_time), 'yyyy-mm-dd hh24:mi:ss') first_seen
, to_char(max(sample_time), 'yyyy-mm-dd hh24:mi:ss') last_seen
-- , max(sql_exec_id) - min(sql_exec_id)
, count(distinct sql_exec_start||':'||sql_exec_id) dist_sqlexec_seen
from
(select
a.*
, session_id sid
, session_serial# serial
, to_char(case when session_state = 'waiting' then p1 else null end, '0xxxxxxxxxxxxxxx') p1hex
, to_char(case when session_state = 'waiting' then p2 else null end, '0xxxxxxxxxxxxxxx') p2hex
, to_char(case when session_state = 'waiting' then p3 else null end, '0xxxxxxxxxxxxxxx') p3hex
, trunc(px_flags / 2097152) dop
, nvl(a.event, a.session_state)||
case
when a.event like 'enq%' and session_state = 'waiting'
then ' [mode='||bitand(p1, power(2,14)-1)||']'
when a.event in (select name from v$event_name where parameter3 = 'class#')
then ' ['||case when a.p3 <= (select max(r) from bclass)
then (select class from bclass where r = a.p3)
else (select decode(mod(bitand(a.p3,to_number('ffff','xxxx')) - 17,2),0,'undo header',1,'undo data', 'error') from dual)
end ||']'
else null
end event2 -- event is null in ash if the session is not waiting (session_state = on cpu)
, case when a.session_type = 'background' or regexp_like(a.program, '.*$[pj]\d $') then
regexp_replace(substr(a.program,instr(a.program,'(')), '\d', 'n')
else
'('||regexp_replace(regexp_replace(a.program, '(.*)@(.*)($.*$)', '\1'), '\d', 'n')||')'
end || ' ' program2
, case when bitand(time_model, power(2, 01)) = power(2, 01) then 'dbtime ' end
||case when bitand(time_model, power(2, 02)) = power(2, 02) then 'background ' end
||case when bitand(time_model, power(2, 03)) = power(2, 03) then 'connection_mgmt ' end
||case when bitand(time_model, power(2, 04)) = power(2, 04) then 'parse ' end
||case when bitand(time_model, power(2, 05)) = power(2, 05) then 'failed_parse ' end
||case when bitand(time_model, power(2, 06)) = power(2, 06) then 'nomem_parse ' end
||case when bitand(time_model, power(2, 07)) = power(2, 07) then 'hard_parse ' end
||case when bitand(time_model, power(2, 08)) = power(2, 08) then 'no_sharers_parse ' end
||case when bitand(time_model, power(2, 09)) = power(2, 09) then 'bind_mismatch_parse ' end
||case when bitand(time_model, power(2, 10)) = power(2, 10) then 'sql_execution ' end
||case when bitand(time_model, power(2, 11)) = power(2, 11) then 'plsql_execution ' end
||case when bitand(time_model, power(2, 12)) = power(2, 12) then 'plsql_rpc ' end
||case when bitand(time_model, power(2, 13)) = power(2, 13) then 'plsql_compilation ' end
||case when bitand(time_model, power(2, 14)) = power(2, 14) then 'java_execution ' end
||case when bitand(time_model, power(2, 15)) = power(2, 15) then 'bind ' end
||case when bitand(time_model, power(2, 16)) = power(2, 16) then 'cursor_close ' end
||case when bitand(time_model, power(2, 17)) = power(2, 17) then 'sequence_load ' end
||case when bitand(time_model, power(2, 18)) = power(2, 18) then 'inmemory_query ' end
||case when bitand(time_model, power(2, 19)) = power(2, 19) then 'inmemory_populate ' end
||case when bitand(time_model, power(2, 20)) = power(2, 20) then 'inmemory_prepopulate ' end
||case when bitand(time_model, power(2, 21)) = power(2, 21) then 'inmemory_repopulate ' end
||case when bitand(time_model, power(2, 22)) = power(2, 22) then 'inmemory_trepopulate ' end
||case when bitand(time_model, power(2, 23)) = power(2, 23) then 'tablespace_encryption ' end time_model_name
from gv$active_session_history a) a
, dba_users u
, (select
object_id,data_object_id,owner,object_name,subobject_name,object_type
, owner||'.'||object_name obj
, owner||'.'||object_name||' ['||object_type||']' objt
from dba_objects) o
where
a.user_id = u.user_id ()
and a.current_obj# = o.object_id()
and &2
and sample_time between &3 and &4
group by
&1
order by
totalseconds desc
, &1
)
where
rownum <= 15
/

使用方法：
将上述脚本保存为 ashtop.sql

然后执行：
prompt what's going on? showing top timed events of last minute from ash...
@ashtop session_state,event &filter sysdate-1/24/60 sysdate

prompt showing top sql and wait classes of last minute from ash...
@ashtop sql_id,session_state,wait_class &filter sysdate-1/24/60 sysdate

阅读(1443) | 评论(0) | 转发(0) |

上一篇：安装ahf后的后遗症

下一篇：rm -fr /oradata 不要慌

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们