Google Charts for DBA: Tablespaces Allocation
Feb 15, 2009 / By Alex Gorbachev
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! ;-)
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
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.
That’s seriously a great idea. I am pondering how to add this to some of the Maatkit tools right now.
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).
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.
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. :)
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 FilesFirefoxfirefox.exe” “~chart_url”
…only without the smart quotes…
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
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)”.
-set server_out on
+set serverout on
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.
Pingback: Log Buffer #136: A Carnival of the Vanities for DBAs
@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.
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.
@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.
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.
Pingback: Realizar gráficas con MySQL y Google Graph | dominios, diseño web, ecommerce - Mantis Technology Solutions Blog
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.
Pingback: Gildus» Blog Archive » Realizar gráficas con MySQL y Google Graph
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
Thanks Google Chart Team! Keep up good work!
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
Thanks Hoang. I edited your post — added the code formatting.
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!
@poratips: Thanks for your feedback. did you forget to paste your error?
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??
Pingback: A Faster MySQL Database Size Google Chart « JZ Talk Blogger