Active Session History query analyze
In this article i´m trying to make a query performance analysis based on ASH (active session history) views. Sometimes you need an historical point of view of a query due to this query is increasing its executions times, or query changed its execution plan. First step is to select top queries your database has: 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 for example, in my database: SQL> select * from ( select SQL_ID , sum(d...