SampleIO Subsystem

The SampleIO subsystem is useful for balancing the I/O load of a DBEnvironment.

The SampleIO screens display the amount of data buffer swapping activity for DBEFiles, tables, indexes, and referential constraints. For more information, refer to the section "Load Balancing" in the chapter "Guidelines on System Administration."

You should use the IO subsystem to tune the size of the data buffer pool before you use the SampleIO subsystem to balance load.




	NOTE: Use the SampleIO subsystem sparingly, because it significantly increases CPU usage.

Using the SET SAMPLING Command

The SET SAMPLING command enables or disables sampling of the data buffer pool. Sampling only occurs if SAMPLING is ON and you access a SampleIO screen. The counters on the SampleIO screens are set to 0 when you issue SET DBENVIRONMENT, and are then incremented as sampling occurs. The counters only reflect activity that was "seen" during sampling. The counters are cumulative; they represent the total activity observed over all samples taken since you issued SET DBENVIRONMENT.

By default, SQLMON does not display SampleIO screens during sampling. Instead, it displays a scale that allows you to determine the amount of sampling that has occurred. For example, if you issue the SET REFRESH 10 and SET CYCLE 5 commands and then invoke a SampleIO screen, SQLMON displays the following:

  SQLMONITOR SAMPLEIO => /sa

  SAMPLING = ON
  REFRESH = 10	   (One set of samples will be taken every 10 seconds).
  CYCLE = 5          (A total of 5 sets of samples will be taken, then you
  		      will automatically return to the SQLMONITOR prompt).

  ________10________20________30________40________50
  12345678901234567890123456789012345678901234567890

The command SET REFRESH 10 means that SQLMON will take one set of samples every 10 seconds, and SET CYCLE 5 means that SQLMON will take 5 sets of samples and then return to the prompt.

The scale includes the following elements:

The current value of the SAMPLING variable. When you see a refresh scale, SAMPLING is ON, because this scale is never printed if SAMPLING is OFF. To view the results of previous sampling, issue the command SET SAMPLING OFF and then invoke the SampleIO screen you want to see.
The current value of the REFRESH variable, which determines the number of seconds that SQLMON pauses between each refresh cycle.
The current value of the CYCLE variable, which determines the number of refresh cycles that occur before you return to the SQLMON prompt. If CYCLE is OFF, you must press Return to exit sampling and return to the prompt.
SQLMON displays a period each time a refresh cycle completes.

In the example above, SQLMON displays a period every 10 seconds, because REFRESH is set to 10. It displays 5 periods, because CYCLE is 5. SQLMON takes a total of 125 samples, which is 5 refresh cycles X 25 samples each.

After SQLMON has completed the sampling, issue the command SET SAMPLING OFF. If you are using SQLMON interactively, and if CYCLE has a value other than OFF, you should also issue SET CYCLE OFF. Then, you can invoke the SampleIO screens in which you are interested.

For example, the following screen lists the amount of I/O for each DBEFile.

PartsDBE0, which exists in the SYSTEM DBEFileSet and contains the system catalog, has the most read I/O activity. The only DBEFiles with write I/O activity are PurchDataF1 and PartsDBE0.

DBEFiles provide storage for objects that exist within a DBEFileSet. I/O occurs for a DBEFile whenever I/O occurs for the tables, indexes, or referential constraints within the DBEFileSet. You can use the SampleIO screen to determine which DBEFiles (and therefore which DBEFileSets) have the most I/O.

In the SampleIO screen above, we see that objects in the PurchFS DBEFileSet are showing slightly more I/O activity than objects in the WarehFS DBEFileSet. You can use the SampleIO Tables, SampleIO Indexes, and SampleIO TabIndex screens to obtain I/O information about the individual objects in these DBEFileSets.

In the following screen, we see that PurchDB.SupplyPrice is the table with the most activity in the PurchFS DBEFileSet, and is responsible for the write I/O activity that we saw on the SampleIO screen.

PurchDB.Parts shows the most activity in the WarehFS DBEFileSet. The next example shows that an index on PurchDB.Parts is also undergoing I/O.

Using the SET DISPLAYSAMPLES Command

If you want to display screens in the SampleIO subsystem during sampling, instead of seeing the refresh scale shown above, you can do so by issuing a SET DISPLAYSAMPLES ON command. When DISPLAYSAMPLES is ON, the screen is refreshed after each set of samples is obtained, that is, after each refresh cycle.

When DISPLAYSAMPLES is OFF, you see a refresh scale instead of the screen, and a period is displayed each time a refresh cycle completes. Because SQLMON does not display screen images, a smaller amount of output is created when sampling occurs, which might be especially desirable for batch jobs. In addition, SQLMON needs less CPU time, because some processing to sort and format the information displayed on the screen is avoided.

