Google Charts for DBA: Tablespaces Allocation

Posted in: MySQL, Technical Track

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('http://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
http://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! ;-)

email

Interested in working with Alex? Schedule a tech call.

About the Author

What does it take to be chief technology officer at a company of technology experts? Experience. Imagination. Passion. Alex Gorbachev has all three. He’s played a key role in taking the company global, having set up Pythian’s Asia Pacific operations. Today, the CTO office is an incubator of new services and technologies – a mini-startup inside Pythian. Most recently, Alex built a Big Data Engineering services team and established a Data Science practice. Highly sought after for his deep expertise and interest in emerging trends, Alex routinely speaks at industry events as a member of the OakTable.

35 Comments. Leave new

By using Oracle Application Express (APEX), you can skip the “copy the URL step” by integrating the PL/SQL block in an dynamic generated HTML page. For instructions see http://jornica.blogspot.com/2007/12/integrating-google-chart-api-in-apex.html.

With kind regards,

Jornica

Reply

Very clever trick.

I would add that this can be used directly in a CGI script, in particular you could issue a Redirect header to the generated URL.

Reply

That’s seriously a great idea. I am pondering how to add this to some of the Maatkit tools right now.

Reply

Alex,

Thanks for sharing this info. Its working like a charm.

But a small correction is required, the pl/sql block is anonymous and you need to convert it into stored procedure (usage_chart).

Reply

If you use Apex to show the chart, why not using the build in Chart functionalities of Apex: Flash (anychart), svg, …?

Reply

Thanks to all leaving the feedback. Keep ’em coming…

@Jornica: Being a production DBA, it’s practically impossible to add APEX to each *production* instance. I had few other ideas how to try to make it real-time and generate pages from the DB directly:
* I thought to try configuring Oracle Protocol Server (used for XDB) but I couldn’t figure out quickly how to call specific procedure. Perhaps, it can be returned as XML and XSLT-processed to get the right format but that’s above that simple example I needed.
* I thought to write a simple HTTP server in Perl using HTTP::Daemon and forward requests to the DB.
Any other ideas?

@Toby: integrating into CGI would be cool. Any idea how to make it *without* installing anything on the server? I keep thinking about light-weight Perl HTTP sever.

@Barob: Woohoo! Great idea.

@Asif: I didn’t want to create a PL/SQL stored object deliberately — some production environments prefer to avoid it. In my example, I created file usage_chart.sql and called it from SQL*Plus using ‘@’ directive. For procedure, I would use EXEC, so no corrections on this part.

Reply

@thierry: Again, APEX requires rolling out the whole environment. We can argue as much as we want that it’s dead easy. The reality is — APEX doesn’t get deployed to production environments to give DBA’s a tool to plot performance stats and etc.

Reply

Nice article! I’m a huge fan of the Google Charts API. This query works better if you have more than one datafile in any of your tablespaces. I think you’ve inspired me to create a stacked bar graph which will show allocated vs free space in each tablespace.

SELECT tablespace_name tsname
, SUM(bytes)/1024/1024 mb
, ROUND(SUM(bytes)/1024/1024 / SUM(SUM(bytes)/1024/1024) OVER () *100,2) pct
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY 3;

Reply

@neil: Oh no. Shame on me! :) I forgot it while playing with analytical functions. Thanks — fixed now.

However, it has to be a bit more complex as I couldn’t combine analytical functions with standard GROUP BY. I’m sure it’s jut my “deep” knowledge of analytical functions. :)

Reply

did you run the query I posted? :D

Reply
William Robertson
February 16, 2009 11:53 am

You could also just call the browser from the command line, e.g. in Windows with Firefox:

set define ~
set autoprint off

var chart_url varchar2(1000)

DECLARE
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;

:chart_url := ‘http://chart.apis.google.com/chart?cht=p&chs=400×200’ ||
‘&chtt=Database Tablespaces (MB)’ || ‘&chl=’ || chl || ‘&chd=t:’ || chd || ‘&chdl=’ || chdl;

dbms_output.put_line(:chart_url);

:chart_url := replace(:chart_url,’ ‘,’%20’);
END;
/

col chart_url new_value chart_url
select :chart_url from dual;

ho “C:\Program Files\Firefox\firefox.exe” “~chart_url”

Reply
William Robertson
February 16, 2009 11:58 am

…only without the smart quotes…

Reply

Hi Alex, nice trick.

In sqlplus you can do something like this to automate the trick further:

set define ^

host start ^url

set define &

(or on linux with firefox you could run host firefox ^url) instead

Reply

Hi Alex.
I like it a lot! This opens up a whole new other world.

