Google Charts for DBA: Tablespaces Allocation

Pythian DBA’s have daily reports for each monitored database and some of the components are using charts to visualize the data. I’m a big fan of charts myself (when applied appropriately) and want to show how you can generate simple charts directly from the database. You’d be very surprised how easy it can be done from *any* database without installing any additional software or configuring something special.

This method is not limited to Oracle by any means — use it with MySQL, SQL Server or any other database as well as without a database — yes, visualize your sar data now!

In this example, we will plot a pie diagram with Oracle tablespaces. This would be very handy when you are starting to analyze the space allocation for a database. Here is the end result of the report for my Grid Control repository test database:

The secret weapon is Google Chart API — it produces nice charts by sending specially crafted URL’s.

Here is the PL/SQL block that generates required URL:
Updated 16-FEB-09 AEDT: fixed the query to group by tablespace_name. Thanks to Neil Kodner.
Updated 21-FEB-09 AEDT: fixed serverout. Thanks Mike.

SET define OFF
SET serverout ON
DECLARE
  t    VARCHAR2(30);
  u    NUMBER;
  chd  VARCHAR2(4000);
  chdl VARCHAR2(4000);
  chl  VARCHAR2(4000);
  CURSOR c
  IS
     SELECT tablespace_name tsname,
      ROUND(bytes / SUM(bytes) over () * 100, 2) pct,
      ROUND(bytes / 1024 / 1024) mb
       FROM
      (SELECT tablespace_name,
        SUM(bytes) bytes
         FROM dba_data_files
     GROUP BY tablespace_name
      )
 ORDER BY 3 DESC;
BEGIN
  FOR usage IN c
  LOOP
    IF chd IS NULL THEN
      chd  := usage.pct;
    ELSE
      chd := chd || ',' || usage.pct;
    END IF;
    IF chdl IS NULL THEN
      chdl  := usage.tsname;
    ELSE
      chdl := chdl || '|' || usage.tsname;
    END IF;
    IF chl IS NULL THEN
      chl  := usage.mb;
    ELSE
      chl := chl || '|' || usage.mb;
    END IF;
  END LOOP;
  dbms_output.put_line('https://chart.apis.google.com/chart?cht=p&chs=400x200'
    || '&chtt=Database Tablespaces (MB)'
    || '&chl=' || chl || '&chd=t:' || chd || '&chdl=' || chdl);
END;
/

In this example, we prepare 3 sets in the cursor for loop — chart data (percentage), chart labels (tablespace sizes in MB) and chart legend labels. At the end, the code prints out the URL including pre-generated sets as well as some other attributes like chart size and title. Google Chart API has all the glory details.

Here is the result:

SQL> @usage_chart
https://chart.apis.google.com/chart?cht=p&chs=400×200&chtt=Database Tablespaces (MB)&chl=1960|400|250|160|100|5&chd=t:68,14,9,6,3,0&chdl=MGMT_TABLESPACE|SYSTEM|UNDOTBS1|
SYSAUX|MGMT_ECM_DEPOT_TS|USERS

PL/SQL procedure successfully completed.

Now just copy this URL into your browser and you are done! Make sure you copy it as one line without breaks. If you want to take it further, you can produce the whole HTML pages as reports with <img> tags.

OK. I see you are rushing to try it out and generate your own charts… Good for you but don’t be selfish — share your helpful code fragments with the community! ;-)