rem Author:  Mark Lang, 1998
rem Name:    lastsql.sql
rem Purpose: Display last executed SQL statement by session with stats
rem Usage:   @lastsql <%user%> <%status%>
rem Subject: session
rem Attrib:  sql dba
rem Descr:
rem Notes:
rem SeeAlso: @sqlarea
rem History:
rem          01-feb-98   Initial release

@setup

column username format a10
column status format a10
--column sid format a15
--column serial# format a15
column osuser format a10
column machine format a8
column process format a9
column client_info format a15
column text format a80 word
set long 20000
set lines 200
set pages 1000

define usr="upper('SYSADM')"
define sta="upper('ACTIVE')"

select
s.username
,s.sid sidd
,s.serial# serialno
,s.osuser
,s.machine
,s.process
,s.client_info
,'(diskr='||l.disk_reads
||', bgets='||l.buffer_gets
||', opt='||l.optimizer_mode||':'||l.optimizer_cost 
||', rows='||l.rows_processed
||', sorts='||l.sorts
||', mem=s:'||l.sharable_mem||',p:'||l.persistent_mem||',r:'||l.runtime_mem
||')'||&&cr
||l.sql_text
  text
from gv$session s, gv$sql l
where s.sql_address = l.address
and s.username is not null and s.username <> 'SYSTEM'
and s.status like &&sta
;

undef usr sta

@setdefs