Reply

@neil: Ah, I see the trick now. I even understand why. ;-)

@William & @Tanel: Good idea. Cheers.

@Kim: Very welcome. Share your new world with us. ;-)

Reply
William Robertson
February 19, 2009 10:13 am

@Tanel: I tried “start” first (rather than hardcoding an application path) but XP couldn’t seem to detect the type and just opened a blank command window.

btw small bug in my version – the “new_value” expression at the bottom needs to take the leading colon of “:chart_url” into account (or the column needs an alias).

@neil: The “Percent” expression could also be “ROUND(100 * RATIO_TO_REPORT(SUM(bytes/1048573)) OVER (),2)”.

Reply

-set server_out on

+set serverout on

Reply

I wouldn’t write your daemon with HTTP::Daemon directly. Use something like HTTP::Server::Simple which takes care of a lot of the drudgery.

Reply
Log Buffer #136: A Carnival of the Vanities for DBAs
February 20, 2009 12:00 pm

[…] Pythian’s Alex Gorbachev also got into the visualizations, in his item on Google Charts for the DBA. […]

Reply

@Mike: Hehe… how did I mange to screw this one up? :) thanks. fixed.

@Andy: Yep. You are right — I had a look at HTTP::Server::Simple and its CGI stuff — looks dead simple.

Reply

Re: CGI
One of my favourite hacks is calling the mysql client from a bash CGI. Doesn’t even need Perl.

e.g.

#!/bin/bash
echo Content-type: text/plain
echo
mysql -u foo -pbar db -B -e ‘SELECT name FROM emp’

A similar technique could emit the Redirect from your query.

Reply

@toby

ouch, yes its nice it produces html output. but static passwords readable my a webserver, that is likely readable by anyone else that has a cgi on that system isn’t something that should be a favoured practice.

Reply

Updated by Alex Gorbachev (formatting)

Hi Alex,
Great idea…this got me thinking about a stacked bar chart for tablespace usage, so I’ve modified your script a little.
An example of the result is:

Modified script is as follows. Thanks again.

SET define OFF
SET serverout ON
DECLARE
  t    VARCHAR2(30);
  u    NUMBER;
  chd  VARCHAR2(4000);
  chd2 VARCHAR2(4000);
  chxl1 VARCHAR2(4000);
  chxl2 VARCHAR2(4000);
  chxl VARCHAR2(4000);
  chl  VARCHAR2(4000);
  CURSOR c
  IS
 select ts.tablespace_name tsname,round((1-(free/total))*100) as used_pct, round((free/total*100)) as free_pct from
  (select tablespace_name,sum(bytes) as total from dba_data_files group by tablespace_name) ts,
  (select tablespace_name,sum(bytes) as free from dba_free_space group by tablespace_name) fs
 where ts.tablespace_name=fs.tablespace_name order by ts.tablespace_name;

BEGIN
  FOR usage IN c
  LOOP
    IF chd IS NULL THEN
      chd  := usage.used_pct;
    ELSE
      chd := chd || ',' || usage.used_pct;
    END IF;
    IF chd2 IS NULL THEN
      chd2  := usage.free_pct;
    ELSE
      chd2 := chd2 || ',' || usage.free_pct;
    END IF;

    IF chxl1 IS NULL THEN
      chxl1  := usage.tsname;
    ELSE
      chxl1 := chxl1 || '|' || usage.tsname;
    END IF;
    IF chxl2 IS NULL THEN
      chxl2  := usage.used_pct;
    ELSE
      chxl2 := usage.used_pct ||'|'||chxl2;
    END IF;
  END LOOP;
  chd := chd ||'|'|| chd2;
  chxl:='1:|'||chxl1||'|2:|'||chxl2;
  dbms_output.put_line('http://chart.apis.google.com/chart?cht=bhs&chs=400x200&chco=4D89F9,C6D9FD'
    || '&chtt=Tablespace Usage (%)'
    || '&chxt=x,y,r'
    || '&chl=' || chl || '&chd=t:' || chd || '&chxl=' || chxl);
END;
/
Reply

Thanks John. I modified your comment for formatting if you don’t mind.

Reply
Realizar gráficas con MySQL y Google Graph | dominios, diseño web, ecommerce - Mantis Technology Solutions Blog
February 24, 2009 11:07 pm

[…] Buen ejemplo para obtener la URL que nos dibuja gráficas usando Google Graph mediante procedimientos almacenados de MySQL. Está sacado de este ejemplo, que a su vez está sacado de este otro para Oracle. […]

Reply

