SAP Knowledge Base Article - Preview

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

Symptom

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


Read more...

Environment

  • 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

Product

SAP Solution Manager 7.2

Keywords

KBA , BC-DB-SYB , SAP Business Suite on Sybase ASE Database Platform , BW-SYS-DB-SYB , BW on Sybase ASE Database Platform , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.