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

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

v$active_session_history

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:

 

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 '10-JUL-04 09:57:00 PM' AND

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

        A.EVENT# = B.EVENT# AND

        A.SESSION_ID= 123 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:\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.

 


 

            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

SEE CODE DEPOT FOR FULL SCRIPTS

with
   sql_exec2child as
 (select inst_id, sql_exec_start, sql_id, sql_exec_id
 from (select inst_id,
 sql_exec_start,
 sql_opname,
 sql_id,
 sql_exec_id,
 program,
 count(distinct sql_child_number)
 from gv$active_session_history
 where sql_exec_id is not null
 group by inst_id,
 sql_exec_start,
 sql_opname,
 sql_id,
 sql_exec_id,
 program
 having count(distinct sql_child_number) > 1
 order by count(distinct sql_child_number) desc)
 where rownum <= 1)
 select distinct ash.sql_id,
 program,
 sql_child_number,
 sql_opname,
 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 /

 
 
 
Get Complete Oracle Tuning Details 

The landmark book "Oracle Tuning: The Definitive Reference Third Edition" has been updated with over 800 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle performance and you can buy it for 40% off directly from the publisher.
 

 

 


 

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
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 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.