oracle IO性能分析

Running Sun丶

于 2024-08-02 00:50:16 发布

阅读量452

点赞数 4

文章标签： oracle

本文链接：https://blog.csdn.net/weixin_49889731/article/details/140860432

版权

在IO问题发生时间段:
1.可以查'dba_hist_system_event'来看该IO相关等待时间的平均响应时间的变化趋势.
2.直接查'dba_hist_event_histogram'或者'sys.wrh$_event_histogram'来看该IO相关等待事件的等待时间直方图分布.
3.也可以从整体数据库的IO变化量上(dba_hist_sysstat),看出问题时段的总体IO量,比正常时候的IO量到底是否有增加.
4.也可以从某个IO function即某个进程上面(dba_hist_iostat_function),看问题时间段某个进程的IO request次数、IO的量.
比如说:LGWR进程的写request次数、写的量。

说明:
1.如上视图均可关联dba_hist_snapshot
2.所有dba_hist相关视图都是由对应的v$动态性能视图汇聚而来,当时的数据可以查对应的v$视图

=================================================================================================================

==>--例1:
直方图分布可能更加精确,因为查看平均响应时间可能会因为粒度太粗、时间跨度长而被平均了.
正常情况下,应该<8ms的占据了90%甚至更多,> 32ms的应该占比接近为0.
可以看到,昨天smscp异常时候,在16 – 32ms之间的已经占比很多了.

--查等待事件的等待时间直方图分布:
select b.end_interval_time, a.*
from (select snap_id,
decode(wait_time_milli,
1,
'<1ms',
2,
'<2ms',
4,
'<4ms',
8,
'<8ms',
16,
'<16ms',
32,
'<32ms',
64,
'<64ms',
128,
'<128ms',
256,
'<256ms',
512,
'<512ms',
1024,
'<1s',
2048,
'<2s',
4096,
'<4s',
8192,
'>=4s',
wait_time_milli) time_histg,
total_wait,
round(delta_wait_count / total_wait * 100, 2) as per_of_waits
from (select snap_id,
wait_time_milli,
delta_wait_count,
sum(delta_wait_count) over(partition by snap_id order by snap_id) as total_wait
from (select xx.snap_id,
xx.wait_time_milli,
xx.wait_count - yy.wait_count as delta_wait_count
from dba_hist_event_histogram xx,
dba_hist_event_histogram yy
where xx.event_name = 'log file parallel write'
and xx.event_id = yy.event_id
and xx.snap_id = yy.snap_id + 1
and xx.instance_number = yy.instance_number
and xx.wait_time_milli = yy.wait_time_milli
and xx.snap_id in (--20540)))
order by snap_id, wait_time_milli) a,
dba_hist_snapshot b
where a.snap_id = b.snap_id;

==>snop_id:--20536
END_INTERVAL_TIME SNAP_ID TIME_HISTG TOTAL_WAIT PER_OF_WAITS
---------------------------- ------- ---------- ---------- ------------
22-10月-22 03.00.49.801 下午 20536 <1ms 89464 0.71
22-10月-22 03.00.49.801 下午 20536 <2ms 89464 11.32
22-10月-22 03.00.49.801 下午 20536 <4ms 89464 26.39
22-10月-22 03.00.49.801 下午 20536 <8ms 89464 18.28
22-10月-22 03.00.49.801 下午 20536 <16ms 89464 13.16
22-10月-22 03.00.49.801 下午 20536 <32ms 89464 8.21
22-10月-22 03.00.49.801 下午 20536 <64ms 89464 7.56
22-10月-22 03.00.49.801 下午 20536 <128ms 89464 6.06
22-10月-22 03.00.49.801 下午 20536 <256ms 89464 4.72
22-10月-22 03.00.49.801 下午 20536 <512ms 89464 2.72
22-10月-22 03.00.49.801 下午 20536 <1s 89464 0.83
22-10月-22 03.00.49.801 下午 20536 <2s 89464 0.03
22-10月-22 03.00.49.801 下午 20536 <4s 89464 0
22-10月-22 03.00.49.801 下午 20536 >=4s 89464 0

正常情况下是这样的,8ms以上的占比很少:

