Optimizing number of open objects

If the default number of open objects is insufficient, SAP ASE displays a message after trying to reuse active object descriptors.

1. Use sp_countmetadata to find the total number of object metadata cache descriptors:

   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 SAP ASE reports this information:

   There are 1340 user objects in all database(s), 
   requiring 2894 Kbytes of memory. The 'open objects'
   configuration parameter is currently set to 500.

2. Configure number of open objects to account for the number of open objects:

   sp_configure "number of open objects", 1407

   1407 covers the 1340 user objects, plus 5 percent to accommodate temporary tables.

   This new configuration is only a starting point; base the ideal number on the active object metadata cache descriptors, not the total number of objects.

3. During a peak period, find the number of active metadata cache descriptors:

   sp_monitorconfig "open objects"

   Usage information at date and time: Aug 20 2007  1:32PM..
   Name                   Num_free  Num_active  Pct_act  Max_Used  Reuse_cnt
      Instance_Name
   ---------------------  --------  ----------  -------  --------  ---------
      --------------------
   number of open objects      560         847    71.40       1497         0
      NULL

   In this example, 1497 is the maximum number of object descriptors that have been used since the server was last started.

4. Configure the number of open objects to 1497, plus 10 percent (150), for a total of 1647:

   sp_configure "number of open objects", 1647