forked from abdulirfan3/Oracle_SQL_Scripts
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathasyncio.sql
More file actions
55 lines (55 loc) · 1.7 KB
/
asyncio.sql
File metadata and controls
55 lines (55 loc) · 1.7 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
set linesize 999
set pagesize 999
set numwidth 14
set numformat 999G999G999G990
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
column inst format a4
with subq as
(select 'ASYNC',
to_char(inst_id) inst,
substr(filename,1,60),
open_time,
close_time,
elapsed_time/100,
substr(device_type,1,10) devtype,
set_count,
set_stamp,
maxopenfiles agg,
buffer_size,
buffer_count,
buffer_size*buffer_count buffer_mem,
io_count,
total_bytes,
bytes,
decode(nvl(close_time,sysdate),
open_time,
null,
io_count*buffer_size/((nvl(close_time,sysdate)-open_time)*86400))*1 rate,
effective_bytes_per_second eff
from gv$backup_async_io where type<>'AGGREGATE'
union all
select 'SYNC',
to_char(inst_id),
substr(filename,1,60),
open_time,
close_time,
elapsed_time/100,
substr(device_type,1,10) devtype,
set_count,
set_stamp,
maxopenfiles agg,
buffer_size,
buffer_count,
buffer_size*buffer_count buffer_mem,
io_count,
total_bytes,
bytes,
decode(nvl(close_time,sysdate),
open_time,
null,io_count*buffer_size/((nvl(close_time,sysdate)-open_time)*86400))*1 rate,
effective_bytes_per_second eff
from gv$backup_sync_io where type<>'AGGREGATE')
select subq.*,
io_count*buffer_size/((nvl(close_time,sysdate)-open_time)*86400+agg)*1 rate_with_create,
decode(buffer_mem,0,null,rate/buffer_mem)*1000 efficiency
from subq order by open_time;