编程-使用treemaps图形化表示数据库

在一个 Oracle 数据库中，DBA 经常要寻找快要用完的表空间，然后要么增加一个新的数据文件，要么删除旧的数据，从而保证应用程序不至于出于因空间错误而失败。我想将treemaps应用到这个问题上可能非常有趣。我发现到从一个数据库查询中产生一个图形图像在各种环境下有区别，因此我决定将这分两部分写这篇文章。

第一部分讨论使用 PHP 文件产生 PNG 图像；第二部分则分析使用modplsql从 PL/SQL 产生一个 SVG 格式的图像。大多数浏览器都支持 PNG 格式的图像文件，但是并不是每个人都安装了 PHP 服务器。然而将相同的代码应用到其它服务器上相当简单，比如说应用到 Java servlet上，Java servlet具有动态产生图像文件的能力。

有几种不同的算法可以用来产生treemap。我选择 pivot 算法，该算法能够避免一个常见的问题，即非常小的数据会挤到另外一个小区域内。

在这个例子中，我想要产生一个treemap，使用该treemap来包含一个矩形表示每一个表空间。每个矩形都用绿色、黄色和红色三种颜色不同的深浅度标出，并用此法显示在每个表空间中还有多少自由空间。绿色表空间表示有很多自由空间，黄色表空间表示自由空间还有一半，红色表空间表示已经几乎没有自由空间。在每个基本颜色之间我们使用不同的深浅度。（相比查看一大堆表空间大小和百分比来说这样可读性更好。）

数据和表现之间可能的结合有无数种。Treemaps非常擅长显示有很多条目的数据。对这个例子进行扩展，可以将每个矩形再分成小矩形，分别用来表示每个数据文件，相应地还可以继续再分成更小的矩形，分别用来表示已用的或自由的区域（extent）。我们还要编写一个小程序来显示 SGA 分配和使用统计情况。

为了生成用于产生图表的数据，我需要 DBA_DATA_FILES（里面有已分配的总空间数）和DBA_FREE_SPACE。我可以通过拿“总字节数”减去“已用字节数”来计算“自由字节数”，从而避免查询自由 extent 表。这个视图的 USER_ 和 ALL_ 版本不能查看到所有的数据，但是我不想将 DBA 权限给那些想得到这些数据的人。所以我拆开了 DNA_DATA_FILES 和 DBA_FREE_SPACE，从中先删除了不相关的表，然后将它们合并到了一个单独的视图中，使用该视图显示已用的空间（自由空间为“0”）和自由空间（已用空间为“0”）。这样就提高了查询速度，而且通过将这个视图授权给 PUBLIC，任何数据库用户都可以访问其数据。

下面是我的视图定义，数据库版本为 Oracle 9.2.0.1.0。

create or replace view treemap_data_view

as

selectts.nametablespace_name,

       0 free_bytes,

       ts.blocksize * f.blocksused_bytes,

       ts.blocksize * f.blockstotal_bytes

  fromsys.file$ f, sys.ts$ ts

 where f.spare1 is null

   andf.ts# = ts.ts#

union all

selectts.name,

       0,

       decode(hc.ktfbhccval,0,ts.blocksize * hc.ktfbhcsz,null),

       decode(hc.ktfbhccval,0,ts.blocksize * hc.ktfbhcsz,null)

  fromsys.ts$ ts, sys.x$ktfbhchc

wherehc.ktfbhctsn = ts.ts#

union all

selectts.name,

       f.length * ts.blocksize,

       0,

       f.length * ts.blocksize

fromsys.ts$ ts, sys.fet$ f

wherets.ts# = f.ts#

  andts.bitmapped = 0

union all

selectts.name,

       f.ktfbfeblks * ts.blocksize,

       0,

       f.ktfbfeblks * ts.blocksize

fromsys.ts$ ts, sys.x$ktfbfe f

wherets.ts# = f.ktfbfetsn

  andts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0;

 

grant select on treemap_data_view to public;

