Home
 | 
Miscellaneous
 | 
Useful SQL scripts
Overview
Recent Article
All Archives
Topics
Comments 
Last modified: September 2013
You will find here some of the most useful performance monitoring SQL scripts which can come very handy.
Top Activity (_top_activity.sql)
Script source
SQL>
set heading on
set linesize 210
set termout off
set verify off
set underline =
set tab off
set feed off
set pages 100

clear columns
clear breaks
clear computes

COLUMN cpukount NEW_VALUE cpu noprint 
select value cpukount from v_$parameter where name like '%cpu_count%';

set serveroutput off

variable st_time varchar2(20);
variable en_time varchar2(20);
variable to_sess number;

declare

start_time varchar2(22);
end_time varchar2(22);
tot_sessions number;

begin
  select to_char(sysdate-(5/1440),'dd-mon-yyyy hh24:mi:ss') into start_time from dual;
  select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') into end_time from dual;
  :st_time := start_time;
  :en_time := end_time;
	select count(*) into tot_sessions from v$active_session_history where to_char(sample_time,'dd-mon-yyyy hh24:mi:ss') between :st_time and :en_time;
	:to_sess := tot_sessions;
end;
/

set termout on

col mysession for a350
set heading off
select '// ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') || ', ' || 
(select lower(sys_context('USERENV','SESSION_USER')) || '@' "current_user" from dual) || '@' || (select upper(instance_name) from v$instance) || 
(select ' (' || sys_context('USERENV','SID') || ') //' "current_sess" from dual) || chr(10) ||
'// *** ACTIVE SESSIONS FOR SELECT INTERVAL (' || upper(:st_time) || ' - ' || substr(:en_time ,13,8) || ') *** //' || chr(10) ||
rpad('_',200,'_') as mysession1 from dual;
set heading on

col "SAMPLE TIME" 		for a19
col "ON CPU"					for a11
col "ALL WAITS"				for a11
col "ACTIVE SESSIONS (OVERALL)"	for a36

SELECT
	substr(sample_time,1,17) as "SAMPLE TIME",
	rpad('|',round(avg(oncpu_)),'|') || ' ' || rpad('|',round(avg(waiting_)),'|') as "ACTIVE SESSIONS (OVERALL)",
	ltrim(to_char(round(avg(oncpu_),2),99.99)) as "ON CPU",
	ltrim(to_char(round(avg(waiting_),2),99.99)) as "ALL WAITS"
FROM
	(
	SELECT
		sample_time,
		count(oncpu) as oncpu_,
		count(waiting) as waiting_
	FROM
		(
		SELECT
			to_char(sample_time,'DD-MON-YYYY HH24:MI:SS') as sample_time,
			session_state,
			decode(session_state,'ON CPU',1,0,0) as oncpu,
			decode(session_state,'WAITING',1,0,0) as waiting
		FROM
			v$active_session_history
		WHERE
			to_char(sample_time,'dd-mon-yyyy hh24:mi:ss') BETWEEN :st_time AND :en_time
		)
	GROUP BY
		sample_time
	)
GROUP BY
	substr(sample_time,1,17)
ORDER BY
	1
/
	
set heading off
SELECT '      ' || ' Maximum CPU  ' || rpad('|',&&cpu,'|') || ' ' || '&cpu' as "Activity" FROM dual;
set heading on

column "WAIT_CLASS" for a20
column "EVENT" for a42
col "ACTIVITY (%)" for a18

set heading off
prompt
SELECT '// *** TOP WAITS (' || upper(:st_time) || ' - ' || substr(:en_time ,13,8) || ') *** //' FROM dual;
set heading on

break on "WAIT_CLASS"

WITH top10 AS 
(
SELECT 
  wait_class,
  event,
  to_char(activity,99999.99) as actperc,
  activity as actperc1
FROM
  (
  SELECT 
    substr(NVL(wait_class,'ON CPU'),1,18) as wait_class,
    substr(NVL(event, 'CPU'),1,40) as event,
    round(trunc(((count(*)*100)/:to_sess),3),2) as activity
   FROM
     v$active_session_history 
   WHERE to_char(sample_time,'dd-mon-yyyy hh24:mi:ss') between :st_time and :en_time
   GROUP BY wait_class, event 
   ORDER BY 3 desc
   )
WHERE
  rownum < 11
)
SELECT
  wait_class as "WAIT_CLASS",
  event as "EVENT",
  ltrim(rpad('|',to_char(trunc((actperc*100/(to_char((SELECT max(actperc1) FROM top10),999.99)))/10),99),'|') || ' ' || ltrim(to_char(actperc,999.99))) || ' ' as "ACTIVITY (%)"  
