ASH Script for Finding out Top Wait Events in Oracle

prompt ************************************
prompt **** ASH OVERALL WAIT PROFILE
prompt ************************************
set lines 999

SELECT MIN(sample_time) min_ash_available,sysdate-MIN(sample_time) available duration FROM v$active_session_history;

select * from (
select NVL(event,’CPU’) event,count(*),
round((ratio_to_report(sum(1)) over ()*100),1) rr
from gv$active_session_history
WHERE user_id0
AND sample_timetrunc(sysdate-1)
group by event
order by 2 desc
) where rownum<10;

Advertisements

Count number of sessions oracle

— Count number of sessions oracle for 7 days, Provided you keep data that long —

select to_char(BEGIN_INTERVAL_TIME,'HH24'), ROUND(SUM(CURRENT_UTILIZATION)/COUNT(*))
FROM DBA_HIST_RESOURCE_LIMIT l,
DBA_HIST_SNAPSHOT ss
where
ss.SNAP_ID = l.SNAP_ID
AND l.RESOURCE_NAME LIKE 'sessions'
AND STARTUP_TIME between sysdate-7 and sysdate+1
GROUP BY to_char(BEGIN_INTERVAL_TIME,'HH24')
ORDER BY 1 asc;

PLS-00201: identifier ‘SYS.DBMS_SUPPORT’ must be declared

This error generally occurs when you are trying to trace a user session using the DBMS_SUPPORT package. But this package isn’t installed by default in Oracle. So you have to install it. You can install it using the script dbmssupp.sql

Go to $ORACLE_HOME/rdbms/admin and run  dbmssupp.sql to install the package.

cd $ORACLE_HOME/rdbms/admin

SQL> @dbmssupp.sql

Package created.
Package body created.

SQL> exit

 

Now try the tracing again and it should work.