Hi we are using version 11.2.0.4 of oracle Exadata. Many times , we are seeing high UNDO reads due to "delayed block cleanout" effect on certain queries and those were giving uneven response time, sometimes they run in few minutes and some other time the same query takes Hrs due to the firstly visiting those blocks. And the stats captured for the session were showing "transaction tables consistent reads - undo records applied" and "clean outs only - consistent read gets" as the top two for that session during the slowness period, which is giving evidence of delayed block clean out effect.
We are doing array based insert to the base transaction tables through out the day 24/7, and we are seeing the major UNDO reads for the INDEX reads as index blocks were updated and we have the table not getting Updated/deleted but getting inserted 24/7. And sometimes the big reporting read queries face the UNDO read issue which appears to be delayed block cleanout effect. So we were thinking of any approach which will make the cleanout process finished before the long queries triggered. We were thinking as we gather stats once in 3hrs so the stats gather should help in cleaning the blocks, but as currently we have stats gather happening in parallel degree, so the cleanout must not be happening due to direct path read(as its happening in Parallel Degree) and as per my understanding it has to be happen in serial mode to do the block cleanout. But we have the base table daily range partitioned and each of these partitions are holding ~200million records and having size ~30GB+ each, so gathering stats(with cascade=>true, so that index block will also be visited) in serial will take significant amount of time. So need experts view , what would be the ideal way to get the index and table blocks cleaned frequently with minimal DB resource usage, so that it wont cause the reporting query slowed down which first touches those blocks?
I was thinking, if we collect stats on just one column to make it happen in lesser Db resource , that should also make it visit all the rows/tables blocks, but will that visit all the index blocks too?