Active sessions
select sid,serial#,username,status,blocking_session,to_char(logon_time,'DD-MON-YYYY HH24:MI:SS') ,sql_address,machine from v$session where username not in ('SYS','SYSTEM','DBSNMP') and username is not null order by status, logon_time;
select session_id,blocking_session,blocking_session_serial#,sql_exec_start,min(sample_time),max(sample_time), count(*), action
from DBA_HIST_ACTIVE_SESS_HISTORY s
where sample_time > sysdate -300
and event= 'enq: TX - row lock contention'
group by session_id,blocking_session,blocking_session_serial#,sql_exec_start,action
having count(*) > 9
order by min(sample_time) desc
Kill Session
col cmd for a70
select username ,'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' cmd from v$session where status='INACTIVE' and username not in ('DBSNMP','SYS') order by username;
Active sessions with query details
col user for a20
break on username on osuser on sid on serial# on mins_running on SQL_ID
select s.username||'-'||s.osuser as "user", s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_id,q.sql_text
from v$session s join v$sqltext_with_newlines q
on s.sql_address = q.address
where status='ACTIVE'
and type <>'BACKGROUND'
-- and last_call_et> 60 -- time in seconds
order by s.username,s.osuser ,q.sql_id,s.sid,serial#,q.piece
SELECT
SUBSTR(SS.USERNAME,1,8) USERNAME,
SS.OSUSER "USER",
AR.MODULE || ' @ ' || SS.machine CLIENT,
SS.PROCESS PID,
TO_CHAR(AR.LAST_LOAD_TIME, 'DD-Mon HH24:MM:SS') LOAD_TIME,
AR.DISK_READS DISK_READS,
AR.BUFFER_GETS BUFFER_GETS,
SUBSTR(SS.LOCKWAIT,1,10) LOCKWAIT,
W.EVENT EVENT,
SS.status,
AR.SQL_fullTEXT SQL
FROM V$SESSION_WAIT W,
V$SQLAREA AR,
V$SESSION SS,
v$timer T
WHERE SS.SQL_ADDRESS = AR.ADDRESS
AND SS.SQL_HASH_VALUE = AR.HASH_VALUE
AND SS.SID = w.SID (+)
AND ss.STATUS = 'ACTIVE'
AND W.EVENT != 'client message'
ORDER BY SS.LOCKWAIT ASC, SS.USERNAME, AR.DISK_READS DESC
Long queries :
Query running for more than 5 minutes
col username for a15
col osuser for a10
col machine for a15
col sql_text for a60 word_wrapped
select osuser,username,
sid,serial#, -- program, machine,
sql_text,
start_time,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 run_time,
to_number(sysdate-to_date(c.start_time,'MM/DD/YY HH24:MI:SS'))*24*60 query_active_time
from
v$session a,
v$sqlarea b,
v$transaction c
where ( (a.sql_address = b.address and a.sql_hash_value = b.hash_value )
or ( a.prev_sql_addr = b.address and a.prev_hash_value = b.hash_value ) )
and c.ses_addr = a.saddr
and a.status = 'ACTIVE'
and sysdate-to_date(c.start_time,'MM/DD/YY HH24:MI:SS') > 5/(60*24);
Query running for more than 15 minutes
col user for a20
break on user on osuser on sid on serial# on mins_running on SQL_ID skip 1
select s.username||'-'||s.osuser as "user", s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_id,q.sql_text
from v$session s join v$sqltext_with_newlines q
on s.sql_address = q.address
where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et > 60 *15 -- last_call_et in seconds
order by s.username,s.osuser ,q.sql_id,s.sid,serial#,q.piece;
col MESSAGE for a90
COLUMN percent FORMAT 999.99
COLUMN percent FORMAT 999.99
SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1;
Query running from last one hour
SELECT
SUBSTR(SS.USERNAME,1,8) USERNAME,
SS.OSUSER "USER",
AR.MODULE || ' @ ' || SS.machine CLIENT,
SS.PROCESS PID,
TO_CHAR(AR.LAST_LOAD_TIME, 'DD-Mon HH24:MM:SS') LOAD_TIME,
AR.DISK_READS DISK_READS,
AR.BUFFER_GETS BUFFER_GETS,
SUBSTR(SS.LOCKWAIT,1,10) LOCKWAIT,
W.EVENT EVENT,
SS.status,
AR.SQL_fullTEXT SQL
FROM V$SESSION_WAIT W,
V$SQLAREA AR,
V$SESSION SS,
v$timer T
WHERE SS.SQL_ADDRESS = AR.ADDRESS
AND SS.SQL_HASH_VALUE = AR.HASH_VALUE
AND SS.SID = w.SID (+)
AND ss.STATUS = 'ACTIVE'
AND W.EVENT != 'client message'
and LAST_LOAD_TIME < (sysdate - (1/(24))) -- from last 1 hour
ORDER BY SS.LOCKWAIT ASC, SS.USERNAME, AR.DISK_READS DESC;
SELECT
SUBSTR(SS.USERNAME,1,8) USERNAME,
SS.OSUSER "USER",
AR.MODULE || ' @ ' || SS.machine CLIENT,
SS.PROCESS PID,
TO_CHAR(AR.LAST_LOAD_TIME, 'DD-Mon HH24:MM:SS') LOAD_TIME,
AR.DISK_READS DISK_READS,
AR.BUFFER_GETS BUFFER_GETS,
SUBSTR(SS.LOCKWAIT,1,10) LOCKWAIT,
W.EVENT EVENT,
SS.status,
AR.SQL_fullTEXT SQL
FROM V$SESSION_WAIT W,
V$SQLAREA AR,
V$SESSION SS,
v$timer T
WHERE SS.SQL_ADDRESS = AR.ADDRESS
AND SS.SQL_HASH_VALUE = AR.HASH_VALUE
AND SS.SID = w.SID (+)
AND ss.STATUS = 'ACTIVE'
AND W.EVENT != 'client message'
and LAST_LOAD_TIME < (sysdate - (1/(24))) -- from last 1 hour
ORDER BY SS.LOCKWAIT ASC, SS.USERNAME, AR.DISK_READS DESC;
Query running from last threshold seconds
SELECT SYS_CONTEXT('USERENV','SERVER_HOST') ENV,
SYS_CONTEXT('USERENV','DB_NAME') DATABASE_NAME,
ROUND(A.ELAPSED_TIME / A.EXECUTIONS / 1000000) SQL_AVERAGE_ELAPSED_TIME,
A.SQL_ID SQL_ID,
A.SQL_FULLTEXT SQL_TEXT,
A.EXECUTIONS SQL_EXECUTIONS,
NVL(S.PROGRAM,A.MODULE) SESSION_PROGRAM_NAME,
NVL(S.USERNAME,A.PARSING_SCHEMA_NAME) SESSION_USER_NAME,
S.OSUSER SESSION_OS_USER_NAME
FROM V$SQLAREA A,
V$SESSION S
WHERE A.SQL_ID = S.SQL_ID(+)
AND A.EXECUTIONS > 0
AND ROUND(A.ELAPSED_TIME / A.EXECUTIONS / 1000000) > &threshold-in-seconds
Queries which ran between time t1 and t2
select a.sql_id,dbms_lob.substr(b.sql_text,4000,1)
from dba_hist_active_sess_history a, dba_hist_sqltext b
from dba_hist_active_sess_history a, dba_hist_sqltext b
where sample_time between to_date('20140101:07:00','yyyymmdd:hh24:mi') and to_date('20140120:09:00','yyyymmdd:hh24:mi')
and b.sql_id=a.sql_id
union all
select a.sql_id ,dbms_lob.substr(b.sql_text,4000,1)from v$active_session_history a ,v$sqlarea b
where sample_time between to_date('20140101:07:00','yyyymmdd:hh24:mi') and to_date('20140120:09:00','yyyymmdd:hh24:mi')
and b.sql_id=a.sql_id;
select a.SAMPLE_TIME,
SQL_ID,
SQL_PLAN_HASH_VALUE ,
-- a.SQL_OPNAME, -- include in 11gr2
-- a.SQL_EXEC_START, -- include in 11gr2
a.program,
a.client_id,
b.SQL_TEXT,
(case when c.executions_delta > 0 then c.elapsed_time_delta/ c.executions_delta/ 1000000/60 else 0 end) Minutes_per_exe,
e.username
from DBA_HIST_ACTIVE_SESS_HISTORY a
join dba_hist_sqltext b on (a.SQL_ID = b.SQL_ID)
join dba_users e on (a.user_id = e.user_id)
left outer join dba_hist_sqlstat c on (a.sql_id = c.sql_id)
left outer join dba_hist_snapshot d on (c.snap_id = d.snap_id and
c.dbid = d.dbid and
c.instance_number = d.instance_number and
a.sample_time between d.begin_interval_time and d.end_interval_time)
where b.sql_text like '%&sql_text%'
and a.sample_time between trunc(sysdate) -9 and trunc(sysdate);
select a.SAMPLE_TIME,
SQL_ID,
SQL_PLAN_HASH_VALUE ,
-- a.SQL_OPNAME, -- include in 11gr2
-- a.SQL_EXEC_START, -- include in 11gr2
a.program,
a.client_id,
b.SQL_TEXT,
(case when c.executions_delta > 0 then c.elapsed_time_delta/ c.executions_delta/ 1000000/60 else 0 end) Minutes_per_exe,
e.username
from DBA_HIST_ACTIVE_SESS_HISTORY a
join dba_hist_sqltext b on (a.SQL_ID = b.SQL_ID)
join dba_users e on (a.user_id = e.user_id)
left outer join dba_hist_sqlstat c on (a.sql_id = c.sql_id)
left outer join dba_hist_snapshot d on (c.snap_id = d.snap_id and
c.dbid = d.dbid and
c.instance_number = d.instance_number and
a.sample_time between d.begin_interval_time and d.end_interval_time)
where b.sql_text like '%&sql_text%'
and a.sample_time between trunc(sysdate) -9 and trunc(sysdate);
Expensive query by Rank between time t1 and t2
select * from
(select s.sql_id, max(s.ELAPSED_TIME_TOTAL/s.EXECUTIONS_TOTAL)/1000000/60/60 Hrs_per_exec,
RANK() OVER (ORDER BY (max(s.ELAPSED_TIME_TOTAL/s.EXECUTIONS_TOTAL)) DESC) elapsed_rank,
RANK() OVER (ORDER BY (max(s.CPU_TIME_TOTAL/s.executions_total)) DESC) cpu_rank
from dba_hist_sqlstat s, dba_hist_snapshot sn
where sn.begin_interval_time between to_date('07-JAN-2014 0001','dd-mon-yyyy hh24mi')
and to_date('07-JAN-2014 2300','dd-mon-yyyy hh24mi')
and sn.snap_id=s.snap_id and s.executions_total >0
group by s.sql_id
)
--where cpu_rank <=100 and elapsed_rank<=100
where elapsed_rank<=100;
Queries which ran at particular snap id or hour in the past(Query may be running and not completed at that time)
select * from
(
select
sql.sql_id c1,
sql.buffer_gets_delta c2_buffers,
sql.disk_reads_delta c3_reads,
sql.iowait_delta c4_IO,
sql.elapsed_time_delta/1000000/decode(sql.executions_delta,0,1,sql.executions_delta)/60/60 c5_Etime_Hr ,
sql.executions_delta ,EXECUTIONS_TOTAL
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and
s.snap_id= (select snap_id
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='01-DEC-2014'
and EXTRACT(HOUR FROM begin_interval_time) between 13 and 13 )
order by
c5_etime_Hr desc)
where rownum < 16;
Then check the sqltext
select * from dba_hist_sqltext where sql_id= '&1';
Then check the object with sql id
with master as (
select
sql_id,
sql_plan_hash_value,
sql_opcode,
sum(cpu) cpu,
sum(wait) wait,
sum(io) io,
sum(total) total,
decode(sum(io),0,null, decode(objn,-1,NULL,objn)) objn,
row_number() over ( partition by sql_id order by io desc ) seq,
ratio_to_report( sum(io)) over ( partition by sql_id ) pct
from (
select
ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE , sql_opcode,
current_obj# objn,
sum(decode(ash.session_state,'ON CPU',1,0)) cpu,
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) wait ,
sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) io ,
sum(decode(ash.session_state,'ON CPU',1,1)) total
from dba_hist_active_sess_history ash
where SQL_ID is not NULL
group by sql_id, SQL_PLAN_HASH_VALUE , sql_opcode, current_obj#
)
group by sql_id, SQL_PLAN_HASH_VALUE , sql_opcode, objn,io
)
select * from (
select
sql_id,
sql_plan_hash_value,
aud.name aud_action,
sum(cpu) cpu,
sum(wait) wait,
sum(io) io,
sum(total) total,
round(max(decode(seq,1,pct,null)),2)*100 pct_io,
max(decode(seq,1,o.object_name,null)) pct_io_obj
from master,audit_actions aud , dba_objects o
where
objn=o.object_id(+)
and sql_opcode=aud.action
and sql_id ='&1'
group by sql_id,sql_plan_hash_value,aud.name
order by total desc );
Queries which ran between n1 seconds to n2 seconds or more than n seconds to execute
select trunc((elapsed_time_delta/executions_delta)/1000000,4) "AVG_EXECUTION_TIME",
PLAN_HASH_VALUE "PLAN_HASH_VALUE", execution_date "EXECUTION_DATE"
from
(
select sum(elapsed_time_delta) elapsed_time_delta,
sum(executions_delta) executions_delta,
PLAN_HASH_VALUE,
to_char(trunc(end_interval_time),'mm/dd/yy') execution_date
from dba_hist_sqlstat a,
dba_hist_snapshot b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
group by plan_hash_value,to_char(trunc(end_interval_time),'mm/dd/yy')
)
where executions_delta > 0
and trunc((elapsed_time_delta/executions_delta)/1000000,4) between &nsec1 and &nsec2
order by execution_date;
Elapsed time of query at each execution
select
snap_id,
SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
from DBA_HIST_SQLSTAT
where SQL_ID in ('&sqlid')
group by snap_id ,SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS
or
select end_interval_time execution_date, executions_delta, elapsed_time_delta/1000000
from dba_hist_sqlstat a,
dba_hist_snapshot b
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and PLAN_HASH_VALUE = &1;
Find SQL using PID from top command
High cpu reported in top command. Get the PID from the top command
ps -aef|grep <PID_from_top>
select
s.username su,
substr(sa.sql_text,1,540) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid='&spid';
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
Find Queries which ran in parallel
select a.BEGIN_INTERVAL_TIME
, a.INSTANCE_NUMBER
, b.SQL_ID
, b.PLAN_HASH_VALUE
, b.executions_delta EXEC_DELTA, round(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000,2)/b.PX_SERVERS_EXECS_DELTA/60 "Elapsed time (sec.)"
, b.PX_SERVERS_EXECS_TOTAL PX_SERV_TOT
, b.PX_SERVERS_EXECS_DELTA PX_SERV_DELTA
from dba_hist_snapshot a,
dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.begin_interval_time>sysdate-1
and a.instance_number=b.instance_number
and b.PX_SERVERS_EXECS_TOTAL>0
and round(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000,2)>1;
select sql_id , max(px_servers_execs_total)
from (
select distinct a.sql_id , px_servers_execs_total --,dbms_lob.substr(b.sql_text,4000,1), px_servers_execs_total
from dba_hist_active_sess_history a, dba_hist_sqltext b , dba_hist_sqlstat c
where sample_time between to_date('20160124:08:00','yyyymmdd:hh24:mi') and to_date('20160124:08:01','yyyymmdd:hh24:mi')
and b.sql_id=a.sql_id and b.sql_id=c.sql_id
and c.snap_id in ( select snap_id from dba_hist_active_sess_history
where sample_time between to_date('20160124:08:00','yyyymmdd:hh24:mi') and to_date('20160124:08:01','yyyymmdd:hh24:mi') )
union all
select a.sql_id , px_servers_execs_total--,dbms_lob.substr(b.sql_text,4000,1) , px_servers_execs_total
from v$active_session_history a ,v$sqlarea b , dba_hist_sqlstat c
where sample_time between to_date('20160124:08:00','yyyymmdd:hh24:mi') and to_date('20160124:08:01','yyyymmdd:hh24:mi')
and b.sql_id=a.sql_id and b.sql_id=c.sql_id
and c.snap_id in ( select snap_id from dba_hist_active_sess_history
where sample_time between to_date('20160124:08:00','yyyymmdd:hh24:mi') and to_date('20160124:08:01','yyyymmdd:hh24:mi') )
order by sql_id
)
group by sql_id ;
All queries executed on a day
select v.SQL_TEXT, EXECUTIONS
/*v.PARSING_SCHEMA_NAME,
v.FIRST_LOAD_TIME,
v.DISK_READS,
v.ROWS_PROCESSED,
v.ELAPSED_TIME,
v.service */
from v$sql v
where to_date(v.FIRST_LOAD_TIME,'YYYY-MM-DD hh24:mi:ss') between to_date('20160307:00:01','yyyymmdd:hh24:mi') and to_date('20160307:23:59','yyyymmdd:hh24:mi')
and PARSING_SCHEMA_NAME = upper('&1');
http://gavinsoorma.com/2012/11/ash-and-awr-performance-tuning-scripts/
http://www.nocoug.org/download/2008-08/a-tour-of-the-awr-tables.nocoug-Aug-21-2008.abercrombie.html
