Google Charts for DBA: Tablespaces Allocation

Feb 15, 2009 / By Alex Gorbachev

Tags: , , ,

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! ;-)

35 Responses to “Google Charts for DBA: Tablespaces Allocation”

  • Jornica says:

    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

  • Toby says:

    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.

  • Baron says:

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

  • Asif Momen says:

    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).

  • thierry says:

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

  • 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.

  • @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.

  • neil kodner says:

    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;

  • @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. :)

  • neil kodner says:

    did you run the query I posted? :D

  • 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”

  • …only without the smart quotes…

  • Tanel Poder says:

    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

  • Kim Njeru says:

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

  • @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. ;-)

  • @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)”.

  • Mike O'Regan says:

    -set server_out on

    +set serverout on

  • Andy Lester says:

    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.

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

  • @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.

  • Toby says:

    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.

  • hpavc says:

    @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.

  • John Dixon says:

    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;
    /
  • Thanks John. I modified your comment for formatting if you don’t mind.

  • Realizar gráficas con MySQL y Google Graph | dominios, diseño web, ecommerce - Mantis Technology Solutions Blog says:

    [...] 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. [...]

  • Andy Rivenes says:

    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.

  • [...] 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. [...]

  • 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

  • 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

  • Alex Gorbachev says:

    Thanks Google Chart Team! Keep up good work!

  • Hoang says:

    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
    
  • Alex Gorbachev says:

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

  • poratips says:

    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!

  • Alex Gorbachev says:

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

  • kartik says:

    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??

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>