SAP Knowledge Base Article - Preview

3290990 - Performance issue when procedure access a view after applying SP04 PL02 - SAP ASE

Symptom

Procedure runs fine on ASE 16.0 SP02 PL05 HF2 but it doesn't end in ASE 16 SP04 PL02.

The procedure  references a view.

There are differences in the plan between versions:

======

  • SP04 PL02

       |   |   |   |   |   |   |
       |   |   |   |   |   |   |SCAN Operator (VA = 0)
       |   |   |   |   |   |   |  FROM TABLE
       |   |   |   |   |   |   |  db01.db0.tb01
       |   |   |   |   |   |   |  VwTb01
       |   |   |   |   |   |   |  Table Scan.
       |   |   |   |   |   |   |  Forward Scan.
       |   |   |   |   |   |   |  Positioning at start of table.
       |   |   |   |   |   |   |  Using I/O Size 128 Kbytes for data pages.
       |   |   |   |   |   |   |  With MRU Buffer Replacement Strategy for data pages.

  • SP02 PL05 HF2

       |   |   |   |   |   |   |   |   |   |   |   |   |   |
       |   |   |   |   |   |   |   |   |   |   |   |   |   |SCAN Operator (VA = 1)
       |   |   |   |   |   |   |   |   |   |   |   |   |   |  FROM TABLE
       |   |   |   |   |   |   |   |   |   |   |   |   |   |  db01.db0.tb01
       |   |   |   |   |   |   |   |   |   |   |   |   |   |  VwTb01
       |   |   |   |   |   |   |   |   |   |   |   |   |   |  Using Clustered Index.
       |   |   |   |   |   |   |   |   |   |   |   |   |   |  Index : <index>
       |   |   |   |   |   |   |   |   |   |   |   |   |   |  Forward Scan.
       |   |   |   |   |   |   |   |   |   |   |   |   |   |  Positioning by key.
       |   |   |   |   |   |   |   |   |   |   |   |   |   |  Keys are:
       |   |   |   |   |   |   |   |   |   |   |   |   |   |    col01 ASC
       |   |   |   |   |   |   |   |   |   |   |   |   |   |    col02 ASC
       |   |   |   |   |   |   |   |   |   |   |   |   |   |    cold03 ASC
       |   |   |   |   |   |   |   |   |   |   |   |   |   |    cold04 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.

======

This doesn't occur if using base table instead of using view.


Read more...

Environment

  • SAP Adaptive Server Enterprise 16.0 SP02 PL05 HF2
  • SAP Adaptive Server Enterprise 16.0 SP04 PL02

Product

SAP Adaptive Server Enterprise 16.0

Keywords

bad different query plan optimizer table scan view materialize trace flag traceflag TF 4419 -T4419 827616 CR 827616 CR827616 CR#827616 , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Product Enhancement

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.