Skip to Content

Oracle Optimization chosing wrong index instead of TABLE FULL SCAN

When we change workcenter, it is extremely slow. It takes more than 10 minutes and time out. There is a OSS Note 415031 which talks about index on AFRU table. The query on AFRU itself takes around 9 minutes. AFRU table has around 5 million rows.

I have couple of question, when I look at my explain statement, it is choosing a custom index which does not have the 'WHERE' field. I am curious, why isn't oracle optimizer not choosing table scan? I would expect a full table scan of AFRU table (with more than 5 million rows to be still faster than using index which does not have the appropriate field). Is it possible that it is getting delayed due to choosing of the wrong index?

I was trying to put HINT in a custom program and see if I can force TABLE FULL SCAN and it did seem faster.

I am perplexed. This is a one off scenario, I was thinking table scan may not be all that bad. but the optimizer is never choosing table scan. I would like to avoid creating new index for such an exception scenario, but timing out is not an option.

STATISTICS

Parse Timestamp: 20180208 17:57:45

System: RQ1

SQL_ID 18fyjygmadgtd, child number 1

-------------------------------------

SELECT /*+ FIRST_ROWS (1) */ * FROM "AFRU" WHERE "MANDT"=:A0 AND

"ARBID"=:A1 AND ROWNUM <=:A2

Plan hash value: 4214817510

-------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 256 (100)| |

|* 1 | COUNT STOPKEY | | | | | |

|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| AFRU | 1 | 585 | 256 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | AFRU~Z01 | | | 8 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1

2 - SEL$1 / AFRU@SEL$1

3 - SEL$1 / AFRU@SEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=:A2)

2 - filter("ARBID"=:A1)

3 - access("MANDT"=:A0)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - "MANDT"[VARCHAR2,9], "AFRU"."RUECK"[VARCHAR2,30], "AFRU"."RMZHL"[VARCHAR2,24],

"AFRU"."GRUND"[VARCHAR2,12], "AFRU"."PERNR"[VARCHAR2,24], "AFRU"."ISDD"[VARCHAR2,24],

"AFRU"."ISDZ"[VARCHAR2,18], "AFRU"."IERD"[VARCHAR2,24], "AFRU"."IERZ"[VARCHAR2,18],

"AFRU"."ISBD"[VARCHAR2,24], "AFRU"."ISBZ"[VARCHAR2,18], "AFRU"."IEBD"[VARCHAR2,24],

"AFRU"."IEBZ"[VARCHAR2,18], "AFRU"."ISAD"[VARCHAR2,24], "AFRU"."ISAZ"[VARCHAR2,18],

"AFRU"."KAPTPROG"[VARCHAR2,12], "AFRU"."OBMAT"[VARCHAR2,54],

"AFRU"."OBCHA"[VARCHAR2,30], "AFRU"."LICHA"[VARCHAR2,45], "AFRU"."MYEAR"[VARCHAR2,12],

"AFRU"."ME_SFCID"[VARCHAR2,180], "AFRU"."ROLE_ID"

3 - "AFRU".ROWID[ROWID,10], "MANDT"[VARCHAR2,9], "AFRU"."CATSBELNR"[VARCHAR2,30],

"AFRU"."STZHL"[VARCHAR2,24]

Table AFRU

Last statistics date 02/06/2018 23:35
Analyze Method ample 5,775,114 Rows
Number of rows 5,775,114
Number of blocks allocated 490,556
Number of empty blocks 0
Average space 0
Chain count 0
Average row length 585
Partitioned NO
Parallel degree 1


NONUNIQUE Index AFRU~Z01

Column Name #Distinct

MANDT 2
CATSBELNR 964,686
STZHL 11,121

NONUNIQUE Index AFRU~Z02

Column Name #Distinct

BUDAT 5,792
WERKS 104


UNIQUE Index AFRU~0

Column Name #Distinct

MANDT 2
RUECK 3,146,901
RMZHL 80,338

Column statistics

Column #Distinct U #BU AVGL Data Type Len

ABARB 101 1 4 VARCHAR2 9
AENAM 632 1 3 VARCHAR2 36
ANZMA 1 1 2 NUMBER 22
APLFL 2 1 5 VARCHAR2 18
APLZL 541 1 9 VARCHAR2 24
ARBID 1146 1 9 VARCHAR2 24
AUERU 3 1 2 VARCHAR2 3
AUFNR 2167229 1 13 VARCHAR2 36
AUFPL 2203561 1 11 VARCHAR2 30
AUSOR 2 1 2 VARCHAR2 3
BELNR_IST 1 1 2 VARCHAR2 30
BELNR_UMB 1 1 2 VARCHAR2 30
BEMOT 2 1 3 VARCHAR2 6
BUDAT 5792 1 9 VARCHAR2 24
CANUM 1 1 5 VARCHAR2 12
CATSBELNR 964686 1 4 VARCHAR2 30
STZHL 11121 1 9 VARCHAR2 24

