cancel
Showing results for 
Search instead for 
Did you mean: 

SAP/Oracle 12c ignoring index on large table

former_member214380
Participant
0 Kudos

Hello everyone,

since a couple of weeks we are dealing with an issue on a SAP system (SAP ERP 6.0 EHP5). The server is on Windows 2008 R2 and DB is Oracle 12c with latest bundle patch.

Issue is: Oracle/SAP does not seem to use the index of table TOA03 after we upgraded from Oracle 11g to 12c. Instead of using the index a full table scan is taking place every time the table is accessed. Since the table contains about 30.000.000 entries you could imagine this takes a while. Before the transaction was running about 3 seconds and now it is about 60 seconds.

So far we did a trace (ST12) to get to the execution plan (please see attachment).

Here it shows a full table scan runs - every time. We also tested this in the quality assurance system (also Oracle 12c) and there it is running just fine as it always was.

I also attached a screenshot of the present indexes.

We collected stats, checked index quality, rebuild index, created a system copy just to see that this behaviour remains in the same state and so on. I also checked the parameters via SAP note 1171650 and 1888485 with no success.

Currently I have no more ideas - do you? 🙂

Any help is highly appreciated!

Regards and thanks

Daniel

former_member214380
Participant
0 Kudos

Attaching index picture failt - trying it again.

View Entire Topic
fidel_vales
Employee
Employee
0 Kudos

Hi,

Good. Lets try to explain why I thin the CBO goes for a Full Table Scan.

The conditions on the WHERE clause are:

"MANDT"=:A0

"SAP_OBJECT"=:A1

("OBJECT_ID" LIKE :A2 OR "OBJECT_ID" LIKE :A3)

MANDT and SAP_OBJECT have only one different value, the CBO will think they do not filter any data and the OBJECT_ID is an OR with two LIKES. At the end CBO asumes that 3 million rows will be returned.

Here is the mistake of the CBO as no row is ever returned in 9K executions. Probably the SAP_OBJECT is a very good filter and CBO does not know (and we do not use bind peeking and histograms or the new features like cardinality feedback or ...)

The index Z01 is almost the same as the primary index. No needed.

So now we have an statement where the CBO thinks 3 Mo rows are returned and two options:

1 .- Index scan, block by block and accessing the table

2.- FTS.

As the table is compressed and smaller than the "good" index (250K blocks against 400K blocks) and a not good clustering factor, the decision seems to be clear (for the CBO) I will not enter here on the calculations.

How to solve the issue? Cheating. I do not know the ABAP code, but I would create a baseline (see note 1776485) => for example, force the "good" access path changing the statistics (increase a lot the number of different values for SAP_OBJECT) create the baseline and return the NDV to the previous value. That should do it. For this query. Also possible is to do a SQL patch but there is no note for it (still)