Category Archives: Oracle Active Session History (ASH)

This category regroups all scripts based on the v$active_session_history view.
These scripts will help you to diagnose performance problems and easily identify bottlenecks.
This view helps you calculate the Average Active Session metric which is a precise way to compare workload during diffrent periods.

Top 10 sessions from v$active_session_history

Description

This query returns the top 10 sessions from v$active_session_history. The result is ordered by total resources consumed by the session including I/O, WAITS and CPU
Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it.

top 10 sessions from v$active_session_history

You can restrict period of time analyzed by filtering with the sample_time column.

Oracle instance activity repartition

Description

This query will show you repartition percentage between I/O, WAITS and CPU from the v$active_session_history view
Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it.

Instance activity repartition

This query returns percentage of I/O, CPU and waits consumed by the instance for the total time covered by v$active_session_history. You can specify a time interval by adding a clause in the subquery specifying a range for sample_time.

Top 10 queries from v$active_session_history

Description

This query returns the top 10 queries by resource consumption (CPU+IO+WAIT) in the last hour from v$active_session_history.
Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it.

top 10 queries from v$active_session_history

This query return top queries by resources consumed, you can easily return the top I/O, WAITS or CPU queries by changing the order by clause.

You can see my other post about how to build a graph from the v$active_session_history view here