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

Description

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

Description

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,
  COMMIT/60 AS COMMIT,
  configuration/60 AS configuration,
  administrative/60 AS administrative,
  network/60 AS network,
  queueing/60 AS queueing,
  clust/60 AS clust,
  other/60 AS other
FROM
  (SELECT
     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,
   (SELECT
      TRUNC(begin_time,'MI') AS sample_time,
      VALUE/100 AS cpu_ora_consumed
    FROM v$sysmetric_history
    WHERE GROUP_ID=2
    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.

SELECT
  sysmetric_history.sample_time,
  cpu,
  bcpu,
  DECODE(SIGN((cpu+bcpu)-cpu_ora_consumed), -1, 0, ((cpu+bcpu)-cpu_ora_consumed)) AS cpu_ora_wait,
  scheduler,
  uio,
  sio,
  concurrency,
  application,
  COMMIT,
  configuration,
  administrative,
  network,
  queueing,
  clust,
  other
FROM
  (SELECT
     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,
  (SELECT
     TRUNC(begin_time,'MI') AS sample_time,
     VALUE/100 AS cpu_ora_consumed
   FROM v$sysmetric_history
   WHERE GROUP_ID=2
   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:

ash

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

Find missing index

Description

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 ' || c.name || ' of the table ' || us.name || '.' || o.name || ' 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   us.name='&SCHEMA_NAME'
  and   c.name 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;