/
==>snop_id:--20540
END_INTERVAL_TIME SNAP_ID TIME_HISTG TOTAL_WAIT PER_OF_WAITS
---------------------------- ------- ---------- ---------- ------------
22-10月-14 07.00.49.701 下午 20540 <1ms 1091640 38.28
22-10月-14 07.00.49.701 下午 20540 <2ms 1091640 51.09
22-10月-14 07.00.49.701 下午 20540 <4ms 1091640 9.03
22-10月-14 07.00.49.701 下午 20540 <8ms 1091640 1.37
22-10月-14 07.00.49.701 下午 20540 <16ms 1091640 0.17
22-10月-14 07.00.49.701 下午 20540 <32ms 1091640 0.04
22-10月-14 07.00.49.701 下午 20540 <64ms 1091640 0.01
22-10月-14 07.00.49.701 下午 20540 <128ms 1091640 0
22-10月-14 07.00.49.701 下午 20540 <256ms 1091640 0
22-10月-14 07.00.49.701 下午 20540 <512ms 1091640 0
22-10月-14 07.00.49.701 下午 20540 <1s 1091640 0
22-10月-14 07.00.49.701 下午 20540 <2s 1091640 0
22-10月-14 07.00.49.701 下午 20540 <4s 1091640 0
22-10月-14 07.00.49.701 下午 20540 >=4s 1091640 0
=================================================================================================================

==>--例2
可以看到昨天下午的整库的physical read total IO requests并没有明显增加,但是physical read total bytes增加到了3000GB一个小时,其它时候也有这么高的,但是都是在晚上,只有昨天是在下午15点这个生产时间的snapshot,开发有大量direct path read所以对应用产生了较大影响。

++++++++++++++++++++++++++
+++--查整库IO相关指标1:+++
++++++++++++++++++++++++++
--get_stat.sql
with s as
(select sn.instance_number,
sn.snap_id,
sn.end_interval_time stime,
(s.value - lag(s.value, 1)
over(partition by sn.instance_number order by sn.snap_id)) value
--(sn.end_interval_time - lag(sn.end_interval_time, 1) over (order by sn.snap_id))2460*60 elap_time,
--round((s.value - lag(s.value, 1) over (order by sn.snap_id))/((sn.end_interval_time - lag(sn.end_interval_time, 1) over (order by sn.snap_id))2460*60)) value_s
from dba_hist_snapshot sn,dba_hist_sysstat s
where s.snap_id = sn.snap_id
and s.stat_name = '&1'
and s.instance_number = sn.instance_number)
select instance_number,
snap_id,
stime,
value,
rank() over(partition by instance_number order by value desc) ranking,
min(value) over(partition by instance_number) min_phy,
max(value) over(partition by instance_number) max_phy,
round(avg(value) over(partition by instance_number)) avg_phy
from s
where (to_char(stime, 'hh24') between 9 and 12 or
to_char(stime, 'hh24') between 14 and 17)
and to_char(stime, 'D') not in ('1', '7')
and stime between to_date(&2, 'yyyymmdd') and to_date(&3, 'yyyymmdd')
order by instance_number, snap_id
/
==>IM simd decode selective calls
==>20230401
==>20230414

INSTANCE_NUMBER SNAP_ID STIME VALUE RANKING MIN_PHY MAX_PHY AVG_PHY
--------------- ---------- ------------------------------ ------ ---------- ---------- ---------- ----------
1 513 12-APR-23 09.00.26.266 AM 0 1 0 0 0
1 514 12-APR-23 10.00.57.488 AM 0 1 0 0 0
1 515 12-APR-23 11.00.25.908 AM 0 1 0 0 0
1 516 12-APR-23 12.00.57.109 PM 0 1 0 0 0
1 518 12-APR-23 02.00.47.750 PM 0 1 0 0 0
1 519 12-APR-23 03.00.26.191 PM 0 1 0 0 0
1 520 12-APR-23 04.00.51.482 PM 0 1 0 0 0
1 521 12-APR-23 05.00.16.729 PM 0 1 0 0 0
1 536 13-APR-23 09.00.23.522 AM 0 1 0 0 0
1 537 13-APR-23 10.00.04.915 AM 0 1 0 0 0
1 538 13-APR-23 11.00.41.281 AM 0 1 0 0 0

---physical read total IO requests
---physical read total bytes
---physical write total IO requests
---physical write total bytes
---以上四个指标即IOPS(requests)、IO吞吐量(bytes)

=================================================================================================================

