Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    22

    Unanswered: how to set number of open objects

    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

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    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.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Mar 2008
    Posts
    22
    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

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Increase the parameter "number of open objects":
    Code:
    sp_configure "numbr of open objects", <value>
    go
    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.
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    May 2008
    Posts
    4

    Try this stored proc....

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •