TwelveC

The goal of this blog is not to analyze the reasons behind working of Oracle optimize as every database implementation is unique. The main objective  is to understand what happens when Oracle optimizer chooses Index Fast Full Scan over Unique Scan. Significantly more occurrences of this behavior was observed after upgrading to Oracle12c.

As per Oracle documentation, An index fast full scan reads the index blocks in unsorted order, as they exist on disk. This scan does not use the index to probe the table like range scan, but reads the index instead of the table, essentially treating the index itself as a table.

The optimizer generally considers index fast full scan when a query criteria is met by the index itself, i.e. index contains all the columns that are needed for the query. You can also force Index Fast Full Scan with the hint INDEX_FFS.

Note: Since an index fast full scan reads the index blocks in unsorted order ,  a fast full scan cannot eliminate a sort operation.

So what happens when Oracle chooses Index Fast Full Scan over Unique Scan?

With respect to performance, you will not see any noticeable difference initially. However with increased concurrency, you may notice your CPU reaching the sky and subsequent performance degradation.  You will feel lucky if you are able to execute even a simple troubleshooting query.

Your “Top 10 Foreground Events by Total Wait Time” might look something like the following:

The #1 top wait event is “latch: cache buffers chains” and apart from that you can see many more latch related wait events in Top 10.

Now what is wait event “latch: cache buffers chains”?

This event  is generally caused by block contention (hot blocks) when multiple sessions repeatedly access one or more blocks that are protected by the same cache buffers chains latch. Why did this happen suddenly to my database. No suspense here. Further investigation revealed change in execution plan from unique scan to Index Fast Full Scan.

The new plan with Index Fast Full Scan caused 1219 more buffer gets in my database resulting in latch contention because of concurrency. So additional buffer gets with concurrency caused the latching issue.

To Summarize, Index Fast Full scans may be good fit for DSS applications more so because index  full scans can be parallelized. When it comes to OLTP, It looks OK as long as the SQL statement is not frequently executed.  But once concurrency kicks in, your will notice performance degradation and 100% CPU usage.