PL/SQL Logging - How to control?

Answer 1

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.

Answer 2

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='';

Answer 3

Wouldn't it be easier to setup a context and add a name value pair to it? You can change the value in the context using a trigger on your debugtab table.