Hi,

I have Install Sybase 12.5.3 on HP-Unix. I am getting regular warning on my production box.

01:00000:00161:2008/05/06 12:42:53.61 server Increase the config parameter 'number of open objects' to avoid descriptor reus
e. Reuse may result in performance degradation.

Here is an output
1> sp_countmetadata 'open objects'
2> go
There are 4780 user objects in all database(s), requiring 6535 Kbytes of memory.
The 'open objects' configuration parameter is currently set to 3500.
(return status = 0)

1> sp_configure "number of open objects"
2> go
Parameter Name Default Memory Used Config Value
Run Value Unit Type
------------------------------ ----------- ----------- ------------
----------- -------------------- ----------
number of open objects 500 4785 3500
3500 number dynamic

Then What is value of sp_configure "number of open objects"?


Regards,
Riyaz

The value in the 'run value' column is the value currently used. The column 'config value' is the value that is configured. For dynamic parameters this is always the same as 'run value', static parameters can have differing values.

Thanks martijnvs,

Then how to aviod following warning.
01:00000:00161:2008/05/06 12:42:53.61 server Increase the config parameter 'number of open objects' to avoid descriptor reus
e. Reuse may result in performance degradation.

Regards,
Riyaz

Increase the parameter "number of open objects":
Lots of info about these things can be found in the Sybase ASE-documentation: http://manuals.sybase.com/onlinebook...=68248;lang=en
Search on anything you want to know.

Hey, I wrote this stored proc to help some of my clients. Perhaps it will help you to. Let me know if it does!

sample output is below:

1> sp_metacheck
2> go

Metadata Configuration Review
-----------------------------

Number of Open Databases
============================
Current Configuration: 12
Total Number in ASE: 7
Max Number Used: 7
Current Active: 7
Number Reused: 0
--> Configuration OK!


Number of Open Objects
============================
Current Configuration: 200
Total Number in ASE: 662
Max Number Used: 243
Current Active: 242
Number Reused: 0

Recommendation:
======> Increase 'Number of Open Objects' to at least 662


Number of Open Indexes
============================
Current Configuration: 500
Total Number in ASE: 90
Max Number Used: 50
Current Active: 42
Number Reused: 0
--> Configuration OK!


thanks,

Chris McCartney
chris@peakperformancefinancial.com

flanders% more config.txt
use sybsystemprocs
go

if object_id('sp_metacheck') <> NULL
drop proc sp_metacheck
go

print "creating sp_metacheck proc"
print " "
go

-- -----------------------------------------
--
-- Written by: Chris McCartney, April 2008
--
-- chris@peakperformancefinancial.com
--
-- Feel free to use as you wish!
-- Let me know if there are any issues!
--
-- I would love to add more parameters to
-- this check, but I have not made the
-- time. Please feel free to add to this
-- procedure and please email me with your
-- changes!
--
-- thanks!
-- Chris
-- -----------------------------------------
create proc sp_metacheck
as
declare @n int
declare @dbid int
declare @oo_used int
declare @oo_conf int
declare @oo_max int
declare @oo_reused int
declare @oo_active int
declare @oi_used int
declare @oi_conf int
declare @oi_max int
declare @oi_reused int
declare @oi_active int
declare @od_used int
declare @od_conf int
declare @od_max int
declare @od_reused int
declare @od_active int
declare @msg varchar(200)

set nocount on
set proc_return_status off

select @oo_used = 0
select @oi_used = 0
select @od_used = 0

-- load temp table with IDnum and dbids
-- this temp table will remain the same throughout this proc
create table #tmp (num numeric(3,0) identity, dbid int)
insert #tmp select dbid from master..sysdatabases
-- get open objects info
-- ================================================== ===========================
select @n = count(*) from master..sysdatabases
while @n > 0
begin
select @dbid = dbid from #tmp where num = @n
select @oo_used = @oo_used + count_metadata(107, db_name(@dbid))
select @n = @n - 1
end
select @oo_conf = value from master..syscurconfigs where config = 107
select @oo_active = config_admin(22,107,2,0,"open_object_reuse_request s",null)
select @oo_max = config_admin(22,107,3,0,"open_object_reuse_request s",null)
select @oo_reused = config_admin(22,107,4,0,"open_object_reuse_request s",null)
-- get open indexes info
-- ================================================== ===========================
select @n = count(*) from master..sysdatabases
while @n > 0
begin
select @dbid = dbid from #tmp where num = @n
select @oi_used = @oi_used + count_metadata(263, db_name(@dbid))
select @n = @n - 1
end
select @oi_conf = value from master..syscurconfigs where config = 263
select @oi_active = config_admin(22,263,2,0,"open_index_reuse_requests ",null)
select @oi_max = config_admin(22,263,3,0,"open_index_reuse_requests ",null)
select @oi_reused = config_admin(22,263,4,0,"open_index_reuse_requests ",null)