FROM
  top10
GROUP BY wait_class, event, actperc
ORDER BY 3 desc
/

set heading off
SELECT chr(10) ||
'See also :' || chr(10) || 
'---------------------------------------------------------------------------------------' || chr(10) || 
'@_top_sqls   @_top_sessions   @_top_waits   @_top_sqls_waiting   @_top_sessions_waiting' || chr(10) || chr(10) || '/' FROM dual;
set heading on

set feed on
set pages 20
clear columns
clear breaks
clear computes
prompt
Script in action (Click here)
Top SQLs (_top_sqls.sql)
Script source
SQL>
set linesize 210
set verify off;
set underline =
set termout on
set tab off
set feed off

clear columns
clear breaks
clear computes

set serverout on
set feed off

col "SQL_ID (EXEC. TIME)" for a22
col "SQL_TEXT" for a52
col "PARSING_SCHEMA" for a18
col sql_type for a22
col " ACT (%)" for a9 justify right
col "ACTIVITY (%)" for a18
col program for a40
col action for a40
col "PLAN_HASH" for 999999999999
column " EXEC. TIME" for 9999999.99 noprint

variable st_time varchar2(20);
variable en_time varchar2(20);
variable to_sess number;

declare

start_time varchar2(22);
end_time varchar2(22);
tot_sessions number;

BEGIN
  SELECT to_char(sysdate-5/1440,'dd-mon-yyyy hh24:mi:ss') into start_time FROM dual;
  SELECT to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') into end_time FROM dual;
  :st_time := start_time;
  :en_time := end_time;
	SELECT count(*) into tot_sessions FROM v$active_session_history WHERE sql_id IS NOT NULL and to_char(sample_time,'dd-mon-yyyy hh24:mi:ss') BETWEEN :st_time AND :en_time;
	:to_sess := tot_sessions;
END;
/

col mysession for a350
set heading off
SELECT '// ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') || ', ' || 
(SELECT lower(sys_context('USERENV','SESSION_USER')) || '@' "current_user" FROM dual) || '@' || (select upper(instance_name) FROM v$instance) || 
(SELECT ' (' || sys_context('USERENV','SID') || ') //' "current_sess" FROM dual) || chr(10) ||
'// *** TOP SQLs FOR SELECT INTERVAL (' || upper(:st_time) || ' - ' || substr(:en_time ,13,8) || ') *** //' || chr(10) ||
rpad('_',200,'_') as mysession1 FROM dual;
set heading on

break on " WAIT_CLASS"


WITH top10 AS 
(
SELECT 
  ash.sql_id as sql_id,
  substr(aud.name,1,20) as sql_type, 
  to_char(t_counts,99999.99) as actperc,
  t_counts as actperc1,
  sql_plan_hash_value as phash,
  ltrim(substr(vsph.sql_text,1,50)) as sqltxt,
  round((vsph.cpu_time/1000000)/(decode(vsph.executions,0,.00001,vsph.executions)),2) as exectime,
  vsph.parsing_schema_name as parsing_schema
FROM 
  (
  SELECT * FROM 
    (
    SELECT 
      sql_id,
      sql_plan_hash_value,
      sql_opcode, 
      round(trunc(((count(*)*100)/:to_sess),3),2) t_counts
    FROM
      v$active_session_history 
    WHERE
      sql_id IS NOT NULL AND to_char(sample_time,'dd-mon-yyyy hh24:mi:ss') BETWEEN :st_time AND :en_time
    GROUP BY
      sql_id, sql_plan_hash_value, sql_opcode ORDER BY t_counts desc
	) WHERE rownum < 11
  ) ash, 
  audit_actions aud,
  v$sqlarea_plan_hash vsph
WHERE
  ash.sql_opcode = aud.action AND
  ash.sql_id = vsph.sql_id(+) AND
  ash.sql_plan_hash_value = vsph.plan_hash_value(+)
ORDER BY 
  3 desc
)
SELECT 
  ltrim(rpad('|',to_char(trunc((actperc*100/(to_char((SELECT max(actperc1) FROM top10),999.99)))/10),99),'|') || ' ' || ltrim(to_char(actperc,999.99))) || ' ' as "ACTIVITY (%)",
  sql_id || decode(ltrim(to_char(exectime,999.99)),'','',(' (' || ltrim(to_char(exectime,999.99)) || ')')) as "SQL_ID (EXEC. TIME)", 
  exectime as " EXEC. TIME",
  sql_type,
  parsing_schema as "PARSING_SCHEMA",
  sqltxt as "SQL_TEXT",  
  phash as "PLAN_HASH"
