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 --

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