Skip to Main Content
  • Questions

  • SQL execution plan - access predicates determination

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Eric.

Asked: June 20, 2017 - 4:22 pm UTC

Last updated: July 06, 2017 - 11:44 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hello Tom,

I know Tom has retired. But glad that a group of Oracle expertise help to keep operation of AskTom. It is indeed very helpful.

I was facing a SQL plan issue in an SAP/Oracle Environment. The SQL is: 

SELECT
  DISTINCT "MATNR","VKORG","VTWEG","MTPOS"
FROM
  "MVKE"
WHERE
  "MANDT"=:A0 AND (("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A4 AND "VTWEG"=:A5) OR ("MATNR" IN
  (:A1,:A2,:A3) AND "VKORG"=:A9 AND "VTWEG"=:A10) OR ("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A14 AND
  "VTWEG"=:A15) OR ("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A19 AND "VTWEG"=:A20) OR ("MATNR" IN
  (:A1,:A2,:A3) AND "VKORG"=:A24 AND "VTWEG"=:A25))


The MVKE has a primary index - MVKE~0 which consists table fields of MANDT, MATNR, VKORG, VTWEG and in that order. THe oracle execution plan was Oracle was using MVKE~0 index range scan as expected and all other fields in where-clause were used as "filter predicates".. but access predicates has only one field of MANDT. The problem is that mandt is a "client" field in SAP world with only 1 distinct value but mantnr field has hundreds and thousands of values which is the most selective fields in this index. This led to our performance issue. The issue was fixed now with some Oracle static parameters changes like SGA size by ERP Oracle team. What was leading to our original problem? What Oracle Static parameters have higher weight on "access predicates"? I googled internet but my questions remain.

Thanks a lot.

Following is the new plan using proper access predicates( old plan using "mandt" as access predicates has only two steps)

SQL Statement
----------------------------------------------------------------------------------------------------------------------
SELECT
  DISTINCT "MATNR","VKORG","VTWEG","MTPOS"
FROM
  "MVKE"
WHERE
  "MANDT"=:A0 AND (("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A4 AND "VTWEG"=:A5) OR ("MATNR" IN
  (:A1,:A2,:A3) AND "VKORG"=:A9 AND "VTWEG"=:A10) OR ("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A14 AND
  "VTWEG"=:A15) OR ("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A19 AND "VTWEG"=:A20) OR ("MATNR" IN
  (:A1,:A2,:A3) AND "VKORG"=:A24 AND "VTWEG"=:A25))


Execution Plan

----------------------------------------------------------------------------------------------------------------------
Explain from v$sql_plan not possible -> Explain from PLAN_TABLE is displayed !
No values in v$sql_plan for Address: 00000006860DF8F0 Hash_value:  1957305630 Child_number:  0 Instance_ID: 1
Sql_id:

System: F6R
Plan hash value: 1827367495

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     5 |   180 |     8  (13)| 00:00:01 |
|   1 |  CONCATENATION                |        |       |       |            |          |
|   2 |   INLIST ITERATOR             |        |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| MVKE   |     1 |    36 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | MVKE~0 |     1 |       |     1   (0)| 00:00:01 |
|   5 |   INLIST ITERATOR             |        |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| MVKE   |     1 |    36 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | MVKE~0 |     1 |       |     1   (0)| 00:00:01 |
|   8 |   INLIST ITERATOR             |        |       |       |            |          |
|   9 |    TABLE ACCESS BY INDEX ROWID| MVKE   |     1 |    36 |     1   (0)| 00:00:01 |
|* 10 |     INDEX UNIQUE SCAN         | MVKE~0 |     1 |       |     1   (0)| 00:00:01 |
|  11 |   INLIST ITERATOR             |        |       |       |            |          |
|  12 |    TABLE ACCESS BY INDEX ROWID| MVKE   |     1 |    36 |     1   (0)| 00:00:01 |
|* 13 |     INDEX UNIQUE SCAN         | MVKE~0 |     1 |       |     1   (0)| 00:00:01 |
|  14 |   INLIST ITERATOR             |        |       |       |            |          |
|  15 |    TABLE ACCESS BY INDEX ROWID| MVKE   |     1 |    36 |     1   (0)| 00:00:01 |
|* 16 |     INDEX UNIQUE SCAN         | MVKE~0 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - SEL$1
   3 - SEL$1_1 / MVKE@SEL$1
   4 - SEL$1_1 / MVKE@SEL$1
   6 - SEL$1_2 / MVKE@SEL$1_2
   7 - SEL$1_2 / MVKE@SEL$1_2
   9 - SEL$1_3 / MVKE@SEL$1_3
  10 - SEL$1_3 / MVKE@SEL$1_3
  12 - SEL$1_4 / MVKE@SEL$1_4
  13 - SEL$1_4 / MVKE@SEL$1_4
  15 - SEL$1_5 / MVKE@SEL$1_5
  16 - SEL$1_5 / MVKE@SEL$1_5

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("MANDT"=:A0 AND ("MATNR"=:A1 OR "MATNR"=:A2 OR "MATNR"=:A3) AND
              "VKORG"=:A24 AND "VTWEG"=:A25)
   7 - access("MANDT"=:A0 AND ("MATNR"=:A1 OR "MATNR"=:A2 OR "MATNR"=:A3) AND
              "VKORG"=:A19 AND "VTWEG"=:A20)
       filter(LNNVL("VKORG"=:A24) OR LNNVL("VTWEG"=:A25) OR LNNVL("MATNR"=:A1)
              AND LNNVL("MATNR"=:A2) AND LNNVL("MATNR"=:A3))
  10 - access("MANDT"=:A0 AND ("MATNR"=:A1 OR "MATNR"=:A2 OR "MATNR"=:A3) AND
              "VKORG"=:A14 AND "VTWEG"=:A15)
       filter((LNNVL("VKORG"=:A19) OR LNNVL("VTWEG"=:A20) OR LNNVL("MATNR"=:A1)
              AND LNNVL("MATNR"=:A2) AND LNNVL("MATNR"=:A3)) AND (LNNVL("VKORG"=:A24) OR
              LNNVL("VTWEG"=:A25) OR LNNVL("MATNR"=:A1) AND LNNVL("MATNR"=:A2) AND
              LNNVL("MATNR"=:A3)))
  13 - access("MANDT"=:A0 AND ("MATNR"=:A1 OR "MATNR"=:A2 OR "MATNR"=:A3) AND
              "VKORG"=:A9 AND "VTWEG"=:A10)
       filter((LNNVL("VKORG"=:A14) OR LNNVL("VTWEG"=:A15) OR LNNVL("MATNR"=:A1)
              AND LNNVL("MATNR"=:A2) AND LNNVL("MATNR"=:A3)) AND (LNNVL("VKORG"=:A19) OR
              LNNVL("VTWEG"=:A20) OR LNNVL("MATNR"=:A1) AND LNNVL("MATNR"=:A2) AND
              LNNVL("MATNR"=:A3)) AND (LNNVL("VKORG"=:A24) OR LNNVL("VTWEG"=:A25) OR
              LNNVL("MATNR"=:A1) AND LNNVL("MATNR"=:A2) AND LNNVL("MATNR"=:A3)))
  16 - access("MANDT"=:A0 AND ("MATNR"=:A1 OR "MATNR"=:A2 OR "MATNR"=:A3) AND
              "VKORG"=:A4 AND "VTWEG"=:A5)
       filter((LNNVL("VKORG"=:A9) OR LNNVL("VTWEG"=:A10) OR LNNVL("MATNR"=:A1)
              AND LNNVL("MATNR"=:A2) AND LNNVL("MATNR"=:A3)) AND (LNNVL("VKORG"=:A14) OR
              LNNVL("VTWEG"=:A15) OR LNNVL("MATNR"=:A1) AND LNNVL("MATNR"=:A2) AND
              LNNVL("MATNR"=:A3)) AND (LNNVL("VKORG"=:A19) OR LNNVL("VTWEG"=:A20) OR
              LNNVL("MATNR"=:A1) AND LNNVL("MATNR"=:A2) AND LNNVL("MATNR"=:A3)) AND
              (LNNVL("VKORG"=:A24) OR LNNVL("VTWEG"=:A25) OR LNNVL("MATNR"=:A1) AND
              LNNVL("MATNR"=:A2) AND LNNVL("MATNR"=:A3)))




and we said...

Hi Eric,

