SAP Knowledge Base Article - Preview

3289046 - Different query plan after applying SP03 PL12 - SAP ASE

Symptom

After applying  ASE 16.0 SP03 PL12 then it was noted procedures with different plans from ASE 16.0 SP03 PL10.

The following query:

======

select eadnr
from tab01 a (index ix01)
where a.status in ('gepland', 'vi uitgesteld', 'vi')
and a.col01 >= '20221017 00:00:00'
and a.col02 <= '20221017 23:59:59'

======

  • PLAN ASE 16.0 SP03 PL10

ROOT:EMIT Operator (VA = 4)
|
| |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
| |
| | |SCAN Operator (VA = 0)
| | | FROM OR List
| | | OR List has up to 3 rows of OR/IN values.
| |
| | |RESTRICT Operator (VA = 2)(5)(0)(0)(0)(0)
| | |
| | | |SCAN Operator (VA = 1)
| | | | FROM TABLE
| | | | tab01
| | | | a
| | | | Index : ix01
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Keys are:
| | | | col01 ASC
| | | | col02 ASC
| | | | col03 ASC
| | | | Using I/O Size 16 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | | Using I/O Size 16 Kbytes for data pages.
| | | | With LRU Buffer Replacement Strategy for data pages.


=======

  • PLAN ASE 16.0 SP03 PL12

QUERY PLAN FOR STATEMENT 2 (at line 1).
Optimized using Serial Mode
Optimized using the forced options (internally generated Abstract Plan).


STEP 1
The type of query is SELECT.

2 operator(s) under root

|ROOT:EMIT Operator (VA = 2)
|
|   |RESTRICT Operator (VA = 1)(5)(0)(0)(10)(0)
|   |
|   |   |SCAN Operator (VA = 0)
|   |   |  FROM TABLE
|   |   |  tab01
|   |   |  a
|   |   |  Index : ix01
|   |   |  Forward Scan.
|   |   |  Positioning by key.
|   |   |  Keys are:
|   |   |    col01 ASC
|   |   |    col02 ASC
|   |   |  Using I/O Size 16 Kbytes for index leaf pages.
|   |   |  With LRU Buffer Replacement Strategy for index leaf pages.
|   |   |  Using I/O Size 16 Kbytes for data pages.
|   |   |  With LRU Buffer Replacement Strategy for data pages.

=======

The data is the same and the index is: create nonclustered index ix01 on tab01 (col01, col02, col03).

In SP03 PL12, we can see that optimizer is not using all columns defined on index and it uses much more logical IO's.


Read more...

Environment

  • SAP Adaptive Server Enterprise 16.0 SP03 PL10
  • SAP Adaptive Server Enterprise 16.0 SP03 PL12

Keywords

bad different query plan optimizer more logical IOS ios performance index covered all columns NESTED LOOP JOIN inner join FROM OR LIST 827583 CR 827583 CR827583 CR#827583 , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem

About this page

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

Search for additional results

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