剩下的工作就是用PHP 文件来产生图表。该文件接受宽度和长度参数来调整被约束的区域的大小。我为图像分配了一个颜色表，颜色0是背景色，颜色1是文本和边界颜色，颜色2-128是绿色和黄色之间的颜色，颜色129-256是黄色和红色之间的颜色。

函数gendate将数据从数据库装入到一个本地表中。函数ImageCenterString在矩形的中央画一些文本（表空间的名字）。

函数TreemapDrawGroup画treemap矩形，使用相应的颜色填充矩形，并在矩形上写名字。

TreemapGroup函数递归地将数据分成交互的垂直和水平分割，划分的依据是每一边的大小，即相对于表空间的大小的百分比。数据首先填入一个数组然后使用 PHP 函数“array_sum()”将两边相加得出总的大小。下面是 PHP 代码：

<?php

// send header

Header("Content-Type: image/png");

 

// look

$font = 4;

$fontwidth = ImageFontWidth($font);

$fontheight = ImageFontHeight($font);

// the query

$username = ‘scott‘;

$password = ‘tiger‘;

$sql = <<<SQL

selecttablespace_name,

       sum(free_bytes),

       sum(used_bytes),

       sum(total_bytes)

   fromtreemap_data_view

  group by tablespace_name

SQL;

 

//   where tablespace_name in (‘SYSTEM‘,‘EJALA‘,‘TOOLS‘)

// generate treemap data

functiongendata()

{

    global $username,$password,$sql;

    // arrange this way to take advantage of "array_sum"

    $tmdata = array

    (

        ‘name‘=>array(),

        ‘freebytes‘=>array(),

        ‘usedbytes‘=>array(),

        ‘totalbytes‘=>array()

    );

    $conn = ora_logon($username,$password);

    ora_commitoff($conn);

    $cursor = ora_open($conn);

    ora_parse($cursor,$sql);

    ora_exec($cursor);

    $i = 0;

    while (ora_fetch($cursor))

    {

        $tmdata[‘name‘][$i] = trim(ora_getcolumn($cursor,0));

        $tmdata[‘freebytes‘][$i] = ora_getcolumn($cursor,1);

        $tmdata[‘usedbytes‘][$i] = ora_getcolumn($cursor,2);

        $tmdata[‘totalbytes‘][$i] = ora_getcolumn($cursor,3);

        $i++;

    }

    ora_close($cursor);

    ora_logoff($conn);

    return $tmdata;

}

 

// draw text centered in a rectangle

functionImageCenterString(&$image,$x,$y,$wd,$ht,$string)

{

    global $font,$fontwidth,$fontheight;

    $tw = $fontwidth * strlen($string);

    $cx = ($tw > $wd) ?0 : ($wd - $tw) >> 1;

    $cy = ($fontheight > $ht) ?0 : ($ht - $fontheight) >> 1;

    ImageString($image,$font,$x+$cx,$y+$cy,$string,1);

}

 

functionTreemapDrawGroup(&$image,$x,$y,$wd,$ht,&$tmdata,$i)

{

    $pctfree = $tmdata[‘freebytes‘][$i]/$tmdata[‘totalbytes‘][$i];

    $color = round($pctfree * 254) + 2.0;

    ImageFilledRectangle($image,$x+1,$y+1,$x+$wd-1,$y+$ht-1,$color);

    ImageCenterString($image,$x,$y,$wd,$ht,$tmdata[‘name‘][$i]);

}

 

functionTreemapGroup($lvl,&$image,$x,$y,$wd,$ht,&$tmdata)

