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 for Me (Login required).

Search for additional results

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