Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle Tips by Burleson Consulting


Oracle10g introduces the v$active_session_history view that keeps a history for recent active sessions’ activity. Oracle takes snapshots of active database sessions every second without placing serious overhead on the system. A database session is considered active by Oracle when it is consuming CPU time or waiting for an event that does not belong to the idle wait class. This view contains a considerable amount of information that is available in the v$session view, but it also has the sample_time column that points to a time in the past when a session was doing some work or waiting for a resource. v$active_session_history view contains a single row for each session when sampling was performed.


An interesting possibility becomes available with the introduction of the v$active_session_history view in Oracle10g. With this tool, Oracle DBAs are now able to trace sessions without the need to use the well known 10046 event to perform extended tracing. All tracing can be performed now using only SQL queries without the need to review raw trace files and format them using the TKPROF utility.


Oracle keeps session history in the circular memory buffer in the SGA. This means that the greater the database activity is, the smaller the amount of time session history available in the ASH view is. In this instance, it might help that the AWR dba_hist_active_sess_history view stores the ASH history for a longer time; however, the dba_hist_active_sess_history view stores ASH data snapshots only for the times the AWR snapshots were taken.


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:







        v$EVENT_NAME B,

        v$SQLAREA C


                              '10-JUL-04 09:59:00 PM' AND

        A.EVENT# = B.EVENT# AND

        A.SESSION_ID= 123 AND

        A.SQL_ID = C.SQL_ID



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:





  f.file_name        “Data File”,

  COUNT(*)           “Wait Number”,

  SUM(h.time_waited) “Total Time Waited”


  v$active_session_history h,

  dba_data_files           f


  h.current_file# = f.file_id

GROUP BY f.file_name



The sample output looks like:


Data File                              Wait Number Total Time Waited

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

D:\ORACLE\ORADATA\DBDABR\SYSAUX01.DBF         5514         994398837

D:\ORACLE\ORADATA\DBDABR\SYSTEM01.DBF         2579         930483678

D:\ORACLE\ORADATA\DBDABR\UNDOTBS01.DBF         245           7727218

D:\ORACLE\ORADATA\DBDABR\USERS01.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.






  h.event "Wait Event",

  SUM(h.wait_time + h.time_waited) "Total Wait Time"


  v$active_session_history h,

  v$event_name e


      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



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


   sql_exec2child as
 (select inst_id, sql_exec_start, sql_id, sql_exec_id
 from (select inst_id,
 count(distinct sql_child_number)
 from gv$active_session_history
 where sql_exec_id is not null
 group by inst_id,
 having count(distinct sql_child_number) > 1
 order by count(distinct sql_child_number) desc)
 where rownum <= 1)
 select distinct ash.sql_id,
 sql_plan_operation || ' ' || sql_plan_options as PLAN_OPERATION
 from gv$active_session_history ash, sql_exec2child
 where ash.sql_id = sql_exec2child.sql_id
 and ash.sql_exec_id = sql_exec2child.sql_exec_id
 and ash.sql_exec_start = sql_exec2child.sql_exec_start
 order by 1, 2 31 /

Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training





Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational