Category Archives: Diagnostic and Tuning

This category contains scripts meant to help diagnose or troubleshoot performance problems related to the Oracle Database.

Top 10 queries from v$active_session_history


This query returns the top 10 queries by resource consumption (CPU+IO+WAIT) in the last hour from v$active_session_history.
Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it.

top 10 queries from v$active_session_history

select * from (
		 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 <11

This query return top queries by resources consumed, you can easily return the top I/O, WAITS or CPU queries by changing the order by clause.

You can see my other post about how to build a graph from the v$active_session_history view here

Graph from v$active_session_history


This query generate the data needed to build the load graph from v$active session_history, the output represent what sessions did in your database for the last hour, be careful, the v$active_session_history is part of the diagnostic pack, if you don’t have a license for this pack you should not run this query.

Query to get graph from v$active_session_history

SELECT sysmetric_history.sample_time,
  cpu/60 AS cpu,
  bcpu/60 AS bcpu,
  DECODE(SIGN((cpu+bcpu)/60-cpu_ora_consumed), -1, 0, ((cpu+bcpu)/60-cpu_ora_consumed)) AS cpu_ora_wait,
  scheduler/60 AS scheduler,
  uio/60 AS uio,
  sio/60 AS sio,
  concurrency/60 AS concurrency,
  application/60 AS application,
  configuration/60 AS configuration,
  administrative/60 AS administrative,
  network/60 AS network,
  queueing/60 AS queueing,
  clust/60 AS clust,
  other/60 AS other
     TRUNC(sample_time,'MI') AS sample_time,
     DECODE(session_state,'ON CPU',DECODE(session_type,'BACKGROUND','BCPU','ON CPU'), wait_class) AS wait_class
   FROM v$active_session_history
   WHERE sample_time>sysdate-INTERVAL '1' HOUR
   AND sample_time<=TRUNC(SYSDATE,'MI')) ash
   PIVOT (COUNT(*) FOR wait_class IN ('ON CPU' AS cpu,'BCPU' AS bcpu,'Scheduler' AS scheduler,'User I/O' AS uio,'System I/O' AS sio,
   'Concurrency' AS concurrency,'Application' AS application,'Commit' AS COMMIT,'Configuration' AS configuration,
   'Administrative' AS administrative,'Network' AS network,'Queueing' AS queueing,'Cluster' AS clust,'Other' AS other)) ash,
      TRUNC(begin_time,'MI') AS sample_time,
      VALUE/100 AS cpu_ora_consumed
    FROM v$sysmetric_history
    AND metric_name='CPU Usage Per Sec') sysmetric_history
WHERE ash.sample_time (+)=sysmetric_history.sample_time
ORDER BY sample_time;

The previous query will only work in 11g and more as it uses the PIVOT keywork, the following will work with 10g.

  DECODE(SIGN((cpu+bcpu)-cpu_ora_consumed), -1, 0, ((cpu+bcpu)-cpu_ora_consumed)) AS cpu_ora_wait,
     TRUNC(sample_time,'MI') AS sample_time,
     SUM(DECODE(session_state,'ON CPU',DECODE(session_type,'BACKGROUND',0,1),0))/60 AS cpu,
     SUM(DECODE(session_state,'ON CPU',DECODE(session_type,'BACKGROUND',1,0),0))/60 AS bcpu,
     SUM(DECODE(wait_class,'Scheduler',1,0))/60 AS scheduler,
     SUM(DECODE(wait_class,'User I/O',1,0))/60 AS uio,
     SUM(DECODE(wait_class,'System I/O',1,0))/60 AS sio,
     SUM(DECODE(wait_class,'Concurrency',1,0))/60 AS concurrency,
     SUM(DECODE(wait_class,'Application',1,0))/60 AS application,
     SUM(DECODE(wait_class,'Commit',1,0))/60 AS COMMIT,
     SUM(DECODE(wait_class,'Configuration',1,0))/60 AS configuration,
     SUM(DECODE(wait_class,'Administrative',1,0))/60 AS administrative,
     SUM(DECODE(wait_class,'Network',1,0))/60 AS network,
     SUM(DECODE(wait_class,'Queueing',1,0))/60 AS queueing,
     SUM(DECODE(wait_class,'Cluster',1,0))/60 AS clust,
     SUM(DECODE(wait_class,'Other',1,0))/60 AS other
   FROM v$active_session_history
   WHERE sample_time>sysdate- INTERVAL '1' HOUR
   AND sample_time<=TRUNC(SYSDATE,'MI')
   GROUP BY TRUNC(sample_time,'MI')) ash,
     TRUNC(begin_time,'MI') AS sample_time,
     VALUE/100 AS cpu_ora_consumed
   FROM v$sysmetric_history
   AND metric_name='CPU Usage Per Sec') sysmetric_history
WHERE ash.sample_time (+)=sysmetric_history.sample_time
ORDER BY sample_time;

Here is a sample graph I generated using SQL Server Reporting Services:


I would like to thank Yannick Jaquier, for providing these queries, you can find his orginal post here

Find missing index


This script list the top 10 columns most used in equality predicates or equijoin predicates and which are not indexed.
If you want to be more precise in your analyze you can limit the list to table containing more than a defined number of rows by adding the dba_tables table and filter on the num_rows column.

Missing Index script

ACCEPT SCHEMA_NAME PROMPT 'Choose the schema to analyze:'

select * from (
  select 'the column ' || || ' of the table ' || || '.' || || ' was used ' || u.equality_preds || ' times in an equality predicate and ' || u.equijoin_preds || ' times in an equijoin predicate and is not indexed' as colum_to_index
  from sys.col_usage$ u,
       sys.obj$ o,
       sys.col$ c,
       sys.user$ us
  where u.obj# = o.obj#
  and   u.obj# = c.obj#
  and   us.user# = o.owner#
  and   u.intcol# = c.col#
  and not in (select column_name from dba_ind_columns where index_owner ='&SCHEMA_NAME')
  and   (u.equality_preds > 100 OR u.equijoin_preds > 100)
  order by u.equality_preds+u.equijoin_preds desc)
WHERE rownum <11;