Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

Calculating values for -tablerangesize and -indexrangesize

« Go Back

Information

 
Article Number000048306
EnvironmentProduct: OpenEdge
Version: All supported versions
OS: All supported platforms
Other: VST
Question/Problem Description
How to verify the current values of -basetable -tablerangesize ?
How many indexes statistics are currently being tracked by -baseindex -indexrangesize ?
How to scope database startup parameters: -tablerangesize and -indexrangesize for the current database's Schema?
What do I need to track all table and index Activity and Status?
What are the number of tables needed to collect _TableStat statistics?
Where can I find the number of indexes needed to track all _IndexStat statistics?
Steps to Reproduce
Clarifying Information
Database startup parameters for collecting _TableStat, _UserTableStat and _IndexStat, _UserIndexStat statistics:
  • -basetable -tablerangesize
  • -baseindex -indexrangesize
Error Message
Defect/Enhancement Number
Cause
Resolution
The following sample ABL code needs to be run against an online database in order to find the current values of the following database startup parameters against the current schema definitions in order to collect _TableStat, _UserTableStat and _IndexStat, _UserIndexStat statistics.  OpenEdge 10.1B and later introduced user level statistics for tables (_UserTableStat) and indexes (_UserIndexStat).   
  • -basetable <starting table number> -tablerangesize <number_of_tables>
  • -baseindex <starting index number> -indexrangesize <number_of_indexes>
Example ABL Code:
RUN CheckStatBase.

DEFINE VARIABLE vMinTableID AS INTEGER NO-UNDO.
DEFINE VARIABLE vMaxTableID AS INTEGER NO-UNDO.
DEFINE VARIABLE vMinIndexID AS INTEGER NO-UNDO.
DEFINE VARIABLE vMaxIndexID AS INTEGER NO-UNDO.

/* ------------------------------------------------------------------------- */
PROCEDURE CheckStatBase.

/* Check vMaxTableID: */
FOR LAST DICTDB._TableStat NO-LOCK.
    FOR EACH DICTDB._File NO-LOCK
        WHERE DICTDB._File._File-Number LT 32768 /* exclude SQL92 tables */
        BY DICTDB._File._File-Number DESCENDING:

ASSIGN vMaxTableID = MIN(DICTDB._TableStat._TableStat-id,
                                DICTDB._File._File-Number).

IF DICTDB._TableStat._TableStat-id LT DICTDB._File._File-Number  THEN
    MESSAGE
    "Statistics for tables with numbers higher than"
    DICTDB._TableStat._TableStat-id SKIP
    "will be missed."
    "To get full statistics start a database with" SKIP
    "-tablerangesize"
      ( DICTDB._File._File-Number
      - DICTDB._TableStat._TableStat-ID
      + INTEGER(RECID(DICTDB._TableStat))
      ) VIEW-AS ALERT-BOX WARNING BUTTONS OK.

        ELSE MESSAGE
            "CURRENT -tablerangesize SET TO:" DICTDB._TableStat._TableStat-id  SKIP
            "Which IS sufficient FOR CURRENT SCHEMA Tables: " vMaxTableID
             VIEW-AS ALERT-BOX INFO BUTTONS OK.
        LEAVE.
    END. /* FOR EACH _File */
END. /* FOR LAST _TableStat */


/* Check vMinTableID: */
FOR FIRST DICTDB._TableStat NO-LOCK.
    FOR EACH DICTDB._File NO-LOCK
        WHERE DICTDB._File._File-Number GT 0
        BY DICTDB._File._File-Number /* ASCENDING */:

        ASSIGN vMinTableID = MAX(DICTDB._TableStat._TableStat-id,
                               DICTDB._File._File-Number).

        IF DICTDB._TableStat._TableStat-id GT DICTDB._File._File-Number  THEN
            MESSAGE
            "Statistics for tables with numbers lower than"
            DICTDB._TableStat._TableStat-id SKIP
            "will be missed."
            "To get full statistics start a database with" SKIP
            "-basetable" DICTDB._File._File-Number
            VIEW-AS ALERT-BOX WARNING BUTTONS OK.

                ELSE MESSAGE
                    "-basetable = " vMinTableID " which IS sufficient FOR CURRENT SCHEMA"
                        VIEW-AS ALERT-BOX INFO BUTTONS OK.
            LEAVE.
    END. /* FOR EACH _File */