Add comment
10|10000 characters needed characters exceeded

  • We can't tell you because Oracle does a decision based on your data. Please post the EXPLAIN (do a trace via ST05, display it, and click Explain), the statistics on your table and indexes (click on the table name from the EXPLAIN).

    You may get more information in Oracle forums.

  • Get RSS Feed

5 Answers

  • Feb 10 at 08:40 AM

    There has been some excellent input already, and I also agree there is some peculiarity about your data and statistics that's causing Oracle to choose the index.

    I see your statistics are fairly recent, so they are likely not at fault. But I have another theory to offer: Is the ARBID field field generally filled or are many empty? It boils down to data distribution, which the statistics can sometimes misinterpret. If the uniqueness is 100% then it should choose a full table scan. But if 90% of rows are empty or you have a few values that occur many times (typical of status fields) then the uniqueness is very low, and statistically speaking the optimiser may decide that we're likely to hit a match on a non-indexed field quite easily because most of the values are similar. This is particularly an issue when searching for values in a mostly empty column.

    Also, just for interest, see if "UP TO 1 ROWS" behaves different to SELECT SINGLE. While we're at it, also try selecting without restrictions and variations on SELECT ... UP TO 10/100/1000 ROWS.

    Add comment
    10|10000 characters needed characters exceeded

    • Well I'd consider that a good result. Because it means that it's not because of your index.

      I would try deleting your Z-index. If it then underperforms, please note it on your OSS, because then I would class it a fault that SAP should fix.

  • Feb 08 at 07:01 AM

    What's wrong with creating a new index? Especially if its recommended in a note?

    It seems to me the issue is with your custom index. If you create your own index it can severely screw things up if it's not wisely chosen. Find out when the custom index was added, and for what purpose - maybe there's a single custom program where the developer decided it would be a good idea. Perhaps that program just needs rewriting and you can delete the custom index.

    You should contact Basis and make sure that the database statistics are up-to-date. If they're not, that can also slow things down.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 14 at 11:24 AM

    Hi,

    If you want to create a custom index specifically for this query use the columns in the given order: MANDT,ARBID.

    Oracle rarely chooses FULL TABLE SCAN, especially in a case of a big table, like this. There is a parameter that influences the choiche between index access and table access, this is optimizer_index_cost_adj. In the SAP world, we do not really tweak or play around with this. We have our own recommendation depending in case of OLTP or OLAP System Type is used.
    At the first look, for me it seems to be there is no corresponding index exists yet (if only AFRU~Z01 and AFRU~0 exists), so the optimizer is choosing a BAD index access, because it 'thinks' it is better to use a bad index rather then scanning all the table blocks.

    So, creating a new index with the given columns would help you, especially because all columns from the where condition is covered, so first filtering of the data can be done by scanning much less blocks, and by using the index all other values can be read from the table.


    Add comment
    10|10000 characters needed characters exceeded

    • Hi Krish,

      The parameter mentioned me has a great influence on how CBO decides between Full Table Scans and INdex Scans.

      More information is documented in below link:

      https://docs.oracle.com/database/121/REFRN/GUID-BA25DBA7-3826-48CA-849B-6D8E3326A1B4.htm#REFRN10143

      Based on our experience in SAP, there is 2 accepted values existing for this parameter.

      Either 20 or 100, depending on the SAP System type. For OLAP we set 20, and for OLTP we do not set (means the Oracle default is taken, which is 100).

      With my statement I wanted to say, that we have only these 2 values in SAP. The reason why we do not set any other value that, this parameter influence the whole system and any deviation from the SAP standard makes the whole SAP System crazy and so the performance goes down the toilett.

      The values 20 and 100 had been tested for years within SAP and only with these two values (again, depending on the SAP System Type) can we make absolutely sure that the owerall system perfomance is at the closest as we expects.

      But of course this does not mean that there won't be any performance problems happening. There are always exceptions, especially in IT and so in SAP as well.

  • Feb 07 at 08:13 PM

    Please discuss this with your Basis admin / DBA and if they have no other input then open an incident with SAP Support.

    With 5 mil records I'd also strongly suggest to consider data archiving some time soon.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 09 at 08:41 AM

    Could you run a SQL trace and post the "Explanation of SQL access path" display with the "Explain" button (Alternative, use ST04 or ST04n)

    Add comment
    10|10000 characters needed characters exceeded