Re: making graphical reports from oracle metrics
Date: Mon, 1 Dec 2008 14:29:11 -0600 (CST)
I have a simple query that reports user tables sorted descending by size. I had been dumping this into MS Exxhell every Monday, then going through the gyrations of creating the graph to print out and hang on my wall for passers by (especially management) to see. The problems with this approach are that it's time-consuming and the MS Exxhell graph generation is difficult for me to create consistently (fonts, size, layout, etc). This made for a less-than-professional look as I showed folks the graph history.
What I ended up doing is using Quest's Toad to create a saved FastReport for my query. Now when I run the query via Toad, I can very easily generate a consistent bar graph of just my top 20 tables with an additional bitbucket column to sum the sizes of the next 30 tables without modifying the SQL -- it's literally two mouse clicks. Additionally, I could schedule this to run automagically every Monday at 6:30 AM to be waiting for me when I get in. Unfortunately, the portability of my laptop prevents me from using this feature, but it might work well in your environment.
As I've been using commercial Toad since it's inception, I'm not as familiar with the free version of Toad, but it might be worth a look for you if the (wonderful) commercial version isn't viable. Also, a FastReports FAQ for Toad can be found here:
Of course, I have the huge disclaimer that I'm a Toad bigot and have been for over 10 years. :)
HTH! GL! Rich
> I take no credit for this. I read this at Howard Rodgers site
> www.dizwell.com a long time ago. I've tested it and had better success
> with 64-bit distros. For some reason, my lines got wrapped before I
> wanted them to with the 32-bit distros and XML is picky. If you have
> 10g (and presumably 11g), this should work to produce an XML and then
> you can open it with Excel. I know this won't produce the graphics but
> might provide an easier way to get it into Excel.
> set long 100000
> set pagesize 400
> select dbms_xmlgen.getxml('select * from emp') from dual;