END. /* FOR FIRST _TableStat */


/* Check vMaxIndexID: */
FOR LAST DICTDB._IndexStat NO-LOCK.
    FOR EACH DICTDB._Index NO-LOCK
        BY DICTDB._Index._Idx-num DESCENDING:

        ASSIGN vMaxIndexID = MIN(DICTDB._IndexStat._IndexStat-id,
                         DICTDB._Index._Idx-num).

        IF DICTDB._IndexStat._IndexStat-id LT DICTDB._Index._Idx-num  THEN
            MESSAGE
            "Statistics for indices with numbers higher than"
            DICTDB._IndexStat._IndexStat-id SKIP
            "will be missed."
            "To get full statistics start a database with" SKIP
            "-indexrangesize"
            ( DICTDB._Index._Idx-num
              - DICTDB._IndexStat._IndexStat-id  /*-baseindex = _IndexStat-id - recid*/
              + INTEGER(RECID(DICTDB._IndexStat))
              ) VIEW-AS ALERT-BOX WARNING BUTTONS OK.

                ELSE MESSAGE
            "CURRENT -indexrangesize SET TO:" DICTDB._IndexStat._IndexStat-id  SKIP
            "Which IS sufficient FOR CURRENT SCHEMA Indexes: " vMaxIndexID
             VIEW-AS ALERT-BOX INFO BUTTONS OK.

                LEAVE.
    END. /* FOR EACH _Index */
END. /* FOR LAST _IndexStat */

/* Check vMinIndexID: */
FOR FIRST DICTDB._IndexStat NO-LOCK.
    FOR EACH DICTDB._Index NO-LOCK
        WHERE DICTDB._Index._Idx-num GT 0
        BY DICTDB._Index._Idx-num /* ASCENDING */:

        ASSIGN vMinIndexID = MAX(DICTDB._IndexStat._IndexStat-id,
                                 DICTDB._Index._Idx-num).

        IF DICTDB._IndexStat._IndexStat-id GT DICTDB._Index._Idx-num  THEN
            MESSAGE
            "Statistics for indices with numbers lower than"
            DICTDB._IndexStat._IndexStat-id SKIP
            "will be missed."
            "To get full statistics start a database with" SKIP
            "-baseindex" DICTDB._Index._Idx-num
            VIEW-AS ALERT-BOX WARNING BUTTONS OK.

                ELSE MESSAGE
                    "-baseindex = " vMinIndexID " which IS sufficient FOR CURRENT SCHEMA"
                    VIEW-AS ALERT-BOX INFO BUTTONS OK.
        LEAVE.
    END. /* FOR EACH _Index */
END. /* FOR FIRST _IndexStat */

END PROCEDURE. /* CheckStatBase */


EXAMPLE output:
 
Statistics for tables with numbers higher than 50 will be missed. (current -tablerangesize = 50)
To get full statistics start the database with -tablerangesize 913 (total table objects for current schema = 913)

basetable = 1 is sufficient for current schema

Statistics for indices with numbers higher than 50 will be missed. (current -indexrangesize = 50)
To get full statistics start the database with -indexrangesize 1093 (total index objects for current schema = 1093)

baseindex = 1 is sufficient for current schema

 
Once these database startup parmeters have been correctly sized, review the related -omsize database startup parameter.  Refer to Article 20398, How To set Storage Object Cache Size (-omsize)  
Workaround
Notes
Attachment 
Last Modified Date11/23/2016 2:21 PM