Startup Parameters

[Jack@dba]

Hi All,

When I am gathering information for startup parameters from progress.some parameters I not able to understand like basetable,baseindex,tablerange size and indexrange size.what type of statistics we can collect from table and index.
I gone through the previous post for basetable base index..... But ...I not able to understand.

What is base table and base index
What is table and index range size
How can we recommend best values for these parameters.

Kinldy help on this...

Our Db size is 50gb 11.2 Enterprise edition and Environment is IBM Aix 5.1.

Thanks....

 

[Rob Fitzpatrick]

Table definitions, for both system and applications tables, are stored in the _File table. Index definitions are stored in _Index. Each table and index has a unique number that identifies it.

CRUD (Create/Read/Update/Delete) statistics for tables and indexes are written to the statistics virtual system tables (VSTs). Database-wide statistics, since the DB was opened, are written to the _TableStat and _IndexStat VSTs. Per-user statistics, for the users who are currently logged in, are written to the _UserTableStat and _UserIndexStat VSTs. Each record in these VSTs contains a table number or index number; you can use this number to query the related _File or _Index record to get the name of the object.

These VSTs don't necessarily contain records for all tables and indexes. You use the statistics startup parameters to determine which objects will have CRUD stats in the statistics VSTs. In other words, the values of these parameters determine how many records these tables contain, and also what their starting points are.

The default values of -basetable and -baseindex are 1; they correspond to a table number and an index number. The defaults for -tablerangesize and -indexrangesize are 50. They tell the database how many contiguous object numbers should have their statistics tracked. With these defaults, you will have access to statistics for tables 1 through 50 (if they exist) and indexes 1 through 50. So if you actually had 55 tables in your database, numbered 1 through 55, you would have no statistics for tables 51 to 55.

Typically you would leave the values of -basetable and -baseindex at their defaults, assuming you only want statistics for application objects. Application table numbers start at 1; application index numbers start at 8. If you use the defaults for those then you would want to set -tablerangesize to at least the number of the highest-numbered table, and -indexrangesize to at least the highest index number.

To find these numbers:

find last dictdb._file no-lock where _file._tbl-type = "T" use-index _file-number no-error. 
find last dictdb._index no-lock where not _index._index-name begins "_" use-index _index-number no-error.
display _file._file-number _index._idx-num.

Depending on how much your schema changes and how often you restart your database you might want to set the *rangesize parameters higher than you need. If you do that then newly-added tables or indexes, created via online schema changes, will have available statistics without having to restart the database.

 

[Rob Fitzpatrick]

You might wonder why you would ever use non-default values for -basetable or -baseindex. Sometimes it can be interesting to examine your CRUD stats for system objects. Some applications can do a surprising amount of I/O against them, e.g. due to compiles or dynamic queries.

All system tables and some system indexes have negative object numbers. So for example if you wanted to get CRUD statistics for the _StorageObject table (table number -70), you would set -basetable to -70. You would also increase your value for -tablerangesize by 71, to keep the top of the statistics range at the same value.

Note: if you are using OE Explorer/OE Management to configure your DBs (not likely as you are on AIX), they have a bug where they won't let you set non-negative values for -basetable or -baseindex. In that case I leave them at the default and put my -basetable and -baseindex parameters in the "other arguments" field.

 

[Rob Fitzpatrick]

Simple code for reading table CRUD:

for each dictdb._tablestat no-lock by _tablestat-read descending:
  find dictdb._file no-lock where _file._file-number = _tablestat._tablestat-id no-error.
  if not available ( _file ) then next.
  display
  _tablestat._tablestat-id
  label  "Table#"
  format "->>>>9"
  _file._file-name
  label  "Table name"
  format "x(32)"
  _tablestat._tablestat-create
  label  "Creates"
  format ">>,>>>,>>9"
  _tablestat._tablestat-read
  label  "Reads"
  format ">>,>>>,>>>,>>9"
  _tablestat._tablestat-update
  label  "Updates"
  format ">>,>>>,>>9"
  _tablestat._tablestat-delete
  label  "Deletes"
  format ">>,>>>,>>9"
  .
end.

Output:

┌───────────────────────────────────────────────────────────────────────────────────────┐
│Table# Table name                          Creates          Reads    Updates    Deletes│
│────── ──────────────────────────────── ────────── ────────────── ────────── ──────────│
│     1 Invoice                                   0              0          0          0│
│     2 Customer                                  0              0          0          0│
│     3 Item                                      0              0          0          0│
│     4 Order                                     0              0          0          0│
│     5 Order-Line                                0              0          0          0│
│     6 Salesrep                                  0              0          0          0│
│     7 State                                     0              0          0          0│
│     8 Local-Default                             0              0          0          0│
│     9 Ref-Call                                  0              0          0          0│

 

[Rob Fitzpatrick]

Simple code for reading index CRUD:

for each dictdb._indexstat no-lock by _indexstat._indexstat-read descending:
  find dictdb._index no-lock where _index._idx-num = _indexstat._indexstat-id no-error.
  if not available( _index ) then next.
  find dictdb._file no-lock where recid( _file ) = _index._file-recid no-error.
  display
  _file._file-number 
  column-label "Table#"
  format "->>>>9"
  _file._file-name 
  column-label "Table name"
  format "x(32)"
  _indexstat._indexstat-id
  column-label "Index#"
  format "->>>>9"
  _index._index-name 
  column-label "Index name"
  format "x(32)"
  if recid( _index ) = _file._prime-index then "P" else " "
  column-label "P"
  format "x"
  if _index._unique then "U" else ""
  column-label "U"
  format "x"
  _indexstat._indexstat-create
  column-label "Creates"
  format ">>,>>>,>>9"
  _indexstat._indexstat-read
  column-label "Reads"
  format ">>,>>>,>>>,>>9"
  _indexstat._indexstat-delete
  column-label "Deletes"
  format ">>,>>>,>>9"
  .
