Overall DB issue :
select event, total_waits, TIME_WAITED from v$system_event where TIME_WAITED > 0
and event not in (
'rdbms ipc message',
'pmon timer',
'smon timer',
'SQL*Net message from client',
'PL/SQL lock timer',
'jobq slave wait',
'PX Idle Wait',
'wait for stopper event to be increased',
'rdbms ipc reply',
'control file heartbeat',
'PX Deq: Txn Recovery Start',
'reliable message',
'PX Deq: Signal ACK',
'SQL*Net break/reset to client')
order by time_waited desc;
Current Running SQLs
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15
select S.USERNAME,sid,serial#,a.sql_id,a.SQL_TEXT, --i.host_name,machine,
S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,'DD-MON-RR HH24:MI') login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
-- and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
order by S.seconds_in_wait asc
/
Current Running SQLs
set pages 50000 lines 32767
col program format a20
col sql_text format a50
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text
from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/
Top Sql for tuning (order by buffer or elapsed time)
SELECT * FROM (SELECT parsing_schema_id, SQL_ID,Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) dskrds_pex,
a.disk_reads,
a.buffer_gets,
a.executions EXE, a.sorts,
ELAPSED_TIME Etime,
ELAPSED_TIME/Decode(a.executions,0,1,a.executions) ET_pex,
CPU_TIME
FROM v$sqlarea a
ORDER BY 5 DESC)
WHERE rownum <= 5
column gets_per_exe format 999,999,990
column disk_reads format 999,999,990
column buffer_gets format 999,999,990
column executions format 999,999,990
column sorts format 999,990
column hash_value format 99999999990
column sql_id format a14
column sql_text format a50 word_wrapped
column username format a13
column cpu_time format 999,990.00 heading 'CPU(s)'
column elapsed_time format 999,990.00 heading 'Elapsed(s)'
select u.username
, v.disk_reads
, v.buffer_gets
, v.executions
-- , v.sorts
, v.buffer_gets/v.executions as gets_per_exe
, (v.cpu_time/1000000) as cpu_time
, (v.elapsed_time/1000000) as elapsed_time
-- , v.address
-- , v.hash_value
, v.sql_id
, substr (v.sql_text,1,70) as sql_text
-- , a.name as action_type
from v$sqlarea v, dba_users u, audit_actions a
where v.parsing_schema_id = u.user_id
and v.command_type = a.action
-- and u.username not in ('SYS','SYSTEM')
and v.buffer_gets >= ( select min (v2.buffer_gets) from ( select v3.buffer_gets from v$sqlarea v3 order by buffer_gets desc ) v2 where rownum < 20 )
order by v.disk_reads desc
/
Top Sql from ASH with Object
col PCT_IO_OBJ for a25
col aud_action for a11
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
group by sql_id,sql_plan_hash_value,aud.name
order by total desc )
where rownum < 10
Top Sql from ASH (without objects)
col type for a10
col "CPU" for 999999
col "IO" for 999999
select * from (
select
ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type,
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,
audit_actions aud
where SQL_ID is not NULL
-- and ash.dbid=&DBID
and ash.sql_opcode=aud.action
-- and ash.sample_time > sysdate - &minutes /( 60*24)
group by sql_id, SQL_PLAN_HASH_VALUE , aud.name
order by sum(decode(session_state,'ON CPU',1,1)) desc
) where rownum < 10
/
Elapsed Time Changing per Execution :
DEF days_of_history_accessed = '31';
DEF captured_at_least_x_times = '10';
DEF captured_at_least_x_days_apart = '5';
DEF med_elap_microsecs_threshold = '1e4';
DEF min_slope_threshold = '0.1';
DEF max_num_rows = '20';
SET lin 200 ver OFF;
COL row_n FOR A2 HEA '#';
COL med_secs_per_exec HEA 'Median Secs|Per Exec';
COL std_secs_per_exec HEA 'Std Dev Secs|Per Exec';
COL avg_secs_per_exec HEA 'Avg Secs|Per Exec';
COL min_secs_per_exec HEA 'Min Secs|Per Exec';
COL max_secs_per_exec HEA 'Max Secs|Per Exec';
COL plans FOR 9999;
COL sql_text_80 FOR A80;
PRO SQL Statements with "Elapsed Time per Execution" changing over time
WITH
per_time AS (
SELECT h.dbid,
h.sql_id,
SYSDATE - CAST(s.end_interval_time AS DATE) days_ago,
SUM(h.elapsed_time_total) / SUM(h.executions_total) time_per_exec
FROM dba_hist_sqlstat h,
dba_hist_snapshot s
WHERE h.executions_total > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
AND CAST(s.end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed.
GROUP BY
h.dbid,
h.sql_id,
SYSDATE - CAST(s.end_interval_time AS DATE)
),
avg_time AS (
SELECT dbid,
sql_id,
MEDIAN(time_per_exec) med_time_per_exec,
STDDEV(time_per_exec) std_time_per_exec,
AVG(time_per_exec) avg_time_per_exec,
MIN(time_per_exec) min_time_per_exec,
MAX(time_per_exec) max_time_per_exec
FROM per_time
GROUP BY
dbid,
sql_id
HAVING COUNT(*) >= &&captured_at_least_x_times.
AND MAX(days_ago) - MIN(days_ago) >= &&captured_at_least_x_days_apart.
AND MEDIAN(time_per_exec) > &&med_elap_microsecs_threshold.
),
time_over_median AS (
SELECT h.dbid,
h.sql_id,
h.days_ago,
(h.time_per_exec / a.med_time_per_exec) time_per_exec_over_med,
a.med_time_per_exec,
a.std_time_per_exec,
a.avg_time_per_exec,
a.min_time_per_exec,
a.max_time_per_exec
FROM per_time h, avg_time a
WHERE a.sql_id = h.sql_id
),
ranked AS (
SELECT RANK () OVER (ORDER BY ABS(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago)) DESC) rank_num,
t.dbid,
t.sql_id,
CASE WHEN REGR_SLOPE(t.time_per_exec_over_med, t.days_ago) > 0 THEN 'IMPROVING' ELSE 'REGRESSING' END change,
ROUND(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago), 3) slope,
ROUND(AVG(t.med_time_per_exec)/1e6, 3) med_secs_per_exec,
ROUND(AVG(t.std_time_per_exec)/1e6, 3) std_secs_per_exec,
ROUND(AVG(t.avg_time_per_exec)/1e6, 3) avg_secs_per_exec,
ROUND(MIN(t.min_time_per_exec)/1e6, 3) min_secs_per_exec,
ROUND(MAX(t.max_time_per_exec)/1e6, 3) max_secs_per_exec
FROM time_over_median t
GROUP BY
t.dbid,
t.sql_id
HAVING ABS(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago)) > &&min_slope_threshold.
)
SELECT LPAD(ROWNUM, 2) row_n,
r.sql_id,
r.change,
TO_CHAR(r.slope, '990.000MI') slope,
TO_CHAR(r.med_secs_per_exec, '999,990.000') med_secs_per_exec,
TO_CHAR(r.std_secs_per_exec, '999,990.000') std_secs_per_exec,
TO_CHAR(r.avg_secs_per_exec, '999,990.000') avg_secs_per_exec,
TO_CHAR(r.min_secs_per_exec, '999,990.000') min_secs_per_exec,
TO_CHAR(r.max_secs_per_exec, '999,990.000') max_secs_per_exec,
(SELECT COUNT(DISTINCT p.plan_hash_value) FROM dba_hist_sql_plan p WHERE p.dbid = r.dbid AND p.sql_id = r.sql_id) plans,
REPLACE((SELECT DBMS_LOB.SUBSTR(s.sql_text, 80) FROM dba_hist_sqltext s WHERE s.dbid = r.dbid AND s.sql_id = r.sql_id), CHR(10)) sql_text_80
FROM ranked r
WHERE r.rank_num <= &&max_num_rows.
ORDER BY r.rank_num
/
COL instance_number FOR 9999 HEA 'Inst';
COL end_time HEA 'End Time';
COL plan_hash_value HEA 'Plan|Hash Value';
COL executions_total FOR 999,999 HEA 'Execs|Total';
COL rows_per_exec HEA 'Rows Per Exec';
COL et_secs_per_exec HEA 'Elap Secs|Per Exec';
COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';
COL io_secs_per_exec HEA 'IO Secs|Per Exec';
COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';
COL ap_secs_per_exec HEA 'App Secs|Per Exec';
COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';
COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';
COL ja_secs_per_exec HEA 'Java Secs|Per Exec';
SELECT h.instance_number,
TO_CHAR(CAST(s.end_interval_time AS DATE), 'YYYY-MM-DD HH24:MI') end_time,
h.plan_hash_value,
h.executions_total,
TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
FROM dba_hist_sqlstat h,
dba_hist_snapshot s
WHERE h.sql_id = '&sql_id.'
AND h.executions_total > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
ORDER BY
h.sql_id,
h.instance_number,
s.end_interval_time,
h.plan_hash_value
/
SQL with multiple Execution Plans :
WITH
per_phv AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.dbid,
h.sql_id,
h.plan_hash_value,
MIN(s.begin_interval_time) min_time,
MAX(s.end_interval_time) max_time,
MEDIAN(h.elapsed_time_total / h.executions_total) med_time_per_exec,
STDDEV(h.elapsed_time_total / h.executions_total) std_time_per_exec,
AVG(h.elapsed_time_total / h.executions_total) avg_time_per_exec,
MIN(h.elapsed_time_total / h.executions_total) min_time_per_exec,
MAX(h.elapsed_time_total / h.executions_total) max_time_per_exec,
STDDEV(h.elapsed_time_total / h.executions_total) / AVG(h.elapsed_time_total / h.executions_total) std_dev,
MAX(h.executions_total) executions_total,
MEDIAN(h.elapsed_time_total / h.executions_total) * MAX(h.executions_total) total_elapsed_time
FROM dba_hist_sqlstat h,
dba_hist_snapshot s
WHERE h.snap_id BETWEEN 0 AND 2183
AND h.dbid = 3867210122
AND h.executions_total > 1
AND h.plan_hash_value > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
AND CAST(s.end_interval_time AS DATE) > SYSDATE - 31
GROUP BY
h.dbid,
h.sql_id,
h.plan_hash_value
),
ranked1 AS (
SELECT /*+ MATERIALIZE NO_MERGE */
RANK () OVER (ORDER BY STDDEV(med_time_per_exec)/AVG(med_time_per_exec) DESC) rank_num1,
dbid,
sql_id,
COUNT(*) plans,
SUM(total_elapsed_time) total_elapsed_time,
MIN(med_time_per_exec) min_med_time_per_exec,
MAX(med_time_per_exec) max_med_time_per_exec
FROM per_phv
GROUP BY
dbid,
sql_id
HAVING COUNT(*) > 1
),
ranked2 AS (
SELECT /*+ MATERIALIZE NO_MERGE */
RANK () OVER (ORDER BY r.total_elapsed_time DESC) rank_num2,
r.rank_num1,
r.sql_id,
r.plans,
p.plan_hash_value,
TO_CHAR(CAST(p.min_time AS DATE), 'YYYY-MM-DD/HH24') min_time,
TO_CHAR(CAST(p.max_time AS DATE), 'YYYY-MM-DD/HH24') max_time,
ROUND(p.med_time_per_exec / 1e6, 3) med_secs_per_exec,
p.executions_total executions,
ROUND(p.med_time_per_exec * p.executions_total / 1e6, 3) aprox_tot_secs,
ROUND(p.std_time_per_exec / 1e6, 3) std_secs_per_exec,
ROUND(p.avg_time_per_exec / 1e6, 3) avg_secs_per_exec,
ROUND(p.min_time_per_exec / 1e6, 3) min_secs_per_exec,
ROUND(p.max_time_per_exec / 1e6, 3) max_secs_per_exec,
REPLACE((SELECT DBMS_LOB.SUBSTR(s.sql_text, 4000) FROM dba_hist_sqltext s WHERE s.dbid = r.dbid AND s.sql_id = r.sql_id), CHR(10)) sql_text
FROM ranked1 r,
per_phv p
WHERE r.rank_num1 <= 20 * 5
AND p.dbid = r.dbid
AND p.sql_id = r.sql_id
)
SELECT /*+ NO_MERGE */
r.sql_id,
r.plans,
r.plan_hash_value,
r.min_time,
r.max_time,
r.med_secs_per_exec,
r.executions,
r.aprox_tot_secs,
r.std_secs_per_exec,
r.avg_secs_per_exec,
r.min_secs_per_exec,
r.max_secs_per_exec,
r.sql_text
FROM ranked2 r
WHERE rank_num2 <= 20
ORDER BY
r.rank_num2,
r.sql_id,
r.min_time,
r.plan_hash_value;
Hot object used between snap1 and snap2 :
select User_Name
,Object_Name
,Hot_Row_ID
,Cnt
from
(
select u.Username as User_Name
,a.Object_Name as Object_Name
,dbms_rowid.rowid_create(1, d.Current_Obj#
,d.Current_File#
,d.Current_Block#
,d.Current_Row#) as Hot_Row_ID
,count(*) as Cnt
from dba_hist_active_sess_history d
,dba_users u
,dba_objects a
,v$Event_Name e
where d.snap_id between 32002 and 32005
and d.Event_ID = e.Event_ID
and d.User_id = u.User_ID
and u.Username != 'SYS'
and e.Name not like 'SQL*Net%'
and d.Wait_Class like 'User I/O'
and d.Current_Obj# = a.Object_ID
-- and a.Object_Type = 'TABLE'
group by u.Username
,a.Object_Name
,dbms_rowid.rowid_create(1, d.Current_Obj#
,d.Current_File#
,d.Current_Block#
,d.Current_Row#)
order by 4 desc, 2
snapper.sql : http://files.e2sn.com/scripts/snapper.sql
