To be fair to the 10046 trace, the v$active_session_history does not catch session activity that is extremely fast, but it should catch activity that causes the most waits and resource consumption and will, therefore, be useful to the DBA. Statistically, the v$active_session_history does catch extremely fast operations if they occur sufficiently often to contribute to user time.

How can the information available through the v$active_session_history view be used?. If a session that is experiencing delays or hangs has been identified and the goal is to identify the SQL statement(s) the session is issuing, along with the wait events being experienced for a particular time period, a query similar to this one can be issued:
SELECT C.SQL_TEXT,
B.NAME,
COUNT(*),
SUM(TIME_WAITED)
FROM v$ACTIVE_SESSION_HISTORY A,
v$EVENT_NAME B,
v$SQLAREA C
WHERE A.SAMPLE_TIME BETWEEN sysdate - 1/24 AND sysdate and
A.EVENT# = B.EVENT# AND
A.SESSION_ID= 135 AND
A.SQL_ID = C.SQL_ID
GROUP BY C.SQL_TEXT, B.NAME

The current_obj# column can be joined with the dba_objects view to get name of the object, or it can be joined with the current_file# column using dba_data_files to see the name of datafile that was accessed. Even a particular block that caused a wait event can be identified using the current_block# column.

It is also possible to identify hot datafiles, objects, or even data blocks that are being accessed by sessions more frequently than others and thus could be candidates for additional investigations. The hot_files_ash.sql query shows hot datafiles that caused the most wait times during session access:

hot_files_ash.sql

SELECT
f.file_name “Data File”,
COUNT(*) “Wait Number”,
SUM(h.time_waited) “Total Time Waited”
FROM
v$active_session_history h,
dba_data_files f
WHERE
h.current_file# = f.file_id
GROUP BY f.file_name
ORDER BY 3 DESC

The sample output looks like:
Data File Wait Number Total Time Waited

-------------------------------------- ----------- -----------------

D:ORACLEORADATADBDABRSYSAUX01.DBF 5514 994398837

D:ORACLEORADATADBDABRSYSTEM01.DBF 2579 930483678

D:ORACLEORADATADBDABRUNDOTBS01.DBF 245 7727218

D:ORACLEORADATADBDABRUSERS01.DBF 141 1548274

To be fair to the 10046 trace, the v$active_session_history does not catch session activity that is extremely fast, but it should catch activity that causes the most waits and resource consumption and will, therefore, be useful to the DBA. Statistically, the v$active_session_history does catch extremely fast operations if they occur sufficiently often to contribute to user time.

The following text includes several helpful queries that run against the v$active_session_history view. The first query, events_waits_hr_ask.sql, reports a list of resources that were in high demand in the last hour. This query does not reflect Idle wait events.
events_waits_hr_ash.sql

SELECT
h.event "Wait Event",
SUM(h.wait_time + h.time_waited) "Total Wait Time"
FROM
v$active_session_history h,
v$event_name e
WHERE
h.sample_time BETWEEN sysdate - 1/24 AND sysdate
AND h.event_id = e.event_id
AND e.wait_class <> 'Idle'
GROUP BY h.event
ORDER BY 2 DESC

The output looks like the following:
Wait Event Total Wait Time

------------------------------- ---------------

Queue Monitor Task Wait 10,256,950

class slave wait 10,242,904

log file switch completion 5,142,555

control file parallel write 4,813,121

db file sequential read 334,871

process startup 232,137

log file sync 203,087

latch free 36,934

log buffer space 25,090

latch: redo allocation 22,444

db file parallel write 714

db file scattered read 470

log file parallel write 182

direct path read temp 169

control file sequential read 160

direct path write temp 112

1.the most wait times in the last hour:
SELECT
s.sid,
s.username,
SUM(h.wait_time + h.time_waited) "total wait time"
FROM
v$active_session_history h,
v$session s,
v$event_name e
WHERE
h.sample_time BETWEEN sysdate - 1/24 AND sysdate
AND h.session_id = s.sid
group by s.sid,s.username

2.the SQL statements that experienced high wait time during last hour:
SELECT
h.user_id,
u.username,
sql.sql_text,
SUM(h.wait_time + h.time_waited) "total wait time"
FROM
v$active_session_history h,
v$sqlarea sql,
dba_users u,
WHERE
h.sample_time BETWEEN sysdate - 1/24 AND sysdate
AND h.sql_id = sql.sql_id
AND h.user_id = u.user_id
group by h.user_id,
u.username,
sql.sql_text

3.database objects that caused the most wait times during the last hour
SELECT
o.owner,
o.object_name,
o.object_type,
SUM(h.wait_time + h.time_waited) "total wait time"
FROM
v$active_session_history h,
dba_objects o,
v$event_name e
WHERE
h.sample_time BETWEEN sysdate - 1/24 AND sysdate
AND h.current_obj# = o.object_id
AND e.event_id = h.event_id
group by o.owner,
o.object_name,
o.object_type

4.Most Active SQL in the previous hour
desc gv$active_session_history

SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1/24
AND session_type = 'BACKGROUND'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;

SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1/24
AND session_type = 'FOREGROUND'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;
SELECT DISTINCT wait_class
FROM gv$event_name
ORDER BY 1;

5.Most Active I/O
SELECT sql_id, COUNT(*)
FROM gv$active_session_history ash, gv$event_name evt
WHERE ash.sample_time > SYSDATE - 1/24
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'User I/O'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;

set linesize 121

SELECT * FROM TABLE(dbms_xplan.display_cursor('gpv3kb4n2f2q1'));