2232213 - resource limit fails to estimate actual I/Os simultaneously - SAP ASE | SAP Knowledge Base Article

SAP Knowledge Base Article - Preview

2232213 - resource limit fails to estimate actual I/Os simultaneously - SAP ASE

Symptom

When setting 2 enforced resource limit (estimate & actual I/Os) for the same login and application, only the first one works.

1> sp_add_resource_limit sa, null, 'at all times', 'io_cost', 400000,1,4,1
2> go
New resource limit created.

1> sp_add_resource_limit sa, null, 'at all times', 'io_cost', 500000,2,4,1
2> go
New resource limit created.

1> sp_help_resource_limit
2> go
 name appname rangename    rangeid limitid limitvalue enforced action scope
 ---- ------- ------------ ------- ------- ---------- -------- ------ -----
 sa   NULL    at all times       1       1     400000        1      4     1     <===   estimate IO
 sa   NULL    at all times       1       1     500000        2      4     1     <===   actual IO

<Turn on showplan & statistics io to keep track of estimate & actual I/Os>

1> set showplan on
2> go
1> set statistics on
2> go
1> select * into target from li_test, wang_test, sun_test
2> go

QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Serial Mode

 
    STEP 1
        The type of query is CREATE TABLE.

    STEP 2
        The type of query is INSERT.

        5 operator(s) under root

Total estimated I/O cost for statement 1 (at line 1): 355963.          <===  estimated I/O cost is 355963
(636056 rows affected)

The estimated I/O cost does not exceed its estimate resource limit which is 400000.

Table: target scan count 0, logical reads: (regular=636625 apf=0 total=636625),
physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: li_test scan count 1, logical reads: (regular=1 apf=0 total=1), physical
reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: wang_test scan count 86, logical reads: (regular=86 apf=0 total=86),
physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: sun_test scan count 7396, logical reads: (regular=7396 apf=0 total=7396),
physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total actual I/O cost for this command: 1288216.                       <====   actual I/O cost is 1288216
Total writes for this command: 6072
(636056 rows affected)

This indicates total actual I/O cost has exceeded its coresponding resource limit(500000) but the query still can be run successfully.


Read more...

Environment

  • SAP Adaptive Server Enterprise (ASE)
  • Dataserver

Product

SAP Adaptive Server Enterprise 15.7

Keywords

resource limit, resource limitation, resource cost control, I/O, CR790695, CR#790695, 790695 , 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 ONE Support launchpad (Login required).

Search for additional results

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