21

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.

Ian Carpenter
  • 8,346
  • 6
  • 50
  • 82
  • 1
    Ask Tom instrumention link is broken, can you please fix it ? – Sathyajith Bhat Aug 05 '09 at 21:02
  • Hi Sathya, thanks for pointing that out - should be ok now. – Ian Carpenter Aug 06 '09 at 07:09
  • Thanks, that presentation was interesting. – Sathyajith Bhat Aug 08 '09 at 01:25
  • Was APC's review removed? Where is a link to APC's review? – Kuberchaun Jan 23 '13 at 20:56
  • @JustBob No idea what happened but the link is back working now. In case of need the full link is: http://radiofreetooting.blogspot.co.uk/2005/06/oracle-logging-in-plsql.html – Ian Carpenter Jan 24 '13 at 19:16
  • Thanks. As Tom Kyte says the impact of logging is 0. It's like wearing a seat belt, the overhead is accepted for what you get back. Of course if you have time to write it faster go for it if it's truly needed. I will be looking and doing before and after testing and judging log4plsql once I have some numbers to go by. – Kuberchaun Jan 24 '13 at 20:25
  • 2
    Tom doesnt recommend his old instrumentation now http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:54174245781686 So he recommends https://github.com/tmuth/Logger---A-PL-SQL-Logging-Utility – Andrew Russell Apr 01 '14 at 02:39
  • 1
    @IanCarpenter Tyler Muth's logger has moved again to https://github.com/OraOpenSource/Logger – TrojanName Oct 12 '18 at 12:51
  • @TrojanName - Excellent work mate, I have updated the link. – Ian Carpenter Oct 19 '18 at 16:12

4 Answers4

8

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

dpbradley
  • 11,645
  • 31
  • 34
  • +1 Thanks for the reply - is there any chance of a small demo to illistrate your idea? I'm struggling to see how I can avoid the recompilation issue. Here is the procedure I have been working with (i have had to "flatten" it for space reasons: CREATE OR REPLACE PROCEDURE IANC_CC IS BEGIN $IF $$debug $THEN DBMS_OUTPUT.PUT_LINE('cc code is in place');$END DBMS_OUTPUT.PUT_LINE('normal execution');END; -- Turn the plsql flags on alter procedure ianc_cc compile plsql_ccflags = 'debug:true' reuse settings; Thanks Ian – Ian Carpenter Aug 05 '09 at 14:56
  • Thanks for taking the time to provide an example, I would have upvoted the response even further if I could have! Thanks again – Ian Carpenter Aug 06 '09 at 07:06
5

Reviewing the same problem, and found the following project which seems to be still be active, https://github.com/tmuth/Logger---A-PL-SQL-Logging-Utility

Patrick
  • 618
  • 2
  • 10
  • 20
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.

Stephen ODonnell
  • 4,441
  • 17
  • 19
  • +1 Thanks Stephen, especially for the feedback on some real world usage of the TK instrumentation as well as the tip on customising it. – Ian Carpenter Aug 05 '09 at 14:21
  • Hey @StephenODonnell you can hint the database to do function caching which can cache the result until the debugtab table invalidates the cache. – Andrew Russell Apr 01 '14 at 01:10
  • @AndrewRussell - that could be a pretty good idea, I didn't try that back when I used this tool (which was a while ago now), but I will give it a try next time. – Stephen ODonnell Apr 03 '14 at 19:53
1

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.

Rigved
  • 11
  • 1