11g中AWR新快照视图

DBA_HIST_IOSTAT_DETAIL视图记录了不同类型和组件功能所作IO的统计数据。这个视图的数据来自于V$IOSTAT_FILE和V$IOSTAT_FUNCTION 2个动态视图的快照。值得一提的是V$IOSTAT_FILE,它是11g中新引入的动态性能视图:

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等多种情况,有待验证。

我们回过头来继续讨论DBA_HIST_IOSTAT_DETAIL视图!

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

其中SMALL_READ/WRITE代表单块读写,LARGE_READ/WRITE代表多块读写,wait_time的单位是千分之一秒(ms)。

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种操作*/

另外,你大概和我一样不太喜欢使用OEM界面,不过OEM界面有个很方便的功能就是显示短期内的Avg Active Session Count(AAS)。什么?那你从来没看到过这种东西?下面这张截图大概可以勾起你的回忆:


早在10g时代就有人写了一个显示短期内AAS的脚本,在11g中该脚本得到了简化了:

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;

我们也可以通过ASH查找Oracle中的TOP SESSION和TOP SQL:

/*找出短期内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));

以上脚本完全可以被ashrpt报表所替代,但在短期内做针对检查仍十分有效。

Comments

  1. admin says

    SQL> select FILETYPE_NAME,asynch_io ,access_method from v$iostat_file;

    FILETYPE_NAME ASYNCH_IO ACCESS_METH
    —————————- ——— ———–
    Other ASYNC_OFF OS_LIB
    Control File ASYNC_OFF
    Log File ASYNC_OFF
    Archive Log ASYNC_OFF
    Data File Backup ASYNC_OFF
    Data File Incremental Backup ASYNC_OFF
    Archive Log Backup ASYNC_OFF
    Data File Copy ASYNC_OFF
    Flashback Log ASYNC_OFF
    Data Pump Dump File ASYNC_OFF
    Data File ASYNC_ON ASM_MANAGED
    Temp File ASYNC_ON ASM_MANAGED
    Data File ASYNC_ON ASM_MANAGED
    Data File ASYNC_ON ASM_MANAGED
    Data File ASYNC_ON ASM_MANAGED
    Data File ASYNC_ON ASM_MANAGED

  2. access_method,使用vertis的话:ODM_LIB ,也算一种。

Comment

*

沪ICP备14014813号

沪公网安备 31010802001379号