FROM 
  top10
GROUP BY
  sql_id, sql_type, actperc, phash, exectime, sqltxt, parsing_schema
ORDER BY
  1 desc, 3 desc
/

set heading off
SELECT '( ACTIVITY (%) is based on sample count: ' || ltrim(to_char(:to_sess,999999999)) || ' )' FROM dual;
SELECT chr(10) ||
'See also :' || chr(10) || 
'---------------------------------------------------------------------------------------' || chr(10) || 
'@_top_sqls_waiting   @_sql_sessions <sql_id>   @_sql_stats <sql_id>   @_sql_id <sql_id>' || chr(10) || chr(10) || '/' FROM dual;

set heading on
set feed on
set pages 20

clear columns
clear breaks
clear computes
prompt
Script in action (Click here)
Top SESSIONS (_top_sessions.sql)
Script source
SQL>
set heading on
set linesize 210
set termout on;
set verify off;
set underline =
set tab off
set feed off

clear columns
clear breaks
clear computes

set serverout on
set feed off
set pages 23

col "SQL_ID" 		format	a18
col "SQL_TYPE"	format  a28
col PLSQL				format  a38
col SQL_TXT			format  a70

variable st_time varchar2(20);
variable en_time varchar2(20);
variable to_sess number;

DECLARE

start_time varchar2(22);
end_time varchar2(22);
tot_sessions number;

BEGIN
  SELECT to_char(sysdate-5/1440,'dd-mon-yyyy hh24:mi:ss') INTO start_time FROM dual;
  SELECT to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') INTO end_time FROM dual;
  :st_time := start_time;
  :en_time := end_time;
	SELECT 
	  sum(kount) INTO tot_sessions 
	FROM 
		(
		SELECT 
		  session_id, 
			session_serial#, 
			count(*) as kount 
		FROM
			v$active_session_history 
		WHERE 
			to_char(sample_time,'dd-mon-yyyy hh24:mi:ss') BETWEEN :st_time AND :en_time
		GROUP BY
			session_id, session_serial#
		);
	:to_sess := tot_sessions;
END;
/

col mysession for a350
set heading off
SELECT '// ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') || ', ' || 
(SELECT lower(sys_context('USERENV','SESSION_USER')) || '@' "current_user" FROM dual) || '@' || (SELECT upper(instance_name) FROM v$instance) || 
(SELECT ' (' || sys_context('USERENV','SID') || ') //' "current_sess" FROM dual) || chr(10) ||
'// *** TOP SESSIONS FOR SELECT INTERVAL (' || upper(:st_time) || ' - ' || substr(:en_time ,13,8) || ') *** //' ||chr(10) ||
rpad('_',200,'_') as mysession1 FROM dual;
set heading on

set pages 13
col "SID, SERIAL#" for a18
col "PROGRAM" for a42
col " ACT (%)" for a9 justify right
col "ACTIVITY (%)" for a18
col "CPU [Others]" for a12
col "TOP SESSIONS]" for a20
col "USERNAME" for a18
col "OSUSER" for a18

