DBA_HIST_ACTIVE_SESS_HISTORY應用

jelephant發表於2015-10-27
--檢視event事件
select event,count(*) cnt from DBA_HIST_ACTIVE_SESS_HISTORY where wait_class<>'Idle' and sample_time<sysdate-1
group by event order by cnt ;


--檢視某時間段內的sql
SET PAUSE ON
SET PAUSE 'Press Return To Continue'
SET HEADING ON
SET LINESIZE 300
SET PAGESIZE 60
 
COLUMN Sample_Time FOR A12
COLUMN username FOR A20
COLUMN sql_text FOR A40
COLUMN program FOR A40
COLUMN module FOR A40
 
SELECT
   sample_time,
   u.username,
   h.program,
   h.module,
   s.sql_text
FROM
   DBA_HIST_ACTIVE_SESS_HISTORY h,
   DBA_USERS u,
   DBA_HIST_SQLTEXT s
WHERE  sample_time
BETWEEN '&s_time' and '&e_time'
--AND INSTANCE_NUMBER=&inst_no
   AND h.user_id=u.user_id
   AND h.sql_id = s.sql_iD
ORDER BY 1


--top sql
	
col type for a10 col "CPU" for 999999.9 col "IO" for 999999.9 select * from ( select      ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type,      sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",      sum(decode(ash.session_state,'WAITING',1,0))    -      sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    "WAIT" ,      sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    "IO" ,      sum(decode(ash.session_state,'ON CPU',1,1))     "TOTAL" from dba_hist_active_sess_history ash,      audit_actions aud where SQL_ID is not NULL    and ash.sql_opcode=aud.action    and ash.sample_time > sysdate - &minutes /( 60*24) group by sql_id, SQL_PLAN_HASH_VALUE   , aud.name order by sum(decode(session_state,'ON CPU',1,1))   desc ) where  rownum < 10 /

--io size
col event for a25
select event,round(min(p3)) mn,
round(avg(p3)) av,
round(max(p3)) mx,
count(*)  cnt
from dba_hist_active_sess_history
--from v$active_session_history
where  (event like 'db file%' or event like 'direct %')
and event not like '%parallel%'
group by event
order by event
/

--查詢2011-12-28日對dj_sz表的update語句。

select to_char(b.begin_interval_time,'yyyy-mm-dd hh24:mi:ss') update_time,c.sql_text sql_text

from dba_hist_active_sess_history a,DBA_HIST_SNAPSHOT b,v$sqlarea c

where a.snap_id=b.snap_id

and a.sql_id=c.sql_id

and b.begin_interval_time>=to_date('2011-12-28','yyyy-mm-dd') and b.begin_interval_time < to_date('2011-12-29','yyyy-mm-dd')

and lower(c.sql_text) like '%update%dj_sz%'

order by update_time


select 
*
from DBA_HIST_ACTIVE_SESS_HISTORY
where 
sample_time 
between 
to_date('18-APR-2012 10:40:00','DD-MON-YYYY HH24:MI:SS')
and 
to_date('18-APR-2012 10:40:10','DD-MON-YYYY HH24:MI:SS')
and instance_number in (1,12)
order by sample_time,instance_number,SESSION_ID;

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29337971/viewspace-1816635/,如需轉載,請註明出處,否則將追究法律責任。

相關文章