Summary information |
|
|---|---|
Default value |
500 |
Range of values |
100–2147483647 |
Status |
Dynamic |
Display level |
Basic |
Required role |
System Administrator |
number of open objects sets the maximum number of objects that can be open simultaneously on Adaptive Server.
If you are planning to make a substantial change, such as loading databases with a large number of objects from another server, you can calculate an estimated metadata cache size by using sp_helpconfig. sp_helpconfig displays the amount of memory required for a given number of metadata descriptors, as well as the number of descriptors that can be accommodated by a given amount of memory. An object metadata descriptor represents the state of an object while it is in use, or cached between uses.
The default run value is 500. If this number is insufficient, Adaptive Server displays a message after trying to re-use active object descriptors. You will need to adjust this value.
To set the number of open objects parameter optimally:
Use sp_countmetadata to find the total number of object metadata cache descriptors. For example:
sp_countmetadata "open objects"
The best time to run sp_countmetadata is when there is little activity in the server. Running sp_countmetadata during a peak time can cause contention with other processes.
Suppose Adaptive Server reports the following information:
There are 340 user objects in all database(s), requiring 140.781 Kbytes of memory. The 'open objects' configuration parameter is currently set to 500
Configure the number of open objects parameter to that value, as follows:
sp_configure "number of open objects", 357
357 covers the 340 user objects, plus 5 percent to accommodate temporary tables.
This new configuration is only a start; the ideal size should be based on the number of active object metadata cache descriptors, not the total number of objects.
During a peak period, find the number of active metadata cache descriptors, for example:
sp_monitorconfig "open objects"
Usage information at date and time: Apr 22 2002 2:49PM. Name num_free num_active pct_act Max_Used Reused -------------- -------- --------- -------- -------- ------ number of open 160 357 71.40 397 No
In this example, 397 is the maximum number of object descriptors that have been used since the server was last started.
Configure the number of open objects to 397, plus 10 percent (40), for a total of 437:
sp_configure "number of open objects", 437
If there is a lot of activity on the server, for example, if tables are being added or dropped, run sp_monitorconfig periodically. You will need to reset the cache size as the number of active descriptors changes. See sp_monitorconfig in the Reference Manual for more information.
