记录一次truncate分区表慢的问题

遇到一个奇怪的问题，这个问题暂时没有明确的结论，目前看上去是io有瓶颈然后数据块太多导致的truncate。

1、问题现象

客户truncate一个分区表，表没有数据，但是占用空间达600GB，truncate花了将近2个小时。为了查明原因，做了一个10046事件来记录整个过程。

alter session set tracefile_identifier='10046'; 

alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;

alter session set events '10046 trace name context forever,level 12';
-- 执行需要被trace的SQL --
ALTER TABLE test.t1  TRUNCATE PARTITION P202202;
--执行结束

-- 如果不退出当前session, 可以用以下命令关闭trace: -- 如果退出session，不用执行自动关闭

alter session set events '10046 trace name context off';

2、排查trc日志

做了tkprof的格式化，把占用时间最长的给列了出来

可以看到，最主要的等待事件是

• cell multiblock physical read (805,447次，总等待6,225.97秒)

这个也查了官方解释，这个是oracle一体机的等待事件：

This is an Exadata wait event similar to 'db file scattered read' and typically indicates the statement is performing a full table scan or an index fast full scan. This wait event is not seen nearly as much as 'db file scattered read' is seen on non-Exadata platforms because many full scans are offloaded to the storage cell via smart scans. This wait event is more prevalent against tables that are smaller and below the threshold for serial direct scan, a requirement for smart scans. Solutions Review the 'Cell Multiblock Physical Read Latency' metric (in Ignite Resources view on the Exadata tab) to determine latencies. If the time to read data is above 2ms, that could indicate configuration issues with the Exadata storage cell. Tune the SQL statement so that it uses an index rather than a full table scan if warranted. If the table is small or the query reads more than 7-10% of the rows in the table, a full table scan could be more efficient than using an index so test the differences. This is especially true in an Exadata system because of the storage cell efficiencies for reading large quantities of data. Use the Ignite Objects tab to determine the most costly full table scan if there are more than one table in the query. Update table and index statistics if they are stale so that Oracle understands the benfits of existing indexes.

有大佬见过的话，求教～