Top 10 queries from v$active_session_history

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

select * from (
	select
		 SQL_ID ,
		 sum(decode(session_state,'ON CPU',1,0)) as CPU,
		 sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,
		 sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,
		 sum(decode(session_state,'ON CPU',1,1)) as TOTAL
	from v$active_session_history
	where SQL_ID is not NULL
	group by sql_id
	order by sum(decode(session_state,'ON CPU',1,1))   desc
	)
where rownum <11

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

7 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

  4. Hi Cyrille, is there any other way to know resource consumption without those ‘licenced views’?

    1. Hi Fernando,

      This view hold history data about sessions by sampling each second the content of the v$session view.
      What you can do if you don’t have the appropriate licensing to query this view is doing the sampling yourself.
      One popular package that I like and which does exactly that, is SASH (Simulated Active Session History).
      You can find it here: https://github.com/pioro/orasash

      Cheers

      Cyrille

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.