DBA_HIST_ACTIVE_SESS_HISTORY应用

--查看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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29337971/viewspace-1816635/