Skip to Content

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:

report zhot_test.

data: i_sbook type table of sbook.

start-of-selection.

DO 100000 times.
select carrid from sbook
into table i_sbook
where carrid = 'AH'
%_HINTS ORACLE 'INDEX_FFS("SBOOK" "SBOOK~0")'.
ENDDO.

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′;


DEGREE


5
To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Lars Breddemann
    really this one was picked very well to demonstrate how the abuse of features can ruin a systems performance.
    Default parallel degrees and Fast Full-hints are typical “go-faster” tools that are used blindfolded.

    Barely understood and misused as silver bullets for a problems that could be easily analysed and solved such features really can turn the high-end database iron into a slow single-session processing machine.

    How much this company could have saved by not buying the super expensive all-features-on-board DBMS but something like MaxDB instead and buy their developers/DBAs a database training…!?

    (0) 

Leave a Reply