Using Progress Analytical Tools

1
Using Progress Analytical Tools
PTW-021

• Adam Backman
• White Star Software
• adam_at_wss.com

2
Outline

• List of available tools
• Using the tools
• Impact of running tools on the system
• Interpreting the data
• Taking action

3
List of Available Tools

• Progress monitor - promon
• Virtual System Tables - VSTs
• Database Analysis
• Chain analysis
• Index analysis

4
Using the Tools - promon

• Command line tool
• promon ltdbnamegt
• Pros
• Easy to use
• Nothing to write
• Low/no impact on the system
• Cons
• No ability to customize
• No provision to run in batch
• No provision to output/save data

5
Using the Tools - VSTs

• 4gl based tool
• Pros
• Very customizable
• Ability to collect exactly what you are looking
  for
• Low/no impact on the system
• Cons
• You need to write your own tool
• Determining the right VST is sometimes a challenge

6
Special Options for Table and Index Statistic VSTs

• All VSTs are on by default but table and index
  statistics only display the first 50 entries (by
  number not by volume)
• Use the following options on broker startup
• -tablebase - The first table number to monitor
• -tablerangesize - The number of tables to monitor
  
• -indexbase - The first index number to monitor
• -indexrangesize - The number of indexes to
  monitor

7
Using the Tools - DB analysis

• Command line tool
• proutil ltdbnamegt -C dbanalys
• Pros
• Only way to determine DB storage efficiency
• Low impact on the system
• Can be run while the system is up
• Cons
• No historical storage of information
• No flexibility of output

8
Impact of Running the Tools

• The information for both promon and VSTs is kept
  in memory anyway so the tools are just reading
  the data with little or no impact to the system
• DB analysis can have an effect on the system
  especially if you are tightly speced on disk
  throughput capacity

9
Interpreting the Data - promon

• What screens to use
• When to use them
• What does the data really mean
• How can I use it to take action

10
Using promon

• What screen to use
• Activity screen
• Block access
• When to run
• Beginning of the day
• Peak times
• End of the day

11
Example Activity Screen
12
Example Block Access
13
Using DB Analysis

• proutil ltdbnamegt -C dbanalys gt output_file
• Output contains multiple sections
• We only care about two sections
• Record block summary
• Index block summary

14
Interpreting the data - DB analysis

• Should I do a chain analysis, a index analysis or
  both
• How often should I run the utility
• What does the data really mean
• How can I use it to take action

15
Example Record Block Summary

• RECORD BLOCK SUMMARY FOR AREA "Archived Ratings"
  151
• Table Records Size Min Max
  Mean Count Factor Factor
• PUB.hsrad 68884747 4.0G 53 68
  62 68884747 1.0 2.0
• PUB.hsrat 353726306 54.1G 120 210 164
  353726306 1.0 1.2
• ------------------------------
  --------------------------------------------
• Subtotals 22611053 58.1G 53 210
  147 422611053 1.0 1.4
• RECORD BLOCK SUMMARY FOR AREA "Archived Comps"
  152
• Table Records Size Min Max Mean
  Count Factor Factor
• PUB.hscma 12675 549.7K 43 46 44
  12675 1.0 1.5
• PUB.hscmd 99680152 6.0G 56 99 64
  99680152 1.0 1.5
• PUB.hscmp 41122686 7.0G 98 301 183
  41122686 1.0 1.5
• PUB.hsgft 9217509 793.0M 67 104 90
  9217509 1.0 2.4
• -----------------------------
  ------------------------------------------
• Subtotals 50033022 13.8G 43 301 98
  150033022 1.0 1.6

16
Example Index Block Summary

• INDEX BLOCK SUMMARY FOR AREA "Archived Ratings"
  151
• --------------------------------------------------
  -----------------------------------
• Table Index
  Fields Levels Blocks Size Util Factor
• PUB.hsrad
• prradstat 18 3 3
  4248 21.6M 5.2 1.7
• prrat 19 4 3
  205141 1.4G 90.3 1.2
• PUB.hsrat
• audit 13 3 4
  397168 3.0G 99.9 1.0
• cmday 14 3 4
  421648 2.0G 62.3 1.8
• cmtbl 45 4 4
  1080387 4.2G 50.8 2.0
• kkcus 15 3 4
  842577 4.2G 66.1 1.7
• prrat 12 2 4
  441148 3.0G 89.0 1.2
• sltrn 16 3 4
  449431 2.9G 85.3 1.3
• sylic 17 4 4
  471460 3.2G 88.1 1.2

17
Interpreting the Data - VSTs

• Which VSTs to use
• When to use them
• What does the data really mean
• How can I use the data to take action

18
Which VSTs to Use

• _TableStat - Table usage
• _IndexStat - Index usage
• _ActSummary - Activity information
• _Transaction - Active transaction listing
• _AreaStatus - Storage area information
• _UserIO - User data request activity

19
_TableStat

• Displays read, update, create and delete
  information about each table
• Dont forget about -tablerangesize
• _TableStat-id _File._File-Num
• FOR EACH _TableStat
• by _TableStat-Read DESCENDING
• DISPLAY _TableStat.
• END.

20
_IndexStat

• Displays read, update, split and delete
  information about each index
• Dont forget about -indexrangesize
• _ IndexStat-id _Index._Idx-Num
• FOR EACH _IndexStat
• by _IndexStat-Read DESCENDING
• DISPLAY _IndexStat.
• END.

21
_ActSummary

• Akin to the activity screen from promon
• Need to do calculations to get differences
• Two free application (protop and VST dashboard)
  can show you how this is done.
• FIND FIRST _ActSummary.
• DISPLAY _ActSummary.

22
_Trans

• Good to find old transactions
• Could be used to alert the DBA if a transaction
  duration broke a threshold
• Long running transactions are generally not a
  good thing as they tend to grow the BI abnormally

23
_AreaStatus

• This allows you to know how full your areas is
  while the DB is up and running
• Record contains the total number and number of
  used (high water) blocks in an area
• If stored over time you could trend your storage
  and know when you will need to increase capacity
  in advance

24
_UserIO

• Total requests by the user
• Most writes should be done by the page writers
• No breakdown as to which tables are being
  accessed
• Good for initial track down of bad code

25
Taking Action

• Immediate Action
• Things that can be changed on the fly
• Planned action
• Wait for a customary outage and make changes
• Advanced action
• Trending items to plan for future growth

26
Immediate Action

• -spin
• Number of APWs
• Application code

27
Planned Action

• Increasing broker parameters
• Increasing capacity (Disk, memory CPU)
• Changing application code

28
Advanced Action

• Planning for growth
• Disk storage and throughput capacity
• CPU Utilization
• Memory utilization
• DB storage capacity
• Code efficiency

29
Conclusion

• Lots of information available
• The utilities included in OpenEdge for free are
  great for a snapshot of how things are now
• For utilities that keep track over time you will
  need an additional product like Fathom Management
  or you can write your own

30
Conclusion Continued

• Use promon to monitor during peak periods of
  activity or in isolation to determine program
  efficiency
• VSTs are the basis of Fathom Management and they
  can be used to determine the reliability and
  performance data about your database
• DB analysis should be run at least quarterly to
  determine the efficiency of the indexes
• DB Analysis can also be used to determine storage
  information for storage area split.

31
Questions