Hi Alex, cool thread. There is a problem with display for lots of tablespaces. In the code the chart is set to 400×200 which works great for 6 tablespaces. It looks like the max Google supports is 300000 pixels, so the only thing to add is some restriction on the number of tablespaces charted per URL based on some useful criteria. Perhaps sorted by free space left for the top n tablespaces that will fit in the allocated chart size.

Reply
Gildus» Blog Archive » Realizar gráficas con MySQL y Google Graph
February 25, 2009 4:32 pm

[…] Buen ejemplo para obtener la URL que nos dibuja gráficas usando Google Graph mediante procedimientos almacenados de MySQL. Está sacado de este ejemplo, que a su vez está sacado de este otro para Oracle. […]

Reply

Hi Andy,

Thanks for the feedback. Glad you liked it.

It’s indeed a rather trivial example and I didn’t want to complicate the query. The way to do it is to display top 10 or top 20 tablespaces by size or so. The rest would be aggregated as “Others” but it would complicate the query and make it more difficult to understand.

Cheers,
Alex

Reply
Google Chart API Team
March 9, 2009 1:59 pm

This is great! We’ve added it to the list of related links in the documentation:

http://groups.google.com/group/google-chart-api/web/useful-links-to-api-libraries?hl=en

Thanks and keep up the cool work!

The Google Chart API Team

Reply
Alex Gorbachev
March 12, 2009 12:00 am

Thanks Google Chart Team! Keep up good work!

Reply

Made a minor change to John Dixon’s bar chart script to properly line up tbspace name and % used:

SET define OFF
SET serverout ON
set lines 500

DECLARE
  t    VARCHAR2(30);
  u    NUMBER;
  chd  VARCHAR2(4000);
  chd2 VARCHAR2(4000);
  chxl1 VARCHAR2(4000);
  chxl2 VARCHAR2(4000);
  chxl VARCHAR2(4000);
  chl  VARCHAR2(4000);
  dbname varchar(10) := '';
  CURSOR c
  IS
 select ts.tablespace_name tsname,round((1-(free/total))*100) as used_pct, round((free/total*100)) as free_pct from
  (select tablespace_name,sum(bytes) as total from dba_data_files group by tablespace_name) ts,
  (select tablespace_name,sum(bytes) as free from dba_free_space group by tablespace_name) fs
 where ts.tablespace_name=fs.tablespace_name order by ts.tablespace_name;
BEGIN
  select database_name into dbname from v$database;
  FOR usage IN c
  LOOP

    IF chd IS NULL THEN
      chd  := usage.used_pct;
    ELSE
      chd := chd || ',' || usage.used_pct;
    END IF;

    IF chd2 IS NULL THEN
      chd2  := usage.free_pct;
    ELSE
      chd2 := chd2 || ',' || usage.free_pct;
    END IF;

    IF chxl1 IS NULL THEN
      chxl1  := usage.tsname;
    ELSE
      chxl1 := usage.tsname|| '|' ||chxl1;-- || '|' || usage.tsname;
    END IF;

    IF chxl2 IS NULL THEN
      chxl2  := usage.used_pct;
    ELSE
      chxl2 := usage.used_pct ||'|'||chxl2;
    --  chxl2 := chxl2 ||'|'||usage.used_pct;
    END IF;
  END LOOP;
  chd := chd ||'|'|| chd2;
  chxl:='1:|'||chxl1||'|2:|'||chxl2;
  dbms_output.put_line('DATABASE: '||dbname);
  dbms_output.put_line('http://chart.apis.google.com/chart?cht=bhs&chs=400x600&chco=4D89F9,C6D9FD'
    || '&chtt=Tablespace_Usage_(%)_for_SID:_'||dbname
    || '&chxt=x,y,r'
    || '&chl=' || chl || '&chd=t:' || chd || '&chxl=' || chxl);

END;
/
quit
Reply
Alex Gorbachev
March 25, 2009 8:41 am

Thanks Hoang. I edited your post — added the code formatting.

Reply

Hi Alex,
Its a wonderful articles and ver very helpful for people woh works with DB.
I tried to copy your code but i am getting follwoing error even though i have SET serverout ON size 1000000.
Could you please email me the solution to how to correct this error?
I tried to google it but couldn’t fine nay useful information which i can use it.
I am running Oracle 9i R2.

My email is: [redacted]

Thanks in advance!

Reply

@poratips: Thanks for your feedback. did you forget to paste your error?

Reply

Hi Alex,

I tried this scirpt with one of our test isntance.. the proc gor excuted and URL also was displayed but whenI copied in the ie.. it says Bad Request .. i have cleared cahce and cookies as well.. any thoughts as to why this is happening??

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *