SAP/Oracle 12c ignoring index on large table

[former_member214380]

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]

Attaching index picture failt - trying it again.

[fidel_vales]

Hi,

in addition to what Stefan asked for (I still have not seen a proper SQLd360) you can run the script SQL_SQL_ID_DataCollector_11g+ from SAP note 1438410. The header has information on how to execute it. Note that you need the licence for the tuning and diagnostic pack to run it (if the oracle licence has been bought via SAP then there is no problem)

[stefan_koehler]

SQLd360 is the much better SQL_SQL_ID_DataCollector_11g ... in years: SQL_SQL_ID_DataCollector_11g is so 80's and SQLd360 is 2017 😉

[fidel_vales]

yes, but in SAP we still are on the 80's as we are limited to the DBACockpit and no access to Os in most of the cases 🙂