Skip to Content

Adaptive cursor - skewed columns

We have a select on LTAP which has millions of rows ... One of the columns (present in the secondary index) used in our select query is highly skewed (Y/space) . Entries with space is couple of hundreds... We could use substitute literals hint to force the query to use the correct index... This table's statistics is generated with histograms.

I saw the blog about using adaptive cursor by Stefan Koehler and that we can use database parameter to turn on the Adaptive cursor. The question is does this make sense to resolve any of the issues like above.. Or would it make better sense to perform coding changes (sometimes in Standard SAP code) on a case by case basis.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    May 30, 2016 at 02:44 PM

    Hi Krish,

    > The question is does this make sense to resolve any of the issues like above.. Or would it make better sense to perform coding changes (sometimes in Standard SAP code) on a case by case basis.

    Both (SAP standard code change or ACS) can make sense. You can change ABAP code with "substitute literals" if the bind values in the SELECT statement do not change often. Otherwise you have to hard parse nearly every single statement which puts high pressure on shared pool and CPU. ACS and bind peeking is exactly the way to solve this issue with a lot of different bind values and you will not get many child cursors if only one column is highly skewed (+ histogram) in your case.

    ACS and bind peeking can be enabled on statement level with SQL patches (or profiles) without changing any ABAP code or global Oracle parameter at all. Be aware that SAP's Oracle parameter recommendations are only recommendations and you can differ from them, but you have to support the difference on your own (or with Oracle directly if you have a direct support contract). So test it 😊

    Regards

    Stefan

    P.S.: Other options for your scenario would be a function based index (+ code change) or fake and lock the statistics for this particular column depending on which value you mostly rely on.

    Add comment
    10|10000 characters needed characters exceeded