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

Reprint V$ACTIVE_SESSION_HISTORY view related knowledge-http://blog.csdn.net/haibusuanyun/article/details/17959973

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.

The column name data type indicates the IDSAMPLE_TIMETIMESTAMP of the SAMPLE_IDNUMBER sample (3) the time the SESSION_IDNUMBER session identifier was taken; mapped to the V$SESSION.SIDSESSION_SERIAL#NUMBER session sequence number (the object used to uniquely identify a session); and mapped to the V$SESSION.SERIAL#USER_IDNUMBEROracle user identifier A numerical representation of the SQL identifier SQL_CHILD_NUMBERNUMBERChild number of the SQL statement that the session was executing at the time of samplingSQL_PLAN_HASH_VALUENUMBERsql cursor plan mapped to the SQL statement executed by the V$SESSION.USER#SQL_IDVARCHAR2 (13) session at the time of sampling. The information for all these session samples may not be available. V$session does not contain this information. FORCE_MATCHING_SIGNATURENUMBERThe signature used when the CURSOR_SHARING parameter is set to FORCESQL_OPCODENUMBERIndicates what phase of operation the SQL statement was in; maps to V$SESSION.COMMAND . "V$SESSION" for information on interpreting this columnSERVICE_HASHNUMBERHash that identifies the Service; maps to V$ACTIVE_SERVICES.NAME_HASHSESSION_TYPEVARCHAR2 (10) session type: FOREGROUNDBACKGROUNDSESSION_STATEVARCHAR2 (7) session state: WAITINGON CPUQC_SESSION_IDNUMBER query coordinator's session ID. This information is only available if the sampled session is a parallel query slave. For all other sessions, the value is 0.QC_INSTANCE_IDNUMBER query the ID of the coordinator instance. This information is only available if the sampled session is a parallel query slave. For all other sessions, the session identifier of the the value is 0.BLOCKING_SESSIONNUMBER blocking session. Populated only when the session was waiting for enqueues or a "buffer busy" wait. Maps toV$SESSION.BLOCKING_SESSION.BLOCKING_SESSION_STATUSVARCHAR2 (11) blocking session state: VALIDNO HOLDERGLOBALNOT IN WAITUNKNOWNBLOCKING_SESSION_SERIAL#NUMBER blocking session sequence number EVENTVARCHAR2 (64) If SESSION_STATE = WAITING, then the event for which the session was waiting for at the time of sampling.If SESSION_STATE = ON CPU, then this column will be NULL.See Also: "Oracle Wait Events" EVENT_IDNUMBERIdentifier of the resource or event for which the session is waiting or for which the session last waited. Interpretation is similar to that of theEVENT column.EVENT#NUMBERNumber of the resource or event for which the session is waiting or for which the session last waited. The Interpretation is similar to that of theEVENT column.SEQ#NUMBER sequence number uniquely identifies waiting (adding each wait) P1TEXTVARCHAR2 (64) the text of the first additional parameter P1NUMBER the first additional parameter P2TEXTVARCHAR2 (64) the text of the second parameter P2NUMBER the second additional parameter P3TEXTVARCHAR2 (64) the third additional parameter P3NUMBER the third additional parameter WAIT_CLASSVARCHAR2 (64) Wait class name of the event for which the session was waiting at the time of sampling. Interpretation is similar to that of the EVENT column. The class identifier of the event sampled by the Maps to V$SESSION.WAIT_CLASS.WAIT_CLASS_IDNUMBER waiting session at the waiting time. Interpretation is similar to that of the EVENTcolumn. Maps to V$SESSION.WAIT_CLASS_ID.WAIT_TIMENUMBER0 if the session was waiting at the time of samplingTotal wait time for the event for which the session last waited if the session was on the CPU when sampledWhether or not WAIT_TIME = 0 is what is useful to find the SESSION_STATE at the time of sampling, rather than the actual value of WAIT_TIMEitself. Maps to V$SESSION.WAIT_TIME.TIME_WAITEDNUMBERIf SESSION_STATE = WAITING, then the time that the session actually spent waiting for that EVENT. This column is set for waits that were in progress at the time the sample was taken.If a wait event lasted for more than a second and was caught waiting in more than one session sample row, then the actual time spent waiting for that wait event will be populated in the last of those session sample rows. At any given time, this information will not be available for the latest session sample.XIDRAW (8) Transaction ID that the session was working on at the time of sampling. V$SESSION does not contain this information.CURRENT_OBJ#NUMBER object ID the object whose session is referenced. This information is only available if the session is waiting for request, cluster, concurrency, and user I / O waiting events. Map to V$SESSION.ROW_WAIT_OBJ#.CURRENT_FILE#NUMBERFile number of the file containing the block that the session is referencing. This information is only available if the session was waiting for Cluster, Concurrency, and User I/O wait events. Maps to V$SESSION.ROW_WAIT_FILE#.CURRENT_BLOCK#NUMBERID of the block that the session is referencing. This information is only available if the session was waiting for Cluster, Concurrency, and User I/O wait events. The name of the Maps to V$SESSION.ROW_WAIT_BLOCK#.PROGRAMVARCHAR2 (48) operating system program MODULEVARCHAR2 (48) Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_MODULE procedureACTIONVARCHAR2 (32) Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_ACTION procedureCLIENT_IDVARCHAR2 (64) Client identifier of the session; maps to Variations. Clients identify identify sql statements that consumed 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

-- find the sql statement that consumes the most Icano 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

-- find the session that consumed 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

-- find the most resource-consuming sql statements 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

-- find the most resource-consuming session 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))

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.