Archived Log Switch Heat Map for SQL Developer...

... or just use SQLPLUS to spool to HTML and open with a browser if you are old school :)

I am on week 4 of my newly reimaged work laptop sans Toad. I am trying to make a go of things with SQL Developer only. So far so good. I have a more comprehensive post on my transition in the works but in the meantime I have been filling in some gaps with User Defined Reports. I started collecting them here: https://github.com/dmann99/SQLDevUDRepPack

I remember Toad had an Archived Log Map screen where you could look for periods where higher than normal numbers of Archived Logs were generated. I seem to remember it had a way to color code so you could see outliers easily.

I cobbled together a PL/SQL script that can be run on SQLPLUS or SQL Developer to show the last 31 days of Log Switches for your database. It uses the highest number of logs generated per hour as 'pure red' and everything below that is a gradient down to 'pure white' for 0 logs generated during that hour. I'm not necessarily saying red is bad... I will leave that judgement up to the DBA and folks that know the workload better... But this will give you a way to quickly see peaks and valleys of your log switches.

Screen shot (click to open in new window):

Source Code
The Source Code is available at this link. To set up in SQL Developer, create a User Defined Report of type "PL/SQL DBMS_OUTPUT". When UDRs of this kind are executed, SQL Developer captures the DBMS_OUTPUT output and renders it as HTML.

Implementation Ideas
I tagged this as v1 since I already have a bunch of ideas I would eventually like to incorporate including some daily totals/averages and ability to alter the time periods.

The code is a little busy because I wanted it totally self contained. It is constructed as an anonymous block that owns 2 private functions inline.

I did learn something new about SQL Developer though, it understands inline CSS tags. I didn't expect this but it worked so I went with it. Unfortunately I wasn't able to embed a nice tidy SCRIPT stylesheet at the top of the document. So maybe SQL Developer really supports 'SS' just not the 'C'ascading part. I will play around with it some more and see what I can get going.

Also if your PL/SQL DBMS_OUTPUT User Defined Report outputs more than 20,000 characters be sure to insert a DBMS_OUTPUT.ENABLE(100000) call at the top of your script to keep from bumping your head on the default output limit. I reached the limit quickly while repeating all the inline CSS to get the formatting I wanted.

-Dave