When SAMPLING is OFF, the SET DISPLAYSAMPLES command has no effect.

A Sample Batch Job

For example, you can use the following script within an SQLMON batch job to obtain SampleIO statistics for the day on a particular DBEnvironment.

  /set dbenv MyDBE
  /set menu off
  #
  ###############################################################################
  # SAMPLING is ON by default, so the following command is not really 
  # necessary.
  ###############################################################################
  /set sampling on
  #
  ###############################################################################
  # Do not generate screens while sampling. This reduces the output
  # generated by this job, and also reduces the amount of CPU SQLMON
  # consumes when it gathers statistics. DISPLAYSAMPLES is OFF by default,
  # so the following command is not really necessary.
  ###############################################################################
  /set displaysamples off
  #

  ###############################################################################
  # Take samples every 10 minutes for 8 hours:                                  #
  ###############################################################################
  # Take 1 set of 25 samples every 10 minutes (10 min x 60 sec/min = 600 sec)
  /set refresh 600
  #
  # Take 48 sets of samples (8 hours = 480 min x (1 cyc/10 min) = 48 cyc)
  /set cycle 48
  #
  ###############################################################################
  # Now perform the sampling. It does not matter which SampleIO screen is
  # used. The following command causes the SampleIO screen to be
  # visited 48 times (a 10-minute pause occurs between each visit).
  ###############################################################################
  /sampleio
  #
  ###############################################################################
  # Now print a report. When SAMPLING is OFF, each screen is painted
  # without taking additional samples of the data buffer pool. We simply
  # view the statistics that were obtained as a result of all of the    
  # previous sampling.
  # 
  # If you set the REFRESH variable to a large value for sampling, it's
  # good practice to reset it to a lower (normal) value when turning
  # sampling off. It's also good practice to reset the CYCLE variable.
  ###############################################################################
  /set sampling off
  /set refresh 10
  /set cycle 1
  ###############################################################################
  # The following commands cause tables and indexes from all DBEFileSets
  # to be included in the report. They will be printed in descending order
  # according to the total amount of I/O that was observed during sampling.
  ###############################################################################
  /set dbefileset off
  /set sortsamp 3
  /set output myfile
  /set
  /sa
  /sa tables
  /sa indexes
  /sa tabi owner.table1
  /sa tabi owner.table2
  /exit

Once you enter this script, you can use the HP-UX at command to execute the job at a particular time.

Understanding the Internals of Sampling

SampleIO statistics are generated entirely by SQLMON. In other words, the data is not obtained by simply reading from some existing table where these statistics are maintained. The more often you perform sampling, the more complete the I/O statistics become. However, SQLMON uses CPU time whenever it takes samples. The larger the number of samples SQLMON takes, the larger the amount of CPU time it consumes. Furthermore, SQLMON needs more CPU time to examine large data buffer pools than small data buffer pools.

When SAMPLING is ON, SQLMON takes 25 "snapshots" of the data buffer pool during each refresh cycle. SQLMON pauses between successive snapshots. The length of the pause is the refresh rate divided by 25.

As SQLMON takes each snapshot, it keeps track of the pages that are currently in the data buffer pool and the pages that were in the data buffer pool during the last snapshot. The SWAPIN, SWAPOUT, and TOTALIO counters are then incremented to reflect the changes.

The SWAPIN value represents read I/O. The SWAPIN value will be incremented by 1 if

A page is in the pool now, but it was not in the pool during the last snapshot.
A page was in the pool last time, but it was dirty then and it is clean now. The page was swapped out to disk and then swapped back in again.
A page was in the pool last time, but it was occupying a different buffer page location than it is now.

The SWAPOUT value represents write I/O. The SWAPOUT value will be incremented by 1 if

A page is in the pool now, but it was not in the pool during the last snapshot. The page is also dirty (if it is not dirty, the SWAPOUT value is not modified).
A page was in the pool last time, but it was clean then and is dirty now.

If the page was dirty during the last snapshot, and it is still dirty, the SWAPOUT column is not modified. When a dirty page stays in the buffer pool for a long time without being swapped out, ALLBASE/SQL is using the information well without paying a high I/O price.

The TOTAL value represents total I/O, and is obtained by adding the SWAPIN and SWAPOUT values.

SampleIO Subsystem

Technical documentation

» Table of Contents

» Index

Using the SET SAMPLING Command

Using the SET DISPLAYSAMPLES Command

A Sample Batch Job

Understanding the Internals of Sampling