Use of histograms - SAP Oracle

[krishg]

We have multiple clients in our production landscape. Two additional clients with very limited data and expected to be retired in a year or two (less than 1% of the data of the entire table).

Unfortunately, when we run a query where there is no appropriate index the optimizer chooses an index randomly (any index with MANDT) and goes forward. This index is extremely inefficient and easily takes multiple minutes (for tables with 2 million rows).

I was thinking histogram would be a solution, but I noticed that even with histogram, SAP will not use histogram due to bind variables (797629 - FAQ: Oracle histograms). I guess with custom program we can use substitution variable with histogram to get over the hump , but I know DB hints are not SAP best practices. Further, this will not work for standard SAP program.

What is SAP recommended best practice for such scenarios? Just wondering is there a way to only not use bind variable for MANDT ?

[JamesZ]

Hi Krish,

How many queries are involved here? If not many and the database is >=12c, we can use sqlpatch.

Best regards,
James

[krishg]

Hi James

Thanks! This sounds like a good solution. To my knowledge, I have encountered at least two queries so far. We are still on Oracle 12.2 (and hoping to upgrade to 19 in the next few months).

What would be the recommended HINT we would put in this query? I guess in one scenarios, I can probably put 'Table scan' directly (especially if I know that it will not run in the other client). Now for queries that could run in multiple clients , I will probably need to create histogram and use bind-literals?

[janos_mucsi-besze]

Hi Krish,

In case of a Z-report (customer created), you could introduce the ABAP Hints to exactly tell which index to use for the specific SQL.

[krishg]

Unfortunately, we have scenarios this happens with standard code as well.

[janos_mucsi-besze]

Hi Krish,

If the SAP standard program is suffering performance degradation, the you could get in touch with the responsible SAP application team. So they can check if a new index creation would make sense.

[krishg]

Hi Janos

I am checking with SAP. There are quite a few queries that chose a bad index. These are one off queries that run always slower than table scan.

In the meantime, What is the expected/ default behavior of SAP in a multi client environment.

My expectation is that SAP should choose table scan in a client (with a bigger table) and potentially indexed scan in the client (small number of rows)

For example if client 100 has 10 million rows and client 200 has 10,000 rows. I would expect SAP to choose table scan in client 100 and indexed scan (any of the index that has mandt) in client 200.

Unfortunately, we don't see this behavior. I am getting run around by the SAP incident processor (asking me to check this params). Would this parameter make a difference?

_enable_ptime_update_for_sys add with value "TRUE"

_spacebg_sync_segblocks add with value "TRUE"

db_create_online_log_dest_2 add with value "+RECO"

My question, is this behavior normal ? James had a solution, but we need to wait to upgrade oracle before applying that solution. is there a way to force SAP to not use bind variables for the MANDT? That may potentially help with this issue.

[janos_mucsi-besze]

MANDT or Client column is always part of the Client-dependent tables and by default the SAP kernel includes the MANDT/Client field in the where condition. Up to my current knowledge, there is no way to get around this.

I don't (fully) agree with your example. Because the index usage depends on different factors, not just on the pure number of rows. In SAP the parameter "optimizer_index_cost_adj" is the most important parameter e.g. Because his makes and index access more charming. It is also important how the SQL Statements are written, if you would select everything from the table (all columns, all values without restriction), then most probably a FULL TABLE SCAN takes place, But if you select maybe the number of rows (count(*)) while the where condition contains only all columns of an index, then the corresponding index is used.

In regards to the parameter, set these the values recommended by SAP.

And to answer your question, without a deep analyze, the situation looks correct to me.

[krishg]

This is happening with SAP queries (in standard program) in a multi client environment. We have three clients in our environment (two of them are active and one inactive). The second active client is like very small. Everytime, there is no appropriate index, the program always chooses index arbitrarily and never choose a table scan. I cannot create a SAP note for every SAP query.

For example, a query on VBAK with GUEEN >December 2020, it choose VBAK~AUD index. The tablescan on this table takes few seconds while the indexed search 1-2 minutes.

My question was if there is a way to use literals instead of Bind variables just for MANDT. Because SAP uses bind variable for MANDT, it choose a same access path irrespective of the client.

In our system, optimizer_index_cost_adj is set to '20'. and we get no warning in EWA regarding this parameter. These are the two parametrs for which we get EWA warning. Could you please elaborate the impact of this parameters on the optimzer query?

_enable_ptime_update_for_sysTRUEdb_create_online_log_dest_2+RECO

[janos_mucsi-besze]

If a Bind Variable or literal is used is basically driven by the SAP application. AS far as I know, BW is using rather literals, while regular ERP is using Bind Variables. A table scan should be slower compared to an index scan. Table scan can be quicker if Multiblock read is used and if all (well, most) of the affected blocks are already in the Buffer Cache. Or if you put a table in Keep Pool.

If the index search is so bad, it worth to reorganize them, especially if they are unbalanced.

In addition, make sure the Oracle SBP is correctly installed. Meaining catsbp/datapatch go texecuted after mopatch/opatch got successfully executed.

The mentioned parameters are not influencing the performance.

Maybe it worth for you to check if our predefined statistics are in place:

1020260 - Delivery of Oracle statistics (Oracle >= 10g)

[krishg]

You mean perform re-org on the table and indexes or just the index? In the past, if I remember correctly we have done that , but it has not made any difference.

The problem I see is that when a query uses an index which has none of the where used list columns, isn't that worse than table scan (especially when one MANDT has 3 million rows and other MANDT has less than 10,000 rows).

[JamesZ]

Hi Krish,

I don't now the specific sql, you may have to test to get best hint your query.

Best regards,
James