-- get open databases info
-- ================================================== ===========================
select @od_used = count_metadata(105, 'master')
select @od_conf = value from master..syscurconfigs where config = 105
select @od_active = config_admin(22,105,2,0,"open_database_reuse_reque sts",null)
select @od_max = config_admin(22,105,3,0,"open_database_reuse_reque sts",null)
select @od_reused = config_admin(22,105,4,0,"open_database_reuse_reque sts",null)
-- display header
print " "
print " Metadata Configuration Review"
print " -----------------------------"

-- Open Databases Section
-- ************************************************** *********************
print " "
print " Number of Open Databases"
print " ============================"
select @msg = " Current Configuration: " + convert(varchar(8),@od_conf)
print @msg
select @msg = " Total Number in ASE: " + convert(varchar(8),@od_used)
print @msg
select @msg = " Max Number Used: " + convert(varchar(8),@od_max)
print @msg
select @msg = " Current Active: " + convert(varchar(8),@od_active)
print @msg
select @msg = " Number Reused: " + convert(varchar(8),@od_reused)
print @msg

if @od_reused > 0
begin
print " "
print " The 'Number of Open Databases' Parameter IS DEFINITELY Under Configured"
print " ================================================== ====================="
select @msg = " ASE had to reuse " + convert(varchar(8),@od_reused) + " open da
tabase"
select @msg = @msg + " structures due to being under configured! This will slow
performance!"
print @msg
print " "
print " Recommendation:"
select @msg = " ======> Increase 'Number of Open Databases' to " + convert(varch
ar(8),@od_used)
print @msg
print " "
end
else if @od_used > @od_conf
begin
print " The 'Number of Open Databases' Config Parameter May be Under Configured"
print " ================================================== ====================="
print " Recommendation:"
select @msg = " ======> Consider increasing ASE's 'Number of Open Databases' to
" + convert(varchar(8),@od_used)
print @msg
print " "
end
else
begin
print " --> Configuration OK!"
print " "
end

-- Open Objects Section
-- ************************************************** *********************
print " "
print " Number of Open Objects"
print " ============================"
select @msg = " Current Configuration: " + convert(varchar(8),@oo_conf)
print @msg
select @msg = " Total Number in ASE: " + convert(varchar(8),@oo_used)
print @msg
select @msg = " Max Number Used: " + convert(varchar(8),@oo_max)
print @msg
select @msg = " Current Active: " + convert(varchar(8),@oo_active)
print @msg
select @msg = " Number Reused: " + convert(varchar(8),@oo_reused)
print @msg

if @oo_reused > 0
begin
print " "
select @msg = " ASE had to reuse " + convert(varchar(8),@oo_reused) + " open ob
jects structures!"
print @msg
print " "
print " Recommendation:"
select @msg = " ======> Increase 'Number of Open Objects' to at least " + conver
t(varchar(8),@oo_used)
print @msg
print " "
end
else if @oo_used > @oo_conf
begin
print " "
print " Recommendation:"
select @msg = " ======> Increase 'Number of Open Objects' to at least " + conver
t(varchar(8),@oo_used)
print @msg
print " "
end
else
begin
print " --> Configuration OK!"
print " "
end
-- Open Indexes Section
-- ************************************************** *********************
print " "
print " Number of Open Indexes"
print " ============================"
select @msg = " Current Configuration: " + convert(varchar(8),@oi_conf)
print @msg
select @msg = " Total Number in ASE: " + convert(varchar(8),@oi_used)
print @msg
select @msg = " Max Number Used: " + convert(varchar(8),@oi_max)
print @msg
select @msg = " Current Active: " + convert(varchar(8),@oi_active)
print @msg
select @msg = " Number Reused: " + convert(varchar(8),@oi_reused)
print @msg

if @oi_reused > 0
begin
print " "
select @msg = " ASE had to reuse " + convert(varchar(8),@oi_reused) + " open in
dex structures!"
print @msg
print " "
print " Recommendation:"
select @msg = " ======> Increase 'Number of Open Indexes' to at least " + conver
t(varchar(8),@oi_used)
print @msg
print " "
end
else if @oi_used > @oi_conf
begin
print " Recommendation:"
select @msg = " ======> Consider increasing ASE's 'Number of Open Indexes' to at
least " + convert(varchar(8),@oi_used)
print @msg
print " "
end
else
begin
print " --> Configuration OK!"
print " "
end

drop table #tmp
go