Some time ago, I encountered a pretty massive latch contention (cache buffer chains) problem on a customer system that brought the processing of some parallel BI jobs to a standstill even though the data processed by the single statements was ridiculously small (about 5 rows per execution). I have taken this as an opportunity to rebuild this as a demo case on one of our internal systems.
For the sake of simplictiy, I have implemented the following ABAP report to simulate the problem, the affected application was basically doing the same thing:
data: i_sbook type table of sbook.
DO 100000 times.
select carrid from sbook
into table i_sbook
where carrid = 'AH'
%_HINTS ORACLE 'INDEX_FFS("SBOOK" "SBOOK~0")'.
What do we see here:
- The query is hinted so that an index fast full scan is done
Additionally, the following preconditions were fullfilled on DB level:
The index SBOOK0 has the parallel degree set. This was originally done by the customer to improve the performance of the query.</li></ul><pre style=”font-size: 12px; overflow: auto; width: 100%; color: #000000; line-height: 14px; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; background-color: #eee; border: #999999 1px dashed; padding: 5px”>SQL> select degree from dba_indexes where index_name = ‘SBOOK0′;