Dienstag, 22. November 2011

PLSQL Profiler Runs => Adding a Gauge

What I like most about PLSQL Developer when it comes to profiler handling, is the representation of Share of total time as Gauge. Consider that max(run_total_time) is 100%, how much share in % does our actual run take? And how can I create that Gauge as part of an interactive Report?

I know that it must be possible somehow, because I have seen a Gauge on the "View Debug" screen!

It took me some time to find a way to implement the Gauge. One day I discovered a post showing how to add a Image to an Interactive report. They simple added HTML tags to the query. So I decided to give it a try.

First I have to adjust the Query to get the 100% value.

SELECT runid ,
  related_run ,
  run_owner ,
  run_date ,
  run_comment ,
  run_total_time ,
  run_comment1,
  max_run_total_time
FROM
  (SELECT r.runid ,
    r.related_run ,
    r.run_owner ,
    TO_TIMESTAMP(TO_CHAR(r.run_date, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY HH24:MI:SS') AS run_date ,
    r.run_comment ,
    r.run_total_time ,
    r.run_comment1,
    (SELECT MAX(run_total_time) FROM plsql_profiler_runs
    ) AS max_run_total_time
  FROM plsql_profiler_runs r
  )

Don't question the Query for now. I know that the performance will be bad, but we will have to change the query anyhow later.

I'm not an HTML expert, so I will have to copy the tag somewhere. I switch on firebug and investigate the elements of the "debug view".


 I use the context menu to copy the HTML code.

<div style="background: #cccccc; border-bottom: 1px solid #ffffff; border-left: 1px solid 
#aaaaaa; border-right: 1px solid #ffffff; border-top: 1px solid #aaaaaa; height: 14px; width: 100px;">
<img alt="33" border="0" height="14" src="/i/1px_trans.gif" style="background: #777777;" width="33" />
</div>

After some thoughts my query will look like this :)
SELECT runid ,
  related_run ,
  run_owner ,
  run_date ,
  run_comment ,
  run_total_time ,
  run_comment1,
  '<div style="width:100px;height:14px;background:#cccccc;border-top:1px solid #aaaaaa;border-left: 1px solid #aaaaaa;border-bottom:1px solid #ffffff;border-right:1px solid #ffffff;"><img src="/i/1px_trans.gif" width="'  
  || ROUND((run_total_time * 100) / max_run_total_time, 0)
  || '" alt="40" height="14" border="0" style="background:#777777;"></div>' AS graph
FROM
  (SELECT r.runid ,
    r.related_run ,
    r.run_owner ,
    TO_TIMESTAMP(TO_CHAR(r.run_date, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY HH24:MI:SS') AS run_date ,
    r.run_comment ,
    r.run_total_time ,
    r.run_comment1,
    (SELECT MAX(run_total_time) FROM plsql_profiler_runs
    ) AS max_run_total_time
  FROM plsql_profiler_runs r
  )
This will add a new column graph to my report. I have to adjust the Report Attributes of the column graph to make it work. So I change the Display type to "Standard Report Column" and switch of all "Allow User To" functions except "Hide" and "Sort".


I have to select the column graph on the interactive report to make it visible and voila, my page looks like this.


Source => Maintenance Dashboard 4.0

Keine Kommentare:

Kommentar veröffentlichen