OCP Oracle DBA in Montreal at Desjardins
Latest posts by Cyrille Modiano (see all)
- Renaming a RAC cluster - 27/09/2018
- Stop/Start all RAC databases at once - 26/09/2018
- RMAN Backup script - 08/11/2017
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
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?
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))
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
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.
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
Hi Cyrille, is there any other way to know resource consumption without those ‘licenced views’?
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
Cyrille, I am trying to pull all sql_fulltext during a time frame, Will this query helps me to get the info I am looking for or there is a better way?
select sql_id,parsing_schema_name,service,module,FIRST_LOAD_TIME,last_load_time,sql_fulltext
from v$sql
where FIRST_LOAD_TIME >=’2020-09-02 10:00:01′
and FIRST_LOAD_TIME <='2020-09-02/15:00:01'
order by first_load_time asc;
Regards,
jorge