了解Maclean Liu|向Maclean Liu提问 Oracle ALLSTARS 全明星(群内有多位Oracle高级售后support，N位OCM和ACE) QQ群 # QQ群号:23549328 # 已经升级到 2000人群，空位多多。欢迎有一定基础的Oracle骨友加入，现在入群需要经过Maclean的技术面试，欢迎面试，请加QQ号:47079569 为好友参加面试 2群基础群 适合刚入门的同学，会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群，空位多多，无需面试

11g中AWR新快照视图

DBA_HIST_IOSTAT_DETAIL视图记录了不同类型和组件功能所作IO的统计数据。这个视图的数据来自于V$IOSTAT_FILE和V$IOSTAT_FUNCTION 2个动态视图的快照。值得一提的是V$IOSTAT_FILE，它是11g中新引入的动态性能视图:
?
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等多种情况，有待验证。
我们回过头来继续讨论DBA_HIST_IOSTAT_DETAIL视图!
?
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
其中SMALL_READ/WRITE代表单块读写，LARGE_READ/WRITE代表多块读写，wait_time的单位是千分之一秒(ms)。
?
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种操作*/
另外，你大概和我一样不太喜欢使用OEM界面，不过OEM界面有个很方便的功能就是显示短期内的Avg Active Session Count(AAS)。什么？那你从来没看到过这种东西？下面这张截图大概可以勾起你的回忆: 早在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;
我们也可以通过ASH查找Oracle中的TOP SESSION和TOP 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
/*找出短期内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报表所替代，但在短期内做针对检查仍十分有效。

posted on   Oracle和MySQL  阅读(444)  评论(0)  编辑  收藏  举报

导航

< 2010年8月 >
日 一 二 三 四 五 六
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 1 2 3 4
5 6 7 8 9 10 11
点击右上角即可分享
微信分享提示