Data Management

Visualize your database with treemaps

This author demonstrates how to generate a simple treemap that uses shades between green, yellow, and red to show which tablespaces have low percentages of free space. Then, he offers a sample PHP script that could generate that graph.

In an Oracle database, DBAs often look for tablespaces that are about to run out of space and either add a new datafile or remove old data so applications don’t fail with space allocation errors. I thought it would be interesting to apply treemaps to this problem.

Most browsers can view the PNG image, but not everyone has a PHP server. However, it should be fairly easy to adapt the same code to other servers, such as a Java servlet, which has the ability to dynamically generate image files.

There are several possible algorithms available for generating treemaps. I chose the pivot algorithm, which tends to avoid a common problem in which very small data squeezes into a small area on the side.

In this example, I want to generate a treemap that contains a rectangle representing each one of my tablespaces. Each rectangle should be colored by various shades of green, yellow, and red to indicate how much free space is available in each tablespace. Green tablespaces have plenty of space; yellow tablespaces are about half full; red tablespaces are nearly full. We'll use various shades between each primary color. (This is easier for me to read than looking at a dump of tablespace sizes and percentages.)

The number of possible combinations of data and presentation is endless. Treemaps are good at displaying data for a large number of items. An extension to this example would be to break each tablespace rectangle into smaller rectangles for each datafile, which, in turn, could be broken into smaller rectangles each used or free extent. We could also write a similar program to show SGA allocation and usage statistics.

To generate the data for this chart, I need DBA_DATA_FILES, (which includes total space allocated) and DBA_FREE_SPACE. I can derive “free bytes” by subtracting "used bytes" from "total bytes" and avoid querying the free extent table. The USER_ and ALL_ versions of these views don’t give me a complete view of the data, but I didn’t want to give DBA privileges to users who want to get this data. So I took apart the definitions for DBA_DATA_FILES and DBA_FREE_SPACE, removed irrelevant tables, and combined them into a single view, which shows used space (with free space set to “0”) and free space (with used space set to “0”). This makes the query fast and, by granting this view to PUBLIC, any database user can access this data.

Listing A is my view definition for an Oracle database.

The PHP file is all that remains to generate the chart. It accepts the parameters' width and height to adjust the size of the constrained area. I allocated a color table for the image such that color 0 is the background color; color 1 is the text and border color; colors 2-128 are for shades between green and yellow; and colors 129-256 are for shades between yellow and red.

The function gendata fetches the data from the database into a local table. The function ImageCenterString draws some text (the name of the tablespace) in the center of a rectangle.

The function TreemapDrawGroup draws the treemap rectangle, fills the rectangle with the appropriate color, and writes the name in the rectangle.

The TreemapGroup function recursively divides the data into alternating vertical and horizontal splits with the size of each side based on the percentage of the total size of the tablespace. The data has been arranged in the array to take advantage of the “array_sum()” PHP function to add the two sides for the total size. Listing B contains the code.

Many server environments don’t have the ability to generate images directly, or don’t want to use server resources for generating images on the server side. We can put the work on client side by having the server generate only the metadata for the image in a well known format. An increasingly popular format is SVG (Scalable Vector Graphics), which is a robust XML format that describes graphics information and supports both JavaScript and CSS.

Listing C features the SVG generated off one of my test databases, which you can copy, save as a file with a .svg extension, and view in a graphics format, if you have an SVG viewer installed (such as Adobe SVG Viewer or QuickTime Viewer).

To generate the code above, I used code that's very similar to the code I used earlier in the column. It allocates a color table at package initialization that generates an internal color table for shades from green to yellow to red, generates SVG wrapper code (including a small stylesheet), pivots the data recursively, and generates the appropriate SVG code for each tablespace/rectangle.

Listing D shows the PL/SQL code to generate the SVG from a modplsql DAD (as treemap.svg).

Editor's Picks