-- ********************************************************************
-- * Copyright Notice   : (c)2009 OraPub, Inc.
-- * Filename           : topdml.sql
-- * Author             : Craig A. Shallahamer
-- * Original           : 27-May-2009
-- * Last Update        : 17-Nov-2009
-- * Description        : topdml.sql - Shows top DML SQL
-- * Usage              : start topdml.sql
-- ********************************************************************

def osm_prog    = 'topdml.sql'
def osm_title   = 'Top DML SQL Summary'

start osmtitlel

set linesize 130

col e_time format 999,999,990 heading "ELAPSED|TIME(s)"
col s_text heading "SQL TEXT (PARTIAL)"
col c_type format a13 heading "SQL TYPE"
col module format a15 trunc
col action format a15 trunc
col service format a15 trunc
col execs format 999,999,990 heading "EXECUTES(k)"
col toprank noprint

select *
from
(
select	SQL_ID,
	elapsed_time/1000000 e_time,
	dense_rank() over(order by elapsed_time desc) toprank, 
	DECODE(command_type, 
		2, 'INSERT', 
		3, 'SELECT', 
		6, 'UPDATE', 
		7, 'DELETE', 
		26, 'LOCK', 
		42, 'DDL', 
		44, 'COMMIT', 
		47, 'PL/SQL BLOCK', 
		command_type) c_type,
	substr(sql_text,1,20) s_text,
	module, executions/1000 execs, action, service
from v$sql
where command_type in (2,6,7,44)
)
where toprank ‹= 10
/

start osmclear