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;

Count number of sessions oracle

-- Count number of sessions oracle for 7 days, Provided you keep data that long -- [sourcecode language="sql"] 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; [/sourcecode]

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. … Continue reading PLS-00201: identifier ‘SYS.DBMS_SUPPORT’ must be declared