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