-
Notifications
You must be signed in to change notification settings - Fork 13
Expand file tree
/
Copy pathdba_hist_sqlstat.sql
More file actions
82 lines (80 loc) · 5.21 KB
/
Copy pathdba_hist_sqlstat.sql
File metadata and controls
82 lines (80 loc) · 5.21 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
--
-- short SQL execution history
-- SQL> @dba_hist_sqlstat "sql_id = '8d49sjc17xwuc' and snap_id between 86116 and 86260 and (elapsed_time_delta > 0 and executions_delta is not null)" PLAN
-- ^order by plan_hash_value first!
col ELA_PER_EXEC for 999,999,999,999
col CPU_PER_EXEC for 999,999,999,999
col GETS_PER_EXEC for 999,999,999,999
col IOWAITS_PER_EXEC for 999,999,999,999
col CLWAITS_PER_EXEC_uS for 999,999,999,999
col APWAITS_PER_EXEC for 999,999,999,999
col CCWAITS_PER_EXEC for 999,999,999,999
select
instance_number as inst,
(snap_id) as Snap_id,
to_char(sn.begin_interval_time,'dd.mm hh24:mi') as begin_snap_time,
round(st.executions_delta) as execs,
-- round(st.executions_delta * (st.rows_processed_delta/decode(st.executions_delta,0,1,st.executions_delta))) as rows_processed,
st.rows_processed_delta as rows_processed,
st.sql_id,
st.plan_hash_value as plan,
st.SQL_PROFILE,
st.optimizer_cost as cost,
module, action,
round(st.parse_calls_delta/decode(st.executions_delta,0,1,st.executions_delta)) as PARSE_PER_EXEC,
round(st.elapsed_time_delta/decode(st.executions_delta,0,1,st.executions_delta)) as ELA_PER_EXEC,
round(st.elapsed_time_delta/decode(st.rows_processed_delta,0,1,rows_processed_delta)) as ELA_PER_ROW,
round(st.cpu_time_delta/decode(st.executions_delta,0,1,st.executions_delta)) as CPU_PER_EXEC,
round(st.buffer_gets_delta/decode(st.executions_delta,0,1,st.executions_delta)) as GETS_PER_EXEC,
round(st.disk_reads_delta/decode(st.executions_delta,0,1,st.executions_delta)) as disk_reads_per_exec,
round(st.physical_read_bytes_delta/decode(st.executions_delta,0,1,st.executions_delta)/1024/1024) as READ_MB_PER_EXEC,
round(st.physical_read_requests_delta/decode(st.executions_delta,0,1,st.executions_delta)) as READS_PER_EXEC,
round(st.physical_write_bytes_delta/decode(st.executions_delta,0,1,st.executions_delta)/1024/1024) as WRITES_MB_PER_EXEC,
round(st.physical_write_requests_delta/decode(st.executions_delta,0,1,st.executions_delta)) as WRITES_PER_EXEC,
round(st.direct_writes_delta/decode(st.executions_delta,0,1,st.executions_delta)) as DIRECT_WRITES_PER_EXEC,
round(st.rows_processed_delta/decode(st.executions_delta,0,1,st.executions_delta)) as ROWS_PER_EXEC,
round(st.fetches_delta/decode(st.executions_delta,0,1,st.executions_delta)) as FETCHES_PER_EXEC,
round(st.iowait_delta/decode(st.executions_delta,0,1,st.executions_delta)) as IOWAITS_PER_EXEC,
round(st.clwait_delta/decode(st.executions_delta,0,1,st.executions_delta)) as CLWAITS_PER_EXEC_uS,
round(st.apwait_delta/decode(st.executions_delta,0,1,st.executions_delta)) as APWAITS_PER_EXEC,
round(st.ccwait_delta/decode(st.executions_delta,0,1,st.executions_delta)) as CCWAITS_PER_EXEC,
round(st.parse_calls_delta/decode(st.executions_delta,0,1,st.executions_delta)) as PARSE_PER_EXEC,
round(st.plsexec_time_delta/decode(st.executions_delta,0,1,st.executions_delta)) as PLSQL_PER_EXEC,
round(st.px_servers_execs_delta/decode(st.executions_delta,0,1,st.executions_delta)) as PX_PER_EXEC,
round(st.clwait_delta/1000000) as clwaits_sec
from dba_hist_sqlstat st join dba_hist_snapshot sn using(snap_id,instance_number)
where --sql_id = '
&&1
-- and snap_id between &&2 and nvl('&&3', &&2)
-- and executions_delta > 0
--and (st.elapsed_time_delta > 0 and st.executions_delta is not null)
order by --decode(upper('&2'),'PLAN',plan_hash_value,0),
-- snap_id,
snap_id, instance_number, sql_id
/*
select to_char(sql_exec_start,'dd.mm.yyyy hh24:mi:ss') as SQL_EXEC_START,
max(sample_time) - SQL_EXEC_START as duration,
sql_exec_id,
sql_plan_hash_value,
module,
action,
round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
round(max(pga_allocated) / 1024 / 1024 / 1024, 3) as max_pga_gb,
max(px_used) as max_px_used
from (select sql_exec_start, sql_exec_id, sql_plan_hash_value, module, action, sample_id,
sum(temp_space_allocated) as temp_space_allocated,
sum(pga_allocated) as pga_allocated,
count(distinct session_serial#) - 1 as px_used,
sample_time
from dba_hist_active_sess_history
where sql_id = '&&1'
and snap_id between &2 and nvl('&&3', &&2)
and sql_exec_id > 0
group by sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, sample_time
having sum(temp_space_allocated) is not null)
group by SQL_EXEC_START, sql_exec_id, sql_plan_hash_value, module, action
--having max(temp_space_allocated) / 1024 / 1024 / 1024 > 2 -- GB
order by 1
*/
/
--@@dba_hist_sqlstat2