WITH top10 AS 
(SELECT 
  sid,
  username,
  osuser,
  program,
  to_char(activity,99999.99) as actperc,
  activity as actperc1
FROM 
  (
  SELECT * FROM
    (
    SELECT 
      a.session_id || ', ' || a.session_serial# as sid,
      substr(b.username,1,16) as username,
	    decode((select count(*) from v$session where sid = a.session_id and serial# = a.session_serial#),0,'Sess. Expired',substr(s.osuser,1,16)) as osuser,
      substr(a.program,1,40) as program,
      round(trunc(((count(*)*100)/:to_sess),3),2) as activity
    FROM 
      v$active_session_history a, dba_users b, v$session s
    WHERE 
	  to_char(a.sample_time,'dd-mon-yyyy hh24:mi:ss') BETWEEN :st_time AND :en_time AND a.user_id = b.user_id(+) AND a.session_id = s.sid(+) AND a.session_serial# = s.serial#(+)
    GROUP BY 
	  a.session_id, a.session_serial#, b.username, s.osuser, a.program
    ORDER BY 
	  5 desc
    )
  WHERE
    rownum < 11
  )
)
SELECT 
  ltrim(rpad('|',to_char(trunc((actperc*100/(to_char((SELECT max(actperc1) FROM top10),999.99)))/10),99),'|') || ' ' || ltrim(to_char(actperc,999.99))) || ' ' as "ACTIVITY (%)",
  sid as "SID, SERIAL#",
  username as "USERNAME",
  osuser as "OSUSER",  
  program as "PROGRAM"
FROM
  top10
GROUP BY
  sid, username, osuser, program, actperc
ORDER BY
  1 desc
/

set heading off
SELECT '( ACTIVITY (%) - is based on sample count: ' || ltrim(to_char(:to_sess,999999999)) || ' )' FROM dual;
SELECT chr(10) ||
'See also :' || chr(10) || 
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------' || chr(10) || 
'@_sess_activity <sid> <serial>   @_sess_details <sid>   @_top_waits   @_top_sessions_waiting   @_sess_waits <sid>   @_sqls_user <sql_id> <dbuser> <osuser>   @_sqls_sid <sid>' || chr(10) || chr(10) || '/' FROM dual;

set heading on
set feed on
set pages 20

clear columns
clear breaks
clear computes
prompt

Script in action (Click here)
Top WAITS (_top_waits.sql)
Script source
SQL>
set heading on
set linesize 210
set termout on;
set verify off;
set underline =
set tab off
set feed off

clear columns
clear breaks
clear computes

set serverout on
set feed off
set pages 23

col "WAIT_CLASS"   for a20
col "WAIT_EVENT"    for a32
col " ACT (%)" for a9 justify right
col "ACTIVITY" for a10
col "ACTIVITY (%)" for a18
col "SID"           for a18
col "USERNAME"      for a18
col "PROGRAM"       for a32
col "ACTION"        for a32

variable st_time varchar2(20);
variable en_time varchar2(20);
variable wtclasss   varchar2(20);
variable sess_state varchar2(20);
variable to_sess number;

DECLARE

start_time varchar2(22);
end_time varchar2(22);
tot_sessions number;

BEGIN
  SELECT to_char(sysdate-5/1440,'dd-mon-yyyy hh24:mi:ss') into start_time FROM dual;
  SELECT to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') into end_time FROM dual;
  :st_time := start_time;
  :en_time := end_time;
	SELECT count(*) into tot_sessions FROM v$active_session_history WHERE to_char(sample_time,'dd-mon-yyyy hh24:mi:ss') BETWEEN :st_time AND :en_time;
	:to_sess := tot_sessions;
END;
/

col mysession for a350
set heading off
SELECT '// ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') || ', ' || 
(SELECT lower(sys_context('USERENV','SESSION_USER')) || '@' "current_user" FROM dual) || '@' || (SELECT upper(instance_name) FROM v$instance) || 
(SELECT ' (' || sys_context('USERENV','SID') || ') //' "current_sess" FROM dual) || chr(10) ||
'// *** TOP WAITS FOR SELECT INTERVAL (' || upper(:st_time) || ' - ' || substr(:en_time ,13,8) || ') *** //' || chr(10) ||
rpad('_',200,'_') as mysession1 FROM dual;
set heading on

break on "WAIT_CLASS"

with top10 as
(
SELECT 
  wait_class,
  event,
  to_char(activity,99999.99) as actperc,
  activity as actperc1
FROM 
  (
  SELECT 
    substr(nvl(wait_class,'ON CPU'),1,18) as wait_class, 
	substr(nvl(event,'CPU'),1,30) as event, 
	round(trunc(((count(*)*100)/:to_sess),3),2) as activity
  FROM 
    v$active_session_history
  WHERE
    to_char(sample_time,'dd-mon-yyyy hh24:mi:ss') between :st_time AND :en_time
  GROUP BY
    wait_class, event 
  ORDER BY 
    3 desc
  )
WHERE 
  rownum < 11
)
SELECT 
  wait_class as "WAIT_CLASS",
  event as "WAIT_EVENT",
  ltrim(rpad('|',to_char(trunc((actperc*100/(to_char((SELECT 
		    max(actperc1)
          FROM top10
  ),999.99)))/10),99),'|') || ' ' || ltrim(to_char(actperc,999.99))) || ' ' as "ACTIVITY (%)"
FROM
  top10
GROUP BY
  wait_class, event, actperc
ORDER BY
  3 desc
/

set heading off
SELECT '( ACT (%) - is based on sample count: ' || :to_sess || ' )' FROM dual;
set heading on

prompt
prompt
accept wtclass prompt 'Supply CLASS for detailed wait sessions: ';

set heading off
SELECT '// *** TOP WAIT SESSIONS (' || '&&wtclass' || ') *** //' FROM dual;
set heading on

variable to_sessw number;

DECLARE

tot_sessionsw number;

BEGIN
	:wtclasss   := '&&wtclass';
	:sess_state := 'WAITING';
	IF :wtclasss = 'ON CPU' THEN
     :sess_state := 'ON CPU';
	END IF;
	SELECT 
		sum(kounts) into tot_sessionsw
	FROM 
		(
		SELECT
			session_id,
			session_serial#,
			count(*) as kounts
		FROM
			v$active_session_history
		WHERE 
			to_char(sample_time,'dd-mon-yyyy hh24:mi:ss') BETWEEN :st_time AND :en_time AND 
			(wait_class IS NULL or wait_class like '&&wtclass%') AND session_state = :sess_state GROUP BY session_id, session_serial#
		);
	:to_sessw := tot_sessionsw;
END;
/

break on "WAIT_CLASS"

with top10 as
(
SELECT 
  wait_class,
  sid,
  username,
  program,
  to_char(activity,99999.99) as actperc,
  activity as actperc1
FROM
  (
  SELECT 
    substr(nvl(wait_class,'ON CPU'),1,19) as wait_class,
    a.session_id || ',' || a.session_serial# as sid,
    substr(b.username,1,16) as username,
    substr(a.program,1,30) as program,
    round(trunc(((count(*)*100)/:to_sessw),3),2) as activity
  FROM 
    v$active_session_history a, dba_users b
  WHERE 
	to_char(a.sample_time,'dd-mon-yyyy hh24:mi:ss') BETWEEN :st_time AND :en_time 
	AND (wait_class IS NULL or wait_class like '&&wtclass%') AND session_state = :sess_state AND
    a.user_id = b.user_id(+)
  GROUP BY
    session_id, session_serial#, b.username, a.program, wait_class
  ORDER BY 5 desc
  )
WHERE 
  rownum < 11
)
SELECT
  wait_class as "WAIT_CLASS",
  sid as "SID",
  username as "USERNAME",
  program as "PROGRAM",
  ltrim(rpad('|',to_char(trunc((actperc*100/(to_char((SELECT 
		    max(actperc1)
          FROM top10
  ),999.99)))/10),99),'|') || ' ' || ltrim(to_char(actperc,999.99))) || ' ' as "ACTIVITY (%)"
FROM
  top10
GROUP BY
  wait_class, sid, username, program, actperc
ORDER BY
  5 desc
/

set heading off
SELECT '( ACTIVITY (%) - is based on sample count: ' || :to_sessw || ' )' FROM dual;
prompt
SELECT '// *** WAIT EVENTS *** //' as mssg FROM dual;
set heading on

col "WAIT_EVENT"    for a42

with top10 as
(
SELECT 
  wait_class,
  wait_event,
  to_char(activity,99999.99) as actperc,
  activity as actperc1
FROM
  (
  SELECT 
    substr(nvl(wait_class,'ON CPU'),1,19) as wait_class,
    substr(nvl(event,'CPU'),1,40) as wait_event,
    round(trunc(((count(*)*100)/(SELECT 
							       count(*)
                                 FROM
                                   v$active_session_history 
                                 WHERE
                                   to_char(sample_time,'dd-mon-yyyy hh24:mi:ss') BETWEEN :st_time AND :en_time AND 
								   (wait_class IS NULL or wait_class like '&&wtclass%') AND session_state = :sess_state
                                 )),3),2) as activity
  FROM 
    v$active_session_history a
  WHERE 
	to_char(a.sample_time,'dd-mon-yyyy hh24:mi:ss') between :st_time AND :en_time 
	AND (wait_class IS NULL or wait_class like '&&wtclass%') AND session_state = :sess_state
  GROUP BY
    wait_class, event
  ORDER BY 3 desc
  )
WHERE 
  rownum < 11
)
SELECT
  wait_class as "WAIT_CLASS",
  wait_event as "WAIT_EVENT",
  ltrim(rpad('|',to_char(trunc((actperc*100/(to_char((SELECT 
		    max(actperc1)
          FROM top10
  ),999.99)))/10),99),'|') || ' ' || ltrim(to_char(actperc,999.99))) || ' ' as "ACTIVITY (%)"
FROM
  top10
GROUP BY
  wait_class, wait_event, actperc
ORDER BY
  3 desc
/

set heading off
SELECT chr(10) ||
'See also :' || chr(10) || 
'---------------------------------------------------------------------------------------------------------------------------------------------------' || chr(10) || 
'@_top_activity   @_top_sqls   @_top_sessions   @_top_sessions_waiting   @_sess_activity <sid> <serial#>   @_sess_details <sid>   @_sess_waits <sid>' || chr(10) || chr(10) || '/' FROM dual;

set heading on
set feed on
set pages 20
undef wtclass wtclasss sess_state
clear columns
clear breaks
clear computes
prompt
            
Script in action (Click here)
Please write your comment if this was useful.

Shannura

/