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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Andrzej.

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

Last updated: August 05, 2020 - 3:10 am UTC

Version: 10g - 19c

Viewed 1000+ 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 Connor 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.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What about external logging systems

Andrzej, July 28, 2020 - 8:13 am UTC

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
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.

Andrzej, August 04, 2020 - 7:54 am UTC

The idea about buffered queue sounds good. It is worth considering.
Thanks !
Connor McDonald
August 05, 2020 - 3:10 am UTC

glad we could help

More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library