Alex Rivera | Logout

PL/SQL Logging - How to control?

Asked 2009-08-05T09:55:40.430
22

I am looking to introduce a logging framework into our existing Oracle application to replace the use of DBMS_OUTPUT.

The framework will be used primarly to aid debugging and would detail such things as starting x procedure, details of parameters, ending procedure x etc. It should also have the functionality to be turned on for all or just one program unit, various levels of trace in fact what is pretty much standard logging functionality.

Implementing these requirements should be relatively straightforward, however where I would like your help is how best to turn this functionality off and on. What I am trying to achieve is the smallest possible performance hit when the trace is turned off. Which hopefully should be most of the time!

As the application is using 10g release 2, I initially I liked the look of wrapping the logging mechanism inside conditional compilation so that logging framework is not even visible during normal operation. Unfortunately I have had to grudgingly abandon this idea as most of the application is built using stand-a-lone procedures & functions so turning on a logging functionality could potentially invalidate a lot of code.

I have had look a several existing opensource and other's frameworks\functionality for inspiration:

log4plsql (http://log4plsql.sourceforge.net/)

APC's review here especially under acceptable impact gives me concerns.

OraLog project (http://oralog.sourceforge.net )

No updates since 2007

PL/VISION (her

oracle-database plsql oracle10g instrumentation
Edit
Report

1 Answer

3

In our application, we make heavy use of Ask Tom's debug.f instrumentation. One thing I quickly noticed was that the 'debugtab' was getting queried way too much to see if logging was on or not for every single log message. I hacked a change into it to only check the table once every 100 log messages and now it works pretty well.

My point is to try and avoid checking a table for each log message to see whether it should be output or not. Often you want to turn logging on in the middle of a long running process, so it's important you can do that. In my case, I decided I could live with waiting a few seconds until 100 logging calls had gone past before it actually noticed logging was turned on.

answered 2009-08-05T12:24:22.983

Your Answer