2423689 - rsdb/max_blocking_factor for slow "For All Entries" / large IN clause queries - SAP ASE Business Suite

You are running SAP applications on SAP ASE database.

You notice long-running sequential reads on large SAP tables causing performance degradation on the system.

The offending ABAP in these incidents contains the FOR ALL ENTRIES construct. The corresponding slow-running queries on the database, mainly reference several LIKE/OR/AND clauses with one or many IN predicate values as shown in the sample queries below:

SELECT DISTINCT * FROM "STKO" WHERE "MANDT" = ? AND ( ( "STLTY" = ?
AND "STLNR" = ? AND "STLAL" = ? AND "LKENZ" = ? AND "LOEKZ" = ? AND "STLST" IN (
? , ? ) ) OR ( "STLTY" = ? AND "STLNR" = ? AND "STLAL" = ? AND "LKENZ" = ? AND
"LOEKZ" = ? AND "STLST" IN ( ? , ? ) ) OR ( "STLTY" = ? AND "STLNR" = ? AND
"STLAL" = ? AND "LKENZ" = ? AND "LOEKZ" = ? AND "STLST" IN ( ? , ? ) ) OR (
"STLTY" = ? AND "STLNR" = ? AND "STLAL" = ? AND "LKENZ" = ? AND "L
OEKZ" = ? AND "STLST" IN ( ? , ? ) ) OR ( "STLTY" = ? AND "STLNR" = ? AND
"STLAL" = ? AND "LKENZ" = ? AND "LOEKZ" = ? AND "STLST" IN ( ? , ? ) ) OR (
"STLTY" = ? AND "STLNR" = ? AND "STLAL" = ? AND "LKENZ" = ? AND "LOEKZ" = ? AND
"STLST" IN ( ? , ? ) ) OR ( "STLTY" = ? AND "STLNR" = ? AND "STLAL" = ? AND
"LKENZ" = ? AND "LOEKZ" = ? AND "STLST" IN ( ? , ? ) ) OR ( "STLTY" = ? AND
"STLNR" = ? AND "STLAL" = ? AND "LKENZ" = ? AND "LOEKZ" = ? AND "STLST" IN
 ( ? , ? ) ) OR ( "STLTY" = ? AND "STLNR" = ? AND "STLAL" = ? AND "LKENZ" = ?
AND "LOEKZ" = ? AND "STLST" IN ( ? , ? ) ) OR ( "STLTY" = ? AND "STLNR" = ? AND
"STLAL" = ? AND "LKENZ" = ? AND "LOEKZ" = ? AND "STLST" IN ( ? , ? ) ) OR (
"STLTY" = ? AND "STLNR" = ? AND "STLAL" = ? AND "LKENZ" = ? AND "LOEKZ" = ? AND
"STLST" IN ( ? , ? ) ) OR ( "STLTY" = ? AND "STLNR" = ? AND "STLAL" = ? AND
"LKENZ" = ? AND "LOEKZ" = ? AND "STLST" IN ( ? , ? ) ) ) /* R3:SAP
LZPPM:6110 T:STKO M:100 */

 SELECT DISTINCT "STLTY" ,"STLNR" ,"STLAL" FROM "STKO" WHERE "MANDT" =
? AND ( ( "STLTY" = ? AND "STLNR" = ? ) OR ( "STLTY" = ? AND "STLNR" = ? ) OR (
"STLTY" = ? AND "STLNR" = ? ) OR ( "STLTY" = ? AND "STLNR" = ? ) OR ( "STLTY" =
? AND "STLNR" = ? ) OR ( "STLTY" = ? AND "STLNR" = ? ) OR ( "STLTY" = ? AND
"STLNR" = ? ) OR ( "STLTY" = ? AND "STLNR" = ? ) OR ( "STLTY" = ? AND "STLNR" =
? ) OR ( "STLTY" = ? AND "STLNR" = ? ) OR ( "STLTY" = ? AND
"STLNR" = ? ) OR ( "STLTY" = ? AND "STLNR" = ? ) OR ( "STLTY" = ? AND "STLNR" =
? ) OR ( "STLTY" = ? AND "STLNR" = ? ) OR ( "STLTY" = ? AND "STLNR" = ? ) OR (
"STLTY" = ? AND "STLNR" = ? ) OR ( "STLTY" = ? AND "STLNR" = ? ) OR ( "STLTY" =
? AND "STLNR" = ? ) OR ( "STLTY" = ? AND "STLNR" = ? ) OR ( "STLTY" = ? AND
"STLNR" = ? ) OR ( "STLTY" = ? AND "STLNR" = ? ) ) /* R3:SAPLCPSC:3966 T:STKO
M:100 */

Queries referencing SCM APO tables: /SAPAPO/MATLOC, /SAPAPO/MATMOD

Above queries can be isolated via any of the following methods:

• SAP system: ST05 or ST12 sql trace
• DBACockpit: Process Monitor (runtime view) and SQL Statement Monitor (historical view)

 If these jobs fail with SQL701 errors - run out of procedure cache please see SAP KBA 2231450

• SAP Adaptive Server Enterprise (ASE) 15.7 for Business Suite
• SAP Adaptive Server Enterprise (ASE) 16.0 for Business Suite
• SAP Enterprise Resource Planning (ERP) 6.0
• SAP Customer Relationship Management (CRM) 7.0
• SAP Supplier Relationship Management (SRM) 7.0
• SAP Supply Chain Management (SCM) 7.0
• SAP Solution Manager (SOLMAN) 7.1
• SAP NetWeaver (NW) - All versions 
• SAP BW SCM APO

FAE, "For All Entries", "IN clause" , "slow query" , KBA , BC-DB-SYB , Business Suite on Adaptive Server Enterprise , BW-SYS-DB-SYB , BW on Adaptive Server Enterprise , Problem