Storage Areas (a)

Storage Areas (a)

Displays detailed information on database storage areas.

Metric Description
# Area Number. The first 6 are reserved.
BX Shows whether cached blocks are found in the primary buffer pool (B1) or alternate buffer pool (B2)
Area Name Storage Area name.
Allocated Amount of disk space allocated to the storage area
Variable Amount of disk space allocated to the variable extent for the storage area
Tot GB Total gigabytes in use for the storage area.
Hi Water High water mark of disk space for the storage area
Free GB Gigabytes of storage available for use in the storage area
%Allo Disk space in use as a percentage of disk space allocated to the area.
%LastX Disk space used in the last extent as a percentage of the maximum amount of disk space allocated
BSZ Block size
RPB Records per block
CSZ Cluster size (blocks per cluster)
#Tbls Number of tables in the storage area
#Idxs Number of indices in the storage area
#LOBs Number of LOBs in the storage area
#Exts Number of data extents in the storage area
Var? Indicates whether or not there is a variable extent in the storage area
* Reserved for miscellaneous notes of interest
Max% Amount of disk space in use as a percentage of the maximum amount of disk space for the storage area
Bug% For OpenEdge versions PRIOR to v10.x, the number of rows in an area as a percentage of the maximum allowable rows (2 billion)

Insight into Storage Areas

  1. While it is possible to store data in area 6, the Schema area, you should not. It is a type 1 storage area and should be reserved for the schema tables.

  2. A storage area should only contain data of one type: table, index, or LOB. The image captured above is of the sports2000 database which does not follow this recommendation because it was created 15 years ago along with the first type 2 storage areas!

  3. Our recommendation is to have one matching index storage area for every table storage area. I.e. if you decide that tables Customer, Order, and Orderline belong in the storage area "Data Area One", then you should create an "Index Area One" containing the indexes of these tables.

  4. For 98% of databases, all variable data extents are sufficient. See the discussion below.

Do I Need Fixed Length Storage Areas?

The most common argument against variable-length storage areas is that there is a performance cost every time the area needs to grow. Technically, this is true. However, in the real world, the cost is less than negligible.

Don't believe us? How much does your database grow every month? In our experience, 1-2 GB of growth per month covers more than 98% of the databases out there. For our example, let's assume 2GB of growth per month on a system that is only running Monday-Friday, 9-5. That's 8 hours per day, approximately 21 days per month, or 168 hours per month. 2 GB of growth, divided by 168 hours = 12 MB per hour.

A 512 block per cluster storage area in an 8KB block size database will grow 4 MB, or one cluster (512 X 8192 bytes = 4 MB), per extension. This means that our theoretical database will grow 3 times per hour (12 MB of growth per hour, divided by 4 MB per expansion event). An ok SAN (not too fast, not too slow), can grow a file at approximately 10 MB/sec. Pull out your calculator, and you'll see that the "performance penalty" for our 2 GB / month database growth is 400ms, three times per hour.

There are cases when fixed-length data extents are preferred, but those situations are rare. Not sure? Ask the experts.

There could be an argument against variable length AI extents as these tend to grow quickly and continuously. However, our benchmark testing has shown that even for AI extents, the benefits of variable extents outweigh the minor performance cost. Or to put it another way, it is very rare that the business will notice the effect of variable length AI extents, even if the DBA can measure the degradation. Most OpenEdge OLTP applications are 98% read versus 2% write. A small degradation on a very small portion of database activity is by definition negligible.