本文介绍基于 ASH Report 量化分析租户 ssstore_read IO 吞吐抖动根因。
详细说明
问题现象
租户主节点每日 00:00 ~ 00:30 区间内，租户 ssstore_read 的 IO 吞吐量上涨至 1.5GB/s 以上，同时 ssstore_read 的 IO 耗时上涨至 8ms 以上，起止时间大致固定。
分析过程
在 sys 租户下执行以下命令，获取抖动时段的 ASH 报告。
CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT( '2026-03-12 00:07:00', '2026-03-12 00:20:00', NULL, NULL, NULL, 'TEXT', NULL, NULL, 1002 );注意
在 OceanBase 数据库 V4.4.2 版本，需指定业务租户 ID 生成 ASH Report 报告。
查看 ASH Report 中的 IO Bandwidth 章节内容。
+---------------------+------------------------------------------------------------------------------------------------+--------------------+--------+----------------+--------------------+--------------------+----------+------------+ | Node| Program Module Action / SQL ID| Plan Hash| Type| IOPS| IO Size(MB)| IO Bandwidth(MB/s)| Object ID| % Object ID| +---------------------+------------------------------------------------------------------------------------------------+--------------------+--------+----------------+--------------------+--------------------+----------+------------+ | xx.xx.xx.xx:2882 | T1002_LogService/LogIOWorker/| 0|disk_write| 1564.42| 3364.53| 113.17| 0| 100.00%| | xx.xx.xx.xx:2882 | T1002_LogService/LogIOWorker/aggregate io task| 0|disk_write| 1648.17| 50725.12| 66.07| 0| 100.00%| | xx.xx.xx.xx:2882 | T1002_LogService/LogIOWorker/aggregate io task| 0|disk_write| 1651.36| 56946.52| 59.64| 0| 100.00%| | xx.xx.xx.xx:2882 | T1002_BFBuildTask/BFBuildTask/BloomFilterBuild| 0|disk_read| 19.12| 27140.00| 38.25| 0| 100.00%| | xx.xx.xx.xx:2882 | T1002_LogService/LogIOWorker/aggregate io task| 0|disk_write| 3493.35| 6663.44| 27.50| 0| 100.00%| | xx.xx.xx.xx:2882 | A27522B7C6A0E53BDD9F48E74B1B7308| 4058911751418824560|disk_read| 1555.71| 234404.90| 24.48| 201891| 6.02%| | xx.xx.xx.xx:2882 | 63D1FFFDE28ECB360E333A8D9D05389A|11357197997738832342|disk_read| 1542.19| 218260.27| 24.33| 201799| 2.94%| | xx.xx.xx.xx:2882 | 3331994F495A81B6F15D5BE3BE412566|11357197997738832342|disk_read| 1507.65| 219536.66| 23.86| 201795| 2.74%| | xx.xx.xx.xx:2882 | 1FFB8F49C81812F256220C4712522945|11357197997738832342|disk_read| 1483.53| 220388.42| 23.56| 201820| 2.65%| | xx.xx.xx.xx:2882 | BEF0E2401D6773FDBADADC7296CCCB05| 4058911751418824560|disk_read| 1478.27| 170404.54| 23.43| 201891| 9.00%| | xx.xx.xx.xx:2882 | A8E2E3F4530280BDCCDCCDEC0054A7F6| 6269093822223643688|disk_read| 1497.44| 47075.52| 22.67|1152921504607041452| 81.72%| | xx.xx.xx.xx:2882 | 756ABD6D9AF32BE7041549C106791A78|11357197997738832342|disk_read| 1323.67| 218941.19| 21.00| 201790| 3.00%| | xx.xx.xx.xx:2882 | D4A52A1E2FDDE57658A70C8E3EFD92CA|12135249176271597750|disk_read| 1296.01| 156.57| 17.43| 269179| 100.00%| | xx.xx.xx.xx:2882 | 2BC5EA33BEE4697BCCED211251BE417B| 4058911751418824560|disk_read| 1030.28| 443.44| 16.44| 253845| 99.99%| | xx.xx.xx.xx:2882 | EDA39C965AB50452B5E94990E0F65FF3| 9647999503821790023|disk_read| 1080.15| 145.04| 14.56| 269325| 100.00%| | xx.xx.xx.xx:2882 | B1A905D7AE4EC9720F2F91FD73E64DE8| 1351086926401127487|disk_read| 1065.26| 116.10| 13.99| 269471| 100.00%| | xx.xx.xx.xx:2882 | 86E6CB564E03330223F121CDE57D20C2|10600498582267990390|disk_read| 938.39| 119.69| 13.83| 269909| 100.00%| | xx.xx.xx.xx:2882 | FC735EF7557BC6DE19370CDBBEF4E42B| 3285727337379281519|disk_read| 792.33| 228.16| 12.62| 267414| 100.00%| | xx.xx.xx.xx:2882 | 729E15C22086DD2671FB9DF4B90CE8B7|10034483430643459188|disk_read| 782.63| 603.61| 12.59| 253771| 100.00%| | xx.xx.xx.xx:2882 | 8106C63F6ED8659F27302740D31D5FC6| 6167798947122340558|disk_read| 715.36| 1936.22| 11.35| 253873| 67.63%| | xx.xx.xx.xx:2882 | 8BCBB8BD1B7E57AF6CC7EF2E7C5AC7B7| 7054614306834699777|disk_read| 1269.88| 101.33| 11.08| 212020| 100.00%| | xx.xx.xx.xx:2882 | 4B7CD0BB3AC02AD228561AD6038706D9|11357197997738832342|disk_read| 661.02| 694.52| 10.57| 253771| 100.00%| | xx.xx.xx.xx:2882 | 3AE50C001B3B76555C6E2A25216336D8|15778766441977252546|disk_read| 630.19| 281.67| 10.10| 265744| 100.00%| | xx.xx.xx.xx:2882 | T1002_TimerWK/TimerWK/TabletGCTask| 0|disk_read| 32.86| 88.58| 9.70| 0| 100.00%| | xx.xx.xx.xx:2882 | D09C85A767CE91F29D45E86B0DFEA800| 9749146622550590789|disk_read| 589.70| 92.33| 9.42| 253873| 100.00%| | xx.xx.xx.xx:2882 | BE8F09D1BBBE9B0E35B310719B9B5812| 9784446145046175682|disk_read| 552.89| 90.12| 8.93| 0| 100.00%| | xx.xx.xx.xx:2882 | 154663D65B65BE7F9FC9D5551940FDA2| 9555928632135671097|disk_read| 402.20| 2495.99| 8.25| 265923| 100.00%| | xx.xx.xx.xx:2882 | 7235A1335E771651F36C8DF0AE79381D|15937155271251015174|disk_read| 505.66| 292.10| 8.02| 265679| 67.52%| | xx.xx.xx.xx:2882 | B8801FC993307505F901C882AB97E52C|12941237285755660851|disk_read| 497.99| 162.94| 7.92| 0| 82.34%| | xx.xx.xx.xx:2882 | 1233330D6254FC47D36F75F6C2D73B88| 7259655605471307834|disk_read| 1455.23| 1295.57| 6.80| 201866| 38.23%| | xx.xx.xx.xx:2882 | 8106C63F6ED8659F27302740D31D5FC6|12520049711361668163|disk_read| 392.60| 173.96| 6.20| 253873| 68.02%| | xx.xx.xx.xx:2882 | 8BD7894F8D810708B2C6FAE392F9ADF0|15585606919877308261|disk_read| 391.76| 6980.24| 6.17|1152921504607039514| 58.23%| | xx.xx.xx.xx:2882 | C9FFDEF9F35CB39CFD1A7D19A764B0F9| 7259655605471307834|disk_read| 1296.57| 1094.91| 6.09| 201862| 77.93%| | xx.xx.xx.xx:2882 | AE6F121FB4EA90C375657802CFD0240C| 3106112168409403463|disk_read| 385.68| 60.70| 6.05| 266411| 100.00%| | xx.xx.xx.xx:2882 | 59FCE3F9E38390E2BC0CF1496E7B6F7D|10601032138592362922|disk_read| 353.75| 57.64| 5.71| 0| 100.00%| | xx.xx.xx.xx:2882 | 52B462DE66EB3414E6F4D78B50E3C7DB| 7031412132123272711|disk_read| 432.63| 465.60| 5.71| 201790| 66.09%| | xx.xx.xx.xx:2882 | 38FE915BB5A7F23D832525E124A03170| 7259655605471307834|disk_read| 1165.11| 456.01| 5.70| 201849| 66.78%| | xx.xx.xx.xx:2882 | T1002_ReplaySrv/ReplaySrv/| 0|disk_read| 3.01| 56.37| 5.65| 0| 100.00%| | xx.xx.xx.xx:2882 | 81102C92ABE73D0B4E25C2053A909D1A| 1040076778455985959|disk_read| 1981.71| 46.64| 5.28| 272134| 100.00%| | xx.xx.xx.xx:2882 | 612A0BECF902D8446D4F3394D229BA10|16552870926426001763|disk_read| 387.53| 144.85| 5.24| 269471| 99.31%| | xx.xx.xx.xx:2882 | 440B5E8AC6D7A83F0924ADF7B1765D56|11233190617282414842|disk_read| 321.37| 52.75| 5.22| 253873| 100.00%| | xx.xx.xx.xx:2882 | CFC823A8725D7097E956C41860841B41| 7259655605471307834|disk_read| 1015.65| 824.95| 4.88| 201845| 34.98%| | xx.xx.xx.xx:2882 | DD7E31942792AAE5FDA82E22E4DB23D8| 1040076778455985959|disk_read| 350.23| 41.65| 4.85| 0| 100.00%| | xx.xx.xx.xx:2882 | 2228E4A427AC6E1245B9987E267A6561| 6167798947122340558|disk_read| 311.31| 96.42| 4.75| 253873| 63.31%| | xx.xx.xx.xx:2882 | C5DCF653D4C904D68D0C9E78DDC7F23E| 6300671504768185455|disk_read| 303.39| 42.54| 4.63| 266097| 100.00%| | xx.xx.xx.xx:2882 | 6254001679BAA9263561A27CE71E5B62| 6348250941684313022|disk_read| 304.88| 653.39| 4.51| 265731| 69.04%| | xx.xx.xx.xx:2882 | 7EA648A31F7A46C54C7259E8BA30DEB2| 5194073556714776618|disk_read| 268.11| 43.60| 4.41|1152921504606847014| 100.00%| | xx.xx.xx.xx:2882 | E8E25DE978532DBE24FC77DB91ADFDF0| 5194073556714776618|disk_read| 268.48| 42.71| 4.31|1152921504606847014| 100.00%| | xx.xx.xx.xx:2882 | 0D306A122C8A9605FD44D7114A95126A| 6348250941684313022|disk_read| 279.89| 6430.29| 4.17| 265731| 60.70%| | xx.xx.xx.xx:2882 | 1CCE07910915907D3B9BD6C3DF9A0B44| 7259655605471307834|disk_read| 820.91| 228.40| 4.03| 201846| 62.58%| +---------------------+------------------------------------------------------------------------------------------------+--------------------+--------+----------------+--------------------+--------------------+----------+------------+确认 TOP IO SQL 调用信息。
通过查询
gv$ob_plan_cache_plan_stat获取 SQL 执行详情，发现这些 SQL 是由 DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC 触发的自动统计信息收集任务。select sql_id,executions,avg_exe_usec,first_load_time,last_active_time,query_sql from gv$ob_plan_cache_plan_stat where sql_Id in('756ABD6D9AF32BE7041549C106791A78','A8E2E3F4530280BDCCDCCDEC0054A7F6','BEF0E2401D6773FDBADADC7296CCCB05','1FFB8F49C81812F256220C4712522945','3331994F495A81B6F15D5BE3BE412566','63D1FFFDE28ECB360E333A8D9D05389A','A27522B7C6A0E53BDD9F48E74B1B7308') order by avg_exe_usec desc;输出结果如下：
+----------------------------------+------------+--------------+----------------------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | sql_id | executions | avg_exe_usec | first_load_time | last_active_time | query_sql | +----------------------------------+------------+--------------+----------------------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | BEF0E2401D6773FDBADADC7296CCCB05 | 1 | 102405351 | 2026-03-12 00:16:48.699854 | 2026-03-12 00:18:31.101905 | SELECT /*+ NO_REWRITE DBMS_STATS OPT_PARAM('ROWSETS_MAX_ROWS', 256) FULL("XXX_XXX_XXXX_XXXXXXX_XXX") OPT_PARAM('APPROX_COUNT_DISTINCT_PRECISION', 10) USE_PX, PARALLEL(100) QUERY_TIMEOUT(6303170754)*/ TOP_K_FRE_HIST(0.001000, "SEGMENT5", 254, 28) FROM "FINDATA"."XXX_XXX_XXXX_XXXXXXX_XXX" | | A27522B7C6A0E53BDD9F48E74B1B7308 | 1 | 99785964 | 2026-03-12 00:15:05.936262 | 2026-03-12 00:16:45.718090 | SELECT /*+ NO_REWRITE DBMS_STATS OPT_PARAM('ROWSETS_MAX_ROWS', 256) FULL("XXX_XXX_XXXX_XXXXXXX_XXX") OPT_PARAM('APPROX_COUNT_DISTINCT_PRECISION', 10) USE_PX, PARALLEL(100) QUERY_TIMEOUT(6303170748)*/ TOP_K_FRE_HIST(0.001000, "SEGMENT5", 254, 28) FROM "FINDATA"."XXX_XXX_XXXX_XXXXXXX_XXX" | | 1FFB8F49C81812F256220C4712522945 | 1 | 97784661 | 2026-03-12 00:09:12.547954 | 2026-03-12 00:10:50.329308 | SELECT /*+ NO_REWRITE DBMS_STATS OPT_PARAM('ROWSETS_MAX_ROWS', 256) FULL("XXX_XXXX_X17_XXXX_XXX") OPT_PARAM('APPROX_COUNT_DISTINCT_PRECISION', 10) USE_PX, PARALLEL(100) QUERY_TIMEOUT(6754068761)*/ TOP_K_FRE_HIST(0.001000, "CANCEL_SERIAL", 254, 1) FROM "FINDATA"."XXX_XXXX_X17_XXXX_XXX" | | 3331994F495A81B6F15D5BE3BE412566 | 1 | 97493009 | 2026-03-12 00:10:53.648661 | 2026-03-12 00:12:31.137791 | SELECT /*+ NO_REWRITE DBMS_STATS OPT_PARAM('ROWSETS_MAX_ROWS', 256) FULL("XXX_XXXX_X17_XXXX_XXX") OPT_PARAM('APPROX_COUNT_DISTINCT_PRECISION', 10) USE_PX, PARALLEL(100) QUERY_TIMEOUT(6754068763)*/ TOP_K_FRE_HIST(0.001000, "CORP_ACCOUNTNO", 254, 18) FROM "FINDATA"."XXX_XXXX_X17_XXXX_XXX" | | 756ABD6D9AF32BE7041549C106791A78 | 1 | 96517463 | 2026-03-12 00:12:32.578449 | 2026-03-12 00:14:09.092178 | SELECT /*+ NO_REWRITE DBMS_STATS OPT_PARAM('ROWSETS_MAX_ROWS', 256) FULL("XXX_XXXX_X17_XXXX_XXX") OPT_PARAM('APPROX_COUNT_DISTINCT_PRECISION', 10) USE_PX, PARALLEL(100) QUERY_TIMEOUT(6754068758)*/ TOP_K_FRE_HIST(0.001000, "CANCEL_SERIAL", 254, 1) FROM "FINDATA"."XXX_XXXX_X17_XXXX_XXX" | | 63D1FFFDE28ECB360E333A8D9D05389A | 1 | 96116686 | 2026-03-12 00:07:35.366420 | 2026-03-12 00:09:11.479600 | SELECT /*+ NO_REWRITE DBMS_STATS OPT_PARAM('ROWSETS_MAX_ROWS', 256) FULL("XXX_XXXX_X17_XXXX_XXX") OPT_PARAM('APPROX_COUNT_DISTINCT_PRECISION', 10) USE_PX, PARALLEL(100) QUERY_TIMEOUT(6754068752)*/ TOP_K_FRE_HIST(0.001000, "CORP_ACCOUNTNO", 254, 18) FROM "FINDATA"."XXX_XXXX_X17_XXXX_XXX" | +----------------------------------+------------+--------------+----------------------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.514 sec)这些 SQL 主要是通过
TOP_K_FRE_HIST函数进行统计信息收集，涉及表大小分别为：XXX_XXX_XXXX_XXXXXXX_XXX: 1,936,699,284 行。
XXX_XXXX_X17_XXXX_XXX: 1,989,160,981 行。
问题原因
租户 ssstore_read IO 吞吐突增原因为自动统计信息收集 MAINTENANCE WINDOW 期间对大表进行统计信息收集。
补充说明
Q： 自动统计信息收集 MAINTENANCE WINDOW 为 22:00 ~ 次日 02:00，为何 IO 吞吐抖动时段固定发生于 00:00 ~ 00:30？
A： MAINTENANCE WINDOW 触发的是 DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC，该过程内部调用 gather_database_table_stats，通过 ObBasicStatsEstimator::get_need_stats_tables 按批获取“需要收集统计信息的表”，再按返回的 table_id 顺序依次收集。 由于业务表每日变更量波动不大，因此每次统计信息收集的时间段基本固定，导致 ssstore_read 的 IO 吞吐抖动发生在每天 00:00 ~ 00:30 区间。
适用版本
OceanBase 数据库 V4.x 版本。
