-
Notifications
You must be signed in to change notification settings - Fork 10
/
Copy pathash_queries_wip.sql
101 lines (86 loc) · 3.29 KB
/
ash_queries_wip.sql
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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
set linesize 200
set pages 50000
column tm_delta_time format 99G999G999G999 heading "tm time|delta (µs)"
column tm_delta_db_time format 99G999G999G999 heading "tm db|delta (µs)"
column tm_delta_cpu_time format 99G999G999G999 heading "tm cpu|delta (µs)"
column time_waited format 99G999G999G999 heading "waited|(µs)"
column delta_time format 99G999G999G999 heading "delta time|(µs)"
column first_time format a15 heading "first sample"
column last_time format a15 heading "last sample"
select
sum(ash.tm_delta_time) tm_delta_time,
sum(ash.tm_delta_db_time) tm_delta_db_time,
sum(ash.tm_delta_cpu_time) tm_delta_cpu_time,
sum(ash.time_waited) time_waited,
sum(ash.delta_time) delta_time,
to_char(min(ash.sample_time), 'DD/MM HH24:MI:SS') first_time,
to_char(max(ash.sample_time), 'DD/MM HH24:MI:SS') last_time
from
gv$active_session_history ash
where
ash.program = 'batch.exe'
and ash.sample_time between
to_date('22/11/2012 10:38', 'DD/MM/YYYY HH24:MI')
and to_date('22/11/2012 18:20', 'DD/MM/YYYY HH24:MI')
;
set linesize 200
set pages 50000
column sql_id format a15 heading "sql id"
column tm_delta_time format 99G999G999G999 heading "tm time|delta (µs)"
column tm_delta_db_time format 99G999G999G999 heading "tm db|delta (µs)"
column tm_delta_cpu_time format 99G999G999G999 heading "tm cpu|delta (µs)"
column time_waited format 99G999G999G999 heading "waited|(µs)"
column delta_time format 99G999G999G999 heading "delta time|(µs)"
select
sql_id,
sum(ash.tm_delta_time) tm_delta_time,
sum(ash.tm_delta_db_time) tm_delta_db_time,
sum(ash.tm_delta_cpu_time) tm_delta_cpu_time,
sum(ash.time_waited) time_waited,
sum(ash.delta_time) delta_time
from
gv$active_session_history ash
where
ash.program = 'batch.exe'
and ash.sample_time between
to_date('22/11/2012 18:20', 'DD/MM/YYYY HH24:MI')
and to_date('23/11/2012 02:16', 'DD/MM/YYYY HH24:MI')
group by
sql_id
order by
tm_delta_db_time desc
;
set linesize 200
set pages 50000
column sample_id format 99999999 heading "sample"
column sample_time format a30 heading "sample time"
column session_id format 9999999 heading "session"
column session_serial# format 9999999 heading "serial#"
column sql_id format a15 heading "sql id"
column tm_delta_time format 99G999G999G999 heading "tm time|delta (µs)"
column tm_delta_db_time format 99G999G999G999 heading "tm db|delta (µs)"
column tm_delta_cpu_time format 99G999G999G999 heading "tm cpu|delta (µs)"
column time_waited format 99G999G999G999 heading "waited|(µs)"
column delta_time format 99G999G999G999 heading "delta time|(µs)"
select
ash.sample_id,
ash.sample_time,
ash.session_id,
ash.session_serial#,
ash.sql_id,
ash.tm_delta_time,
ash.tm_delta_db_time,
ash.tm_delta_cpu_time,
ash.time_waited,
ash.delta_time
from
gv$active_session_history ash
where
ash.program = 'batch.exe'
and ash.sample_time > to_date('21/11/2012 22:00', 'DD/MM/YYYY HH24:MI')
and ash.sql_id = 'c6v8hz7wg8mym'
order by
ash.sample_id,
ash.session_id,
ash.session_serial#
;