Shulou(Shulou.com)06/01 Report--

V$ACTIVE_SESSION_HISTORY displays sampling session activity in the database. ASH takes snapshots from v$session every second, stores them in V$ACTIVE_SESSION_HISTORY, and collects wait information for all active sessions. If the ASH data is flushed to disk, you need to query the relevant information from the DBA_HIS_ACTIVE_SESS_HISTORY view.

This view is the core of the ASH and is used to record the historical wait information of the active SESSION, which is sampled every second, which is recorded in memory, and the expected value is to record the content for an hour.

Usage example: find the sql statement that consumes the most CPU in the last minute

SELECT sql_id, count (*), round (count (*) / sum (count (*)) over (), 2) pctload

FROM V$ACTIVE_SESSION_HISTORY

WHERE sample_time > sysdate-1 / (24 * 60)

AND session_type 'BACKGROUND'

AND session_state ='ON CPU'

GROUP BY sql_id

ORDER BY count (*) desc

Usage example: find the sql statement that consumes the most Imax O in the last minute.

SELECT ash.sql_id,count (*)

FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EVT

WHERE ash.sample_time > sysdate-1 / (24060)

AND ash.session_state = 'WAITING'

AND ash.event_id = evt.event_id

AND evt.wait_class = 'USER Iplink O'

GROUP BY ash.sql_id

ORDER BY count (*) desc

Usage example: find the session that consumes the most CPU in the last minute

SELECT session_id,count (*)

FROM V$ACTIVE_SESSION_HISTORY

WHERE session_state ='ON CPU'

AND sample_time > sysdate-1 / (24060)

GROUP BY session_id

ORDER BY count (*) desc

Usage example: find the most resource-consuming sql statement in the last minute

SELECT ash.sql_id

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 (en.wait_class,'USER I Accord Olympiad 1), 0)) "WAIT"

Sum (decode (ash.session_state,'WAITING',decode (en.wait_class,'USER I Accord Olympiad 1), 0)) "IO"

Sum (decode (ash.session_state,'ON CPU',1,1)) "TOTAL"

FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EN

WHERE SQL_ID is not null and en.event#=ash.event# and ash.sample_time > sysdate-1 / (24060)

GROUP BY ash.sql_id

ORDER BY sum (decode (ash.session_state,'ON CPU',1,1)) desc

Usage example: find the session that consumes the most resources in the last minute

SELECT ash.session_id,ash.session_serial#,ash.user_id,ash.program

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 (en.wait_class,'USER I Accord Olympiad 1), 0)) "WAITING"

Sum (decode (ash.session_state,'WAITING',decode (en.wait_class,'USER I Accord Olympiad 1), 0)) "IO"

Sum (decode (ash.session_state,'ON CPU',1,1)) "TOTAL"

FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EN

WHERE en.event# = ash.event# and ash.sample_time > sysdate-1 / (24060)

GROUP BY ash.session_id,ash.user_id,ash.session_serial#,ash.program

ORDER BY sum (decode (ash.session_state,'ON CPU',1,1))

-

It is a good choice to use awr,ash,addm when there are performance problems in the database. In fact, querying v$active_session_history directly can also quickly locate and solve the problem.

In fact, if you look at the v$active_session_history view, you can get a lot of information by combining some views.

Give a few examples to illustrate:

1. Make sure that the object has a high wait:

SELECT a.currentproof objusted, o.object_name, o.object_type, a.event, SUM (a.wait_time + a.time_waited) total_wait_time

FROM v$active_session_history a, dba_objects o

WHERE a.sample_time BETWEEN SYSDATE-30 / 1440 AND SYSDATE AND a.current_obj# = o.object_id

GROUP BY a.current_obj#, o.object_name, o.object_type, a.event

ORDER BY total_wait_time desc; 2. Take a look at the waiting events for a while:

SELECT a.event, SUM (a.wait_time + a.time_waited) total_wait_time

FROM v$active_session_history a

WHERE a.sample_time BETWEEN SYSDATE-30 / 1440 AND SYSDATE

GROUP BY a.event

ORDER BY total_wait_time DESC; 3. Look at the answer. There's something wrong with it:

SELECT s.SID, s.username, SUM (a.wait_time + a.time_waited) total_wait_time

FROM v$active_session_history a, v$session s

WHERE a.sample_time BETWEEN SYSDATE-30 / 1440 AND SYSDATE AND a.session_id = s.SID

GROUP BY s.SID, s.username

ORDER BY total_wait_time DESC

Of course, this can only query the nearest one will be a little more accurate, the reply will not be able to exit.

4. Look at that sql statement that has a problem.

SELECT a.user_id, d.username, s.sql_text, SUM (a.wait_time + a.time_waited) total_wait_time

FROM v$active_session_history a, v$sqlarea s, dba_users d

WHERE a.sample_time BETWEEN SYSDATE-15 / 1440 AND SYSDATE AND a.sql_id = s.sql_id AND a.user_id = d.user_id

GROUP BY a.user_id, s.sql_text, d.username

Order by SUM (a.wait_time + a.time_waited) desc

-- the query here is the v$sqlarea view. You can also use the view DBA_HIST_ACTIVE_SESS_HISTORY instead of v$active_session_history to query historical information.

Select * from dba_objects where wner='SYS' and object_name like 'DBA_HIST%' and object_type='VIEW'

Use these views to locate many information problems

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.