关于cell smart table scan一次问题处理

情况描述：

在exadata上跑了12.1.0.2版本的数据库，昨晚有个跑批sql语句，执行了十几个小时依然没有执行完。

SQL语句就是一条insert...select...语句，单独执行select部分发现几秒钟可以出结果，数量也不大，但是当带上insert执行的时候，就一种遥遥无期的感觉。

在观察insert执行过程中，发现这条sql语句的等待事件一直是cell smart table scan，按照我个人理解，应该是单块读或者多块读方面的等待事件。

由于是在exadata上，很多技术我现在也不熟悉，有点一知半解。

在网上找了下关于cell smart table scan的解释：

This is an Exadata wait event typically seen during full table scans that have been offloaded to the storage cells. This event replaces waits on “direct path read” in many cases. As with direct path reads, data is returned directly to the PGA rather than going through the buffer cache. When the storage cells process full table scans they can apply columns filters and perform column projection so that not all blocks are returned, only the ones that are needed.

Solutions

This event indicates that a full table scan is being performed. In some cases this could be faster than an index lookup, but is not a replacement for query tuning. If the query will return a small subset of the data, utilizing an index may be more efficient. Test the differences to understand any performance penalties incurred by doing a smart table scan vs. the index lookup.

Also, ensure the smart table scan is being done effectively by reviewing the Cell Smart Table Scan Latency metric on the Exadata tab under Resources in Ignite. The Objects tab in Ignite will also show response time information by object. This is critical for understanding which table is causing the majority of wait times.

即cell smart table scan事件类似于direct path read直接路径读，绕过buffer cache，直接将结果给到pga，返回给客户端。同时cell smart  table scan会将全表扫描offload到存储节点上，利用诸如列过滤、列映射等技术，只返回我们所需要的数据。

但是这种cell smart table scan的方式不能作为一种优化查询的手段，对于较小结果集查询的问题，走索引的方式可能会更加迅速。

关于最后一段话，没有很好的理解，私认为大概意思是对于表对象开启这种cell smart table scan智能扫描，可能会产生一些等待时间。

-----------------------------------------------------------------------------------

问题分析及处理：

个人怀疑是不是采了cell smart table scan方面的bug，导致一直卡在那里。

后来我试了加hint，用parallel并发等发生执行，可以避过cell smart table scan这个等待事件，几秒钟出结果。

又测试了改写了select部分的语句，也可以避过cell smart table scan

------------------------------------------------------------------------------------

总结下：

新版本还是有些莫名其妙的问题，不一定有能力解决它，可以考虑换种方式，绕过它。