How to find active sqls

Following script can be used to determine long running active sqls with elapsed time and wait event:

set lines 233
select /*+ ordered */ to_char(s.sid,'99999'),
substr(username,1,8),
substr(t.sql_text,1,60),
nvl(last_call_et,0) TIME_sec,
t.hash_value,
substr(w.event,1,30) WAIT_EV,
substr(machine,1,10) MACHINE
from v$session s,v$sqltext t,v$session_wait w
where s.sql_address=t.address
and s.sql_HASH_VALUE=t.HASH_VALUE
and w.sid=s.sid
and username between 'AAAAAAAAA' and 'ZZZZZZZZZ'
and s.status='ACTIVE'
and t.piece=0
--and upper(sql_text) not like 'BEGIN%'
order by TIME_SEC
/

In order to find sqls with high executions and little elapsed times

1-) Take a snap of v$sqlarea:

create table tmp as select sql_id, sql_text, executions, ELAPSED_TIME, CPU_TIME, DISK_READS, DIRECT_WRITES, BUFFER_GETS from v$sqlarea;

2-) Track sqls depending on executions:

select a.sql_id,substr(b.sql_text,1,50),
b.executions-a.executions,
b.ELAPSED_TIME - a.ELAPSED_TIME,
b.CPU_TIME - a.CPU_TIME,
b.DISK_READS - a.DISK_READS,
b.DIRECT_WRITES - a.DIRECT_WRITES,
b.BUFFER_GETS - a.BUFFER_GETS
from tmp a, v$sqlarea b
where a.sql_id=b.sql_id and b.executions-a.executions > 100 order by b.executions-a.executions
/
CREATING A JOB FOR ACTIVE SQL EXECUTIONS HISTORYOne day i need to monitor sql executions over time in order to determine batch-OLTP transactions over time. You may wonder why i did not use AWR report, EM or something else.. because usually i spent much more time on those tools to find what i need.

One important note, because my focus was sql execution counts, i put "a.sql_id=b.sql_id and b.executions-a.executions > 10000" condition in my sql withing procedure sp_sqlarea_snap_history in order to eliminate low execution sqls to be ignored. If your focus on something else (for example buffer gets or CPU) then i suggest change this condition which suits your case. Sometimes a sql executed a few times may consume much more resource then an other sql executes thousand times....

create table sqlarea_snap as select sysdate snap_time, sql_id, sql_text, executions, ELAPSED_TIME, CPU_TIME, DISK_READS, DIRECT_WRITES, BUFFER_GETS from v$sqlarea where 1=2;

create table sqlarea_snap_history
(SNAP_BEGIN_TIME DATE,
SNAP_END_TIME DATE,
SQL_ID VARCHAR2(13),
SQL_TEXT VARCHAR2(1000),
EXECUTIONS_DELTA NUMBER,
TPS NUMBER,
ELAPSED_TIME_DELTA NUMBER,
CPU_TIME_DELTA NUMBER,
DISK_READS_DELTA NUMBER,
DIRECT_WRITES_DELTA NUMBER,
BUFFER_GETS_DELTA NUMBER);

create or replace procedure sp_sqlarea_snap_history
as
begin
EXECUTE IMMEDIATE 'insert into sqlarea_snap_history select a.SNAP_TIME, sysdate, a.sql_id, b.sql_text, b.executions-a.executions, '
'trunc((b.executions-a.executions)/((sysdate-snap_time)*24*60*60)), b.ELAPSED_TIME - a.ELAPSED_TIME, b.CPU_TIME - a.CPU_TIME, '
'b.DISK_READS - a.DISK_READS, b.DIRECT_WRITES - a.DIRECT_WRITES, b.BUFFER_GETS - a.BUFFER_GETS from sqlarea_snap a, v$sqlarea b '
'where a.sql_id=b.sql_id and b.executions-a.executions > 10000 order by b.executions-a.executions';
EXECUTE IMMEDIATE 'drop table sqlarea_snap';
EXECUTE IMMEDIATE 'create table sqlarea_snap as select sysdate snap_time, sql_id, sql_text, executions, ELAPSED_TIME, CPU_TIME, '
'DISK_READS, DIRECT_WRITES, BUFFER_GETS from v$sqlarea ';
end;
/


exec dbms_job.isubmit(20,'sp_sqlarea_snap_history;',sysdate,'sysdate+1/144',false);

Finally, here is the query

select SNAP_BEGIN_TIME, EXECUTIONS_DELTA, TPS, SQL_TEXT from sqlarea_snap_history where SNAP_BEGIN_TIME > trunc(sysdate) order by SNAP_BEGIN_TIME desc, EXECUTIONS_DELTA desc;