++++++++++++++++++++++++++
+++--查整库IO相关指标2:+++
++++++++++++++++++++++++++
select e.snap_id,
to_char(e.startup_time, 'yyyy-mm-dd hh24:mi:ss') instance_startup_time,
to_char(e.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') snapshot_end_time,
b.value - a.value IO_requests,
round((d.value - c.value)/1024/1024/1024) IO_GB
from dba_hist_sysstat a,
dba_hist_sysstat b,
dba_hist_sysstat c,
dba_hist_sysstat d,
dba_hist_snapshot e
where a.stat_name = 'physical read total IO requests'
and b.stat_name = 'physical read total IO requests'
and c.stat_name = 'physical read total bytes'
and d.stat_name = 'physical read total bytes'
and a.snap_id = e.snap_id - 1
and b.snap_id = e.snap_id
and c.snap_id = e.snap_id - 1
and d.snap_id = e.snap_id
and e.end_interval_time
BETWEEN TO_DATE('20230401 09:00:00', 'yyyymmdd hh24:mi:ss')
AND TO_DATE('20230414 18:00:00', 'yyyymmdd hh24:mi:ss')
order by e.begin_interval_time;

SNAP_ID INSTANCE_STARTUP_TI SNAPSHOT_END_TIME IO_REQUESTS IO_GB
---------- ------------------- ------------------- ----------- ----------
507 2023-03-30 00:11:32 2023-04-12 03:00:33 15878 0
508 2023-03-30 00:11:32 2023-04-12 04:00:06 15509 0
509 2023-03-30 00:11:32 2023-04-12 05:00:41 15728 0
510 2023-03-30 00:11:32 2023-04-12 06:00:06 15406 0
511 2023-03-30 00:11:32 2023-04-12 07:00:25 15637 0
512 2023-03-30 00:11:32 2023-04-12 08:00:00 15476 0
513 2023-03-30 00:11:32 2023-04-12 09:00:26 15735 0
514 2023-03-30 00:11:32 2023-04-12 10:00:57 15743 0
515 2023-03-30 00:11:32 2023-04-12 11:00:25 15434 0
516 2023-03-30 00:11:32 2023-04-12 12:00:57 15748 0
517 2023-03-30 00:11:32 2023-04-12 13:00:19 15422 0
518 2023-03-30 00:11:32 2023-04-12 14:00:47 15703 0
519 2023-03-30 00:11:32 2023-04-12 15:00:26 15454 0
520 2023-03-30 00:11:32 2023-04-12 16:00:51 15686 0
521 2023-03-30 00:11:32 2023-04-12 17:00:16 15446 0
522 2023-03-30 00:11:32 2023-04-12 18:00:47 15726 0
523 2023-03-30 00:11:32 2023-04-12 20:03:27 10325 0
524 2023-03-30 00:11:32 2023-04-12 21:00:41 14876 0
525 2023-03-30 00:11:32 2023-04-12 22:00:54 17116 0
526 2023-03-30 00:11:32 2023-04-12 23:00:22 37388 1
527 2023-03-30 00:11:32 2023-04-13 00:00:52 15961 0
528 2023-03-30 00:11:32 2023-04-13 01:00:57 14534 0
.
.
59 rows selected.

=================================================================================================================

==>--例3:

可以看到问题发生时段,10.22 14点-15点,LGWR的large_write_megabytes和requests有明显增加,而平时有如此高峰的是有晚上和凌晨。

--查某个IO进程的读写指标,例如LGWR:
SQL> select * from dba_hist_iostat_function_name;

DBID FUNCTION_ID FUNCTION_NAME CON_DBID CON_ID
---------- ----------- ------------------------------------- ---------- ----------
1827819324 0 RMAN 1827819324 0
1827819324 1 DBWR 1827819324 0
1827819324 2 LGWR 1827819324 0
1827819324 3 ARCH 1827819324 0
1827819324 4 XDB 1827819324 0
1827819324 5 Streams AQ 1827819324 0
1827819324 6 Data Pump 1827819324 0
1827819324 7 Recovery 1827819324 0
1827819324 8 Buffer Cache Reads 1827819324 0
1827819324 9 Direct Reads 1827819324 0
1827819324 10 Direct Writes 1827819324 0

1827819324 11 Smart Scan 1827819324 0
1827819324 12 Archive Manager 1827819324 0
1827819324 13 Others 1827819324 0
1827819324 14 Inmemory Populate 1827819324 0

15 rows selected.

=================================================================================================================

--查某个IO进程:例如LGWR的IO requset和IO bytes dba_hist_iostat_function:
select e.snap_id,
to_char(e.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') snapshot_end_time,
b.small_read_megabytes - a.small_read_megabytes small_read_megabytes,
b.small_write_megabytes - a.small_write_megabytes small_write_megabytes,
b.large_read_megabytes - a.large_read_megabytes large_read_megabytes,
b.large_write_megabytes - a.large_write_megabytes large_write_megabytes,
b.small_read_reqs - a.small_read_reqs small_read_reqs,
b.small_write_reqs - a.small_write_reqs small_write_reqs,
b.large_read_reqs - a.large_read_reqs large_read_reqs,
b.large_write_reqs - a.large_write_reqs large_write_reqs,
b.number_of_waits - a.number_of_waits number_of_waits,
b.wait_time - a.wait_time wait_time
from dba_hist_iostat_function a,
dba_hist_iostat_function b,
dba_hist_snapshot e
where a.FUNCTION_NAME = 'LGWR'
and b.FUNCTION_NAME = 'LGWR'
and a.snap_id = e.snap_id - 1
and b.snap_id = e.snap_id
and e.end_interval_time BETWEEN
TO_DATE('20230401 09:00:00', 'yyyymmdd hh24:mi:ss') AND
TO_DATE('20230414 18:00:00', 'yyyymmdd hh24:mi:ss')
order by e.begin_interval_time;

SNAP_ID SNAPSHOT_END_TIME SMALL_READ_MEGABYTES SMALL_WRITE_MEGABYTES LARGE_READ_MEGABYTES LARGE_WRITE_MEGABYTES SMALL_READ_REQS SMALL_WRITE_REQS LARGE_READ_REQS LARGE_WRITE_REQS NUMBER_OF_WAITS WAIT_TIME
------- ------------------- -------------------- --------------------- -------------------- --------------------- --------------- ---------------- --------------- ---------------- --------------- ----------
507 2023-04-12 03:00:33 0 3 0 4 0 3845 0 6 0 0
508 2023-04-12 04:00:06 0 3 0 4 0 3925 0 6 0 0
509 2023-04-12 05:00:41 0 4 0 3 0 3918 0 6 0 0
510 2023-04-12 06:00:06 0 3 0 4 0 3810 0 6 0 0
511 2023-04-12 07:00:25 0 3 0 4 0 3840 0 7 0 0
512 2023-04-12 08:00:00 0 3 0 4 0 3856 0 6 0 0
513 2023-04-12 09:00:26 0 4 0 3 0 3893 0 6 0 0
514 2023-04-12 10:00:57 0 3 0 5 0 3918 0 8 0 0
515 2023-04-12 11:00:25 0 3 0 3 0 3786 0 7 0 0
516 2023-04-12 12:00:57 0 3 0 5 0 3923 0 9 0 0
//Smll read的bytes和request都没有增加。

=================================================================================================================

--查等待事件的平均等待时间的变化趋势:
with t as
(select s.snap_id,
s.instance_number,
s.end_interval_time,
total_waits - lag(total_waits, 1) over(partition by s.instance_number order by s.snap_id) waits,
(time_waited_micro - lag(time_waited_micro, 1)
over(partition by s.instance_number order by s.snap_id)) / 1000 twt
from dba_hist_system_event ev, dba_hist_snapshot s
where ev.instance_number = s.instance_number
and ev.snap_id = s.snap_id
and event_name = 'log file parallel write'
and s.end_interval_time BETWEEN
TO_DATE('20230401 10:00:00', 'yyyymmdd hh24:mi:ss') AND
TO_DATE('20230414 11:00:00', 'yyyymmdd hh24:mi:ss'))
select to_char(end_interval_time, 'YYYYMMDD HH24:MI'),
instance_number,
sum(waits),
sum(twt),
round(sum(twt) / sum(waits), 2) wt
from t
where (to_char(end_interval_time, 'hh24') between 9 and 12 or
to_char(end_interval_time, 'hh24') between 14 and 17)
group by to_char(end_interval_time, 'YYYYMMDD HH24:MI'), instance_number
order by 1, instance_number;

TO_CHAR(END_IN INSTANCE_NUMBER SUM(WAITS) SUM(TWT) WT
-------------- --------------- ---------- ---------- ----------
20230412 09:00 1 2760 972.881 .35
20230412 10:00 1 2569 581.378 .23
20230412 11:00 1 2528 612.524 .24
20230412 12:00 1 2708 679.808 .25
20230412 14:00 1 2709 695.133 .26
20230412 15:00 1 2593 635.156 .24
20230412 16:00 1 2782 625.647 .22
20230412 17:00 1 2499 505.175 .2
20230413 09:00 1 2506 949.13 .38
20230413 10:00 1 -893319 -400069.72 .45
20230413 11:00 1 2422 555.207 .23

TO_CHAR(END_IN INSTANCE_NUMBER SUM(WAITS) SUM(TWT) WT
-------------- --------------- ---------- ---------- ----------
20230413 12:00 1 2465 581.381 .24
20230413 14:00 1 2999 762.191 .25
20230413 16:00 1 4702 1089.651 .23
20230413 17:00 1 2659 684.187 .26
20230414 09:00 1 617 441.806 .72
20230414 10:00 1 1930 469.204 .24

17 rows selected.

Running Sun丶

关注

4
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
oracle IO性能分析

22-10月-22 03.00.49.801 下午 20536 <4ms 89464 26.39。22-10月-22 03.00.49.801 下午 20536 <8ms 89464 18.28。22-10月-22 03.00.49.801 下午 20536 <16ms 89464 13.16。
复制链接

扫一扫