Configure Adaptive Server to enable resource limits. Use allow resource limits configuration parameter:
sp_configure "allow resource limits", 1
1 enables the resource limits; 0 disables them. allow resource limits is static, so you must restart the server to reset the changes.
allow resource limits signals the server to allocate internal memory for time ranges, resource limits, and internal server alarms. It also internally assigns applicable ranges and limits to login sessions.
Setting allow resource limits to 1 also changes the output of showplan and statistics i/o, as follows:
showplan displays estimated I/O cost information for DML statements. The information displayed is the optimizer’s cost estimate for the query as a unitless number. The total estimated I/O cost is displayed for the query as a whole. This cost estimate is dependent on the table statistics (number and distribution of values) and the size of the appropriate buffer pools. It is independent of such factors as the state of the buffer pools and the number of active users. For more information, see “Messages describing access methods, caching, and I/O cost” on page 93 in Performance and Tuning: Monitoring and Analyzing.
statistics i/o includes the actual total I/O cost of a statement according to the optimizer’s costing formula. This value is a number representing the sum of the number of logical I/Os multiplied by the cost of a logical I/O and the number of physical I/Os multiplied by the cost of a physical I/O. For more information on these numbers, see “How Is “Fast” Determined?” in the Performance and Tuning Guide.
