Skip to Main Content
  • Questions
  • Best approach for information logging in PL/SQL

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Andrzej.

Asked: July 24, 2020 - 2:56 pm UTC

Answered by: Connor McDonald - Last updated: August 05, 2020 - 3:10 am UTC

Category: PL/SQL - Version: 10g - 19c

Viewed 100+ times

You Asked

Hi Tom,

I'm wondering what is the best approach (in term of performance) for logging information about PL/SQL code execution and procedures/functions usage.
I know that the most common approach is to use PRAGMA AUTONOMOUS_TRANSACTION and insert any data into table or use audit log but I have many systems written in PL/SQL as backend whith API exposed as package procedures and functions. In many situations I need to gather some information about values in parameters passed to procedure.

P.S.
Generally in my opinion logging this kind of information on production environment isn't the best approach but unfortunately I'm not able to convince decision makers.


and we said...

You can "have your cake and eat it" :-)

For example, I wrote this a while back as a generic instrumentation facility for PLSQL

https://github.com/connormcd/instrumentation

which just logs details to a PL/SQL array for performance purposes, but you can toggle it to do dbms_output and/or log to a table, and you can do that from a different session if you want.

Similarly, check out the Logger

https://github.com/OraOpenSource/Logger

which is in common usage.

and you rated our response

  (2 ratings)

Reviews

What about external logging systems

July 28, 2020 - 8:13 am UTC

Reviewer: Andrzej from Warsaw

Yes, this are quite well known solutions but at the end even whet there are only select queries in procedure than Oracle has to create a transaction and usually write data through lgwr.
I wish there was asynchronous logging.

What about logging using audit through syslog to external system (over UDP) or logstach, fluentd to store this kind of information in dedicated logging systems like: Datadog, ELK, Prometheus.
Connor McDonald

Followup  

July 29, 2020 - 2:36 am UTC

You could do asynch logging by using a buffered queue - that would be a simple add-on to either mine or logger.

And once you use AQ, then the potential external subscribers is large.

August 04, 2020 - 7:54 am UTC

Reviewer: Andrzej

The idea about buffered queue sounds good. It is worth considering.
Thanks !
Connor McDonald

Followup  

August 05, 2020 - 3:10 am UTC

glad we could help

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.