{

    $cnt = count($tmdata[‘name‘]);

    switch ($cnt)

    {

    case 1:

        // use entire area for rectangle

        TreemapDrawGroup($image,$x,$y,$wd,$ht,$tmdata,0);

        break;

    case 2:

        // draw split region

        $pct = $tmdata[‘totalbytes‘][0]

            / ($tmdata[‘totalbytes‘][0] + $tmdata[‘totalbytes‘][1]);

        if ($lvl % 2)

        {

            // odd = vertical split

            $ht1 = $ht * $pct;

            TreemapDrawGroup($image,$x,$y,$wd,$ht1,$tmdata,0);

            ImageLine($image,$x,$y+$ht1,$x+$wd,$y+$ht1,1);

            TreemapDrawGroup($image,$x,$y+$ht1,$wd,$ht-$ht1,$tmdata,1);

        } else {

            /// even = horizontal split

            $wd1 = $wd * $pct;

            TreemapDrawGroup($image,$x,$y,$wd1,$ht,$tmdata,0);

            ImageLine($image,$x+$wd1,$y,$x+$wd1,$y+$ht,1);

            TreemapDrawGroup($image,$x+$wd1,$y,$wd-$wd1,$ht,$tmdata,1);

        }

        break;

    default:

        // recursively pivot slices

        $s = $cnt >> 1;     // determine split point

        $tm1 = array

        (

            ‘name‘ => array_slice($tmdata[‘name‘],0,$s),

            ‘freebytes‘ => array_slice($tmdata[‘freebytes‘],0,$s),

            ‘usedbytes‘ => array_slice($tmdata[‘usedbytes‘],0,$s),

            ‘totalbytes‘ => array_slice($tmdata[‘totalbytes‘],0,$s)

        );

        $tm2 = array

        (

            ‘name‘ => array_slice($tmdata[‘name‘],$s),

            ‘freebytes‘ => array_slice($tmdata[‘freebytes‘],$s),

            ‘usedbytes‘ => array_slice($tmdata[‘usedbytes‘],$s),

            ‘totalbytes‘ => array_slice($tmdata[‘totalbytes‘],$s)

        );

        $tm1sum = (float)array_sum($tm1[‘totalbytes‘]);

        $tm2sum = (float)array_sum($tm2[‘totalbytes‘]);

        $pct = $tm1sum / ($tm1sum + $tm2sum);

        if ($lvl % 2)

        {

            // odd = vertical split

            $ht1 = $ht * $pct;

            TreemapGroup($lvl+1,$image,$x,$y,$wd,$ht1,$tm1);

            ImageLine($image,$x,$y+$ht1,$x+$wd,$y+$ht1,1);

            TreemapGroup($lvl+1,$image,$x,$y+$ht1,$wd,$ht-$ht1,$tm2);

        } else {

            // even = horizontal split

            $wd1 = $wd * $pct;

            TreemapGroup($lvl+1,$image,$x,$y,$wd1,$ht,$tm1);

            ImageLine($image,$x+$wd1,$y,$x+$wd1,$y+$ht,1);

            TreemapGroup($lvl+1,$image,$x+$wd1,$y,$wd-$wd1,$ht,$tm2);

        }

        break;

    }

}

 

// create image and colors

$wd = ($_REQUEST["width"]) ? $_REQUEST["width"] : 640;

$ht = ($_REQUEST["height"]) ? $_REQUEST["height"] : 480;

$im = ImageCreate($wd,$ht);

// allocate colors

ImageColorAllocate($im,255,255,255);    // background = white

ImageColorAllocate($im,0,0,0);          // text = black

// allocates 254 shades from green -> yello -> red

for ($i=0;$i<126;$i++) { ImageColorAllocate($im,$i*2,255,0); }

for ($i=0;$i<126;$i++) { ImageColorAllocate($im,255,255-($i*2),0); }

 

$tmdata = gendata();

TreemapGroup(0,$im,0,0,$wd,$ht,$tmdata);

// ImageCenterString($im,0,0,$wd,$ht,"count = ".count($tmdata[‘name‘]));

 

// draw border at edges of image

ImageLine($im,0,0,0,$ht-1,1);

ImageLine($im,0,$ht-1,$wd-1,$ht-1,1);

ImageLine($im,$wd-1,$ht-1,$wd-1,0,1);

ImageLine($im,$wd-1,0,0,0,1);

 

// generate image to output

ImagePNG($im);

ImageDestroy($im);

?>

---

本文作者：Scott Stephens已经在Oracle工作了13年有余，他曾经在技术支持、电子商务、市场和软件开发等部门工作。

---