Adaptive cursor - skewed columns

[krishg]

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.

[stefan_koehler · Accepted solution]

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.

[krishg · Accepted solution]

Stefan,

Thanks ! I have a standard SAP program where SAP has in fact used substitute literals (via OSS notes in the past) for a similar query on the same table. As per the same OSS note, we have also generated histogram for that table.

Unfortunately, they have stopped supporting such changes via OSS notes. I am left to changing the standard SAP code or looking at a global parameter to make a change.

ACS can be enabled on statement level with SQL patches without changing any ABAP code or global Oracle parameter at all

What do you mean by "enabled on statement level"? Does it mean a specific statement or for queries accessing certain tables? . Also Could you please elaborate how this can be achieved?

What do you see are the cons of turning on ACS at global level?

[stefan_koehler · Accepted solution]

Hi Krish,

> What do you mean by "enabled on statement level"? Does it mean a specific statement or for queries accessing certain tables?

It means that you can enable almost any feature on SQL statement level. For example you specify different OPT_PARAM() for a specific SQL ID and so you can enable ACS and bind peeking only for this particular SQL. Here is an example how to use SQL patches (Using SQL Patch to add hints to a packaged application (Oracle Optimizer)) ... you would need some more hints to enable all the features, but that does not matter as they can be packed in one patch. You also can see the "SQL Patch field" in the EXPLAIN PLAN header, if you look really closely in transactions like ST05.

> What do you see are the cons of turning on ACS at global level?

ACS needs several conditions to kick in - e.g. check this chart here (Adaptive Cursor Sharing triggering mechanism | Mohamed Houri’s Oracle Notes). You have all of the support on your own as SAP does not recommend it. So i would only enable it in cases where you benefit from it to keep the "self support" as low as possible. Otherwise you would need to re-validate the whole SAP business processes and not only one SQL.

Regards

Stefan

[krishg · Accepted solution]

Thank you , Stephan! I think  SQL Patch does sound useful... So I could add the substitute literal to my SQL statement and create the sqlpatch and it should pick the right index (assuming histogram worked well)..

Are there any side effects that we should be on the look out for?

[stefan_koehler · Accepted solution]

Hi Krish,

> So I could add the substitute literal to my SQL statement and create the sqlpatch and it should pick the right index (assuming histogram worked well)

There is *no* need to change the SAP standard code with substitute literal, if you gonna use the SQL patch. Just enable ACS (incl. bind awareness) and bind peeking for this particular SQL ID with help of the SQL patch and that's it. The (frequency) histogram should be fine / accurate, if you only have two values in this particular column.

> Are there any side effects that we should be on the look out for?

Check the performance of this particular query and how many child cursors are created plus the executions plans for them.

Regards

Stefan

[krishg · Accepted solution]

Stefan,

Thanks for your patience in answering my questions.

Can I use substitute literal while creating the SQL patch like below ? Would that do the trick? or do I have to enable ACS / bind peeking and bind awareness?

SQL> ....*create_patch (sql_text => 'select * from table where col = space',

                                       hint_text =>'&SUBSTITUTE LITERALS&',

                                       name => 'test_skewed' );

Are there any action needed when the system is restarted after maintenance?

Krish

[stefan_koehler · Accepted solution]

Hi Krish,

> Can I use substitute literal while creating the SQL patch like below ?

Forget "substitute literal". This is an ABAP code syntax - nothing on Oracle RDBMS level. You need no literals in your case when using ACS and bind peeking.

> or do I have to enable ACS / bind peeking and bind awareness?

Yes.

> Are there any action needed when the system is restarted after maintenance?

No.

However based on your questions you should really reconsider to enable some "self-supported" features as it seems like you don't know much about it. You have to support it on your own.

Regards

Stefan

[krishg · Accepted solution]

Thanks Stefan, you are not far off the mark..  I know very little in this area. I was hoping SAP would fix it like they did in the past,but they have changed their approach.

I have few queries based on skewed columns , which does not choose the right index. Our DBA's have not provided any solution so far (except for changing the ABAP statement) and that was the reason, I have taken this up.

Your solution seems like the best solution so far. Least intrusive and comprehensive (that It can impact all the program with the same query).

Could you share what would be the hind text to turn on ACS?

[stefan_koehler · Accepted solution]

Hi Krish,

> Could you share what would be the hind text to turn on ACS?

Send me a SQLd360 report for this particular SQL ID by mail (check SCN profile) and i'll do it once exemplary.

Regards

Stefan