DBA_HIST_IOSTAT_DETAIL视图记录了不同类型和组件功能所作IO的统计数据。这个视图的数据来自于V$IOSTAT_FILE和V$IOSTAT_FUNCTION 2个动态视图的快照。值得一提的是V$IOSTAT_FILE，它是11g中新引入的动态性能视图:
我们回过头来继续讨论DBA_HIST_IOSTAT_DETAIL视图!
其中SMALL_READ/WRITE代表单块读写，LARGE_READ/WRITE代表多块读写，wait_time的单位是千分之一秒(ms)。
另外，你大概和我一样不太喜欢使用OEM界面，不过OEM界面有个很方便的功能就是显示短期内的Avg Active Session Count(AAS)。什么？那你从来没看到过这种东西？下面这张截图大概可以勾起你的回忆:
早在10g时代就有人写了一个显示短期内AAS的脚本，在11g中该脚本得到了简化了:
我们也可以通过ASH查找Oracle中的TOP SESSION和TOP SQL:
以上脚本完全可以被ashrpt报表所替代，但在短期内做针对检查仍十分有效。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
select
filetype_name, asynch_io, access_method, retries_on_error
2
from
v$iostat_file;
FILETYPE_NAME ASYNCH_IO ACCESS_METH RETRIES_ON_ERROR
---------------------------- --------- ----------- ----------------
Other ASYNC_OFF OS_LIB 0
Control File ASYNC_OFF 0
Log File ASYNC_OFF 0
Archive Log ASYNC_OFF 0
Data File Backup ASYNC_OFF 0
Data File Incremental Backup ASYNC_OFF 0
Archive Log Backup ASYNC_OFF 0
Data File Copy ASYNC_OFF 0
Flashback Log ASYNC_OFF 0
Data Pump Dump File ASYNC_OFF 0
Data File ASYNC_ON OS_LIB 0
/*ASYNCH_IO列很好地标示了Oracle对于该类型文件是否启用了异步IO,这样我们就无需通过SYSTEM CALL TRACE来确定这一点了;
retries_on_error累计了物理读取的失败次数，对我们发现磁盘坏道有一定帮助。目前的11.2.0.1官方Reference没有ACCESS_METHOD列的资料，猜想可能分为OS_LIB和ASM及Exdata等多种情况，有待验证。
|
|
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
|
SQL>
desc
dba_hist_iostat_detail;
Name
Type Nullable
Default
Comments
--------------------- ------------ -------- ------- --------
SNAP_ID NUMBER
DBID NUMBER
INSTANCE_NUMBER NUMBER
FUNCTION_ID NUMBER
FUNCTION_NAME VARCHAR2(30)
FILETYPE_ID NUMBER
FILETYPE_NAME VARCHAR2(30)
SMALL_READ_MEGABYTES NUMBER
SMALL_WRITE_MEGABYTES NUMBER
LARGE_READ_MEGABYTES NUMBER
LARGE_WRITE_MEGABYTES NUMBER
SMALL_READ_REQS NUMBER
SMALL_WRITE_REQS NUMBER
LARGE_READ_REQS NUMBER
LARGE_WRITE_REQS NUMBER
NUMBER_OF_WAITS NUMBER
WAIT_TIME NUMBER
SQL> col SMALL_READ_MEGABYTES
for
999999;
SQL> col SMALL_WRITE_MEGABYTES
for
999999;
SQL> col LARGE_READ_MEGABYTES
for
999999;
SQL> col LARGE_WRITE_MEGABYTES
for
999999;
SQL> col FILETYPE_NAME
for
a25;
SQL>
select
snap_id,
2 function_name,
3 filetype_name,
4 SMALL_READ_MEGABYTES,
5 SMALL_WRITE_MEGABYTES,
6 LARGE_READ_MEGABYTES,
7 LARGE_WRITE_MEGABYTES,
8 WAIT_TIME
9
from
dba_hist_iostat_detail
10
where
rownum < 16;
SNAP_ID FUNCTION_NAME FILETYPE_NAME SMALL_ SMALL_ LARGE_ LARGE_ WAIT_TIME
---------- ------------------------------ ------------------------- ------ ------ ------ ------ ----------
1 DBWR Control File 1 0 0 0 310
1 LGWR Control File 0 0 0 0 94
1 Others Control File 122 37 0 0 71635
2 DBWR Control File 1 0 0 0 310
2 LGWR Control File 0 0 0 0 94
2 Others Control File 251 77 0 0 159025
3 Others Control File 14 7 0 0 10339
4 Others Control File 96 60 0 0 87516
5 Others Control File 151 96 0 0 139796
6 DBWR Control File 0 0 0 0 15
6 Others Control File 210 135 0 0 189114
7 DBWR Control File 0 0 0 0 15
7 Others Control File 269 174 0 0 239640
8 DBWR Control File 0 0 0 0 15
8 Others Control File 328 213 0 0 288425
15
rows
selected
|
|
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
|
SQL>
select
snap_id,
2 function_name,
3 filetype_name,
4 SMALL_READ_MEGABYTES,
5 SMALL_WRITE_MEGABYTES,
6 LARGE_READ_MEGABYTES,
7 LARGE_WRITE_MEGABYTES,
8 WAIT_TIME
9
from
dba_hist_iostat_detail
10
where
filetype_name =
'Control File'
11
and
(LARGE_READ_REQS > 0
or
LARGE_WRITE_REQS > 0);
未选定行
/*Oracle对控制文件只做单块读写*/
SQL> col FILETYPE_NAME
for
a15;
SQL> col function_name
for
a10;
SQL>
select
function_name,
2 filetype_name,
3 small_read_reqs,
4 small_write_reqs,
5 large_read_reqs,
6 large_write_reqs
7
from
dba_hist_iostat_detail
8
where
filetype_name =
'Log File'
9
and
(SMALL_READ_REQS > 0
or
LARGE_READ_REQS > 0);
FUNCTION_N FILETYPE_NAME SMALL_READ_REQS SMALL_WRITE_REQS LARGE_READ_REQS LARGE_WRITE_REQS
---------- --------------- --------------- ---------------- --------------- ----------------
LGWR Log File 4 9140 0 86
LGWR Log File 4 2762 0 218
LGWR Log File 8 3512 0 222
LGWR Log File 8 4304 0 226
LGWR Log File 4 1996 0 210
LGWR Log File 8 5296 0 252
LGWR Log File 8 6016 0 254
LGWR Log File 8 7224 0 274
LGWR Log File 4 11536 0 232
LGWR Log File 8 13320 0 256
SQL>
select
snap_id,
2 function_name,
3 filetype_name,
4 SMALL_READ_MEGABYTES,
5 SMALL_WRITE_MEGABYTES,
6 LARGE_READ_MEGABYTES,
7 LARGE_WRITE_MEGABYTES,
8 WAIT_TIME
9
from
dba_hist_iostat_detail
10
where
filetype_name =
'Log File'
;
SNAP_ID FUNCTION_NAME FILETYPE_NAME SMALL_ SMALL_ LARGE_ LARGE_ WAIT_TIME
---------- ------------------------------ ------------------------- ------ ------ ------ ------ ----------
2 LGWR Log File 0 60 0 27 30
35 LGWR Log File 0 23 0 48 0
6 LGWR Log File 0 12 0 8 0
7 LGWR Log File 0 15 0 10 0
8 LGWR Log File 0 18 0 12 0
13 LGWR Log File 0 5 0 4 0
15 LGWR Log File 0 6 0 2 0
17 LGWR Log File 0 4 0 3 0
19 LGWR Log File 0 11 0 7 0
20 LGWR Log File 0 14 0 8 0
37 LGWR Log File 0 13 0 27 0
38 LGWR Log File 0 15 0 29 0
41 LGWR Log File 0 13 0 152 46
42 LGWR Log File 0 16 0 153 61
43 LGWR Log File 0 19 0 155 61
49 LGWR Log File 0 2 0 0 0
3 LGWR Log File 0 1 0 0 0
4 LGWR Log File 0 7 0 4 0
5 LGWR Log File 0 9 0 6 0
12 LGWR Log File 0 1 0 0 0
/*日志文件在写出时会伴有少量的日志单块读操作，日志文件的写兼有单块写和多块写2种操作*/
|
早在10g时代就有人写了一个显示短期内AAS的脚本，在11g中该脚本得到了简化了:
|
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
|
set
echo
off
;
set
verify
off
;
alter
session
set
nls_date_format=
'HH24:MI'
;
select
*
from
(
select
nvl(wait_class,
'CPU'
) activity,
trunc(sample_time,
'MI'
)
time
from
v$active_session_history) v pivot(
count
(*)
for
activity
in
(
'CPU'
as
"CPU"
,
'Concurrency'
as
"Concurrency"
,
'System I/O'
as
"System I/O"
,
'User I/O'
as
"User I/O"
,
'Administrative'
as
"Administrative"
,
'Configuration'
as
"Configuration"
,
'Application'
as
"Application"
,
'Network'
as
"Network"
,
'Commit'
as
"Commit"
,
'Scheduler'
as
"Scheduler"
,
'Cluster'
as
"Cluster"
,
'Queueing'
as
"Queueing"
,
'Other'
as
"Other"
))
where
time
> sysdate - interval
'&last_min'
minute
order
by
time
;
|
|
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
|
/*找出短期内
TOP
SQL的sql_id和活动历史*/
select
ash.SQL_ID,
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(en.wait_class,
'User I/O'
, 1, 0),
0))
"WAIT"
,
sum
(decode(ash.session_state,
'WAITING'
,
decode(en.wait_class,
'User I/O'
, 1, 0),
0))
"IO"
,
sum
(decode(ash.session_state,
'ON CPU'
, 1, 1))
"TOTAL"
from
v$active_session_history ash, v$event_name en
where
SQL_ID
is
not
NULL
and
en.event# = ash.event#
group
by
sql_id
order
by
sum
(decode(session_state,
'ON CPU'
, 1, 1))
desc
;
SQL_ID CPU WAIT IO TOTAL
------------- ---------- ---------- ---------- ----------
a01hp0psv0rrh 0 2 7 9
24g90qj2b7ywk 0 5 1 6
2amsp6skc6tjv 0 0 5 5
46quk68k7akpa 0 3 1 4
2ufrf9vk4kcwj 0 0 3 3
1w8m6dwy66ttn 0 0 3 3
8uxr3scz9bmxd 0 0 3 3
6htq3p9j91y0s 0 0 3 3
cvn54b7yz0s8u 0 0 3 3
92f47aa2q2rmd 0 2 1 3
/*找出变量ivl指定分钟内的
TOP
CPU SESSION*/
Select
session_id,
count
(*)
from
v$active_session_history
where
session_state =
'ON CPU'
and
SAMPLE_TIME > sysdate -(&ivl/(24 * 60))
group
by
session_id
order
by
count
(*)
desc
;
输入 ivl 的值: 10
原值 4:
and
SAMPLE_TIME > sysdate -(&ivl/(24 * 60))
新值 4:
and
SAMPLE_TIME > sysdate -(10/(24 * 60))
SESSION_ID
COUNT
(*)
---------- ----------
136 4
/*找出变量ivl指定分钟内
TOP
WAITING SESSION*/
Select
session_id,
count
(*)
from
v$active_session_history
where
session_state =
'WAITING'
and
SAMPLE_TIME > SYSDATE - (&ivl / (24 * 60))
group
by
session_id
order
by
count
(*)
desc
;
输入 ivl 的值: 10
原值 4:
and
SAMPLE_TIME > SYSDATE - (&ivl / (24 * 60))
新值 4:
and
SAMPLE_TIME > SYSDATE - (10 / (24 * 60))
SESSION_ID
COUNT
(*)
---------- ----------
3 11
/*找出短期内的
TOP
SESSION及活动历史*/
select
ash.session_id,
ash.session_serial#,
ash.user_id,
ash.program,
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(en.wait_class,
'User I/O'
, 1, 0),
0))
"WAITING"
,
sum
(decode(ash.session_state,
'WAITING'
,
decode(en.wait_class,
'User I/O'
, 1, 0),
0))
"IO"
,
sum
(decode(session_state,
'ON CPU'
, 1, 1))
"TOTAL"
from
v$active_session_history ash, v$event_name en
where
en.event# = ash.event#
group
by
session_id, user_id, session_serial#, program
order
by
sum
(decode(session_state,
'ON CPU'
, 1, 1));
|
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277524
1281
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