Can you share a little more information regarding you question?

Can you send the original execution plan and the list of initialization parameters that were changed?

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Eric Hu, June 21, 2017 - 2:56 pm UTC

Hello Maria,

Appreciated the quick response. Our Oracle team lead gave me a list of parameters changed: _FIX_CONTROL, SGA_MAX_SIZE and DB_CACHE_SIZE. DBA_CACHE_SIZE was increased from 1GB to 20+GB
I did not capture original plan details in text format but screen shot. But I got some info of old plan via plan history. and copied the relevant info from my screen shot. details below.

I would appreciate it any general guidance on how Oracle determine what field of where-clause is used as access predicates in addition to this specific case. Oracle CBO might think old plan ( access predicate is part of an execution plan) is better. But would still prefer to have technical inside on what was leading to that Oracle behavior..

Many thanks,

Eric

Following is what I got from plan history via SAP tool -
System: F6R
SQL_ID 7at4yzgzdjt4r
--------------------
SELECT DISTINCT "MATNR","VKORG","VTWEG","MTPOS" FROM "MVKE" WHERE
"MANDT"=:A0 AND (("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A4 AND
"VTWEG"=:A5) OR ("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A9 AND
"VTWEG"=:A10) OR ("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A14 AND
"VTWEG"=:A15) OR ("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A19 AND
"VTWEG"=:A20) OR ("MATNR" IN (:A1,:A2,:A3) AND "VKORG"=:A24 AND
"VTWEG"=:A25))

Plan hash value: 3448110256

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9533 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| MVKE | 1 | 36 | 9532 (1)| 00:00:01 |
| 2 | INDEX RANGE SCAN | MVKE~0 | 1 | | 9532 (1)| 00:00:01 |
--------------------------------------------------------------------------------------

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

1 - SEL$1 / MVKE@SEL$1
2 - SEL$1 / MVKE@SEL$1

In the original plan, the access predicates : "mandt"= A0.
Filter Predicates (("VKORG"=:A4 and "VTWEG"=:A5 AND INTERNAL_FUNCTION("MATNR") OR( "VKORG" =:A9...) Or ..("VKORG"=A24 AND "VTWEG" = "A25" AND INTERNAL_FUNCTION("MATNR")))

Oracle Fix_Control parameter change details:
NAME comment RECOMMENDATION I U REMARK IS_SET IS_VALUE SHOULD_BE_VALUE
_fix_control (18405517) to be adjusted change value to "18405517:2" 2 p   Y 18405517:ON 18405517:2
_fix_control (20355502) to be adjusted change value to "20355502:8" 2 p reduces parse time with OR-expansion Y 20355502:ON 20355502:8
_fix_control (4483286) to be adjusted check why set but mentioned with other prerequisites/not mentioned in note Y 4483286:OFF  
_fix_control (5842686) to be adjusted check why set but mentioned with other prerequisites/not mentioned in note Y 5842686:OFF  
Maria Colgan
July 06, 2017 - 11:44 pm UTC

Hi Eric,

Thanks for sharing the additional information, as it helps explain the change in behavior you observed.

The where clause predicates in a query can be implement in one of two ways: as an ACCESS predicate or as a FILTER predicate.

An ACCESS predicates are used to fetch the relevant blocks by applying search criteria to the appropriate columns. While FILTER predicates are evaluated after the blocks have been fetched.

Increasing the size of the SGA or the buffer cache will not change how a where clause predicate is treat (either as a FILTER predicate or an ACCESS predicate).

However, setting _fix_control = 18405517:2 will change how a where clause predicate that contains an IN-LIST with binds is treated, when bind peeking is disabled.

Setting this parameter enables the fix for Bug 18405517, which changes how the selectivity for a where clause predicate that contains an IN-LIST with binds is calculated, when bind peeking is disabled. Thus allowing it to be considered as an access predicate instead of a filter predicate.

Eric Hu, June 26, 2017 - 2:51 pm UTC

Hello Maria,

Many thanks.. That is very helpful.

Does Oracle has document to show a list of known Oracle bugs and their impacts for a specific version related to Oracle SQL execution performance? If so, where can I find such document?

Eric
Connor McDonald
June 28, 2017 - 12:46 am UTC

support.oracle.com

:-)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.