PL/SQL Logging - How to control?

Question

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 (here)

Looks quite old, no changes since Oracle 8i?

Ask Tom Instrumentation (here)

Update 01/04/2014 Tom Kyte now recommends Tyler Muth's Logger

I would be really interested to hear your experiences if you have introduced some form of logging into your Oracle application, how you implemented it and especially how you control it.

Answer 1

You mentioned discarding the idea of conditional compilation because of potential cascading invalidations - there is an approach that is somewhat similar if you're willing to touch the PL/SQL source where logging/tracing is needed that doesn't involve recompilation to enable.

You can still add a name/value pair of your own choosing to PLSQL_CCFLAGS and have your application code do a relatively lightweight query of v$parameter to determine if logging is "turned on". The crudest implementation would be one name/value pair, but you could extend this to have different pairs that would be module-specific so logging could be turned on with a finer granularity.

[Edit] Here's a very simple example in response to your comment/request - you'll obviously want to be more sophisticated in parsing the PLSQL_CCFLAGS string in case it has other existing info, perhaps wrap into a function, etc.:

create or replace procedure ianc_cc
is
cc_flag_val varchar2(4000);
begin 
-- need direct select grant on v_$parameter for this...
select value into cc_flag_val 
  from v$parameter where name = 'plsql_ccflags';
if (cc_flag_val = 'custom_logging:true') then
  dbms_output.put_line('custom logging is on'); 
else  
  dbms_output.put_line('custom logging is off'); 
end if;
end;
/

Now, as a user privileged to issue ALTER SYSTEM:

ALTER SYSTEM set PLSQL_CCFLAGS='custom_logging:true';

and toggle back by:

ALTER SYSTEM set PLSQL_CCFLAGS='';