Problem
You want to see what ASH information is kept in Oracle’s data dictionary.
Solution
There are a couple of data dictionary views you can use to get ASH information. The first,V$ACTIVE_SESSION_HISTORY, can be used to get information on current or recent sessions within your database. The second, DBA_HIST_ACTIVE_SESS_HISTORY, is used to store older, historical ASH information. If you wanted to see all the events and their total wait time for activity within the past 15 minutes in your database, you could issue the following query:
SELECT s.event, sum(s.wait_time + s.time_waited) total_wait
FROM v$active_session_history sWHERE s.sample_time between sysdate-1/24/4 AND sysdate
GROUP BY s.event
ORDER BY 2 desc;
EVENT TOTAL_WAIT
---------------------------------------------------------------- ----------20002600
db file scattered read 15649078
read by other session 9859503
db file sequential read 443298
direct path read temp 156463
direct path write temp 139984
log file parallel write 49469
db file parallel write 21207
log file sync 11793SGA: allocation forcing component growth 11711
control file parallel write 4421
control file sequential read 2122
SQL*Net more data from client 395
SQL*Net more data to client 66
If you wanted to get more session-specific information, and wanted to see the top 5 sessions that were using the most CPU resources within the last 15 minutes, you could issue the following query:
column username format a12column module format a30
SELECT * FROM
(
SELECT s.username, s.module, s.sid, s.serial#, count(*)
FROM v$active_session_history h, v$session s
WHERE h.session_id = s.sidAND h.session_serial# = s.serial#AND session_state= 'ON CPU' AND
sample_time > sysdate - interval '15' minute
GROUP BY s.username, s.module, s.sid, s.serial#
ORDER BY count(*) desc
)
where rownum <= 5;USERNAME MODULE SID SERIAL# COUNT(*)
---------- ---------------------------- ---------- ---------- ----------SYS DBMS_SCHEDULER 536 9 43
APPLOAD etl1@app1 (TNS V1-V3) 1074 3588 16
APPLOAD etl1@app1 (TNS V1-V3) 1001 4004 12
APPLOAD etl1@app1 (TNS V1-V3) 968 108 5
DBSNMP emagent@ora1 (TNS V1-V3) 524 3 2
The SESSION_STATE column has two valid values, ON CPU and WAITING, which denote whether a session is active or is waiting for resources. If you wanted to see the sessions that are waiting for resources, you could issue the same query as previously, with a SESSION_STATE of WAITING. If you wanted to see the most heavily used database objects for a given sample period, you could join V$ACTIVE_SESSION_HISTORY to the DBA_OBJECTS view to get that information. In the following example, we are getting a list of the top 5 database objects in use, along with the event associated with
that database object, over the past 15 minutes:
SELECT * FROM
(
SELECT o.object_name, o.object_type, s.event,SUM(s.wait_time + s.time_waited) total_waitedFROM v$active_session_history s, dba_objects oWHERE s.sample_time between sysdate - 1/24/4 and sysdate
AND s.current_obj# = o.object_idGROUP BY o.object_name, o.object_type, s.event
ORDER BY 4 desc
)
WHERE rownum <= 5;OBJECT_NAME OBJECT_TYPE EVENT TOTAL_WAITED
---------------------------- --------------- ------------------------- ------------WRI$_ALERT_OUTSTANDING TABLE Streams AQ: enqueue block 110070196 ed on low memoryAPP_ETL_IDX1 INDEX read by other session 65248777
APP_SOURCE_INFO TABLE PARTITION db file scattered read 33801035
EMPPART_PK_I INDEX PARTITION read by other session 28077262
APP_ORDSTAT TABLE PARTITION db file scattered read 15569867
How It Works
The DBA_HIST_ACTIVE_SESS_HISTORY view can give you historical information on sessions that have aged out of the V$ACTIVE_SESSION_HISTORY view. Let’s say you had a day when performance was particularly bad on your database. You could zero in on historical session information for a given time frame, provided it is still held within the DBA_HIST_ACTIVE_SESS_HISTORY view. For instance, if you wanted to get the users that were consuming the most resources for a given day when performance was poor, you could issue the following query:
SELECT * FROM
(
SELECT u.username, h.module, h.session_id sid,
h.session_serial# serial#, count(*)FROM dba_hist_active_sess_history h, dba_users uWHERE h.user_id = u.user_idAND session_state= 'ON CPU'
AND (sample_time between to_date('2011-05-15:00:00:00','yyyy-mm-dd:hh24:mi:ss')
AND to_date('2011-05-15:23:59:59','yyyy-mm-dd:hh24:mi:ss'))
AND u.username != 'SYS'
GROUP BY u.username, h.module, h.session_id, h.session_serial#
ORDER BY count(*) desc
)
where rownum <= 5;USERNAME MODULE SID SERIAL# COUNT(*)
------------ ------------------------------ ---------- ---------- ----------APPLOAD1 etl1@app1 (TNS V1-V3) 1047 317 1105
APPLOAD1 etl1@app1 (TNS V1-V3) 1054 468 659
APPLOAD1 etl1@app1 (TNS V1-V3) 1000 909 387
STG oracle@ora1 (TNS V1-V3) 962 1707 353
APPLOAD1 etl1@app1 (TNS V1-V3) 837 64412 328
To then zero in on the database objects, you could issue the following query for the same time frame:
SELECT * FROM
(
SELECT o.object_name, o.object_type, s.event,SUM(s.wait_time + s.time_waited) total_waitedFROM dba_hist_active_sess_history s, dba_objects oWHERE s.sample_timebetween to_date('2011-05-15:00:00:00','yyyy-mm-dd:hh24:mi:ss')
AND to_date('2011-05-15:23:59:59','yyyy-mm-dd:hh24:mi:ss')
AND s.current_obj# = o.object_idGROUP BY o.object_name, o.object_type, s.event
ORDER BY 4 desc
)
WHERE rownum <= 5;OBJECT_NAME OBJECT_TYPE EVENT TOTAL_WAITED
---------------------------- --------------- ------------------------- ------------EMPPART TABLE PARTITION PX Deq Credit: send blkd 8196703427APPLOAD_PROCESS_STATUS TABLE db file scattered read 628675085
APPLOAD_PROCESS_STATUS TABLE read by other session 408577335
APP_SOURCE_INFO TABLE PARTITION db file scattered read 288479849
APP_QUALITY_INFO TABLE PARTITION Datapump dump file I/O 192290534
No comments:
Post a Comment