Top 10 queries from v$active_session_history

Cyrille Modiano

OCP Oracle DBA in Montreal at DBSoft

Latest posts by Cyrille Modiano (see all)

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

5 thoughts on “Top 10 queries from v$active_session_history

  1. I noticed this morning the Oracle tables from a specific schema got dropped in production yesterday evening. We don’t have audit in place. Can I capture information from history and other database views from which machine the delete came and at what time?

  2. On the group by you have
    sum(decode(session_state,’ON CPU’,1,1))

    it should be sum(decode(session_state,’ON CPU’,1,0))

    1. Hello Cesar,

      No for the TOTAL we want all rows not only those on CPU, you can see that as a TOTAL of all activity for a specific sql_id, waits and on_cpu included.

      Cheers.

      Cyrille

  3. I’m sorry, you’re right.

    I came back to check your blog to see if you had more examples of usage of the v$active_session_history view.

    In particular, I’m interested in knowing how to calculate sort of the “idle” CPU… or some sort of way to know, not only how much CPU is/was being used by a session, but how much of the total CPU resources are available, so to know the percentaje of the total available CPU resources is being used by a particular session.

    Thanks in advance.

    1. If you look at the post here:
      https://www.dba-scripts.com/scripts/diagnostic-and-tuning/oracle-active-session-history-ash/sql-activity-last-hour/

      You have two graphs, the red line represents the maximum cpu available( cpu_count in v$parameter). You can easily see the cpu consumed by the sessions and the cpu available. I grouped in this case by SQL_ID so you can see what sql was running and how much resources it consumed but you can do the same and group by session. You will then be able to see what session was consuming resources at a specific time.
      Let me know if i’m not clear.

      Cyrille

Leave a Reply