Skip to Content
0

SAP/Oracle 12c ignoring index on large table

Jan 26, 2017 at 06:24 PM

405

avatar image

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

mrjwy.png (13.7 kB)
10 |10000 characters needed characters left characters exceeded

Attaching index picture failt - trying it again.

index.png (6.6 kB)
0
* Please Login or Register to Answer, Follow or Comment.

9 Answers

Stefan Koehler Jan 26, 2017 at 08:28 PM
0

Hey Daniel,


> Currently I have no more ideas - do you? :-)

Yes, please run SQLd360 for this particular SQL-ID and upload the output (ZIP) file. It includes everything we need for analyzing this issue. A CBO trace might be enough for the start as well, but SQLd360 would be the complete picture.

Best Regards

Stefan Koehler

Independent Oracle performance consultant and researcher

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member
Jan 29, 2017 at 09:17 PM
0

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)

Show 2 Share
10 |10000 characters needed characters left characters exceeded

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 ;-)

2
Former Member
Stefan Koehler

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 :)

2
Daniel Masaladzic Feb 06, 2017 at 05:18 PM
0

Hey guys!

I am so sorry for this delay! Somehow I dont get notifications when topics in SCN are updated :-/ I will see if I can gather the information you requested. Thank you so much!

Some new information in this case until then: We copied the system to a sandbox to do some testing there. Performance was also slow. So we created an additional index on that table and it worked like a charm from that point on. We replicated that onto the problem system but it just does not want to use that index. "Fun" fact: By deactivating/activating the index in the sandbox we can reproduce the issue every time.

Regards
Daniel

Share
10 |10000 characters needed characters left characters exceeded
Daniel Masaladzic Feb 09, 2017 at 12:38 PM
0

Hello,

I created the sql.out by using #1438410. In this file I replaced the original SID with "SID". There are some usernames in there, too. I will have to take them out - hope this wont disturb the analysis.

Unfortunately I cant attach the file due to "This attachment is not permitted because the file type is invalid." I tried renaming it to ".txt", but no chance. Where do you want me to upload it?

I will see if I can create more information by using SQLd360 :-)

Thanks in advance

Daniel

Share
10 |10000 characters needed characters left characters exceeded
Daniel Masaladzic Feb 09, 2017 at 01:53 PM
0

Hello again,

here is the Output File. No idea why I could upload it now :-)

sql.txt

Regards

Daniel


sql.txt (612.9 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member
Feb 14, 2017 at 08:39 AM
0

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)

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 15, 2017 at 06:03 PM
0

This may be about the selectivity of your predicates. If a query uses an index it suffers the overhead of reading from disk the pages which constitute the index itself. If instead it performs a table scan it has the overhead of retrieving data it will ignore. The relative cost of these two options will depend on the selectivity of your index, how the data's clustered, and if the index is covering. The optimiser will choose whichever option actually does the least work. Having up-to-date statistics helps it do this well.

It may be that some other part of your query that you don't show -- a order by or group by for example -- would incur more cost when using the indexes than when using the table scan. For instance, if the query had an order by which is the same as the clustering sequence for the tables a costly sort can be eliminated by processing the rows in the sequence them come off disk.

For different errors and other fixes on oracle you can find here .

Share
10 |10000 characters needed characters left characters exceeded
Daniel Masaladzic Feb 16, 2017 at 04:47 PM
0

Hey there,

thank you for your time and explanations so far! Unfortunately I am not that deep into CBO/DB to follow you by 100%. May I ask a question here: Any idea why can we fix/bring up the issue on the sandbox by adding/removing the Z01-index? Plus the quality assurance system that does not have that problem. Also the program code behind the function module with which the problem occurs is SAP original (SE37 -> "archiv_get_connections") and was not changed as far as I am aware.

Regards
Daniel

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Why not just generating and uploading the requested SQLd360 report? We would be able to answer your question then.

0
Daniel Masaladzic Feb 20, 2017 at 09:58 AM
0

Hello there,

I also have news: Due to Fidels post we compressed the new index and now the issue seems to be resolved - for now.

@Stefan: I did not use your report yet since I need to coordinate with the customer if we can work with non-SAP tools. I am still working on that since I like your approach! Do you think it is still worth to let your report run?

Regards
Daniel

Share
10 |10000 characters needed characters left characters exceeded