Using Oracle DBMS_APPLICATION_INFO package for solution instrumentation

Hello Everyone,

Currently I am working on development of complex solution with Outsystems. Our platform configuration uses Java stack and Oracle DB.

The application several times had performance issues in high loaded periods during which DB locks has been occurred and also application server CPU utilization was high, generally application functionality experienced significant performance degradation. I tried to identify reason of the locks and performance degradation by analyzing logs and DB queries but could not find main reason. There were several missing entity indexes and bad formed queries but nothing special.

We have invited Oracle specialists to analyze this case and they suspect that reason should be in application logic, their recommendation was to use DBMS_APPLICATION_INFO package to set application details which will be useful for tracking application performance and activity for further analysis.

I tried to call set_module and set_action subprograms of DBMS_APPLICATION_INFO package using Advance SQL and only way to make this working was to enclose calls in BEGIN – END block like below:





The problem is that actual SQL statement also should be enclosed in this BEGIN – END block, otherwise executed SQLs will not mark under specified module and action. So SQLs of Aggregates and Advance SQL cannot be marked this why.

Another way to call these subprograms is to use EXECUTE statement like this: EXECUTE DBMS_APPLICATION_INFO.set_module, but in this case SQL error occurs: ORA-00900 invalid SQL statement. Please see attachment.

My question is how can DBMS packages (specially DBMS_APPLICATION_INFO package) be used from Outsystems? Also why EXECUTE statement finishes with this error, can it be used in Advanced SQL?

I will highly appreciate any recommendations or experience.