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
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.