end.

Output:

+------------------------------------------------------------------------------------------------------------------------+
¦Table# Table name                       Index# Index name                       P U    Creates          Reads    Deletes¦
¦------ -------------------------------- ------ -------------------------------- - - ---------- -------------- ----------¦
¦    -1 _File                                 1 _File-Name                       P            0            185          0¦
¦    -2 _Field                                3 _Field-Name                        U          0            100          0¦
¦    -4 _Index-Field                          6 _Index/Number                    P U          0             68          0¦
¦    -3 _Index                                5 _File/Index                      P U          0             39          0¦
¦    -2 _Field                                2 _File/Field                      P U          0              0          0¦
¦    -2 _Field                                4 _Field-Position                    U          0              0          0¦
¦    -4 _Index-Field                          7 _Field                                        0              0          0¦
¦     1 Invoice                               8 Invoice-Num                      P U          0              0          0¦
¦     1 Invoice                               9 Cust-Num                                      0              0          0¦
¦     1 Invoice                              10 Invoice-Date                                  0              0          0¦
¦     1 Invoice                              11 Order-Num                                     0              0          0¦
¦     2 Customer                             12 Cust-Num                         P U          0              0          0¦
¦     2 Customer                             13 Comments                                      0              0          0¦
¦     2 Customer                             14 Country-Post                                  0              0          0¦
¦     2 Customer                             15 Name                                          0              0          0¦
¦     2 Customer                             16 Sales-Rep                                     0              0          0¦
¦     3 Item                                 17 Item-Num                         P U          0              0          0¦
¦     3 Item                                 18 Cat-Description                               0              0          0¦
¦     3 Item                                 19 Item-Name                                     0              0          0¦
¦     4 Order                                20 Order-Num                        P U          0              0          0¦
¦     4 Order                                21 Cust-Order                         U          0              0          0¦
¦     4 Order                                22 Order-Date                                    0              0          0¦
¦     4 Order                                23 Sales-Rep                                     0              0          0¦
¦     5 Order-Line                           24 order-line                       P U          0              0          0¦
¦     5 Order-Line                           25 item-num                                      0              0          0¦
¦     6 Salesrep                             26 Sales-Rep                        P U          0              0          0¦
¦     7 State                                27 State                            P U          0              0          0¦
¦     8 Local-Default                        28 default                          P            0              0          0¦
¦     9 Ref-Call                             29 Call-Num                         P U          0              0          0¦
¦     9 Ref-Call                             30 Cust-Num                           U          0              0          0¦
¦     9 Ref-Call                             31 Sibling                            U          0              0          0¦
¦     9 Ref-Call                             32 Txt                                           0              0          0¦

 

[Rob Fitzpatrick]

what type of statistics we can collect from table and index.

The most important use of these statistics is to look at your table and index reads. Often an application performs poorly because it does too many reads, due to unindexed or poorly-indexed queries. You can use code like the above to quickly pinpoint which of your objects are read most heavily.

As a DBA there is not much you can do about badly-written code, aside from bringing it to the attention of the developers. Sometimes, heavily-read tables and indexes are small and static, and they are good candidates for assignment to the alternate buffer pool.

If you want to look at some (much) fancier CRUD code, download and install ProTop, a powerful free and open-source OpenEdge database monitoring tool: ProTop Version 3.

 

[Cringer]

Paul Koufalis and Peter Judge are doing a session and a Workshop at the PUG Challenge in America on 4GL Code Performance. If you're interested in how to take the findings Rob has outlined and improve the code, or advise your developers how to improve the code, then this session will be invaluable.

 

[Jack@dba]

HI Rob,

Thanks for quick update.
i got some statistics for table and index one of our databases and parameters we are using.

 

[Jack@dba]

HI Rob,

Thanks for quick update.
i got some statistics for table and index from our databases and parameters.

For table statistics all CRUD are zero upto 50 tables only displayed.
For index statistics create fields contain 2 1 2 5 0 and reads values 42717,14773,7201 and 481...etc deletes as 2 0 0

Parameters values

Basetable 1 and 16
Table rangesize 50,250,120,150
Base index 8 285 107
Index range size 50 150 250 525 350

Using the about info what value we can suggest.any thoery to find next possible value.

Sorry I not able to upload the doc from my mobile.

Thanks...

 

[TomBascom]

It is mostly normal for the default of 50 to result in no visible CRUD data. In many applications those happen to be mostly unused tables.

If you want to see the CRUD data the proper values are as Rob indicated -- calculate the number of tables and indexes that you have and use that value.

find last dictdb._file no-lock where _file._tbl-type = "T" use-index _file-number no-error.
find last dictdb._index no-lock where not _index._index-name begins "_" use-index _index-number no-error.
display _file._file-number _index._idx-num.

There is no "one size fits all" and there is no point in looking at a small window of the data -- you either want to know or you don't.

 

[Rob Fitzpatrick]

Basetable 1 and 16
Table rangesize 50,250,120,150
Base index 8 285 107
Index range size 50 150 250 525 350

You've given us two different values for -basetable, four different values for -tablerangesize, three different values for -baseindex, and five different values for -indexrangesize. This makes no sense to me. Do you have two, three, four, or five databases? Or some other number?

Start simple. Don't specify any values for -basetable or -baseindex. Leave them at the defaults. The defaults are fine unless you're doing something more advanced.

Connect to each of your databases separately and run the three-line program above. It will give you the values you need for -tablerangesize and -indexrangesize in each database.

Then start your databases with those parameter values, run the application, and then collect CRUD information from the statistics VSTs.