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

Comments

Hi,
I use a text version of a similar script in SQL Developer that I found on the web. A graphical display would certainly make it easier to see those 'hot spots'. I tried copying and pasting the code in SQL Developer and then clicked the apply button to save it but it doesn't do anything. Using SQL Developer 3.1.07. Have you encountered this behavior?

Thanks,
Leighton

Yes I am afraid I have, sometimes creating a "PL/SQL DBMS_OUTPUT" report works the first time, sometimes it doesn't.

The workaround I have found is to create a simple User Defined Report based on a Query, something simple like "SELECT * FROM DUAL;", that always works. Save it, close it, test it. Then open it back up and you can change the report type from "Query" to "PL/SQL DBMS_OUTPUT", delete your query text and replace it with the PL/SQL you want to use for your report.

After that the demons seem to be gone and it will behave correctly from then on.

-Dave

Very well written. Awesome code.

Sir,

Can you please help providing the Archived Log Switch Heat map script.

The link to download the script is not working.

Thank you

Hi Rahul,

Download link is fixed now!

Add new comment